RE: ORA-01652: unable to extend temp segment
- From: "Roger Xu" <roger_xu@xxxxxxxxxxx>
- To: "Ian Cary (C)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Feb 2006 14:14:29 -0600
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@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@xxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, January 24, 2006 2:56 AM
To: Roger Xu; Oracle-L@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Roger Xu
Sent: 23 January 2006 19:31
To: Oracle-L@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@xxxxxxxxxxxx
Other related posts:
