Table Space in Oracle

Table Space in Oracle

Table Space can be find in the below way using SQL Developer – Right Click on the Connection Click on Manage Database         The same can be achieved using the below SQL query – Database link can be created using following command – SELECT   DF.TABLESPACE_NAME "TABLESPACE_NAME",   TOTALUSEDSPACE "USED",   (DF.TOTALSPACE - TU.TOTALUSEDSPACE) "FREE",   DF.TOTALSPACE "TOTAL",   ROUND(100 * ( (DF.TOTALSPACE - TU.TOTALUSEDSPACE)/ DF.TOTALSPACE)) "PCT_FREE" FROM ( SELECT            TABLESPACE_NAME,            ROUND(SUM(BYTES) / 1048576) TOTALSPACE   FROM  DBA_DATA_FILES   GROUP BY TABLESPACE_NAME ) DF, ( SELECT            ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME   FROM  DBA_SEGMENTS   GROUP BY TABLESPACE_NAME ) TU WHERE DF.TABLESPACE_NAME = TU.TABLESPACE_NAME; Space Consumend by Table Space consumed by a Table can find out in below way in SQL Developer – Go to View -> Report           Go to Data Dictionary Report -> Database Administration -> All Tables -> Storage -> Tables by Tablespace           Same can be achieved by below SQL Query— SELECT LOWER( OWNER ) AS OWNER ,LOWER(TABLE_NAME)  AS TABLE_NAME ,TABLESPACE_NAME ,NUM_ROWS ,BLOCKS*8/1024  AS SIZE_MB ,PCT_FREE ,COMPRESSION ,LOGGING FROM ALL_TABLES WHERE OWNER = :1 ORDER BY 1,2; Datafile Space in Oracle Space consumed by a Datafile can find out in below way in SQL Developer – Go to View -> Report Go to Data Dictionary Report -> Database Administration -> Storage -> Datafile      SQL statement to achieve the same – SELECT  SUBSTR(DF.TABLESPACE_NAME,1,20) "TABLESPACE_NAME",        SUBSTR(DF.FILE_NAME,1,80) "FILE_NAME",        ROUND(DF.BYTES/1024/1024,0) "SIZE (M)",     DECODE(E.USED_BYTES,NULL,0,ROUND(E.USED_BYTES/1024/1024,0)) "USED (M)",        DECODE(F.FREE_BYTES,NULL,0,ROUND(F.FREE_BYTES/1024/1024,0)) "FREE (M)",        DECODE(E.USED_BYTES,NULL,0,ROUND((E.USED_BYTES/DF.BYTES)*100,0)) "% USED" FROM DBA_DATA_FILES DF,    (SELECT FILE_ID,         SUM(BYTES) USED_BYTES     FROM DBA_EXTENTS     GROUP BY FILE_ID) E,    (SELECT MAX(BYTES) FREE_BYTES,         FILE_ID     FROM DBA_FREE_SPACE     GROUP BY FILE_ID) F WHERE E.FILE_ID (+) = DF.FILE_ID AND   DF.FILE_ID  = F.FILE_ID (+) ORDER BY DF.TABLESPACE_NAME,         DF.FILE_NAME; More on Tablespace – http://www.orafaq.com/wiki/Tablespace