RE: Free space for all tablespaces

From: Joe LaCascio <jlacasci_at_wheatonma.edu>
Date: Tue, 11 Jul 2000 13:47:35 -0400 (EDT)
Message-Id: <10555.111693@fatcity.com>

Or try this:

column dummy noprint

column  pct_used format 999.9       heading "%|Used" 
column  name    format a16      heading "Tablespace Name" 

column bytes format 9,999,999,999,999 heading "Total Megs" column used format 99,999,999,999 heading "Used" column free format 999,999,999,999 heading "Free" break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report  

spool subt2.lst

select a.tablespace_name                                                  name,
       b.tablespace_name                                                  dummy,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )     
                                    /1024/1024                            bytes,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )
         /1024/1024  - sum(a.bytes)/count( distinct b.file_id )/1024/1024 used,
       sum(a.bytes)/count( distinct b.file_id ) /1024/1024                free,
       100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
           (sum(a.bytes)/count( distinct b.file_id ) )) /
	        (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used

from sys.dba_free_space a, sys.dba_data_files b where a.tablespace_name = b.tablespace_name group by a.tablespace_name, b.tablespace_name;

spool off

Joe

Joe LaCascio

DBA/System Administrator    phone     508.286.3405
Wheaton College             email     jlacasci_at_wheatonma.edu

On Tue, 11 Jul 2000, Jeffery Stevenson wrote:

> Take a gander at the following tables and see if they help:
>
> sys.sm$ts_avail
> sys.sm$ts_free
> sys.sm$ts_used
>
> :)
>
> Jeffery Stevenson
> Chief Database Geek
> Medical Present Value, Inc.
> Austin, TX
>
> -----Original Message-----
> blair_at_pjm.com
> Sent: Tuesday, July 11, 2000 8:56 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I just want a SQL query to give me the freespace in all tablespaces. This
> doesn't work:
>
> select a.tablespace_name, sum(a.bytes) TOTAL_SPACE, sum(b.bytes)a FREE_SPACE
> from sys.dba_data_files a, sys.dba_free_space b
> where a.tablespace_name = b.tablespace_name
> group by 1;
>
> Does anyone have a query that does work??
>
> thanks,
>
> ..tom
>
>
>
> --
> Author:
> INET: blair_at_pjm.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).
>
>
> --
> Author: Jeffery Stevenson
> INET: jeff_at_mpv.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
Received on Tue Jul 11 2000 - 12:47:35 CDT