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

You May Also Like

Purge AUD$ table using DBMS_AUDIT_MGMT
How to run expdp in pluggable database(PDB)
_use_adaptive_log_file_sync parameter in oracle
Useful flashback related commands
How to recreate physical standby controlfile
Defgen utility in Oracle goldengate
How to generate ADDM report
oracleasm utility for ASMLIB
Steps for changing public hostname for a standalone grid infrastructure
resumable_timeout parameter in oracle database

From This Website

Shell script to monitor asm diskgroup usage
ORA-01536: space quota exceeded for tablespace
How to Read CSV files in R studio
Apply patch on oracle 12.2 database ( Release update)
How to create user in MySQL Database
TNS-01106: Listener using listener name has already been started
How to enable active dataguard in physical standby database
TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump
Rollback database patch in oracle 12c
ORA-30034: Undo tablespace cannot be specified as temporary tablespace