▷ Oracle temp tablespaces (오라클 템프 테이블스페이스 확인)

[ Oracle temp tablespaces ]

SELECT d.tablespace_name tbs_name,
    d.status Status,
    d.CONTENTS Type,
    d.extent_management Ext_manage,
    NVL(a.BYTES / 1024 / 1024, 0) Total_mega,
    NVL(t.BYTES, 1)/1024/1024 ||' / '|| NVL(a.BYTES / 1024 / 1024, 1) Used_mega, -- 가장높게 사용된 용량
    NVL(t.BYTES / a.BYTES * 100, 1) Used_PCT,
    NVL(t.curnt_byte/1024/1024, 1) Cache_mega, -- 현재 사용중인 용량
    (NVL(t.curnt_byte/1024/1024, 1)/NVL(a.BYTES / 1024 / 1024, 0)*100) Cache_PCT
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_USED) curnt_byte,
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';
/