Re: Find out free space for every tablespace

>Michael Kiesel wrote :
> SELECT fs.tablespace_name, sum(fs.blocks), sum(df.blocks)
> FROM dba_free_space fs, dba_data_files df
> WHERE fs.file_id=df.file_id
> GROUP by fs.tablespace_name;
>are giving a wrong value for the total size if there is more
>than one free space in a data file ...
>Please help!

Michael, try this:
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.

-- 
Jay Ramadorai
76734.3633_at_Compuserve.com