RE: ORA-01652: unable to extend temp segment

From: Roger Xu <roger_xu_at_dp7uptx.com>
Date: Mon, 6 Feb 2006 14:14:29 -0600
Message-ID: <A6801E8A03316A4DA597866F77A013F7095E6833@irv2kexch01.tx.bg.corp>

Hi all,

It worked with the following sql statement:

alter index "SAPR3"."VBFA~0" rebuild parallel 3 tablespace PSAPREROGI storage (initial 1000M next 100M maxextents 2147483645 pctincrease 0);

Thanks a lot,

Roger

-----Original Message-----

From: Roger Xu
Sent: Tuesday, January 24, 2006 4:18 PM
To: 'Ian Cary (C)'; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: ORA-01652: unable to extend temp segment

Hi Ian,

With the help from Joseph Amalraj, I was able to find the problem. It should be exactly like what you described in your email. This coming weekend, I am going to to try it with the follow sql statements (smaller extent).

alter index "SAPR3"."VBFA~0" rebuild parallel 3 tablespace PSAPREROGI

storage (initial 100M next 100M pctincrease 0);

Since I have the luxury to kick all users out, I will do it without the online part.

Thanks,

Roger

-----Original Message-----

From: Ian Cary (C) [mailto:Ian.Cary_at_ordnancesurvey.co.uk] Sent: Tuesday, January 24, 2006 2:56 AM
To: Roger Xu; Oracle-L_at_Freelists. Org (E-mail) Subject: RE: ORA-01652: unable to extend temp segment

Roger,

The temporary segment you are seeing will become the index once the statement has been completed.

I suspect you are running out of space because you are running the operation in parallel. When you do this each parallel thread creates its own extents so if, for example, the initial extent of the index is 6Gb by specifying the degree of parallelism as 3 you automatically require 18Gb while the index is being built. Once the parallel slaves have completed these temporary index segments are then coalesced down to give an index of the same size (roughly) as would have been created by a single threaded process.

Hope this helps,

Cheers,

Ian

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu Sent: 23 January 2006 19:31
To: Oracle-L_at_Freelists. Org (E-mail)
Subject: ORA-01652: unable to extend temp segment

SQL> alter index "SAPR3"."VBFA~0" rebuild online parallel 3 tablespace PSAPREROGI; *
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001 ORA-01652: unable to extend temp segment by 89595 in tablespace PSAPREROGI

I got ORA-01652 while rebuilding the 14 GB index to a new tablespace and the tablespace

has 16 GB free space. Before the error occurred, the 31 GB temp tablespace had 13.5 GB

used.

Any idea why it created temp segments in the target tablespace?

How big the target tablespace would be? I thought 16 GB is enough.

Thanks,

Roger

This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the material.

This email has been scanned for all viruses by the MessageLabs Email Security System. Any questions please call 972-721-8257 or email your request to tech_support_at_dp7uptx.com.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 06 2006 - 14:14:29 CST