TABLESPACE Usage Details

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

--
-- DETERMINES DEFAULT TABLESPACE DETAILS
--

SET LINESIZE 132
COLUMN PROPERTY_VALUE FORMAT A30
COLUMN DESCRIPTION FORMAT A40

SELECT *
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%TABLESPACE';

--
-- DETERMINES TABLESPACE USAGE DETAILS
--
vi tablespace_usage.sql

SET HEAD ON
SET VERIFY OFF
SET LINESIZE 132 PAGESIZE 60

PROMPT Type the TABLESPACE NAME you are looking for or Press RETURN key for all TABLESPACES
ACCEPT TABLESPACE_NAME PROMPT "TABLESPACE NAME: "

COL "TABLESPACE" FOR A30
COL "USED SPACE(MB)" FOR 99,999,999
COL "FREE SPACE(MB)" FOR 99,999,999
COL "TOTALSPACE(MB)" FOR 99,999,999

BREAK ON REPORT

COMPUTE SUM OF "TOTALSPACE(MB)" ON REPORT
COMPUTE SUM OF "USED SPACE(MB)" ON REPORT
COMPUTE SUM OF "FREE SPACE(MB)" ON REPORT

SELECT T.TABLESPACE, 
       T.TOTALSPACE AS "TOTALSPACE(MB)", 
       ROUND((T.TOTALSPACE-NVL(FS.FREESPACE,0)),2) AS "USED SPACE(MB)", 
       NVL(FS.FREESPACE,0) AS "FREE SPACE(MB)", 
       ROUND(((T.TOTALSPACE-NVL(FS.FREESPACE,0))/T.TOTALSPACE)*100,2) AS "%USED", 
       ROUND((NVL(FS.FREESPACE,0)/T.TOTALSPACE)*100,2) AS "% FREE" 
FROM 
       (SELECT ROUND(SUM(D.BYTES)/(1024*1024)) AS TOTALSPACE, 
               D.TABLESPACE_NAME TABLESPACE 
        FROM DBA_DATA_FILES D GROUP BY D.TABLESPACE_NAME) T, 
             (SELECT ROUND(SUM(F.BYTES)/(1024*1024)) AS FREESPACE, 
                     F.TABLESPACE_NAME TABLESPACE 
              FROM DBA_FREE_SPACE F GROUP BY F.TABLESPACE_NAME) FS 
WHERE T.TABLESPACE=FS.TABLESPACE (+)
  AND T.TABLESPACE LIKE UPPER('%&TABLESPACE_NAME%') 
ORDER BY T.TABLESPACE
/

SQL> @tablespace_usage.sql

--
-- DISPLAYS USAGE OF TABLESPACE WISE ALONG WITH DATAFILE DETAILS
--

vi tbs_filesystem_usage.sql

SET HEAD ON
SET VERIFY OFF
SET LINESIZE 200 PAGESIZE 60

PROMPT Type the TABLESPACE NAME you are looking for or Press RETURN key for all TABLESPACES
ACCEPT TABLESPACE_NAME PROMPT "TABLESPACE NAME: "

BREAK ON TABLESPACE_NAME SKIP 2

COMPUTE SUM OF ALLOCATED_MB ON TABLESPACE_NAME
COMPUTE SUM OF USED_MB ON TABLESPACE_NAME
COMPUTE SUM OF FREE_MB ON TABLESPACE_NAME

COLUMN ALLOCATED_MB FORMAT 9,999,999,999
COLUMN USED_MB FORMAT 9,999,999
COLUMN FREE_MB FORMAT 9,999,999
COLUMN FILE_NAME FORMAT A65

SELECT A.TABLESPACE_NAME, A.FILE_ID, A.FILE_NAME, A.BYTES/(1024*1024) ALLOCATED_MB, DECODE((A.BYTES/(1024*1024) - B.FREE_MB),NULL,0,ROUND((A.BYTES/(1024*1024) - B.FREE_MB),0)) USED_MB, DECODE(B.FREE_MB,NULL,0,ROUND(B.FREE_MB,0)) FREE_MB, DECODE((A.BYTES/(1024*1024) - B.FREE_MB),NULL,0,ROUND(((A.BYTES/(1024*1024) - B.FREE_MB)/(A.BYTES/(1024*1024)))*100,0)) "%_USED"
FROM DBA_DATA_FILES A,
     (SELECT FILE_ID, SUM(BYTES)/(1024*1024) FREE_MB
      FROM DBA_FREE_SPACE B GROUP BY FILE_ID) B
WHERE A.FILE_ID=B.FILE_ID
  AND A.TABLESPACE_NAME LIKE UPPER('%&TABLESPACE_NAME%')
ORDER BY A.TABLESPACE_NAME, A.FILE_ID
/

SQL> @tbs_filesystem_usage.sql

--
-- DISPLAYS THE TEMPORARY TABLESPACE DETAILS
--

SET LINESIZE 132 PAGESIZE 60

SELECT A.TABLESPACE_NAME TABLESPACE, D.MB_TOTAL,
       SUM (A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_USED,
       D.MB_TOTAL – SUM (A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_FREE
FROM V$SORT_SEGMENT A,
     (SELECT B.NAME, C.BLOCK_SIZE, SUM (C.BYTES) / 1024 / 1024 MB_TOTAL
      FROM V$TABLESPACE B, V$TEMPFILE C
      WHERE B.TS#= C.TS#
      GROUP BY B.NAME, C.BLOCK_SIZE) D
WHERE A.TABLESPACE_NAME = D.NAME
GROUP BY A.TABLESPACE_NAME, D.MB_TOTAL;