This discussion is archived
2 Replies Latest reply: Dec 29, 2010 9:33 AM by 407048 RSS

alter user default tablespace and temporary tablespace

760019 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;

Legend

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