TEMP tablespace

[airmax]

Hello DBA’s

I have a problem with my TEMP tablespace. It’s 1 GB and its full
Shouldn’t be empty? When the tablespace is full i have to restart th
database in order clean up the tablespace, but this is not
solution!!! I don’t want to extend it becuase it will be full again. I
my previous job the table space was 800MB and it was always empty.



What I should do?


Regards


Pano

--
airma
-----------------------------------------------------------------------
airmax's Profile: http://www.dbtalk.net/m62
View this thread: http://www.dbtalk.net/t32458

[HansF]

Post by airmax
Hello DBA’s
I have a problem with my TEMP tablespace. It’s 1 GB and its full.
Shouldn’t be empty? When the tablespace is full i have to restart the
database in order clean up the tablespace, but this is not a
solution!!! I don’t want to extend it becuase it will be full again. In
my previous job the table space was 800MB and it was always empty.
What I should do?

1) Don't panic. This may not be a problem at all!

2) Ask this (and other questions) in a newsgroup that is not obsolete ...
comp.databases.oracle.server is the appropriate group. I've copied to
that group and set the followup and replies to that one for your
convenience.

3) Tell us which version of the database and operating system you are
using. In this case it's probably not significant, but it leaves us (who
answer) with the impression that you care about the environment - making
us much more likely to answer civilly.

4) Read the documentation. IN particular, the following:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref399
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref422
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/physical.htm#sthref543

What these links are telling you ... if the temp space is used for
sorting, it will stay allocated (show 'not free') but will automatically
be used for other sort operations when the current session is finished.

Since a block in a tablespace must be formatted before it is used, leaving
the blocks allocated for sorting (therefore appropriately formatted)
probably saves time. Clearing them might actually introduce a performance
hit. (Note to self - test this!)



So ... your problem is probably not
"how do I keep my temp tablespace from being full?",
but rather
"how much of my temp tablespace is allocated to reusable sort segments?"




By the way ... how big is the disk? Is 1 GB really a problem?