2 Respostas Última resposta: 29/12/2010 11:33 por 407048 RSS

    alter user default tablespace and temporary tablespace

    760019
      Hi guru,

      target : to ensure that users don't have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace

      scenario :
      user default tablespace temporary tablespace
      -------- ------------------------------ ---------------------------
      xxyym system system

      Question: How to alter user ?

      tq
        • 1. Re: alter user default tablespace and temporary tablespace
          MaJo
          Hello !

          use
          alter user <username> default tablespace <tablespace_name>;
          and
          alter user <username> temporary tablespace <temp tablespace_name>;

          also issue
          ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <temp tablespace_name>;
          it will give the temp tablespace as default value when creating users using OEM
          and assign it if you omit the temporary tablespace when creating a user in sqlplus

          Do the same with
          ALTER DATABASE DEFAULT TABLESPACE <tablespace_name>;
          for your normal tablespaces.

          Regards
          Magnus
          • 2. Re: alter user default tablespace and temporary tablespace
            407048
            In a scenario, let's say you want to make USERS the default tablespace for existing users and TEMP the default temporary tablespace, you can also create the alter statements as below into one script based on the output and run it.
            select 'ALTER USER '||username||' DEFAULT TABLESPACE USERS;'  FROM DBA_USERS WHERE DEFAULT_TABLESPACE IN('SYSTEM')
            and username not in('SYS','SYSTEM');
            select 'ALTER USER '||username||' TEMPORARY TABLESPACE TEMP;'  FROM DBA_USERS WHERE TEMPORARY_TABLESPACE IN('SYSTEM');
            As magnus mentioned, don't forget to do this(if USERS and TEMP is what you want to go with) :
            ALTER DATABASE DEFAULT TABLESPACE USERS;
            ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;