利用DBMS_STREAMS_ADM包的REMOVE_STREAMS_CONFIGURATION过程清理流环境时,碰到了ORA-24042错误。
Oracle10203版本,执行下面清除过程报错:
SQL> CONN STRMADMIN/STRMADMIN@192.25.1.101/YANGTK.COMPUTE
已连接。
SQL> SELECT COUNT(*) FROM ALL_CAPTURE;
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM ALL_PROPAGATION;
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM ALL_APPLY;
COUNT(*)
----------
0
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION
BEGIN DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION; END;
*
第 1 行出现错误:
ORA-24042: 不存在 QUEUE "STRMADMIN"."STREAMS_QUEUE" 及 DESTINATION
STREAM1.COMPUTE 的传播日程
ORA-06512: 在 "SYS.DBMS_STREAMS_ADM", line 1846
ORA-06512: 在 line 1
显然错误和传播进程有关,检查执行过程中的流环境:
SQL> SELECT COUNT(*) FROM ALL_CAPTURE;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM ALL_PROPAGATION;
COUNT(*)
----------
1
捕获进程被清除了,但是传播过程并没有被清除。
查询metalink,发现是Oracle的bug,在10204以前的版本,如果部署了QUEUE TO QUEUE的传播方式,则利用REMOVE_STREAMS_CONFIGURATION过程清理流环境就会报错。在metalink上对应的错误号为Bug 4627457,文档ID 4627457.8详细描述了这个问题。
在11g和10204中FIX了这个bug,如果碰到这个错误,只能通过手工清除的方法来解决。
SQL> SELECT PROPAGATION_NAME FROM ALL_PROPAGATION;
PROPAGATION_NAME
------------------------------
TABLE_SINGLE_STREAM
SQL> EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION('TABLE_SINGLE_STREAM', TRUE)
PL/SQL 过程已成功完成。
SQL> SELECT PROPAGATION_NAME FROM ALL_PROPAGATION;
未选定行
下面还需要清除对应的RULE和RULESET:
SQL> SELECT RULE_OWNER, RULE_NAME, RULE_CONDITION
2 FROM DBA_RULES
3 WHERE RULE_OWNER = 'STRMADMIN';
RULE_OWNER RULE_NAME RULE_CONDITION
---------- ----------- -----------------------------------------------------------------------------
STRMADMIN T_STREAM18 (((:dml.get_object_owner() = 'YANGTK' and :dml.get_object_name() = 'T_STREAM'
STRMADMIN T_STREAM19 (((:ddl.get_object_owner() = 'YANGTK' and :ddl.get_object_name() = 'T_STREAM'
SQL> SELECT RULE_SET_OWNER, RULE_SET_NAME, RULE_OWNER, RULE_NAME
2 FROM DBA_RULE_SET_RULES
3 WHERE RULE_NAME IN ('T_STREAM18', 'T_STREAM19');
RULE_SET_OWNER RULE_SET_NAME RULE_OWNER RULE_NAME
------------------------------ ------------------------------ ---------- -----------
STRMADMIN RULESET$_20 STRMADMIN T_STREAM19
STRMADMIN RULESET$_20 STRMADMIN T_STREAM18
SQL> SELECT RULE_SET_OWNER, RULE_SET_NAME
2 FROM DBA_RULE_SETS
3 WHERE RULE_SET_NAME = 'RULESET$_20';
RULE_SET_OWNER RULE_SET_NAME
------------------------------ ------------------------------
STRMADMIN RULESET$_20
SQL> EXEC DBMS_RULE_ADM.DROP_RULE_SET('RULESET$_20', TRUE)
PL/SQL 过程已成功完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-678615/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-678615/