Friday 12 June 2015

How to check tablespace with sysdate - Oracle database

select substr(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-
(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
sysdate
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;

No comments:

Post a Comment