Oracle-L: RE: Space management failures on autoextend datafiles

From: Hately, Mike (NESL-IT) <Mike.Hately_at_npowernorthern.com>
Date: Fri, 08 Nov 2002 06:18:43 -0800
Message-ID: <F001.004FEB8F.20021108061843@fatcity.com>

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

>>> wisernet100_at_yahoo.com 11/07/02 04:43PM >>> maybe temp segments don't cause an autoextend?

at least it's consistent

"Fink, Dan" <Dan.Fink_at_mdx.com> wrote:
> Update... I tried the same test with 8.1.7 on Solaris 8. Same
> result...
>
> -----Original Message-----
> Sent: Thursday, November 07, 2002 1:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I just manually expanded the file and the index build finished with
> no
> problem. I also set the USER_DATA ts as autoextend and it extended
> several
> times without error. The only difference is in the fact that the
> INDEX
> segment is initially created as a TEMP segment. Interesting....
>
> -----Original Message-----
> Sent: Thursday, November 07, 2002 10:34 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Dan,
> That's exactly, what someone described yesterday, it was LMT with
> autoallocate, and they made sure, that there was plenty of space on
> the hard
> drive for TEMP file to grow.
> The only difference: it was not Solaris - it was AIX.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com <mailto:ineyman_at_perceptron.com>
>
>
>
>
> ----- Original Message -----
> To: Multiple recipients of list <mailto:ORACLE-L_at_fatcity.com>
> ORACLE-L
> Sent: Thursday, November 07, 2002 12:15 PM
>
> Igor and Yechiel,
> Thanks for the responses. I'm glad to hear that I am not the
only
> one
> experiencing the problem.
> I should have also mentioned that the tablespace is LMT with
> autoallocate and is nowhere near the max size.
>
> Dan Fink
>
> -----Original Message-----
> Sent: Thursday, November 07, 2002 9:35 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Paulo
>
> When creating an index, or CTAS, oracle use temp segments while
> building and
> rename them after the build finish. So if you do not have enough
> space you
> will get: unable to allocate TEMP segment.
>
> Yechiel Adar
> Mehish
>
> ----- Original Message -----
> To: Multiple recipients of list <mailto:ORACLE-L_at_fatcity.com>
> ORACLE-L
> Sent: Thursday, November 07, 2002 6:04 PM
>
> never heard on this problem but are u sure table the temp tablespace
> of the
> user executing the commeand is temp and not user_indx???
> regards
> Paulo
>
> -----Original Message-----
> Sent: quinta-feira, 7 de Novembro de 2002 15:39
> To: Multiple recipients of list ORACLE-L
>
>
> I have a 9.2 database running on Solaris 8. I'm creating some test
> tables
> with indexes. The USER_INDX tablespace's datafile is set to
> autoextend (as
> are TEMP and USER_DATA). When the system attempts to create indexes,
> instead
> of auotextending the datafile (there is plenty of space on the
> device), it
> throws an ORA-01652: unable to extend temp segment by 128 in
> tablespace
> USER_INDX error. If I manually resize the datafile and rerun it, no
> problems.
>
> Anyone else heard of this behavior? I can't find anything on
Metalink
> that
> fits the problem definition.
>
> Dan Fink
>
>

Do you Yahoo!?
U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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

Received on Fri Nov 08 2002 - 08:18:43 CST