Problem solve Get help with specific problems with your technologies, process and projects.

TEMP tablespace problem

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

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

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.