2) check for parallel degree in tables or indexes (dba_tables or dba_indexes) 'select table_name,degree from dba_tables where degree > 1; or same for indexes. If degree is > 1 then lot of temp segments are required for sorting.
3-Same may result due use of parallel in hint.
4) In short it is application/code related and that is to be fixed first. Beside for DW house application temp space may require big space for large sorts may be 6-8GB.
HTH,
Regards Rafiq
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Sun, 27 Jul 2003 07:34:24 -0800
As suggested, we checked all the tablespaces.
TEMP space was increased from 2GB to 4GB and then to 6GB.
We ended up bouncing the Oracle instance each time TEMP filled up because we really had nothing to lose.
After doing this 3 maybe 4 times, we ran the same scenario in the application and this time (and a few times after that to be sure), TEMP did not fill up - only used 0.5%. Then we decreased TEMP to 2GB and everything still seemed to work.
We had opened a severity level 1 TAR with Oracle and have now reduced it to level 2 because we don't have a case to present to them now.
We don't really understand why this was happening or how the problem got fixed. Since today is a production day, we'll probably find out if it happens again. I know we weren't imagining the entire thing!
Thank you all for your suggestions. They were extremely helpful. Saira
--- John Blake <[EMAIL PROTECTED]> wrote: > May not necessarily mean TEMP tablespace... Check > all of your tablespaces > for available free space, that is contiguous > freespace. > > -----Original Message----- > Guido Konsolke > Sent: Sunday, July 27, 2003 3:04 AM > To: Multiple recipients of list ORACLE-L > > > Hi Saira, > > maybe temp tablespace isn't large enough. > did you try what the doctor suggests? > Did you enlarge the temp seg? > 8-)) > > hth, > Guido > > <<< [EMAIL PROTECTED] 27.07. 05.54 >>> > Hi Gurus, > > After upgrading the OS from AIX 4.3.3 to 5.1, our > Oracle 8i instance is dishing out plenty of ORA-1652 > which means that our application cannot commit any > transactions (or so I am finding in my tests). > > Help. The warehouse needs to be in production > tomorrow! > > Thanks, > Saira > -- > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Guido Konsolke > INET: > [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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.net > -- > Author: John Blake > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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.net -- Author: Saira Somani INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
