SELECT F.FILE_ID, F.FILE_NAME, ROUND(F.BYTES/1024/1024) "Allocated(MB)", NVL(TO_CHAR(ROUND(F.BYTES/1024/1024 - SUM(E.BYTES/1024/1024),1)),'File full') "Used (MB)", NVL(TO_CHAR(ROUND(SUM(E.BYTES/1024/1024),1)), '0') "Free (MB)", LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Autoextend?", LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)" FROM DBA_DATA_FILES F LEFT OUTER JOIN DBA_FREE_SPACE E ON E.FILE_ID = F.FILE_ID GROUP BY F.FILE_ID,F.FILE_NAME,F.BYTES,AUTOEXTENSIBLE,MAXBYTES ORDER BY 1;
For a specific tablespace:
alter session set nls_language='american'; SET LINES 400 COL "File ID" format 999 COL "Tablespace name" format a20 COL "File name" format a50 COL "Available space (MB)" format 9999999999 COL "Free space (MB)" format a20 COL "Percentage used" format a20 COL "Max size (MB)" format a20 COL "Can file grow?" format a20 WITH free_spc AS ( SELECT FILE_ID, SUM(BYTES/1024/1024) AS MBFree FROM DBA_FREE_SPACE fre WHERE TABLESPACE_NAME = UPPER('&&tablespace') GROUP BY FILE_ID ) SELECT F.FILE_ID "File ID", F.TABLESPACE_NAME "Tablespace name", F.FILE_NAME "File name", ROUND(F.USER_BYTES/1024/1024) "Available space (MB)", NVL( TO_CHAR(ROUND(FS.MBFree)), 'NADA') "Free space (MB)", LPAD(NVL(ROUND(( ROUND(F.BYTES/1024/1024 - FS.MBFree)/ROUND(F.BYTES/1024/1024) ) * 100),100),15,' ') || ' %' "Percentage used", LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Can file grow?", LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)" FROM DBA_DATA_FILES F LEFT JOIN free_spc FS ON F.FILE_ID = FS.FILE_ID WHERE F.TABLESPACE_NAME = UPPER('&&tablespace') ORDER BY F.FILE_ID ASC;
No comments:
Post a Comment