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