We are having a problem with TEMP tablespaces generating ORA-1652 at least once a day. We've searched metalink for possible solutions. Running ORACLE 18.104.22.168.
What we see happening is the temporary tablespace does not release extents to be used for other sort operations. Currently, our TEMP tablespace is at 4 Gb for a 35 G database. It is an OLTP app. The SGA ixed Size=38992, Variable Size=171727944, Database Buffers=245760000, Redo Buffers=8388608, SORT_AREA_SIZE = SORT_AREA_RETAIN_SIZE = 10,485,760. Can you give some suggestions for what is going on? Thanks for any help.
Contrary to popular belief, Oracle does not "release" temp segments in a temporary tablespace. When a user is finished sorting in the TEMP tablespace, Oracle holds on to the allocated temp space and marks it available for use by the next process. If you want to see who is using what, query V$SORT_USAGE and V$SORT_SEGMENT.
Now on to solving your problem...
First of all, make sure that your tablespace is TEMPORARY not PERMANENT. Query DBA_TABLESPACES to be sure. You might have permanent objects (like tables and indexes) hogging up your space. Your tablespace is 4 GB. What is your default INITIAL_EXTENT and NEXT_EXTENT for this tablespace? Ideally, INITIAL = NEXT = SORT_AREA_SIZE+DB_BLOCK_SIZE and PCTINCREASE = 0. After all that checks out, you'll need to determine what is chewing up so much space. Again, query V$SORT_USAGE to see who is using the space. By joining the SQLHASH value to V$SQLAREA, you can get the SQL text that the user is running. Are they doing a proper join, or are they computing a cartesian product (space killers!)? Tune the query and you may find that the temp segment usage goes down.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle guru is waiting to answer your toughest questions.
This was first published in April 2001