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;
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