Re: Free space for all tablespaces

From: Veera Prasad <vprasad_at_olf.com>
Date: Tue, 11 Jul 2000 10:35:43 -0400
Message-Id: <10555.111665@fatcity.com>

try this sql st ,

select a.tablespace_name, sum(a.bytes) TOTAL_SPACE, sum(b.bytes) FREE_SPACE from sys.dba_data_files a, sys.dba_free_space b where a.tablespace_name = b.tablespace_name group by a.tablespace_name
/

or Try my sql st

select a.tablespace_name name,b.tablespace_name ,

sum(b.bytes)/count(distinct a.file_id||'.'||a.block_id)/1024/1024 bytes,
sum(a.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 freespace,

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 dba_free_space a, dba_data_files b
where a.tablespace_name = b.tablespace_name group by a.tablespace_name,b.tablespace_name /

regards
Veera
blair_at_pjm.com wrote:

> 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).
Received on Tue Jul 11 2000 - 09:35:43 CDT