[20170210]关于dblink.txt
--昨天朋友要求建立的数据库关闭全部dblink.实际上很简单设置open_links,open_links_per_instance为0就ok了.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter link
NAME TYPE VALUE
----------------------- ------- ------
open_links integer 4
open_links_per_instance integer 4
create public database link "loopback"
connect to scott
identified by "book"
using '127.0.0.1/BOOK';
--//这样单台机器就可以模式dblink测试.
SCOTT@book> select sysdate from dual@loopback;
SYSDATE
-------------------
2017-02-10 08:50:38
SCOTT@book> alter system set open_links=0 scope=spfile;
System altered.
SCOTT@book> alter system set open_links_per_instance=0 scope=spfile;
System altered.
2.重启测试:
SCOTT@book> select sysdate from dual@loopback;
select sysdate from dual@loopback
*
ERROR at line 1:
ORA-02020: too many database links in use
--这样全部dblink都失效.
3.顺便做一些相关总结:
--//首先还原.
SCOTT@book> alter system reset open_links_per_instance;
System altered.
SCOTT@book> alter system reset open_links;
System altered.
--//重启数据库.
--//A.注意一点使用dblink会产生事务,会导致scn提升为2个数据库最高的scn.链接 http://blog.itpub.net/267265/viewspace-2123710/
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
no rows selected
SCOTT@book> select sysdate from dual@loopback;
SYSDATE
-------------------
2017-02-10 08:56:30
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.2.18166
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 2 18166; 10 2 18166 0 0 0 0 ACTIVE 1 1 0A000200F6460000 00000000818BF480 2017-02-10 08:56:30
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 0 BLOCK 0;
SCOTT@book> alter session close database link loopback;
ERROR:
ORA-02080: database link is in use
--//必须提交或者rollback才能关闭dblink.
SCOTT@book> select * from v$dblink;
DB_LINK OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
LOOPBACK 83 YES YES UNKN 0 YES NO 1
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> select * from v$dblink;
DB_LINK OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- ---------------------
LOOPBACK 83 YES YES UNKN 0 NO NO 1
--//IN_TRANSACTION字段显示NO.
SCOTT@book> alter session close database link loopback;
Session altered.
SCOTT@book> select * from v$dblink;
no rows selected
--//还可以使用这个命令关闭exec DBMS_SESSION.CLOSE_DATABASE_LINK (<db link name>);必须先授权使用.
SYS@book> grant execute on DBMS_SESSION to scott;
Grant succeeded.
SCOTT@book> select sysdate from dual@loopback;
SYSDATE
-------------------
2017-02-10 09:03:33
SCOTT@book> commit ;
Commit complete.
SCOTT@book> exec DBMS_SESSION.CLOSE_DATABASE_LINK ('loopback');
PL/SQL procedure successfully completed.
SCOTT@book> select * from v$dblink;
no rows selected
//B.如果要像了解那些会话打开了dblink,可以在sys用户执行:
column GTXID format a30
column waiting format a40
column ORIGIN format a30
column username format a12
Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
g.K2GTITID_ORA "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
)
"S",
w.event "WAITING",
s2.client_info
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and s2.sid = w.sid;
--//在scott用户下执行:
SCOTT@book> select sysdate from dual@loopback;
SYSDATE
-------------------
2017-02-10 09:06:34
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
232 7 58424 21 4 alter system kill session '232,7' immediate;
--//sys用户执行:
SYS@book> @ &r/dblink
ORIGIN GTXID LSESSION USERNAME S WAITING CLIENT_INFO
------------------------------ ------------------------------ ------------------- ------------ -------- ---------------------------------------- --------------------
xxxxdg4-58423 BOOK.e6127bf4.2.29.1643 232.7 SCOTT INACTIVE SQL*Net message from client
