Re: Next Extent Failure Notification for Locally Managed Tablespaces

I have an idea that you can use in situation then you don?t know which 
size will be next extent.
If you have off business ours then you can try to allocate next extent by 
your self (batch script) and if Oracle will fail to allocate it, then 
report problem. In case extent will be allocated successfully, deallocate 
it immediate after allocation.
alter table <table name> allocate extent;
<chechk results>
alter table <table name> deallocate unused;

This technique requires more time to execute check for every segment, but 
it is seams will work in your case (as well as in any extent allocation 
policy).

Any comments?
Jurijs
9268222
============================================
http://otn.oracle.com/ocm/jvelikanovs.html






Niall Litchfield <niall.litchfield@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
22.06.2004 11:17
Please respond to oracle-l
 
        To:     oracle-l@xxxxxxxxxxxxx
        cc: 
        Subject:        Re: Next Extent Failure Notification for Locally 
Managed Tablespaces


Hi

It is going to be somewhat difficult to do what you want,
unfortunately. The allocation type you have means that the next extent
size is indeterminate to the dba (hence the null value in next_extent
column).  We do know what *valid* values for the next extent are, but
I am not aware of anyone who has determined reliably what size will be
picked next. I'd welcome finding out if someone has determined this.

Richard Foote (I think) and I had a couple of different scripts which
ran drops, extends etc. In his example system managed allocation
efficiently reused space, in mine it didn't (which is to say I had
'holes' in the tablespace that could have been reused, except Oracle
decided it wanted a larger next extent than (IMO) it should have done.
I don't think we ever got as far as deciding why Oracle was picking
the extent sizes that it did.

It probably doesn't help to point out that you can avoid this effort
entirely with uniform extents does it?


-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

On Mon, 21 Jun 2004 22:48:35 -0600, Wales, Stephen (RTSI)
<stephen.wales@xxxxxxxxxxxx> wrote:
> 
> No - had I the luxury of doing it over, they would be.  But they are =
> ALLOCATION_TYPE =3D SYSTEM
> 
> Steve
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of DENNIS WILLIAMS
> Sent: Monday, June 21, 2004 2:05 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: Next Extent Failure Notification for Locally Managed
> Tablespaces
> 
> Stephen
>    Uniform extents?
> 
> 
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: