Dear friend,
Below query will reveal the "Used Space" and "Free Space" in all the Tablespaces present in your database:
SELECT DF.TABLESPACE_NAME "TABLESPACE",
DF.BYTES / (1024 * 1024) "SIZE (MB)",
SUM(FS.BYTES) / (1024 * 1024) "FREE (MB)",
NVL(ROUND(SUM(FS.BYTES) * 100 / DF.BYTES),1) "% FREE",
ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
FROM DBA_FREE_SPACE FS,
(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME,DF.BYTES;
To know the space used by a Table, Index or anything use below query:
SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'INDEX_NAME_OF_TABLE';
SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TABLE_NAME';
Tuesday, August 20, 2013
Oracle Query to see Used and Free TableSpace Size
Subscribe to:
Posts (Atom)