Can’t drop UNDO TABLESPACE

oracleoracle-11grac

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following:

CREATE UNDO TABLESPACE UNDOTBS20140508  DATAFILE '+DATA/....../UNDOTBS20140508' SIZE 20G;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES;

When trying to drop the UNDOTBSX02 it show the following error:

Error starting at line : 13 in command -
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 57
ORA-30013: undo tablespace 'UNDOTBSX02' is currently in use
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I checked UNDOTBSX02 for any pending transaction and it is empty.
So how to drop it?

Note : I am not Oracle DBA, but I have to do this task.

Best Answer

I tried the above suggestions, but did not work, such as select SQL did not get results and I still cannot drop the undo table space. The fundamental issue is that the old undo table space is still registered in Oracle as its undo, therefore we need to change it to the new undo table space.

Here are what I did and it worked:

  1. create a new UNDO table space:
CREATE SMALLFILE UNDO TABLESPACE "UNDO" 
DATAFILE '+DATA/t2/datafile/undo_01.dbf' SIZE 10G 
REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10G;
  1. Switch the new UNDO to the "official" undo table space:
alter system set undo_tablespace='UNDO' scope=both;
  1. drop the old undo table space:
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

hope this helps.

Related Question