Re: Space Management w/ LMTs

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Thu, 07 Apr 2005 22:25:45 GMT
Message-ID: <4255b082.778490980@localhost>

Noons,

Was looking for something more specific, like this:

select dt.tablespace_name,

        decode(allocation_type, 'UNIFORM',
dfs.free_space/dt.next_extent) uniform_extents_avail,

        decode(allocation_type, 'UNIFORM', null, trunc(dfs.free_space/ds.want_bytes) ) extents_avail from dba_tablespaces dt,

        (select tablespace_name, sum(bytes) free_space
                from dba_free_space
                group by tablespace_name) dfs,
 	(select tablespace_name, max(bytes) want_bytes 
		from dba_segments
		group by tablespace_name) ds
where 	dt.tablespace_name=dfs.tablespace_name
   and  ds.tablespace_name=dfs.tablespace_name
   and	dt.tablespace_name=ds.tablespace_name

Elapsed: 00:00:29.47

You can put a wrapper around it like

        WHERE uniform_extetns_avail<=50 OR extents_avail<=20

This is a bit slow for my taste though.. I'll have to research the underlying oracle views.

On Thu, 07 Apr 2005 20:53:57 +1000, Noons <wizofoz2k_at_yahoo.com.au> wrote:

>NetComrade apparently said,on my timestamp of 7/04/2005 7:30 AM:
>
>> Can anyone share a 'universal' and fairly light query that they use
>> for space monitoring, one that doesn't rely on percentages of free
>> space.
>> * Dictionary Managed Tablespaces
>> * non-Uniform LMTs
>> * Uniform LMTs
>
>Isn't this:
>http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2252.htm#1309526
>supposed to handle just that?
>
>> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
>> remove NSPAM to email
>
>
>Come to think of it, wasn't there some
>problem with dba_free_space and 8ir3?
>Time to upgrade? <d&r>
>
>--
>Cheers
>Nuno Souto
>in sunny Sydney, Australia
>wizofoz2k_at_yahoo.com.au.nospam

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Apr 07 2005 - 17:25:45 CDT