Friday, September 12, 2014

Query To find free space, used space in a tablespace individual datafile

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(+)
and df.tablespace_name='MVD_LARGE'
GROUP BY df.tablespace_name
        , df.file_name
        , df.bytes
ORDER BY df.tablespace_name

No comments:

Post a Comment