If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

how to reduce size of datafile

Dear all,

I am facing problem as below

i hv temp tablespace in my db. the name of
tablespace : temp (file name temp01.dbf) by default it was in autoextend datafile mode when full)
due to autoextend my developer has done some mistake in writing query statement (in loop). my temp size increase upto maximum extend & full(defined space on OS).
Now i have remove this query & restart database now used temp space is free (only zero). but now
i will try to reduce the size of temp01.dbf file but it giving error
(we can not reduce the size ..)
how to reduce the size of temp01.dbf ? please advice

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:

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: