« 装修日记-瓷砖我选欧神诺 | Blog首页 | Global_name从何处来? »

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)

By eygle on 2007-10-31 16:42 | Comments (3) | Advanced | 1652 |

LogStreams