Streams散记之一-如何清除流配置

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

Streams散记之一-如何清除流配置

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2007/10/remove_streams_configuration.html

站内相关文章|Related Articles

streams流复制中如何初始化复制对象

streams流复制ORA-01403错误解决一则

Streams流复制的异常检测

Streams复制的ORA-01341错误解决

Global_name从何处来?

在完成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

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

-The End-

-----
这篇【Streams散记之一-如何清除流配置】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2007-10-31 16:42 | Comments (3) | Posted to Advanced | Edit |Pageviews:

相关文章	随机文章
streams流复制中如何初始化复制对象 streams流复制ORA-01403错误解决一则 Streams流复制的异常检测 Streams复制的ORA-01341错误解决 Global_name从何处来?	Tools:OSW工具-Oracle的OS watcher 如何通过DB link进行远程过程或函数调用杭州景美阿里情深诺顿害人经典图书推荐系列-之六中国移动之心-谁与争锋

网上相关主题:

留言 (3)

这样可以删除配置，但是在配置stream时建立的queue不会去除。
sqlplus strmadmin/pw
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STREAMS_QUEUE_TABLE TABLE
AQ$_STREAMS_QUEUE_TABLE_S TABLE
AQ$_STREAMS_QUEUE_TABLE_T TABLE
AQ$STREAMS_QUEUE_TABLE_S VIEW
AQ$_STREAMS_QUEUE_TABLE_H TABLE
SYS_IOT_OVER_62941 TABLE
AQ$_STREAMS_QUEUE_TABLE_G TABLE
AQ$_STREAMS_QUEUE_TABLE_I TABLE
AQ$_STREAMS_QUEUE_TABLE_C TABLE
AQ$_STREAMS_QUEUE_TABLE_F VIEW
AQ$STREAMS_QUEUE_TABLE VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AQ$STREAMS_QUEUE_TABLE_R VIEW

12 rows selected.
我一般的做法是在去除上面的配置后，再把queue去掉：
exec dbms_streams_adm.remove_queue(queue_name => 'streams_queue',cascade => true,drop_unused_queue_table => true);

Posted by: Andy at November 11, 2007 7:59 PM

是有很多东西清理不掉，需要手工处理。

Posted by: eygle at November 12, 2007 9:27 AM

Oracle这样处理是对的，Queue是不属于Stream的配置范畴，
也许还有其它东东要用到这些Queue。
再说了，重启后直接drop user cascade流管理用户岂不更好？

Posted by: Peifei at July 4, 2008 9:10 AM

www.eygle.com

Streams散记之一-如何清除流配置

留言 (3)

发表留言: