Originally posted by Ablakios
ramji

If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
1) I was unable to resize it
2) will not deallocate even when there is no sorting going and I hate
to see 8 gig allocated to temporary when I could help with load balancing.
So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:

1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
**************************
SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
FROM v$session t1, V$sort_usage t2
WHERE t1.saddr = t2.session_addr ;

If no is returned proceed withn the 2nd step.

2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
********************************
****Alter tablespace temp coalesce ;

3) THIRD RUN THIS TO RECLAIM THE SPACE
***************************
alter tablespace temp
default storage (pctincrease 1);

FOLLOWED BY

alter tablespace temp
default storage (pctincrease 0);

On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
HTH
Sounds like you've got yourself a job for life there.

How often do you have to resize TEMP? Pretty rarely, i would think.

Why do you need to coalesce free space?
Because you are using a dictionary managed tablespace.

Why do you hate it that the temp space is allocated in an LMT, even when it's not actually in use?
Because it's not what you're used to.

You know how to load balance TEMP? Use a LMT, uniform extent size equal to sort_area_size, and multiple datafiles spread across all your devices. Very easy.


So for interfering with progress and artificially inflating your own workload at the expense of good DB management techniques, you're fired.