how to reduce size of datafile

[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