Wednesday, June 11, 2014

Which Tablespaces Do Not Have Enough Free Space

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