ORA-30034 Undo Tablespace Cannot Be Specified As Temporary Tablespace

 

ORA-30034 :Undo Tablespace Cannot Be Specified As Temporary Tablespace

While Creating an user in temp tablespace user got an error  ORA-30034 :Undo tablespace cannot be specified as temporary tablespace

SQL> create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE UNDOTBS03;

create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE UNDOTBS03;

* 

ERROR ar line 1:

ORA-30034 : Undo tablespace cannot be specified as temporary tablespace

Solution

We cannot set an Undo tablespace has a temporay tablespace for the user ,UNDOTBS03 is a undo tablespace ,so we have to give temp name for the tablespace then it will create user  

 SQL>select distinct tablespace_name from DBA_UNDO_EXTENTS;

TABLESPACE_NAME

----------------------------

UNDOTBS03

SQL>show parameter undo_tablespace;

NAME                   TYPE                VALUE

-------------------------------------------------

undo_tablespace      string              UNDOTBS03

always assign an temp tablespace as temporary  tablespace for an user not for undotablesapce

SQL> create user CHAITANYA  identified by chai123 default tablespace USERS TEMPORARY TABLESPACE TEMP;

user created

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME