오라클 정렬장소인 템프 이상 관련 - Oracle Tempspace

 TEMP TABLESPACE 정보조회하기 

-- 실제 TEMPFILE SIZE 조회;

select tablespace_name, sum(bytes)/1024/1024 mb 

           from dba_temp_files

           group by tablespace_name;

 

-- "high water mark" 조회 (= max used at one time) RAC,OPS에서 NODE마다 다르게 보임;  

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; 

-- 프리용량 조회;

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(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Used (M)", 

 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %" 

 FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) 

 AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 

 UNION ALL 

 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'

 order by 2

 ;