Wednesday, August 22, 2012

Change XS$NULL Default tablespace

Oracle database not allow SYS alter user xs$null, from version 11.2.0.1. It explains why I get ORA-01031: insufficient privileges when I try to alter its default tablespace.

SQL>  alter user xs$null default tablespace TBS_1 quota unlimited on TBS_1;
 alter user xs$null default tablespace TBS_1 quota unlimited on TBS_1
*
ERROR at line 1:
ORA-01031: insufficient privileges
 
But still you can change it by setting the default tablespace name with out giving XS$NULL in the statement . 

SQL> alter database default tablespace TBS_1;

Database altered.

SQL> select default_tablespace from dba_users where username='XS$NULL';

DEFAULT_TABLESPACE
------------------------------
TBS_1
 
Ref: MOS ID 1325766.1, “nobody eve user SYS can alter xs$null“.  
Posted by at