-
Convert permanent tablespace to temporary
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
-
"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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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,
-
I never see the syntax you are talking about at least in 10g. Check this link.
Regards,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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
-
Why not dropping the unwanted one then create your temp tablespace as most people in the world would do? ;-)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|