Temporary datafile - truncate

[rshivagami]

Hi all, I am using Oracle 8.1.6 on Solaris. If I want to free up some space on the server, can I truncate the data in my temporary datafile, If yes, then what are the other issues I need to take care of ?

Thanks.

[Sharma]

You can't truncate from the temporary datafiles. Truncate can be issued against table data but the tables are not stored in temporary tablespace.

[rshivagami]

My temporary datafile is 3 GB in size. Is there no way I can shirnk that space??

[raghud]

We have to think so many factors .

!. check you have so many sorts against the database sql's or
2. When created temp space you might have bigger initial extent /size..

Second case is if you are not using 3Gb temp space just drop it and recreate it with less size making sure currently you don't have data on temp space ...

[rshivagami]

So raghu, you mean to say that taking the tablespace offline, dropping & recreating the datafile should work fine ? What are the other issues I need to take care of ?

Shiva.

[grjohnson]

YOu can RESIZE datafiles smaller, but you can't RESIZE to a size SMALLER than the datafiles HighWatermark.

[KenEwald]

I believe you can resize the data files for ANY tablespace, provided you don't have data blocks beyond the new EOF (End of File) marker and what GRJOHNSON said about the HighWatermark

-Ken

[Edited by KenEwald on 03-11-2002 at 06:39 PM]