Ask the Expert

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 7.3.4.5.

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.


    Requires Free Membership to View

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


This was first published in April 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: