RE: Free Space per tablespace....

From: Shaw, Glen <Glen.Shaw_at_BellSouth.com>
Date: Tue, 5 Dec 2000 13:24:05 -0500
Message-Id: <10701.123697@fatcity.com>

Actually, there is a simple way to do it that is not so nasty. Try the following SQL as an example. You can expound on the information you return to the result set.

select a.tablespace_name, TOT_SIZE_MB, TOT_FREE_MB, (TOT_SIZE_MB - TOT_FREE_MB) TOT_USED_MB,

       Round((TOT_FREE_MB/TOT_SIZE_MB)*100, 2) PCT_FREE, 
       Round((1-(TOT_FREE_MB/TOT_SIZE_MB))*100, 2) PCT_USED
  from (select tablespace_name, round(sum(bytes)/1048576,4) TOT_SIZE_MB 
          from dba_data_files group by tablespace_name) a,
       (select tablespace_name, round(sum(bytes)/1048576,4) TOT_FREE_MB
          from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name order by a.tablespace_name

Hope this helps,
Glen

-----Original Message-----
From: Christopher Spence [mailto:cspence_at_FuelSpot.com] Sent: Tuesday, December 05, 2000 12:37 PM To: 'Rahul Dandekar'; L; L
Subject: RE: Free Space per tablespace....

You go ahead and do it.

I tried that approach and the views made it MUCH easier. It was NASTY before.

"Out of my mind...Back in five minutes."

Christopher R. Spence
OCP Raptor MCSE MCP A+ CNA
Oracle Database Administrator
Fuel Spot

73 Princeton Road
Suite 207
North Chelmsford, MA 01863
(978)-322-5744

-----Original Message-----

From: 	Rahul Dandekar [mailto:orcldba_at_hotmail.com] 
Sent:	12/5/2000 12:30 PM
To:	L; L
Subject:	Re: Free Space per tablespace....

How about doing it in single sql without views... No doubt that this method is correct and I used very similar stuff to get it done. But which way can it be done by simple single sql....

-Rahul

Original Message ----- From: "Christopher Spence" <cspence_at_FuelSpot.com> To: "'Rahul Dandekar'" <orcldba_at_hotmail.com> Sent: Tuesday, December 05, 2000 12:11 PM Subject: RE: Free Space per tablespace....

> Create or replace view free_space_view as
> Select t.tablespace_name, nvl(sum(f.bytes),0) free
> From dba_tablespaces t, dba_free_space f
> Where t.tablespace_name = f.tablespace_name(+)
> Group by t.tablespace_name
> /
>
> Create or replace view used_space_view as
> Select t.tablespace_name, nvl(sum(e.bytes),0) used
> From dba_tablespaces t, dba_extents e
> Where t.tablespace_name = e.tablespace_name(+)
> Group by t.tablespace_name
> /
>
> Select f.tablespace_name "Tablespace"
> , to_char(f.free/1048576, '999,999,999')||'Mb' "Free Mb"
> , to_char(u.used/1048576, '999,999,999')||'Mb' "Used Mb"
> , to_char(f.free/1048576 + u.used/1048576, '999,999,999') || 'Mb'
> "Total Mb"
> From free_space_view f, used_space_view u
> Where f.tablespace_name = u.tablespace_name
> /
>
> "Out of my mind...Back in five minutes."
>
> Christopher R. Spence
> OCP Raptor MCSE MCP A+ CNA
> Oracle Database Administrator
> Fuel Spot
>
> 73 Princeton Road
> Suite 207
> North Chelmsford, MA 01863
> (978)-322-5744
>
>

Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl