Convert permanent tablespace to temporary

[Ro2a.M]

Hello all,
I'm working on oracle 10g,
I created a tablespace TEMP1,

CREATE TABLESPACE TEMP1
DATAFILE 'D:\oracle\DISK2\temp1.dbf' SIZE 1M;

and I want to convert it to temporary tablespace so
I used:

Alter tablespace "TEMP1" TEMPORARY;
ERREUR Ó la ligne 1 :
ORA-10616: Operation not allowed on this tablespace

I don't understand why I cannot do that, knowing that there is absoletly nothing in my tablespace.

I know that I can drop it and create it as temporary, but I'm really curious to know why that doesn't work and how I can do it?

Best regards,
Baraa

[vnktummala]

"permanent" & "temporary" table spaces are ment for two different purposes. In permanent TS you can store the objects permanently where as temporary tablespace is used for sorting operations. Oracle will not allow to convert permanent tablespace into temp tablespace and vise-versa no matter if the tablespace is empty.

If you want to know more indepth about this then you need to dig into oracle internals.

Regards,

[Ro2a.M]

thank you for your answer,

But I cannot understand; since this is not allowed why the query
Alter tablespace "TEMP1" TEMPORARY;
exists??

and I found on the internet that it works fin for some people so I would like to know if that is a version issue (this was possible for earlier versions of oracle and now this is not possible anymore).

Best regards,

[vnktummala]

I never see the syntax you are talking about at least in 10g. Check this link.

Regards,

[Ro2a.M]

It exits in the link that you sent me, precisely, in the tablespace_state_clauses
we have temporary/permanent



http://docs.oracle.com/cd/B19306_01/...te_clauses.htm

Thank you,

Regards

[PAVB]

Why not dropping the unwanted one then create your temp tablespace as most people in the world would do? ;-)

[vnktummala]

Thanks for highlighting Barra, I never used this option. I will check now ...

However, Paul's solution is most widely followed one ... unless until you are just doing some R&D.

Regards,