Tablespace Usage Script

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

--
-- TABLESPACE INFORMATION
--

SET LINESIZE 500 PAGESIZE 26 FEEDBACK OFF HEADING ON TIMING OFF ECHO OFF
SET UNDERLINE =

VARIABLE XXX CHAR(200)

COLUMN "TABLESPACE" FORMAT A16
COLUMN "INITIAL EXTENT(MB)" FORMAT 999,999
COLUMN "STATUS" FORMAT A7
COLUMN "NEXT EXTENT(MB)" FORMAT 999,999
COLUMN "MAX EXTENTS" FORMAT 99,999,999,999
COLUMN "PCT_INCREASE" FORMAT 999
COLUMN "USED SIZE(MB)" FORMAT 9,999,999,999
COLUMN "USED BLOCKS" FORMAT 999,999,999
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(MB)" FORMAT 9,999,999,999
COLUMN "FREE BLOCKS" FORMAT 999,999,999
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(MB)" FORMAT 99,999,999,999
COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999

BREAK ON "TABLESPACE"

DECLARE
CURSOR C1 IS
   SELECT 'PRINT DATE:'||TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||' ***** TABLESPACE INFORMATION FROM DATABASE :'||NAME||' INSTANCE:'||INSTANCE||' *****' XXX
   FROM V$DATABASE,V$THREAD
   WHERE ROWNUM=1;

BEGIN
OPEN C1;
FETCH C1 INTO :XXX;
CLOSE C1;
END;
/

SET PAGESIZE 1
PRINT XXX
SET PAGESIZE 24

TTITLE RIGHT FORMAT 999 'PAGE:' SQL.PNO

BTITLE CENTER '================================================================================================================================================'

SELECT SUBSTR(A.TABLESPACE_NAME,1,16) "TABLESPACE",
       MAX(A.CONTENTS) "TYPE",
       MAX(A.STATUS) "STATUS",
       MAX(A.INITIAL_EXTENT)/1024 "INITIAL EXTENT(KB)",
       MAX(A.NEXT_EXTENT)/1024 "NEXT EXTENT(KB)",
       MAX(A.MAX_EXTENTS) "MAX EXTENTS",
       MAX(A.PCT_INCREASE) "PCT_INCREASE",
       (SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(MB)",
       (SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS",
       TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
       ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
       SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS",
       TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
       SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(MB)",
       SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS"
FROM DBA_TABLESPACES A,
     DBA_DATA_FILES B,
     DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
  AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
ORDER BY 1;

TTITLE OFF
BTITLE OFF
SET FEEDBACK ON