[Oracle] Temp Tablespace 관리를 위한 쿼리

## TEMP 영역 사용 쿼리 조회 ##

SELECT /*+ ordered */ "SID",
             s.serial# "Serial",
             s.MODULE "Module",
             s.program "Program",
             u.TABLESPACE "TS 명", 
             u.CONTENTS "Cont.",
             u.blocks "Temp Blocks",
             ((u.blocks*v.value)/(1024*1024)) "Temp Size(MB)", 
             substr(q.sql_text,1,50) "SQL", substr(p.sql_text,1,50) "이전 SQL" 
FROM    v$sort_usage u,
             v$session s,
             v$sqltext q,
             v$sqltext p, 
             (SELECT value 
              FROM   v$parameter
              WHERE name = 'db_block_size') v 
WHERE  s.saddr = u.session_addr
AND      s.sql_hash_value = q.hash_value(+)
AND      q.piece = 0
AND      s.prev_hash_value = p.hash_value(+)
AND      p.piece = 0;

## TEMP TABLESPACE 사용현황 조회 ##

SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
       TO_CHAR(NVL(t.bytes, 0)/1024/1024, '99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Used (M)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM   sys.dba_tablespaces d,
       (select tablespace_name,
               sum(bytes) bytes
        from   dba_temp_files
        group by tablespace_name) a,
       (select tablespace_name,
               sum(bytes_cached) bytes
        from   v$temp_extent_pool
        group by tablespace_name) t
WHERE  d.tablespace_name = a.tablespace_name(+)
AND    d.tablespace_name = t.tablespace_name(+)
AND    d.extent_management like 'LOCAL'
AND    d.contents like 'TEMPORARY';

## HIGH WATER MARK 조회 ##

 select tablespace_name, sum(bytes_cached)/1024/1024 mb
           from v$temp_extent_pool
           group by tablespace_name;

 

## 실제 사용량 ##

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb 
           from gv$sort_segment ss, sys.ts$ ts  
           where ss.tablespace_name = ts.name 
           group by ss.tablespace_name;
 

## TEMP TABLESPACE 조회 ##
 

select tablespace_name, sum(bytes)/1024/1024 mb 
           from dba_temp_files
           group by tablespace_name;