temp tablespace stays full

[ivo]

Hi all!

The temporary tablespace has been full for atleast 24 hours (when I noticed it first). We have Oracle 7.3.4 and I am using the following to clean it up:

oradebug wakeup

but NOTHING happens.

Could someone please give me more insight into this and also tell me how I can clear it out.

[aarroyob]

This topic have being very discussed here. If you search above you will have very posts to read about this.

Begin with:

http://www.dbasupport.com/forums/sho...1&pagenumber=1

Regards

Angel

[ivo]

Thanx for the info.

I still have a few questions regarding what I read in the link
http://www.dbasupport.com/forums/sho...1&pagenumber=1

Firstly, The parameter SORT_MULTIBLOCK_READ_COUNT doesn't appear in the list of initialisation parameters when I open Enterprise Manger, nor can I find it in any of the books I have here. Is it a direct derivitave of SORT_AREA_SIZE/DB_BLOCK_SIZE?

Secondly, having read the discussion between julian and jmodic I was confused whether julians advice still stands.

Originally posted by julian
Recreate your TEMP t-space using the tempfile keyword and assure that sort_area_size = block_size * sort_multiblock_read_count

Is this the advice for my opening question?

[julian]

It is Jason Couchman who suggests in his book to set sort_multiblock_read_count so that sort_area_size = block_size * sort_multiblock_read_count.

[gpsingh]

SMON does not work on temporary tablespace , until the database shutdown,
One way to coalesce this tablespace is to change pctincrease to 1 or any other value over 0,
run alter tablespace <..> coalesce a number of times and chnage back the pctincrease to .
But u should mention this increases the overhead of creating Sort segment .