PROBLEM:
While creating an user as below got error as ORA-30034:Undo tablespace cannot be specified as temporary tablespace
SQL> create user DBATEST1 identified by DBATEST default tablespace USERS TEMPORARY TABLESPACE UNDOTBS1 ;
create user DBATEST1 identified by DBATEST default tablespace USERS TEMPORARY TABLESPACE UNDOTBS1
*
ERROR at line 1:
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
SOLUTION:
This is because, we cannot set an undo tablespace as temporary tablespace for the user.
In below can see UNDOTBS1 is an undo tablespace.
SQL> select distinct tablespace_name from DBA_UNDO_EXTENTS ;
TABLESPACE_NAME
--------
UNDOTBS1
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------ ---- ---------
undo_tablespace string UNDOTBS1
So always assign an temp tablespace as temporary tablespace for an user.
SQL> create user DBATEST1 identified by DBATEST default tablespace USERS TEMPORARY TABLESPACE TEMP ;
User created
TROUBLESHOOTING