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

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more