Temp tablespace full

[mscim]

Hi, my Oracle 8.1.7.4 database uses a localy managed temp tablespace. But it seems to be growing at an fast rate and there are no signs of the database reclaiming it. How do we reclaim the temp tablespace?

 

[sybaseguru]

This should be automatically done by Oracle. First add another temporary tablespace using "create temporary tablespace" command or adding another datafile to the existing temp tablespace depending on what you prefer.

Temporary tables should get rid of temporary data once the commit is done or the session is ended. This is the default behaviour of Oracle. However, you should be aware that Oracle will use temp tablespace if the PGA (Program/User global Area) is set too small. PGA has a sort area used by Oracle for sort (distinct, order by, group by) and set operations (union,interset, minus). If your temp tablespace is growing and space cannot be reclaimed then you should look at the size of PGA. It appears that temp tablespace is being used constantly for sort activity. Check parameters SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. Also look at the parameter PGA_AGGREGATE_TARGET (total amount of memory that can be used by all servewr processes), though I am not sure the last parameter can be used in 8i.

Good luck

 

[alexhu]

If you running UNIX use these 2 scripts together to find out what is using temp space. This works on 8.0, don't know about 8.1 (assuming you have an OPS$ORACLE user)

Alex

sqlplus / << EOF
set line 130
set pagesize 100
set feedback off
column tablespace format a10
select user, session_num, sqlhash, extents, blocks, tablespace from v\$sort_usage
/
EOF


echo &quot;Enter hash value&quot;
read HASH
sqlplus / << EOF
set line 130
set pagesize 100
set feedback off
column tablespace format a10
select osuser, process from v\$session where sql_hash_value like '%$HASH%'
/
EOF