Oracle Streams学习二(清除流配置)

在完成streams部署之后，如果需要重新配置或舍弃配置，可以选择清除这些配置信息。

这个清除工作的步骤非常简单，只需要如下一条命令:
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

有朋友问过我这样一个问题：这个Remove过程在进行什么工作？为什么会非常缓慢？

要想了解这个过程也非常简单，只需要跟踪一下就可以了，全部过程可以参考如下步骤：

bash-3.00$ sqlplus "/ as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 31 14:53:32 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

 

然后找到这个跟踪文件，仔细研究解读一下就能了解这个操作的内部原理及步骤，以下摘录一些主要步骤。
捕获处理部分：

SELECT CAPTURE_NAME
FROM
DBA_CAPTURE

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 6 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=6 pr=0 pw=0 time=870 us)
1 NESTED LOOPS (cr=4 pr=0 pw=0 time=682 us)
1 TABLE ACCESS FULL STREAMS$_CAPTURE_PROCESS (cr=3 pr=0 pw=0 time=555 us)
1 INDEX UNIQUE SCAN LOGMNR_SESSION_PK (cr=1 pr=0 pw=0 time=113 us)(object id 5788)
1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=170 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=29 us)(object id 11)

********************************************************************************

UPDATE sys.streams$_capture_process SET status = :1, status_change_time =
SYSDATE, ruleset_owner = :2, ruleset_name = :3, flags = :4, start_scn = :5,
first_scn = :6, use_dblink = :7, negative_ruleset_owner = :8,
negative_ruleset_name = :9, version = :10, error_message = :11,
error_number = :12, capture_userid = :13, spare3 = :14
WHERE
capture# = :15

 

移除流规则定义：

begin dbms_streams_adm_utl.remove_streams_rule(NULL, NULL, :1, :2); end;

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.06 0.06 0 12 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.06 0 12 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

DELETE FROM SYS.STREAMS$_RULES R
WHERE
R.RULE_OWNER = NVL(:B4 , R.RULE_OWNER) AND R.RULE_NAME = NVL(:B3 ,
R.RULE_NAME) AND R.STREAMS_NAME = NVL(:B2 , R.STREAMS_NAME) AND
R.STREAMS_TYPE = NVL(:B1 , R.STREAMS_TYPE)

 

删除流规则信息：

DELETE FROM SYS.STREAMS$_MESSAGE_RULES R WHERE R.RULE_OWNER = NVL(:B4 , R.RULE_OWNER) AND R.RULE_NAME = NVL(:B3 , R.RULE_NAME) AND R.STREAMS_NAME = NVL(:B2 , R.STREAMS_NAME) AND R.STREAMS_TYPE = NVL(:B1 , R.STREAMS_TYPE)

当然还有大量对于LOGMINER的处理过程：

BEGIN DELETE FROM sys.aq$_replay_info t WHERE t.eventid = :1 AND t.agent.name
= :2 AND t.agent.address IS NULL AND t.agent.protocol =
sys.dbms_aqadm.LOGMINER_PROTOCOL; END;

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 27 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 27 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

select toid
from
coltype$ where obj#=:1 and intcol#=:2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 3 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID COLTYPE$ (cr=3 pr=0 pw=0 time=64 us)
1 INDEX UNIQUE SCAN I_COLTYPE2 (cr=2 pr=0 pw=0 time=37 us)(object id 170)

********************************************************************************

DELETE FROM SYS.AQ$_REPLAY_INFO T
WHERE
T.EVENTID = :B2 AND T.AGENT.NAME = :B1 AND T.AGENT.ADDRESS IS NULL AND
T.AGENT.PROTOCOL = :B3

 

总之这个清除过程相当复杂，所以类似的操作应当选择在数据库空闲时段来完成，以避免对数据库产生性能冲击，切记切记！