Q

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.


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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close