：

SQL> EXEC DBMS_RULE_ADM.DROP_RULE_SET('RULESET$_167', TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_RULE_ADM.DROP_RULE_SET('STREAMS_QUEUE_D1_R', TRUE)
BEGIN DBMS_RULE_ADM.DROP_RULE_SET('STREAMS_QUEUE_D1_R', TRUE); END;

*
ERROR at line 1:
ORA-24170:
ORA-06512: at "SYS.DBMS_RULE_ADM", line 129
ORA-06512: at line 1

SQL> ALTER SESSION SET EVENTS = '25475 TRACE NAME CONTEXT FOREVER, LEVEL 2';

Session altered.

SQL> EXEC DBMS_RULE_ADM.DROP_RULE_SET('STREAMS_QUEUE_D1_R', TRUE)

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EVENTS = '25475 TRACE NAME CONTEXT OFF';

Session altered.

SQL> SELECT * FROM ALL_RULE_SETS;

no rows selected

关于ORA-24170错误的详细描述和解释，可以参考：

检查是否存在需要清除的RULE：

SQL> SELECT 'EXEC DBMS_RULE_ADM.DROP_RULE(''' || RULE_NAME || ''')' FROM USER_RULES;

'EXECDBMS_RULE_ADM.DROP_RULE('''||RULE_NAME||''')'
--------------------------------------------------------------
EXEC DBMS_RULE_ADM.DROP_RULE('CAT_AREA_QUALITY_DEFINE143')
EXEC DBMS_RULE_ADM.DROP_RULE('CAT_BUYER146')
EXEC DBMS_RULE_ADM.DROP_RULE('CAT_BUYER_DISTRICT145')
.
.
.
EXEC DBMS_RULE_ADM.DROP_RULE('PLT_PLAT164')
EXEC DBMS_RULE_ADM.DROP_RULE('USER_DISTRICT165')

22 rows selected.

SQL> EXEC DBMS_RULE_ADM.DROP_RULE('CAT_AREA_QUALITY_DEFINE143')

PL/SQL procedure successfully completed.

使用上面的方法清除所有的RULE，检查后，关闭队列，删除队列并删除队列表：

SQL> SELECT NAME, QUEUE_TABLE FROM USER_QUEUES;

NAME QUEUE_TABLE
------------------------------ ------------------------------
STREAMS_QUEUE_D1 STREAMS_QUEUE_TABLE_D1
AQ$_STREAMS_QUEUE_TABLE_D1_E STREAMS_QUEUE_TABLE_D1

SQL> EXEC DBMS_AQADM.STOP_QUEUE('STREAMS_QUEUE_D1')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AQADM.STOP_QUEUE('AQ$_STREAMS_QUEUE_TABLE_D1_E')

www.ixdba.net

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AQADM.DROP_QUEUE('STREAMS_QUEUE_D1')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AQADM.DROP_QUEUE('AQ$_STREAMS_QUEUE_TABLE_D1_E')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AQADM.DROP_QUEUE_TABLE('STREAMS_QUEUE_TABLE_D1')

PL/SQL procedure successfully completed.

清掉序列表后，去掉所有的LOG_GROUP信息：

SQL> SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DROP SUPPLEMENTAL LOG GROUP ' || LOG_GROUP_NAME || ';'
2 FROM ALL_LOG_GROUPS;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPSUPPLEMENTALLOGGROUP'||LOG_GROUP_NAME||';'
------------------------------------------------------------------------------------------
ALTER TABLE NDMAIN.PLT_PLAT DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_PLT_PLAT_PK;
ALTER TABLE NDMAIN.PLT_ORG_PLAT DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_PLT_ORG_PLAT_PK;
ALTER TABLE NDMAIN.CAT_REGION DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_CAT_REGION_PK;
ALTER TABLE NDMAIN.CAT_DOSEAGE_FORM DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_CAT_DOSEAGE_FORM_PK;
.
.
.
ALTER TABLE NDMAIN.USER_DISTRICT DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_USER_DISTRICT_PK;
ALTER TABLE NDMAIN.CAT_STREAM_DATA DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_CAT_STR_DATA_PK;

22 rows selected.

SQL> CONN NDMAIN
Enter password: