Wednesday, March 19, 2014

Change db user`s default tablespace

Today I found on my friend`s database some application user`s default tablespace is SYSTEM which is not recommended by Oracle.

Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP.

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';  
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    SYSTEM  
 APPLDESK   SYSTEM  
 APPLTEST    SYSTEM  

Now, use below little bit PLSQL block.

 BEGIN  
  FOR i IN (  
            SELECT username FROM dba_users   
            WHERE default_tablespace in ('SYSTEM') and username like 'APP%'  
          ) LOOP  
   EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS';  
  END LOOP;  
 END;  
 /  

Check

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';   
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    USERS  
 APPLDESK   USERS  
 APPLTEST    USERS

No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...