Check Tablespace Utilisation

Check Tablespace utilisation
COLUMN "Tablespace" FORMAT A30
COLUMN "Used MB" FORMAT 99,999,999
COLUMN "Free MB" FORMAT 99,999,999
COLUMN "Total MB" FORMAT 99,999,999

SELECT   FreeSpace.Tablespace_Name "Tablespace"                  ,
         ( DataFiles.TotalSpace - FreeSpace.FreeSpace ) "Used MB",
         FreeSpace.FreeSpace "Free MB"                           ,
         DataFiles.TotalSpace "Total MB"                         ,
         ROUND ( 100 * ( FreeSpace.FreeSpace / DataFiles.TotalSpace ) ) "Pct. Free",
       DataFiles.autoextensible
FROM
         ( SELECT  Tablespace_Name,
                  ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) TotalSpace,autoextensible
         FROM     DBA_DATA_FILES
         GROUP BY Tablespace_Name,autoextensible
         ) DataFiles              ,
         ( SELECT  Tablespace_Name,
                  ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) FreeSpace
         FROM     DBA_FREE_SPACE
         GROUP BY Tablespace_Name
         ) FreeSpace
WHERE    DataFiles.Tablespace_Name = FreeSpace.Tablespace_Name
--and DataFiles.Tablespace_Name like replace('%_MMGT_SERVICE%',' ')
and DataFiles.Tablespace_Name like replace('%&Tbs%',' ')
ORDER BY 5 DESC;

No comments:

Post a Comment