| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-1652: temp segment in tablespace SYSTEM ?!?
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C3C594.461C3ED6
Content-Type: text/plain
No, they weren't loading into the SYSTEM tablespace. Turned out that the AQUSER account had SYSTEM as a temp space in one of the two instances. Didn't think to check AQUSER when I checked the settings for the users running the procedure on either side of the link.
Still don't know why the temp segment in SYSTEM wasn't listed in the v$session and v$sort query. This is the query I ran:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks,
a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
Robyn
-----Original Message-----
Sent: Thursday, December 18, 2003 12:34 PM
To: Multiple recipients of list ORACLE-L
Are you sure they are not loading blobs into SYSTEM tablespace?
Guang
-----Original Message-----
Sands, Robyn
Sent: Thursday, December 18, 2003 11:29 AM
To: Multiple recipients of list ORACLE-L
This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.
------_=_NextPart_001_01C3C583.D0824CB6
Content-Type: text/plain
hey everyone ...
The developers are loading blobs over database links. One of the 24 subsets of data is failing with the following error:
ORA-1652: unable to extend temp segment by 512 in tablespace SYSTEM
The subset that is failing is reading a large amount of MRP data. The error occurs in the instance the data is being pulled from.
Can someone help me figure out why is this occurring in 'SYSTEM'? All of the users involved in this process are set to use 'TEMP' as their temp space and the target tables are not in 'SYSTEM' either. I've queried v$session and v$sort_usage while the process is running, but all the sorts I see are in 'TEMP'. I've also checked dba_tables and dba_indexes, and aren't any stray objects in the 'SYSTEM' tablespace. What else should I look for? We have plenty of space in TEMP if I can get this process to use it.
TIA ... Robyn
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: gmei_at_incyte.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). - - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. ------_=_NextPart_001_01C3C594.461C3ED6 Content-Type: text/html Content-Transfer-Encoding: quoted-printableReceived on Thu Dec 18 2003 - 12:29:25 CST
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 5.5.2655.35">
<TITLE>RE: ORA-1652: temp segment in tablespace SYSTEM ?!?</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>No, they weren't loading into the SYSTEM tablespace. =
; Turned out that the AQUSER account had SYSTEM as a temp space in one of t= he two instances. Didn't think to check AQUSER when I checked the set= tings for the users running the procedure on either side of the link. = </FONT></P>
<P><FONT SIZE=3D2>Still don't know why the temp segment in SYSTEM wasn't li=
sted in the v$session and v$sort query. This is the query I ran:</FON= T></P>
<P><FONT SIZE=3D2>SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, </F=
ONT>
<BR><FONT SIZE=3D2> a.sid, a.serial#,a.=
username, a.osuser, a.status</FONT>
<BR><FONT SIZE=3D2> FROM v$session a,v$sort_usage b</FONT>
<BR><FONT SIZE=3D2> WHERE a.saddr =3D b.session_addr</FONT>
<BR><FONT SIZE=3D2> ORDER BY b.tablespace, b.segfile#, b.segblk#, b.bl=
ocks;</FONT>
</P>
<P><FONT SIZE=3D2>Robyn</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Guang Mei [<A HREF=3D"mailto:gmei_at_incyte.com">mail=
to:gmei_at_incyte.com</A>] </FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, December 18, 2003 12:34 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: RE: ORA-1652: temp segment in tablespace SYSTEM=
?!?</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>Are you sure they are not loading blobs into SYSTEM table=
space?</FONT>
</P>
<P><FONT SIZE=3D2>Guang</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>Sands, Robyn</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, December 18, 2003 11:29 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>This message is in MIME format. Since your mail reader do=
es not understand</FONT>
<BR><FONT SIZE=3D2>this format, some or all of this message may not be legi=
ble.</FONT>
</P>
<P><FONT SIZE=3D2>------_=3D_NextPart_001_01C3C583.D0824CB6</FONT>
<BR><FONT SIZE=3D2>Content-Type: text/plain</FONT>
</P>
<P><FONT SIZE=3D2>hey everyone ...</FONT>
</P>
<P><FONT SIZE=3D2>The developers are loading blobs over database links.&nbs=
p; One of the 24 subsets</FONT>
<BR><FONT SIZE=3D2>of data is failing with the following error:</FONT>
</P>
<P><FONT SIZE=3D2> ORA-1652: unable to extend temp segment by 5=
12 in tablespace SYSTEM</FONT>
</P>
<P><FONT SIZE=3D2>The subset that is failing is reading a large amount of M=
RP data. The error</FONT>
<BR><FONT SIZE=3D2>occurs in the instance the data is being pulled from.</F=
ONT>
</P>
<P><FONT SIZE=3D2>Can someone help me figure out why is this occurring in '=
SYSTEM'? All of</FONT>
<BR><FONT SIZE=3D2>the users involved in this process are set to use 'TEMP'=
as their temp space</FONT>
<BR><FONT SIZE=3D2>and the target tables are not in 'SYSTEM' either. =
I've queried v$session</FONT>
<BR><FONT SIZE=3D2>and v$sort_usage while the process is running, but all t=
he sorts I see are</FONT>
<BR><FONT SIZE=3D2>in 'TEMP'. I've also checked dba_tables and dba_in=
dexes, and aren't any</FONT>
<BR><FONT SIZE=3D2>stray objects in the 'SYSTEM' tablespace. What els=
e should I look for? We</FONT>
<BR><FONT SIZE=3D2>have plenty of space in TEMP if I can get this process t=
o use it.</FONT>
</P>
<P><FONT SIZE=3D2>TIA ... Robyn</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A HREF=3D"http://=
www.orafaq.net" TARGET=3D"_blank">http://www.orafaq.net</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Guang Mei</FONT>
<BR><FONT SIZE=3D2> INET: gmei_at_incyte.com</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- 858-538-50=
51 <A HREF=3D"http://www.fatcity.com" TARGET=3D"_blank">http://www.fatcity.= com</A></FONT>
<BR><FONT SIZE=3D2>San Diego, California  =
; -- Mailing list and web hosting services</FONT>
<BR><FONT SIZE=3D2>--------------------------------------------------------=
-------------</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an E-Mai=
l message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGu=
ru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB ORACL=
E-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed from=
). You may</FONT>
<BR><FONT SIZE=3D2>also send the HELP command for other information (like s=
ubscribing).</FONT>
</P>
<FONT SIZE=3D3><BR>
<BR>
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - - <B= R> This e-mail and any attachments may contain information which is confidenti= al, proprietary, privileged or otherwise protected by law. The information = is solely intended for the named addressee (or a person responsible for del= ivering it to the addressee). If you are not the intended recipient of this= message, you are not authorized to read, print, retain, copy or disseminat= e this message or any part of it. If you have received this e-mail in error= , please notify the sender immediately by return e-mail and delete it from = your computer.<BR>
</FONT>
</BODY>
</HTML>
------_=_NextPart_001_01C3C594.461C3ED6-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sands, Robyn INET: Robyn.Sands_at_sciatl.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).
 |
 |