RE: Space management failures on autoextend datafiles

Hi Ron,

you're quite right and I'm pointing out that this functionality (auto-extension of a file to accomodate a tempoary segment) works fine for me.
That suggests that (bugs aside) temporary segments are quite capable of forcing a datafile to extend.
The thrust of your earlier mail seemed to be that this was a deliberate design choice on the part of Oracle rather than a bug. Apologies if I misundestood your intention.

Regards,
Mike

-----Original Message-----
Sent: Friday, November 08, 2002 3:59 PM
To: Multiple recipients of list ORACLE-L

Mike,
 In the previous discussion on this thread the problem was that the autoextent would not work for a temp segment created during the index creation and the problem was solved by extending the tablespace to handle the temp segments.
Ron

>>> Mike.Hately_at_npowernorthern.com 11/08/02 09:18AM >>>
Ron,
I don't believe all of that to be true.

In your example idx2 should be created and the tablespace should extend.
Otherwise what would be the point of having AUTOEXTEND on any tablespace
which held only indexes?
I agree that Oracle will use a temporary segment to hold the index data
until it is fully created but Oracle will extend the datafile regardless.
In fact, when I tested this at 8.1.7.3 and 9.2 even an index rebuild extended the datfile in order to accomodate the new index.

regards,
Mike Hately

-----Original Message-----
Sent: Friday, November 08, 2002 12:39 PM To: Multiple recipients of list ORACLE-L

Let's think about this for a minute.
You create and index called idx1 using a designated tablespace that has
sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index.  The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does
not know that the index will complete, be aborted, crash, etc so it can
not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each
temporary function then the database could artificially expand when the
functions were only temporary in nature and the compounded effect could
cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted.
Just a few thoughts and ideas.
Ron

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  INET: Mike.Hately_at_npowernorthern.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).