Re: Generic "Can I extend?" check

This functionally also *kind of* exists in OEM - the agent uses a TCL script called
chunksml.tcl in $ORACLE_HOME/network/agent/events/oracle/rdbms/space. (it's the chunk small event)
Your post made me wonder how this is handled by Oracle - so I peeked at the script on a 9iR2 install ...
They just *ignore* auto-alloc lmt's !!!! :-)
>>> code snippet

    set sql {select tablespace_name from sys.dba_tablespaces where status = 'ONL
INE'}
    if {[string compare $db_version "81"] >= 0} {

        append sql " and allocation_type != 'SYSTEM'"     }
>>>

Damn - now I have to deregister all those events ... flippen' worthless :-)

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3D518D84.4A77_at_yahoo.com...
> I've been asked to write a generic routine to determine if a segment can
> extend. Easy enough except for auto-alloc lmt's where 'next_extent' is
> null (ie indeterminate) in xxx_SEGMENTS.
>
> So here is my current compromise (in pseudo-code)
>
> where nvl(next_extent,
> case
> when initial_extent < 1m then
> case when extents < 16 then next = 64k,
> when extents < 80 then next = 1m,
> when extents < 200 then next = 8m,
> else next = 64m
> when initial_extent >= 1m then
> case when extents < 64 then next = 1m,
> when extents < 184 then next = 8m,
> else next = 64m )
> > largest_free_space_chunk_in_tablespace
>
> The rough translation being:
> - auto-alloc extents are 16x64k, then 64x1m, then 120x8m, then 64m, when
> the initial_extent is less than 1m
> - auto-alloc extents are 64x1m, then 120x8m, then 64m, when the
> initial_extent is equal/more than 1m
>
> In the spirit of "open source", I'm throwing this out the community
> asking for counter-examples which break the above formula.
>
> Thanks
> Connor
>
> --
> ==============================
> Connor McDonald
>
> http://www.oracledba.co.uk
>
> "Some days you're the pigeon, some days you're the statue..."
Received on Sat Aug 10 2002 - 10:09:38 CDT