I have a couple of 7.3.3 databases that I manage where the temporary tablespace
shows being almost 100% used. This tablespace is defined as being temporary and
all the users are configured as this is their temporary tablespace. I have also
run the "alter tablespace temp coalesce;" command with no change in the amount
of available space. Now I am starting to get errors that select statements can'
extend the extents in temp. When I examine the objects none of them exist in the
temp tablespace.

How can the temporary tablespace suck up so much of itself and never let go? Is
there a way to get the temp tablespace to let go of these extents, coalesce and
reuse them?

What is happening when your users do the large SELECT statements is that the
processing cannot be stored in the SORT_AREA_SIZE. This space is used for (among
other things), ORDER BY, GROUP BY, and DISTINCT clauses. When the SORT_AREA_SIZE
is full, processing is done by swapping out to the temporary tablespace.

Eventually, they clean themselves up on their own by a background process.

I would suggest two things:

1) Double-check that there are no "permanent" objects mistakenly placed in the
TEMP tablespace:


2) Increase your SORT_AREA_SIZE in your init.ora and reboot the database. This
should help reduce the space needed by TEMP.

If all else fails, you should increase the size of your TEMP tablespace.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 305+ Oracle tips, visit my Web Page:                      <->
<->             http://www.arikaplan.com                          <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page Back to Ari Kaplan's Home Page