0 积分	0 好友	11 主题

发消息

[SQL调优] 11.2.0.3单实例无法创建物化视图

1^#

发表于 2012-12-4 15:27:40 | 查看: 4525| 回复: 4

我同时在相同配置的两台机器上创建了同样的物化视图，另一台是可以的，在这台主机的时候报错。

请帮忙查一下原因。
DB info:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建物化视图脚本如下：

create materialized view mv_TD_S_SEND_TPL  refresh fast  on demand
next sysdate+1/1440
as
select SMS_CODE,
   SEND_TABLE,
   PRIORITY_LEVEL,
   SMS_CONTENT,
   NOTE,
   CHAN_ID,
   SMS_KIND_CODE,
   SMS_TYPE_CODE,
   FORCE_START_TIME,
   FORCE_END_TIME,
   SMS_NET_TAG,
   EXT1,
   EXT2
from td_s_send_tpl@lk_cswwjk;

报错：
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20000: Trigger xdb_installation_trigger does not support object creation of type SNAPSHOT
ORA-06512: 在 line 36

10046事件出来后看到有如下抛出错误的一段代码：

DECLARE
   sql_text varchar2(200);
   val number;
BEGIN
   if (dictionary_obj_owner != 'XDB') then
      if (dictionary_obj_type = 'FUNCTION' or
         dictionary_obj_type = 'INDEX' or
         dictionary_obj_type = 'PACKAGE' or
         dictionary_obj_type = 'PACKAGE BODY' or
         dictionary_obj_type = 'PROCEDURE' or
         dictionary_obj_type = 'SYNONYM' or
         dictionary_obj_type = 'TABLE' or
         dictionary_obj_type = 'TABLESPACE' or
         dictionary_obj_type = 'TYPE' or
         dictionary_obj_type = 'VIEW' or
         dictionary_obj_type = 'USER' or
         dictionary_obj_type = 'TYPE BODY' or
         dictionary_obj_type = 'BODY' or
         dictionary_obj_type = 'TRIGGER' or
         dictionary_obj_type = 'SEQUENCE'
      )then
      if (dictionary_obj_type  != 'PACKAGE BODY'
         ) then
         sql_text := 'select count(*) from ALL_OBJECTS where owner = :1 and object_name = :2 and object_type = :3';
         execute immediate sql_text into val using dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type;
         if (val = 0) then
            sql_text := 'select count(*) from dropped_xdb_instll_tab where owner = :1 and object_name = :2 and object_type = :3';
            execute immediate sql_text into val using dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type;
            if (val = 0) then
               insert into xdb_installation_tab values
               (dictionary_obj_owner, dictionary_obj_name, dictionary_obj_type);
            end if;
         end if;
      end if;
      else
      raise_application_error(-20000, 'Trigger xdb_installation_trigger does not support object creation of type '||dictionary_obj_type);
      end if;
   end if;
end;
END OF STMT

分享0

收藏0 回复只看该作者道具举报

dla001

2^#

发表于 2012-12-4 15:37:23

把这个trigger禁用了。或是你改这个trigger的代码。

回复只看该作者道具举报

dla001

3^#

发表于 2012-12-4 15:42:43

Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
While trying to create any objects - you get the error related to trigger XDB_INSTALLATION_TRIGGER.
For example -
SQL> create materialized view test_mv as select * from test;
create materialized view test_mv as select * from test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Trigger xdb_installation_trigger does not support object creation of type SNAPSHOT
ORA-06512: at line 36
or
SQL> create sequence test_seq;
create sequence test_seq
ERROR at line 1:
ORA-604: error occurred at recursive SQL level 1
ORA-20000: Trigger xdb_installation_trigger does not support object creation of type SEQUENCE
ORA-6512: at line 32
Cause
XDB install must have failed at some point as triggers XDB_INSTALLATION_TRIGGER and DROPPED_XDB_INSTLL_TRIGGER should have been dropped at the end of the successful XDB install.
Solution
Verify the status of XDB in Database, i.e. -
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects owned by XDB
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';
If XDB is valid in DBA_REGISTRY and there are no invalid objects related to XDB then you can drop the xdb_installation_trigger as below and re-try your operation.
SQL> CONN /AS SYSDBA
SQL> drop trigger sys.xdb_installation_trigger;

复制代码

点评回复只看该作者道具举报

ccboceb7 发表于 2012-12-4 19:14: 谢谢 dla001。

ccboceb7

4^#

发表于 2012-12-4 19:14:15

好了，谢谢 dla001，
顺便我也贴个id:
Getting ORA-20000: Trigger XDB_INSTALLATION_TRIGGER does not Support Object Creation of Type <Some Object Type> [ID 1362488.1]

回复只看该作者道具举报

Liu Maclean(刘相兵

5^#

发表于 2012-12-4 23:02:43

GOOD! :)

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册