 | | | ORA-01652: unable to extend temp segment | ORA-01652: unable to extend temp segment 2006-02-06 - By Roger Xu
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@(protected) Org (E-mail)
Subject: RE: ORA-01652 (See ORA-01652.ora-code.com): 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@(protected)]
Sent: Tuesday, January 24, 2006 2:56 AM
To: Roger Xu; Oracle-L@(protected) Org (E-mail)
Subject: RE: ORA-01652 (See ORA-01652.ora-code.com): 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@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of Roger Xu
Sent: 23 January 2006 19:31
To: Oracle-L@(protected) Org (E-mail)
Subject: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment
SQL> alter index "SAPR3"."VBFA~0" rebuild online parallel 3 tablespace
PSAPREROGI;
*
ERROR at line 1:
ORA-12801 (See ORA-12801.ora-code.com): error signaled in parallel query server P001
ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment by 89595 in tablespace PSAPREROGI
I got ORA-01652 (See ORA-01652.ora-code.com) 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@(protected)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2180" name=GENERATOR><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
.shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>@(protected) {
font-family: Tahoma;
}
@(protected) Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-GB vLink=purple link=blue>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=684295620-25012006>Hi
all,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=684295620-25012006>It
worked with the following sql statement:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=684295620-25012006>alter
index "SAPR3"."VBFA~0" rebuild parallel 3 tablespace
PSAPREROGI</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006>storage (initial 1000M next 100M maxextents 2147483645
pctincrease 0);</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=684295620-25012006
>Thanks
a lot,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=684295620-25012006>Roger</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Original Message-- --<BR><B>From:</B> Roger Xu <BR><B>Sent:</B>
Tuesday, January 24, 2006 4:18 PM<BR><B>To:</B> 'Ian Cary (C)';
Oracle-L@(protected) Org (E-mail)<BR><B>Subject:</B> RE: ORA-01652 (See ORA-01652.ora-code.com): unable to
extend temp segment <BR><BR></FONT></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial color=#0000ff>Hi
Ian,</FONT></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial color=#0000ff>With the
help from Joseph Amalraj, I was able to find the problem. It should be
exactly
like what you described in your email.</FONT></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial color=#0000ff>This
coming
weekend, I am going to to try it with the follow sql statements
(<EM><U>smaller extent</U></EM>).</FONT></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV><SPAN class=097560922-24012006>
<DIV><FONT face=Arial color=#0000ff><STRONG>alter index "SAPR3"."VBFA~0"
rebuild parallel 3 tablespace PSAPREROGI </STRONG></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><STRONG><SPAN
class=097560922-24012006>
</SPAN>storage (initial 100M next 100M<SPAN class=097560922-24012006>
</SPAN>pctincrease 0);</STRONG></FONT></FONT></DIV></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial color=#0000ff>Since I
have the luxury to kick all users out, I will do it without the
<STRONG>online</STRONG> part.</FONT></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV><SPAN class=097560922-24012006><FONT face=Arial
color=#0000ff>Roger</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Original Message-- --<BR><B>From:</B> Ian Cary (C)
[mailto:Ian.Cary@(protected)]<BR><B>Sent:</B> Tuesday, January 24,
2006 2:56 AM<BR><B>To:</B> Roger Xu; Oracle-L@(protected) Org
(E-mail)<BR><B>Subject:</B> RE: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment
<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Roger,<o:p></o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">The temporary
segment you are seeing will become the index once the statement has been
completed. <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Hope this
helps,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Cheers,<o:p></o:p>
</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Ian<o:p></o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN lang=EN-US style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN lang=EN-US
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN
></FONT></B><FONT
face=Tahoma size=2><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B><SPAN style="FONT-WEIGHT: bold"
>On
Behalf Of </SPAN></B>Roger Xu<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> 23 January 2006 19:31<BR><B>
<SPAN
style="FONT-WEIGHT: bold">To:</SPAN></B> Oracle-L@(protected) Org
(E-mail)<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B>
ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment </SPAN></FONT><SPAN
lang=EN-US><o:p></o:p></SPAN></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SQL> alter index
"SAPR3"."VBFA~0" rebuild online parallel 3 tablespace
PSAPREROGI;<BR>*<BR>ERROR at line 1:<BR>ORA-12801 (See ORA-12801.ora-code.com): error signaled in
parallel query server P001<BR>ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment by
89595 in tablespace PSAPREROGI</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I got ORA-01652 (See ORA-01652.ora-code.com) while
rebuilding the 14 GB index to a new tablespace and the
tablespace</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">has 16 GB free space. Before
the
error occurred, the 31 GB temp tablespace had 13.5 GB
</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">used.</SPAN></FONT><o:p></o:p><
/P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Any idea why it created temp
segments in the target tablespace? </SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">How big the target tablespace
would be? I thought 16 GB is enough.</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks,</SPAN></FONT><o:p></o:p
></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Roger</SPAN></FONT><o:p></o:p><
/P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT></P></DIV><PRE> </PRE><
/BLOCKQUOTE></BLOCKQUOTE>
<BR>
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.
<BR>
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ _____<BR>
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@(protected)<BR>
</BODY></HTML>
|
|
|