RE: TS space Usage script

From: Ruiz, Mary A (CAP, CDI) <Mary.Ruiz_at_gecapital.com>
Date: Mon, 12 Jun 2000 09:59:23 -0400
Message-Id: <10526.108650@fatcity.com>

Here is the script I have been using:

set trimspool on
select 'Current tablespace sizing information for ' || name || ' database:' from v$database
/
set pagesi 60
select a.tablespace_name,

        allocated_size as "Allocated Size",
        allocated_size-unused_space as "Used Space",
        unused_space as "Unused Space",
        100-(((allocated_size-unused_space)/allocated_size)*100) as "Pct

Remaining"
from (select tablespace_name, sum(bytes) as allocated_size

        from dba_data_files
        group by tablespace_name) A,
        (select tablespace_name, sum(bytes) as unused_space
        from dba_free_space
        group by tablespace_name) B

where a.tablespace_name = b.tablespace_name order by "Pct Remaining"
/

Hth

Mary Ruiz
ps I am from New Jersey, on assignment in Atlanta
> -----Original Message-----
> From: Rajesh Dayal [SMTP:Rajesh_at_ohitelecom.com]
> Sent: Monday, June 12, 2000 4:23 AM
> To: Multiple recipients of list ORACLE-L
> Subject: TS space Usage script
>
> Hi All,
>
> Recently I had picked up a script (from some site )
> to monitor space usage for diff. tablespaces in a DB,
> but that's giving me very dramatic results, can some
> one please point out what's wrong with that SQL.
> The SQL is
>
> select substr(d.tablespace_name,1,20) Tablespace,
> sum(d.bytes)/1024 Total_inKB ,
> ((sum(d.bytes)/1024) - (sum(f.bytes)/1024)) Used_inKB,
> sum(f.bytes)/1024 Free_inKB,
> (((sum(d.bytes) - sum(f.bytes))/sum(d.bytes))*100) PCT_Used
> from dba_data_files d , dba_free_space f
> where d.tablespace_name = f.tablespace_name
> group by d.Tablespace_name
> /
>
> If we check the results with DBA_DATA_FILES and
> DBA_FREE_SPACE views they simply tell diff. story...
>
> Thanks for your help,
> Rajesh
> --
> Author: Rajesh Dayal
> INET: Rajesh_at_ohitelecom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Mon Jun 12 2000 - 08:59:23 CDT