Sunday, April 26, 2009

Get datafile information


SELECT FILE_NAME,
d.TABLESPACE_NAME,
d.BYTES datafile_size,
NVL(SUM(e.BYTES),0) bytes_used,
ROUND(NVL(SUM(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
d.BYTES - NVL(SUM(e.BYTES),0) bytes_free
FROM DBA_EXTENTS e, DBA_DATA_FILES d
WHERE d.FILE_ID = e.FILE_ID (+)
GROUP BY FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
ORDER BY d.TABLESPACE_NAME,d.FILE_ID

No comments:

Post a Comment