Resizing a temporary tablespace

[yakum0]

I would like to resize the temporary tablespace. It was increased in size to cope with the creation of a large index. However I would like to now recover the disk space by reducing it's size back to what it was.

What I'd like to know is for a temporary tablespace do I need to resize it whilst in restricted mode or in nomount mode i.e. while there are no users on the system? (note there is only one temporary tablespace defined)

Also, can you tell me what the syntax command is for resizing a temporary tablespace? i.e is it the same syntax as for resizing any other tablespace/datafile?

Thanks in advance.

 

[SantaMufasa]

Actually, Yakum, I have successfully "resized" my TEMP tablespaces on several occasions, but it has been under a slightly different syntactical scenario.

I use standard TEMP tablespaces. I create the new TEMP tablespace at minimum initial size, and then I let the TEMP tablespace grow on a just-in-time, as-needed basis with AUTOEXTENDing files. I then drop the old (way too large) TEMP tablespace and drop/rm its associated files. Also remember to reassign users to the new TEMP tablespace. (If you do all of this when no one else is on the system (e.g. "STARTUP RESTRICT"), then you can drop the old tablespace and create the new TEMP tablespace using the "reuse" option and not need to reassign the users.).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.