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.