Checking session coming from dblink in oracle database
select
username,osuser,status,sid,serial#,machine,process,terminal,program
from v$session
where saddr in ( select k2gtdses from sys.x$k2gte );
select * from v$session where machine='remote_machine';
select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
),1,1
) "S",
substr(s2.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and s2.sid=s.indx;
prompt Enter filters(empty for any)...
accept _sid prompt "Sid : ";
accept _globalid prompt "Globalid mask : ";
accept _remote_db prompt "Coordinator DB_NAME mask: ";
col coordinator_db_name for a30 heading "Global coordinator";
--col remote_dbid_reversed for a10;
col trans_id for a16;
col direction for a11;
col globalid for a80;
col globalid_ora for a40 noprint;
col branchid for a80 noprint;
col branches noprint;
col refcount noprint;
col preparecount noprint;
col flags noprint;
col formatid noprint;
col state for a40;
col coupling for a15;
col username for a30;
col event for a35;
col wait_class for a10;
with v$xt_global_transaction as (
select
g.inst_id as inst_id
,g.k2gtdses as saddr
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\1') as coordinator_db_name
-- ,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\2') as remote_dbid_reversed
,to_number(hextoraw(reverse(regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\2'))),'XXXXXXXXXXXX') as coordinator_dbid
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\3') as trans_id
,nvl2(replace(g.k2gtibid,'0'),'FROM REMOTE','TO REMOTE') as direction
,g.k2gtitid_ext /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid
,g.k2gtitid_ora /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid_ora
,g.k2gtibid as branchid
,g.k2gtecnt as branches
,g.k2gterct as refcount
,g.k2gtdpct as preparecount
,g.k2gtifmt as formatid
, decode(bitand(g.k2gtdflg, 512) , 512 ,'[ORACLE COORDINATED]')
||decode(bitand(g.k2gtdflg,1024) ,1024 ,'[MULTINODE]')
||decode(bitand(g.k2gtdflg, 511)
,0 ,'ACTIVE'
,1 ,'COLLECTING'
,2 ,'FINALIZED'
,4 ,'FAILED'
,8 ,'RECOVERING'
,16 ,'UNASSOCIATED'
,32 ,'FORGOTTEN'
,64 ,'READY FOR RECOVERY'
,128 ,'NO-READONLY FAILED'
,256 ,'SIBLING INFO WRITTEN'
) as state
,g.k2gtdflg as flags
,DECODE(g.k2gtetyp
,0 ,'FREE'
,1 ,'LOOSELY COUPLED'
,2 ,'TIGHTLY COUPLED') as coupling
from x$k2gte2 g
,x$ktcxb t
,x$ksuse s
where g.k2gtdxcb = t.ktcxbxba
and g.k2gtdses = t.ktcxbses
and s.addr = g.k2gtdses
)
select
tr.inst_id
,s.sid
,s.serial#
,s.username
-- ,tr.saddr
,tr.coordinator_db_name
-- ,tr.remote_dbid_reversed
,tr.coordinator_dbid
,tr.trans_id
,tr.direction
,tr.globalid
,tr.globalid_ora
,s.event
,s.wait_class
,tr.branchid
,tr.branches
,tr.refcount
,tr.preparecount
,tr.formatid
,tr.state
,tr.flags
,tr.coupling
from v$xt_global_transaction tr
,v$session s
where tr.saddr=s.saddr
and ('&_sid' is null or s.sid='&_sid')
and ('&_globalid' is null or tr.globalid like '&_globalid')
and ('&_remote_db' is null or tr.coordinator_db_name like '&_remote_db')
/
col coordinator_db_name clear;
--col remote_dbid_reversed clear;
col trans_id clear;
col direction clear;
col globalid clear;
col globalid_ora clear;
col branchid clear;
col state clear;
col coupling clear;
col username clear;
col event clear;
col wait_class clear;
undef _sid ;
undef _globalid ;
undef _remote_db ;
################
SQL> alter system cancel sql '123,456';
SQL> alter system cancel sql '123,456,84djy3bnatbvq';
SQL> alter system cancel sql '123,456,@1,84djy3bnatbvq';
select
g.inst_id as inst_id
,g.k2gtdses as saddr
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\1') as remote_db
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\2') as remote_dbid_reversed
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\3') as trans_id
,nvl2(replace(g.k2gtibid,'0'),'FROM REMOTE','TO REMOTE') as direction
,g.k2gtitid_ext /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid
,g.k2gtitid_ora /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid_ora
,g.k2gtibid as branchid
,g.k2gtecnt as branches
,g.k2gterct as refcount
,g.k2gtdpct as preparecount
,g.k2gtifmt as formatid
, decode(bitand(g.k2gtdflg, 512) , 512 ,'[ORACLE COORDINATED]')
||decode(bitand(g.k2gtdflg,1024) ,1024 ,'[MULTINODE]')
||decode(bitand(g.k2gtdflg, 511)
,0 ,'ACTIVE'
,1 ,'COLLECTING'
,2 ,'FINALIZED'
,4 ,'FAILED'
,8 ,'RECOVERING'
,16 ,'UNASSOCIATED'
,32 ,'FORGOTTEN'
,64 ,'READY FOR RECOVERY'
,128 ,'NO-READONLY FAILED'
,256 ,'SIBLING INFO WRITTEN'
) as state
,g.k2gtdflg as flags
,DECODE(g.k2gtetyp
,0 ,'FREE'
,1 ,'LOOSELY COUPLED'
,2 ,'TIGHTLY COUPLED') as coupling
from x$k2gte2 g
,x$ktcxb t
,x$ksuse s
where g.k2gtdxcb = t.ktcxbxba
and g.k2gtdses = t.ktcxbses
and s.addr = g.k2gtdses
/
prompt "Enter filters: "
accept _sid prompt "Sid: ";
accept _globalid prompt "Globalid mask: ";
accept _remote_db prompt "Remote_db mask: ";
col remote_db for a20;
--col remote_dbid_reversed for a10;
col trans_id for a16;
col direction for a11;
col globalid for a80;
col globalid_ora for a40;
col branchid for a80;
col state for a40;
col coupling for a15;
col username for a30;
col event for a35;
col wait_class for a10;
with v$xt_global_transaction as (
select
g.inst_id as inst_id
,g.k2gtdses as saddr
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\1') as remote_db
-- ,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\2') as remote_dbid_reversed
,to_number(hextoraw(reverse(regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\2'))),'XXXXXXXXXXXX') as remote_dbid
,regexp_replace(g.k2gtitid_ora,'^(.*)\.(\w+)\.(\d+\.\d+\.\d+)$','\3') as trans_id
,nvl2(replace(g.k2gtibid,'0'),'FROM REMOTE','TO REMOTE') as direction
,g.k2gtitid_ext /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid
,g.k2gtitid_ora /* utl_raw.cast_to_varchar2(k2gtitid_ext) = g.k2gtitid_ora */ as globalid_ora
,g.k2gtibid as branchid
,g.k2gtecnt as branches
,g.k2gterct as refcount
,g.k2gtdpct as preparecount
,g.k2gtifmt as formatid
, decode(bitand(g.k2gtdflg, 512) , 512 ,'[ORACLE COORDINATED]')
||decode(bitand(g.k2gtdflg,1024) ,1024 ,'[MULTINODE]')
||decode(bitand(g.k2gtdflg, 511)
,0 ,'ACTIVE'
,1 ,'COLLECTING'
,2 ,'FINALIZED'
,4 ,'FAILED'
,8 ,'RECOVERING'
,16 ,'UNASSOCIATED'
,32 ,'FORGOTTEN'
,64 ,'READY FOR RECOVERY'
,128 ,'NO-READONLY FAILED'
,256 ,'SIBLING INFO WRITTEN'
) as state
,g.k2gtdflg as flags
,DECODE(g.k2gtetyp
,0 ,'FREE'
,1 ,'LOOSELY COUPLED'
,2 ,'TIGHTLY COUPLED') as coupling
from x$k2gte2 g
,x$ktcxb t
,x$ksuse s
where g.k2gtdxcb = t.ktcxbxba
and g.k2gtdses = t.ktcxbses
and s.addr = g.k2gtdses
)
select
tr.inst_id
-- ,tr.saddr
,tr.remote_db
-- ,tr.remote_dbid_reversed
,tr.remote_dbid
,tr.trans_id
,tr.direction
,tr.globalid
,tr.globalid_ora
,tr.branchid
,tr.branches
,tr.refcount
,tr.preparecount
,tr.formatid
,tr.state
,tr.flags
,tr.coupling
,s.sid
,s.serial#
,s.username
,s.event
,s.wait_class
,s.event
from v$xt_global_transaction tr
,v$session s
where tr.saddr=s.saddr
and ('&_sid' is null or s.sid='&_sid')
and ('&_globalid' is null or tr.globalid like '&_globalid')
and ('&_remote_db' is null or tr.remote_db like '&_remote_db')
/
col remote_db clear;
--col remote_dbid_reversed clear;
col trans_id clear;
col direction clear;
col globalid clear;
col globalid_ora clear;
col branchid clear;
col state clear;
col coupling clear;
col username clear;
col event clear;
col wait_class clear;
undef _sid ;
undef _globalid ;
undef _remote_db ;
Other Ways :
select userhost , statement_type as type , comment_text from dba_common_audit_trail where comment_text like 'DBLINK%';
SELECT username , sid , machine , program from gv$session where TYPE='USER' and program like 'oracle%' ;
tail -100f listener.log | grep 'PROGRAM=oracle'
select NETWORK_SERVICE_BANNER, CLIENT_DRIVER , CLIENT_LOBATTR , count(*) from v$session_connect_info where CLIENT_DRIVER not like 'jdbc%' group by NETWORK_SERVICE_BANNER, CLIENT_DRIVER , CLIENT_LOBATTR ;
Reference :
How to view open dblink connections (Doc ID 387848.1)
Comments
Post a Comment