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

You May Also Like

Find the active transactions in oracle database
Shared Pool Advisory in oracle
ORA-01536: space quota exceeded for tablespace
Upgrade database using OEM 12C cloud control
Oracle database Security Assessment Tool
TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
DEFERRED_SEGMENT_CREATION parameter in oracle
Shell script for monitoring Alert log
OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2

From This Website

FLASHBACK parameter in DATAPUMP(EXPDP)
OGG-01028 partial record at sequence extract abended
Add database as target oracle 12c cloud control
SAMPLE parameter in EXPDP to export subset of data
Purge AUD$ table using DBMS_AUDIT_MGMT
Useful TFACTL commands
How to generate ADDM report
Display sql_id of the sql, using set feedback in oracle 18C - New feature
_use_adaptive_log_file_sync parameter in oracle
OGG-00665 OCI Error describe for query