Script for displaying Tablespace Usages

From: C.J.Sleith <cbar68_at_ccsun.strath.ac.uk>
Date: 4 May 1994 18:53:33 GMT
Message-ID: <2q8qvd$8bg_at_rockall.cc.strath.ac.uk>

Here's a script that I noticed people were wanting something similar to that displays the current 'fullness' of tablespaces.

Run this one in SQL*Plus as a DBA ( or sys )...

-----------8<----------------8<-----------------------

REM
REM tspace.sql: A piece of nasty SQL*Plus to list statistics on

REM             tablespaces on ORACLE 6 databases. May work on 7, I don't
REM             know...

REM
REM (c)1994 Alligator Descartes - Interstellar Fruitbat REM descarte_at_fruitbat.demon.co.uk
REM column TSPACE format a20 heading "Tablespace Name" column TSIZE format 999.99 heading "Size(M)" column LEFT format 999.99 heading "Left(M)" column USED format 999.99 heading "Used(M)" column PERCENT format 99.99 heading "Used(%)"

SELECT DFS.tablespace_name TSPACE,

       DDF.bytes / ( 1024 * 1024 ) TSIZE, 
       sum( DFS.bytes ) / ( 1024 * 1024 ) LEFT,
       DDF.bytes / ( 1024 * 1024 ) - sum( DFS.bytes ) / ( 1024 * 1024 ) USED,
       ( ( ( DDF.bytes / ( 1024 * 1024 ) ) - ( sum( DFS.bytes ) / ( 1024 * 1024 ) ) ) / ( DDF.bytes / ( 1024 * 1024 ) ) * 100 ) PERCENT

FROM dba_free_space DFS, dba_data_files DDF WHERE DFS.tablespace_name = DDF.tablespace_name GROUP BY DFS.tablespace_name, DDF.bytes ORDER BY 1; exit;
/

-------------8<------------------8<---------------

V.
cbar68_at_ccsun.strath.ac.uk Received on Wed May 04 1994 - 20:53:33 CEST