How to Convert Tablespace to Temporary.

[patel_dil]

Hi All,
I have a Database in which temporary Tablespace is defined with Datafiles instead of Temp Files. What steps I need to follow to convert this 'Temp' Tablespace(Dictionary managed) into True Temporary Tablespace. The users are created with 'this tablespace' as temporary Tablespace.

Thanks.

[jovery]

Just use command:

ALTER TABLESPACE "TEMP" TEMPORARY;

Regards

[patel_dil]

Thanks Jovery, But is it okay if I run this command with active users or Do I need to ask them to stop working till this conversion is done.

Thanks.

[jovery]

I think assuming there are no temporary segments in use it is okay to do it while users are accessing the system.

Regards

[ales]

Correct me if I'm wrong, but I think it's not possible to convert a dictionary managed temporary tablespace to a locally managed temporary tablespace just with "ALTER TABLESPACE TEMPORARY".

[Tarry]

For a true temporary tablespace --
Do
drop tablespace temp;
then
create temporary tablespace temp tempfile '/bla/bla'...

Cheers

[Tarry]

Maybe a little more clearer....

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

appdev@NICK.WAGENBORG.COM>alter tablespace TEMP temporary;
alter tablespace TEMP temporary
*
ERROR at line 1:
ORA-01661: tablespace 'TEMP' is already temporary


appdev@NICK.WAGENBORG.COM>alter tablespace TEMP permanent;

Tablespace altered.

appdev@NICK.WAGENBORG.COM>alter tablespace TEMP temporary;

Tablespace altered.



appdev@NICK.WAGENBORG.COM>drop tablespace TEMP;

Tablespace dropped.

appdev@NICK.WAGENBORG.COM>CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE 
  2  'D:\Oracle\oradata\nick\LMT_TEMP01.dbf' size 60M reuse
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Tablespace created.

Cheers!!

[patel_dil]

My 70th Post.

Thanks Tarry, this is what I wanted. Ales, you was rite. I had to drop the Tablespace and recreate it.

[Raminder]

If I can just add, one must check V$SORT_USAGE to be sure no body is using temp segments, before dropping the temp tablespace.