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

ORA-32773 Operation not Supported for SmallFile Tablespace

ORA-32773 : Operation not Supported for SmallFile Tablespace

ORA-ERROR 32773 : Operation not Supported for SmallFile Tablespace

Problem: While Enabling autoextend for a Tablespace,it will get an error like ORA-32773 :Operation not supported for small file table space

SQL>alter tablespace CHAITANYASMALLTBS autoextend on;

alter tablespace CHAITANYASMALLTBS autoextend on

*

ERROR ar line 1:

ORA_32773:operation not supported for small file tablespace  CHAITANYASMALLTBS

Solution:

A standard tablespace that is small file tablespace can have multiple datafiles,so if you want to enable or disable autoextend on so you have done for each datafile not for tablespace  it cannot be done directly on the tablespace

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME                     BIGFILE

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

CHAITANYASMALLTBS                     NO

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

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

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     NO

SQL>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu02.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu03.dbf' autoextend on;

Database altered;

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

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

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     YES

In BIGFILE TABLESPACE we can do autoextensible in tablelevel also but it have only one datafile 

SQL> create bigfile tablespace CHAITANYABIGFTBS datafile '/oradata/prod/chaitubigfile01.dbf' size 2G;

Tablespace Created.

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

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

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         NO

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like ' CHAITANYABIGFTBS';

TABLESPACE_NAME               BIG

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

CHAITANYABIGFTBS                YES

SQL>alter tablespace CHAITANYABIGFTBS autoextend on;

Tablespace altered

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

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

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         YES

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