how to clean up TEMP tablespace ?

[vr76413]

Hello all

Oracle9.2 on sun 5.8

My TEMP tbs is 98% FULL. This tbs is LOCALLY managed. How do we celan it up without bouncing the instance ?...


Any help is appreciated.

thanks
Ron

[mike2000]

you can't clean up the TEMP tablespace in local extent in 9i. It shows 98% full b/c Oracle preallocated the tablespaces. you should not worry about it unless you want to drop the temp and recreate the new one.

[vr76413]

____________________________________________________________
It shows 98% full b/c Oracle preallocated the tablespaces.
___________________________________________________


Thanks Mike2000...


I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...

reason is , my other 9i DB TEMP tbs is only 17% Full..


any help is appreciated...

thanks
ron

[slimdave]

What you need to do is read the Oracle documentation on how it uses temporary tablespaces -- it changed in 8i.

Search the forum for previous questions on temp tablespaces being full, 'cos this gets asked about once a month.

[Dilippatel]

Temporary segments cleared when Database is shutdown.

A temporary segment created in temp tablespace will not be deallocated at the end of the sort. The extents are MARKED as free, are not placed back on the freelist, and are retained for reuse. An element called the Sort Extent Pool (SEP) is created in the SGA to describe them.
Subsequent sort operations will then be allocated extents from this pool via a memory lookup.

Dilip

[BV1963]

Do you get an error trying to allocate big sort segment?

[jeram2001]

Hi...

I got the same problem, I can release some space in TEMP after running this query :
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks

Then kill the session which is holding the segments, if you have TOP Session utility from OEM it will be easier...

If you restart the DB will solve the problem...

TIA
Jeram
Oracle DBA

[slimdave]

If you restart the DB will solve the problem...

Which problem is that?

[adewri]

Originally posted by vr76413
____________________________________________________________
It shows 98% full b/c Oracle preallocated the tablespaces.
___________________________________________________


Thanks Mike2000...


I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...

reason is , my other 9i DB TEMP tbs is only 17% Full..


any help is appreciated...

thanks
ron

Are you sure that in your other DB the TEMP tablespace is a temporary tablespace and not permanent tablespace.

Do a select tablespace_name,file_name from dba_temp_files to find out if its actually a temporary tablespace.

Because if its a LMT Temporary tablespace then the extents are
pre-allocated.

sys@ACME.WORLD> create temporary tablespace TEMP_TBS tempfile 'e:/temp_tbs01.dbf' size 50M;

Tablespace created.

sys@ACME.WORLD> select tablespace_name,file_id,block_id,blocks,bytes/1024/1024 from v$temp_extent_map
  2  where tablespace_name = 'TEMP_TBS';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID     BLOCKS BYTES/1024/1024
------------------------------ ---------- ---------- ---------- ---------------
TEMP_TBS                                2          9        128               1
TEMP_TBS                                2        137        128               1
TEMP_TBS                                2        265        128               1
TEMP_TBS                                2        393        128               1
TEMP_TBS                                2        521        128               1
TEMP_TBS                                2        649        128               1
TEMP_TBS                                2        777        128               1
TEMP_TBS                                2        905        128               1
TEMP_TBS                                2       1033        128               1
TEMP_TBS                                2       1161        128               1
TEMP_TBS                                2       1289        128               1
TEMP_TBS                                2       1417        128               1
TEMP_TBS                                2       1545        128               1
TEMP_TBS                                2       1673        128               1
TEMP_TBS                                2       1801        128               1
TEMP_TBS                                2       1929        128               1
TEMP_TBS                                2       2057        128               1
TEMP_TBS                                2       2185        128               1
TEMP_TBS                                2       2313        128               1
TEMP_TBS                                2       2441        128               1
TEMP_TBS                                2       2569        128               1
TEMP_TBS                                2       2697        128               1
TEMP_TBS                                2       2825        128               1
TEMP_TBS                                2       2953        128               1
TEMP_TBS                                2       3081        128               1
TEMP_TBS                                2       3209        128               1
TEMP_TBS                                2       3337        128               1
TEMP_TBS                                2       3465        128               1
TEMP_TBS                                2       3593        128               1
TEMP_TBS                                2       3721        128               1
TEMP_TBS                                2       3849        128               1
TEMP_TBS                                2       3977        128               1
TEMP_TBS                                2       4105        128               1
TEMP_TBS                                2       4233        128               1
TEMP_TBS                                2       4361        128               1
TEMP_TBS                                2       4489        128               1
TEMP_TBS                                2       4617        128               1
TEMP_TBS                                2       4745        128               1
TEMP_TBS                                2       4873        128               1
TEMP_TBS                                2       5001        128               1
TEMP_TBS                                2       5129        128               1
TEMP_TBS                                2       5257        128               1
TEMP_TBS                                2       5385        128               1
TEMP_TBS                                2       5513        128               1
TEMP_TBS                                2       5641        128               1
TEMP_TBS                                2       5769        128               1
TEMP_TBS                                2       5897        128               1
TEMP_TBS                                2       6025        128               1
TEMP_TBS                                2       6153        128               1

49 rows selected.

sys@ACME.WORLD>

See above, here i just created a temporary tablespace of size 50M and all extents were preallocated.

HTH

[grjohnson]

I'll give you guys a little tip also regarding Oracle 9i's TEMP tablespace DATATFILES that caught me a while ago.

Just say you have a 10 GB filesystem on UNIX, and we've decide to place a say... 2 4GB datafiles an a 2 GB Temp datafile under it.

If you check the size of the files system... you'll see that there is still 2GB left on the filesystem (even though you've allocated the full 10GB). This is because even though you specified the TEMP TABLESPACE 2GB it's not utilised on unix until the TEMP tablespace is written to. Therefore, a while down the track, somebody comes along and notices 1.5 GB of free filesystem space under the file system and creates another 1.5GB datafiles... guess what, when you're TEMP tablespace starts being used again... it runs out of space.

Just thought I'd mention it...

Bye...