TEMP 테이블스페이스의 Really Status 쿼리

set verify off
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 +                                                                               +
 +   FileName  : temp_tbs_chk.sql                                                +
 +   Purpose   : temp tablespace 상세 조회                                           +
 +   Genarated : 2014/01/16 by TECHDATA                                          +
 +   Modified  :                                                                 +
 +                                                                               +
 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

set pages 40
set line 132
col tbs_name format a15
col Used_mega format a15
col Used_PCT format 999.99
col Cache_PCT format 999.99

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';

clear columns
ttitle off