Oracle的文档上给出了流环境的配置过程，但是没有给出如何才能彻底清除流环境。

这篇文章介绍一下流环境中捕获环境的清除过程。

Oracle9i流环境清除（一）：http://yangtingkun.itpub.net/post/468/414098

Oracle9i流环境清除（二）：http://yangtingkun.itpub.net/post/468/414104

首先确保所有的CATPURE相关的JOB已经停止，而且APPLY和PROPAGATION部分已经根据上两篇的文章进行了清除。

检查一下系统中的捕获配置：

SQL> SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME FROM ALL_CAPTURE;

CAPTURE_NAME QUEUE_NAME RULE_SET_NAME
------------------------------ ------------------------------ ------------------------------
DATA_CAPTURE STREAMS_QUEUE_D1 RULESET$_167

1 row selected.

SQL> SELECT RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME FROM USER_RULE_SETS;

RULE_SET_NAME RULE_SET_EVAL_CONTEXT_OWNER RULE_SET_EVAL_CONTEXT_NAME
------------------------------ ------------------------------ ------------------------------
RULESET$_167 SYS STREAMS$_EVALUATION_CONTEXT
STREAMS_QUEUE_D1_R STRMADMIN AQ$_STREAMS_QUEUE_TABLE_D1_V

2 rows selected.

SQL> SELECT RULE_NAME FROM ALL_RULES;

RULE_NAME
------------------------------
CAT_STREAM_DATA178
CAT_REGION177
.
.
.
CAT_ZONE_DEALER180
CAT_WRAP179

44 rows selected.

SQL> SELECT STREAMS_NAME, RULE_NAME FROM SYS.STREAMS$_RULES;

STREAMS_NAME RULE_NAME
------------------------------ ------------------------------
DATA_CAPTURE CAT_DOSEAGE_FORM172
DATA_CAPTURE CAT_DRUG173
DATA_CAPTURE CAT_METRIC174
.
.
.
DATA_CAPTURE CAT_CATEGORY170
DATA_CAPTURE CAT_DISTRICT171

22 rows selected.

SQL> SELECT * FROM ALL_LOG_GROUPS;

OWNER LOG_GROUP_NAME TABLE_NAME ALWAYS
---------- ------------------------------ ------------------------------ ------
NDMAIN LOG_GROUP_CAT_AREA_QUA_DEF_PK CAT_AREA_QUALITY_DEFINE ALWAYS
NDMAIN LOG_GROUP_PLT_PLAT_PK PLT_PLAT ALWAYS
.
.
.
NDMAIN LOG_GROUP_USER_DISTRICT_PK USER_DISTRICT ALWAYS
NDMAIN LOG_GROUP_CAT_STR_DATA_PK CAT_STREAM_DATA ALWAYS

22 rows selected.

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

这些配置就是需要清除的。

首先还是清除流环境的CAPTURE规则，仍然是通过DBMS_STREAMS_ADM的REMOVE_RULE过程来进行清除：

SQL> EXEC DBMS_STREAMS_ADM.REMOVE_RULE('CAT_DRUG173', 'CAPTURE', 'DATA_CAPTURE')

PL/SQL procedure successfully completed.

全部清除后，检查视图：

SQL> SELECT * FROM SYS.STREAMS$_RULES;

no rows selected

下面清除CAPTURE过程：

SQL> EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE('DATA_CAPTURE')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ALL_CAPTURE;

no rows selected

去掉所有的RULE和RULESET：

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')

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:
Connected.
SQL> ALTER TABLE NDMAIN.PLT_PLAT DROP SUPPLEMENTAL LOG GROUP LOG_GROUP_PLT_PLAT_PK;

Table altered.

清除所有的LOG_GROUP后，利用DBMS_JOB删除所有和CAPTURE相关的JOB，流环境彻底的清除完成。

SQL> EXEC DBMS_JOB.REMOVE(924)

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

根据具体情况还可以选择清除掉源数据表或者STRMADMIN用户。