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

You May Also Like

How to recreate MGMT database in ORACLE 12C GRID
how to change archivelog destination in oracle
sec_case_sensitive_logon parameter in oracle
ORA-01536: space quota exceeded for tablespace
TNS-12542: TNS:address already in use
Important views in For Oracle DBA in daily usage
Upgrade database using OEM 12C cloud control
Lock account automatically with INACTIVE_ACCOUNT_TIME
SGA TARGET ADVISORY IN ORACLE
Deinstall Management Agents Oracle 12c cloud control

From This Website

How to drop SQL baselines in oracle
Useful DGMGRL commands in oracle dataguard
How to use expdp to export data from physical standby database
How to install OPatch in ORACLE RAC
How to find weblogic version in oracle weblogic 12
Upgrade grid infrastructure to 19C oracle
Tablespace monitoring shell script
Shell script to monitor goldengate process
_optimizer_ignore_hint
How to enable flash recovery area in oracle database