Re: Find out free space for every tablespace

From: Reinhard Kuhn <kuhn_at_cas-ps.com>
Date: 1996/01/24
Message-ID: <4e51ea$mod_at_fred.cas-ps.com>#1/1

In article <4e0l54$78o$1_at_mhafn.production.compuserve.com>, 76734.3633_at_CompuServe.COM says...
>
>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

For Oracle >= 7.1 I'd prefer

select A.tablespace_name,

lpad(substr(round(A.bytes / (1024*1024)),1,10)||' M',6) as total, 
lpad(substr(round(B.bytes / (1024*1024)),1,10)||' M',6) as used ,
lpad(substr(round( 100* B.bytes / a.bytes),1,7)||' %',12) as percent_used

from
(select tablespace_name, sum(bytes) bytes from dba_data_files   group by tablespace_name) A,
(select tablespace_name, sum(bytes) bytes from dba_segments   group by tablespace_name ) B
where A.tablespace_name = B.tablespace_name

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /   
                                   

Received on Wed Jan 24 1996 - 00:00:00 CET