Oracle9i流環境清除（三）

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使用者。