Tuesday, August 20, 2013

Oracle Query to see Used and Free TableSpace Size

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