RE: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1

1)for temp usage check v$sort_usage while application/job running. It is some code which may be resulting in cartesian join using hash join.

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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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 <jblake_at_arrow.com> 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
  >
  > <<< saira_somani_at_yahoo.com 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:
  > Guido.Konsolke_at_tkserv-triaton.thyssenkrupp.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).
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.net
  > --
  > Author: John Blake
  > INET: jblake_at_arrow.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).
  

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani
   INET: saira_somani_at_yahoo.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).

_________________________________________________________________
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: rafiq9857_at_hotmail.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).