Below script find out which tablespaces don't have space in the database.
column Today NEW_VALUE mhoy noprint format a1 trunc column Time NEW_VALUE mhora noprint format a1 trunc column inst NEW_VALUE minst noprint format a1 trunc select upper(instance) inst from v$thread; set pagesize 53 set linesize 120 set feedback off ttitle left 'DB Report' - right mtoday skip 1 - left minst - right mtime skip 1 - center 'DATAFILES AND SIZES OF TABLESPACES' skip 3; btitle skip 2 center 'Page :' format 999 sql.pno; break on Tablespace on report compute sum of KBTotal on report compute sum of KBUsed on report compute sum of KBFree on report column Tablespace format a12 column File_name format a32 column status format a10 column KBTotal format 99,999,990 column KBUsed format 99,999,990 column KBFree format 99,999,990 column %Used format 990.99 column %Free format 990.99 column Extents format 990 column MaxExtentKB format 999,999 spool tbsp_free SELECT TO_CHAR(sysdate, 'DD/MM/YY') Today , TO_CHAR(sysdate, 'hh24:mi:ss') Time , df.tablespace_name "Tablespace" , df.file_name "File_name" , count(*) "Extents" , NVL(df.bytes,0)/1024 "KBTotal" , (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed" , SUM(NVL(fs.bytes,0))/1024 "KBFree" , (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0) /1024)) "%Used" , ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024) "%Free" , MAX(NVL(fs.bytes,0))/1024 "MaxExtentKB" FROM dba_data_files df , dba_free_space fs WHERE df.file_id= fs.file_id(+) GROUP BY df.tablespace_name , df.file_name , df.bytes ORDER BY df.tablespace_name / spool off ttitle off btitle off
No comments:
Post a Comment