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
