Database Space Usage Snapshot

From: Alan Schafer <bschafer_at_ocvaxc.cc.oberlin.edu>
Date: 1 Jul 1994 18:16:27 GMT
Message-ID: <2v1mhr$nju_at_news.cc.oberlin.edu>

For all of you DBA!s out there, here is a little script that I find helpful in administering our database. It gives a bird!s-eye view of space usage.

SET PAGESIZE 60
SET LINESIZE 80
SET VERIFY OFF
CLEAR BREAK
TTITLE SKIP 2, LEFT _DATE, CENTER 'Space Usage Snapshot' - RIGHT 'ORSR7004 ', SKIP 2
BTITLE SKIP 2, CENTER 'PAGE - ' FORMAT 999 SQL.PNO SET PAGESIZE 60
SET LINESIZE 80

COLUMN C_USED HEADING 'MB Used' FORMAT 999.99
COLUMN C_FREE HEADING 'MB Free' FORMAT 999.99
COLUMN C_TOTAL HEADING 'Total MB' FORMAT 999.99
COLUMN LARGEST_SEGMENT HEADING 'Largest Free|Segment' FORMAT 999,999,999

COLUMN NUMBER_SEGMENTS HEADING '# Free|Segments' FORMAT 999,999,999 COLUMN TABLESPACE_NAME HEADING 'Tablespace Name' FORMAT A20 COLUMN PERCENT_USED HEADING 'Percent|Used' FORMAT 99.9

DROP TABLE SPACE_1999; CREATE TABLE SPACE_1999 TABLESPACE SCRATCH AS SELECT TABLESPACE_NAME, SUM(BYTES) USED, 0 FREE, 0 TOTAL, 0 LARGEST_SEGMENT, 0 NUMBER_SEGMENTS FROM SYS.DBA_EXTENTS GROUP BY TABLESPACE_NAME; UPDATE SPACE_1999 SET FREE = (SELECT SUM(BYTES) FROM SYS.DBA_FREE_SPACE WHERE SYS.DBA_FREE_SPACE.TABLESPACE_NAME = SPACE_1999.TABLESPACE_NAME); UPDATE SPACE_1999 SET TOTAL = FREE + USED; UPDATE SPACE_1999 SET LARGEST_SEGMENT = (SELECT MAX(BYTES) FROM SYS.DBA_FREE_SPACE WHERE SYS.DBA_FREE_SPACE.TABLESPACE_NAME = SPACE_1999.TABLESPACE_NAME); UPDATE SPACE_1999 SET NUMBER_SEGMENTS = (SELECT COUNT(*) FROM SYS.DBA_FREE_SPACE WHERE SYS.DBA_FREE_SPACE.TABLESPACE_NAME = SPACE_1999.TABLESPACE_NAME GROUP BY TABLESPACE_NAME); SELECT TABLESPACE_NAME, ROUND(USED/1048576,2) C_USED, ROUND(FREE/1048576,2) C_FREE,
ROUND(TOTAL/1048576) C_TOTAL, (NVL(USED,1)/TOTAL)*100 PERCENT_USED, LARGEST_SEGMENT, NUMBER_SEGMENTS
FROM SPACE_1999 ORDER BY TABLESPACE_NAME; drop table space_1999; Received on Fri Jul 01 1994 - 20:16:27 CEST