5 Respostas Última resposta: 20/12/2010 00:19 por Chinar RSS

    Switch to new temporary tablespace

    672680
      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
          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....
            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
              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
                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
                  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).