Convert permanent tablespace to temporary
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Convert permanent tablespace to temporary

  1. #1
    Join Date
    Dec 2011
    Posts
    5

    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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    "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.

  3. #3
    Join Date
    Dec 2011
    Posts
    5
    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,

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  5. #5
    Join Date
    Dec 2011
    Posts
    5
    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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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
  •  



Click Here to Expand Forum to Full Width