Re: Find out free space for every tablespace

Unless I've misunderstood what you've asked for but the previous suggested SQL statements is unneccessary complex.

How's about just use the following:

select tablespace_name, sum(blocks) from sys.dba_free_space group by tablespace_name;

-Ted.

-- 
=============================================
= Theodore Do                               =
= Senior Technical Specialist, UNIX/ORACLE  =
= TRW Information Technology Services       =
= tdo_at_mailsrv1.trw.com                      =
=============================================

mkie_at_izb.de (Michael Kiesel) wrote:


>Hello Jay!

>

>Jay Ramadorai <76734.3633_at_CompuServe.COM> wrote:

>>select fs.tablespace_name, 

>>       sum(fs.blocks) /count(distinct df.file_id),

>>sum(df.blocks) /count(distinct(fs.file_id*1000000) + fs.block_id)

>>from dba_free_space fs, dba_data_files df

>>where fs.tablespace_name = df.tablespace_name

>>group by fs.tablespace_name;

 


>>By joining on tablespace name and dividing the sum from each 

>>table by the distinct count from the other table, you should

>>get what you want. The 1000000 attempts to make the 

>>file_id+block_id combination unique. Hope this helps.

>

>Thanks a lot for your suggestion. So far it does what I wanted.

>BUT: To make the file_id+block_id combination unique, shouldn't

>the brackets be like this?:

>

>	count( distinct(fs.file_id*1000000 + fs.block_id) )

>                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

>                        |<---- unique combination ---->|

>???

>

>--

>Michael Kiesel

>mkie_at_izb.de

>