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

You May Also Like

How to drop a database in postgres
How to generate ADDM report
How to recreate MGMT database in ORACLE 12C GRID
Flashback primary database in dataguard environment
What is colored sql_id in dba_hist_colored_sql
Standby redologs in oracle dataguard
LREG Background Process in oracle
How to move controlfile to a new location in oracle
How to change flash recovery area location
REUSE_DUMPFILES parameter in EXPDP

From This Website

Schema replication using oracle goldengate
Shell script for monitoring Alert log
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Useful TFACTL commands
PDB Lockdown Profiles in Oracle 12.2
ERROR: No checkpoint table specified for ADD REPLICAT
Get sid from ospid
How to run expdp in pluggable database(PDB)
Apply patch on oracle 12.2 database ( Release update)
Shell script to monitor asm diskgroup usage