RE: Free Space per tablespace....

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 05 Dec 2000 14:13:38 -0500
Message-Id: <10701.123703@fatcity.com>

You may try this also:

select sysdate"Date",substr(a.tablespace_name,1,10)
"Table_Space_Name",

    round( a.bytes/( 1024*1024 ), 0) " Avail(MB)",
    round( b.bytes/( 1024*1024 ), 0) " Used (MB)",
    round( c.bytes/( 1024*1024 ), 0) " Free (MB)",
    round( ( round(b.bytes / ( 1024*1024 ), 0 )*100 ) / round(

a.bytes / ( 1024*1024 ),0 ),0 ) " % Full"

from sys.sm$ts_avail a,
     sys.sm$ts_used  b,
     sys.sm$ts_free  c

where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name(+) /
Regards
Rafiq

From: "Shaw, Glen" <Glen.Shaw_at_BellSouth.com> Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject: RE: Free Space per tablespace.... Date: Tue, 05 Dec 2000 10:27:19 -0800

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-----
Sent: Tuesday, December 05, 2000 12:37 PM To: 'Rahul Dandekar'; L; L

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-----

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 ----- To: "'Rahul Dandekar'" <orcldba_at_hotmail.com> Sent: Tuesday, December 05, 2000 12:11 PM

 > 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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shaw, Glen
   INET: Glen.Shaw_at_BellSouth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

_____________________________________________________________________________________

Received on Tue Dec 05 2000 - 13:13:38 CST