ÀÌ ½ºÅ©¸³Æ®´Â ÀÚ½ÅÀÇ Å©±â(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
|
|
|