This discussion is archived
5 Replies Latest reply: Dec 19, 2010 10:19 PM by Chinar RSS

Switch to new temporary tablespace

672680 Pro
Currently Being Moderated
How can I switch to a newly created temporary tablespace?
anyone could help provide the detailed steps of doing this?

Also, if I can see two temporary tablespaces from dba_tablespaces view, then how can I know which one is in use?
NOte: these two temporary tablespace do not belong to same tablespace group.
  • 1. Re: Switch to new temporary tablespace
    sb92075 Guru
    Currently Being Moderated
    How can I switch to a newly created temporary tablespace?
    anyone could help provide the detailed steps of doing this?
    ALTER USER2 TEMPORARY TABLESPACE TEMP2;
    Also, if I can see two temporary tablespaces from dba_tablespaces view, then how can I know which one is in use?
    Both can be "in use" by different users
  • 2. Re: Switch to new temporary tablespace
    Aman.... Oracle ACE
    Currently Being Moderated
    PhoenixBai wrote:
    How can I switch to a newly created temporary tablespace?
    anyone could help provide the detailed steps of doing this?
    Assumingly that you have Temp1 as the default tablespace for the database, you would do this
    alter database default temporary tablespace TEMP2;
    This would change the new default for the database to Temp2. For the users, who have been explicitly assigned the tablespcae Temp1 , you would need to change that using the
    alter user <name> default temporary tablespace Temp2
    Also, if I can see two temporary tablespaces from dba_tablespaces view, then how can I know which one is in use?
    NOte: these two temporary tablespace do not belong to same tablespace group.
    The group or not doesn't matter. Check with the view database_properties .
    SQL> select property_name, property_value
      2   from database_properties
      3   where property_name like '%TEMP%';
    
    PROPERTY_NAME
    ------------------------------
    PROPERTY_VALUE
    --------------------------------------------------------------------------------DEFAULT_TEMP_TABLESPACE
    TEMP
    HTH
    Aman....

    PS: Please make sure that you always mention your 4 digit database version with every post.
  • 3. Re: Switch to new temporary tablespace
    672680 Pro
    Currently Being Moderated
    version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    one more question,

    suppose default temporary tablespace is TEMP while some users are assigned with new temporary tablespace TEMP2, then which tablespace will they use for their query? Will they use TEMP2?

    if so, can I say that, for database, it can`t have multiple undo tablespace IN USE at the same time while it can have multiple temp tablespace in use?
  • 4. Re: Switch to new temporary tablespace
    CKPT Guru
    Currently Being Moderated
    create a new temporary tablespace..

    again you need to spool all the users. because you have to assign new temporary tablespace..

    it is not quiet compilcated as to switch undo ;-)
    Thanks
  • 5. Re: Switch to new temporary tablespace
    Chinar Guru
    Currently Being Moderated
    PhoenixBai wrote:
    version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    one more question,

    suppose default temporary tablespace is TEMP while some users are assigned with new temporary tablespace TEMP2, then which tablespace will they use for their query? Will they use TEMP2?

    if so, can I say that, for database, it can`t have multiple undo tablespace IN USE at the same time while it can have multiple temp tablespace in use?
    Yes that is possible.So there can be some users default tablespace TEMP which its also default database temporary tablespace.But some user`s temporary tablespace can TEMP2.(If you explicitly executed ALTER USER <username> TEMPORARY TABLESPACE TEMP2).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points