Tuesday, August 31, 2010

Delete undo tablespace and create undotablespace

In oracle how to delete the undo tablespace. Whenever I am trying to delete it I am getting the following message

SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Below is also is solution if you have delete the undo datafile and after that getting following message on every start up

SQL> startup
ORACLE instance started.

Total System Global Area 544030720 bytes
Fixed Size 1337776 bytes
Variable Size 406849104 bytes
Database Buffers 130023424 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/u01/app/oracle/oradata/orcl/undotbs02.dbf'




Following is how you can do this:

SQL> alter system set undo_tablespace = '';

System altered.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

No comments: