SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE
SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 50M reuse autoextend on maxsize 500M;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
Now try to drop the old undotablespace.
SQL> drop tablespace UNDOTBS1 including contents;
Tablespace dropped.
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs02.dbf UNDOTBS2
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE
