Oracle Session 현황 SQL

Oracle Session 현황 SQL

• 세션에 연결된 SQL

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks

• 테이블 스페이스 사용량

select se.username,
       sum(su.blocks*ts.block_size/1024/1024)mb_used
       from v$sort_usage su,
       v$session se,
       dba_tablespaces ts,
       v$process pr
where su.session_addr = se.saddr
and se.paddr = pr.addr
and su.tablespace = ts.tablespace_name
group by se.username
order by mb_used desc;

• session 현황

SELECT   S.sid || ',' || S.serial# sid_serial, 
         S.username,
         T.blocks * 8192 / 1024 / 1024 mb_used, 
         T.tablespace,
         Q.sql_fulltext, 
         q.sql_id
FROM v$sort_usage T
  join v$session S on T.session_addr = S.saddr
  left join v$sqlarea Q on T.sqladdr = Q.address
ORDER BY mb_used desc, sid_serial;

select 
   srt.tablespace, 
   srt.segfile#, 
   srt.segblk#, 
   srt.blocks, 
   a.sid, 
   a.serial#, 
   a.username, 
   a.osuser, 
   a.status 
from 
   v$session    a,
   v$sort_usage srt 
where 
   a.saddr = srt.session_addr   
order by 
   srt.tablespace, srt.segfile#, srt.segblk#, 
   srt.blocks