Oracle DBLINK로 들어온 세션 찾기

XXX DB로 부터 DB LINK를 통해서 들어온 세션 확인하기

해당 쿼리는 목적지에서 조회 해야 타고 들어온 세션을 확인 가능

SELECT s.SCHEMANAME,
       s.sid,
       s.serial#,
       p.spid,
       s.CLIENT_INFO,
       s.process source_spid,
      -- to_single_byte(substr(s.machine, 1, 15)) MACHINE,
       s.program,
       s.logon_time,
       s.EVENT,WAIT_CLASS,STATE,
       round(s.last_call_et/3600) 소요시간,
       ltrim(to_char(floor(LAST_CALL_ET/3600),'09'))||':'||ltrim(to_char(floor(mod(LAST_CALL_ET,3600)/60),'09'))||':'||ltrim(to_char(mod(LAST_CALL_ET,60),'09')) LAST_CALL_ET,
       s.status,
       s.MODULE,
       'kill -9 '||p.spid,
       'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''';',
       (select sql_text from v$sql where address = s.sql_address and rownum=1) sql
FROM   V$SESSION s,
       V$PROCESS p,
       (SELECT /*+ ORDERED */
   S.INDX sid,S.KSUSESER "serial#", S.KSUSEMNM MACHINE, S.KSUSEPID "PID",
       SUBSTR(G.K2GTITID_ORA, 1, 35) "GTXID",        
       SUBSTR(S.INDX, 1, 4)||'.'|| SUBSTR(S.KSUSESER, 1, 5) "LSESSION" ,
       SUBSTR(DECODE(BITAND(KSUSEIDL, 11), 1, 'ACTIVE', 0, DECODE(BITAND(KSUSEFLG, 4096), 0, 'INACTIVE', 'CACHED'), 2, 'SNIPED', 3, 'SNIPED', 'KILLED'), 1, 1) "Session status",EVENT
  FROM X$K2GTE G,
       X$KTCXB T,
       X$KSUSE S,
       V$SESSION_WAIT W
 WHERE G.K2GTDXCB =T.KTCXBXBA
   AND G.K2GTDSES=T.KTCXBSES
   AND S.ADDR=G.K2GTDSES
   AND W.SID=S.INDX
   and G.K2GTITID_ORA like 'XXX %')GTX
WHERE  s.paddr = p.addr
and s.sid=GTX.sid
and s.serial#=GTX."serial#"
AND    s.username IS NOT NULL
and    s.paddr = p.addr
order by SQL  ;