Re: TEMP tablespace

On Wed, 09 Aug 2006 01:25:24 -0500, airmax wrote:

>
> 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?

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.