Re: Find out free space for every tablespace

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