Re: HELP: need % of tablespace free

From: Chris Kasten <kasten_at_brookings.net>
Date: 1996/02/13
Message-ID: <3120d8fd.162177676_at_news.brookings.net>#1/1

jul395_at_prb.mhs.compuserve.com (Jonathan Julian) wrote:

>
> I need to know what percentage of a tablespace is filled with data. I
> had hoped it was a simple query in SQL, like
>
> select TOTAL, FREE from USER_TABLESPACE
> where NAME ='tabelspace_name';
>
> but I cannot find this info in the Oracle docs. Thanks in advance.
> Post here or email.
>
> Jonathan Julian
> PRB Associates, Inc.
> jul395_at_prb.mhs.compuserve.com
>

I grabbed this from this forum so I reckon I can give it back :) (was contributed by Reinhard Kuhn I believe)

---

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

HTH, -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Received on Tue Feb 13 1996 - 00:00:00 CET