이 스크립트는 자신의 크기(100%)의 60%를 넘는 데이터파일이나 템프파일을 갖고 있는 테이블스페이스를 보여줍니다.
autoextend나 maxsize unlimited인 대용량 데이터베이스에 유용할듯 합니다.
그리고 지난 90일동안의 증가율도 제공합니다.
set linesize 132
define history_days=90
define threshold_pct=60
SELECT CONTENTS,tablespace_name,
CEIL(SUM(used)/POWER(1024,3)) USED_GB,
CEIL(SUM(total)/POWER(1024,3)) MAXSIZE_GB,
TO_CHAR(100*SUM(used)/SUM(total),'999')||' %' Pct_Used,
TO_CHAR(100*((SUM(nvl2(creation,used,0))))/SUM(total),'S99999')||'% in last &&history_days days' GROWTH
FROM (
SELECT what,tablespace_name,file_name,bytes_used used,
CASE WHEN autoextensible='NO' THEN bytes
WHEN NVL(maxbytes,0) < bytes THEN bytes
ELSE maxbytes
END total
FROM (
SELECT 'tempfile' what,file_id,tablespace_name,file_name,relative_fno,bytes,autoextensible,maxbytes,bytes_used
FROM V$temp_space_header s join DBA_TEMP_FILES f USING (file_id,tablespace_name,relative_fno)
WHERE status ='AVAILABLE'
UNION ALL
SELECT 'datafile' what,file_id,tablespace_name,file_name,relative_fno,f.bytes,autoextensible,maxbytes,
f.bytes-NVL(s.bytes,0) bytes_used
FROM DBA_DATA_FILES f left outer join (
SELECT file_id,tablespace_name,relative_fno,SUM(bytes) bytes
FROM DBA_FREE_SPACE GROUP BY file_id,tablespace_name,relative_fno
) s USING (file_id,tablespace_name,relative_fno)
WHERE status ='AVAILABLE'
) ) join DBA_TABLESPACES USING (tablespace_name)
left outer join (
SELECT name file_name , SYSDATE-creation_time creation FROM v$datafile WHERE creation_time > SYSDATE - &&history_days )
USING (file_name) HAVING SUM(used)/SUM(total) > ( &&threshold_pct / 100)
GROUP BY CONTENTS,tablespace_name ORDER BY 3 DESC;
<결과보기>
CONTENTS TABLESPACE_NAME USED_GB MAXSIZE_GB PCT_US GROWTH
--------- ------------------------------ ---------- ---------- ------ --------------------
TEMPORARY TEMP 1152 1152 100 % +0% in last 90 days
PERMANENT V_APPLI1_DATA_XL_01 506 640 79 % +0% in last 90 days
PERMANENT V_APPLI2_DATA_XL_01 504 640 79 % +9% in last 90 days
PERMANENT V_APPLI3_DATA_XL_01 289 384 75 % +17% in last 90 days
PERMANENT V_INDX_M_01 252 320 79 % +2% in last 90 days
PERMANENT V_APPLI4_DATA_XL_01 234 384 61 % +1% in last 90 days
|
|
|
