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


Related Topics

ORA-32771: cannot add file to bigfile tablespace
ORA-16855: transport lag has exceeded specified threshold
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
ORA-01536: space quota exceeded for tablespace
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
TNS-12542: TNS:address already in use
ORA-32773: operation not supported for smallfile tablespace
TNS-01106: Listener using listener name has already been started
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
ORA-32774: more than one file was specified for bigfile tablespace

You May Also Like

TNS-12542: TNS:address already in use
How to setup dataguard broker configuration (DG broker) in 12c
WHAT IS SQL PROFILE IN ORACLE
Display sql_id of the sql, using set feedback in oracle 18C - New feature
Apply database proactive bundle patch in RAC using manual process
ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only
Apply database patch from OEM Cloud control
Enable DDL replication in goldengate
How to install trace file analyzer( TFACTL)
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c

From This Website

Useful TFACTL commands
Upgrade database using OEM 12C cloud control
ENABLE_DDL_LOGGING in oracle 12c
How to recreate MGMT database in ORACLE 12C GRID
Ansible script to run script on remote server and fetch the output
How to move controlfile to a new location in oracle
OGG-00665 OCI Error describe for query
How to find execution history of an sql_id
How to recreate physical standby controlfile
Upgrade grid infrastructure to 19C oracle