Scenerio
Oracle Database 12c Standard Edition
Release 12.1.0.2.0 - 64bit Production.
Database - In No archivelog Mode.
Red Hat Enterprise Linux Server
release 6.5 (Santiago)
SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including
contents
*
ERROR at line 1:
ORA-01548: active rollback segment
'_SYSSMU1_1255349037$' found, terminate dropping tablespace
Workaround
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/database/as3phc/undo01.dbf' size 500m autoextend off;
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/database/as3phc/undo01.dbf' size 500m autoextend off;
SQL> ALTER SYSTEM SET
UNDO_TABLESPACE = undotbs;
SQL> drop tablespace UNDOTBS01
including contents;
drop tablespace UNDOTBS01 including
contents
*
ERROR at line 1:
ORA-01548: active rollback segment
'_SYSSMU1_1255349037$' found, terminate dropping tablespace
SQL> drop rollback segment
'_SYSSMU1_1255349037$';
drop rollback segment
'_SYSSMU1_1255349037$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment
name
SQL> select segment_name, status,
tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
-----------------------------------------------------------------------------------------------------
SYSTEM ONLINE
SYSTEM
_SYSSMU1_3780397527$ PARTLY
AVAILABLE UNDOTBS1
...
...
SQL> alter system set
"_offline_rollback_segments="_SYSSMU1_1255349037$" scope=spfile;
SQL> Shutdown immediate;
SQL> Startup
SQL> select segment_name, status,
tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
-----------------------------------------------------------------------------------------------------
SYSTEM ONLINE
SYSTEM
_SYSSMU1_1255349037$ NEEDS RECOVERY UNDOTBS1
...
...
SQL> drop tablespace UNDOTBS1
including contents and datafiles;
drop tablespace UNDOTBS1 including
contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment
'_SYSSMU1_1255349037$' found, terminate
dropping tablespace
SQL> drop rollback segment
"_SYSSMU1_1255349037$";
Rollback segment dropped.
SQL> drop tablespace UNDOTBS1
including contents and datafiles;
Tablespace dropped.
SQL> alter system reset
"_offline_rollback_segments" scope=spfile;
SQL> shut immediate;
SQL> Startup
SQL> select
segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
---------------------------------------------------------------------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1_1266566787$ ONLINE UNDOTBS
...
...
...
_______________________________________________________________________
Note : Ideas & Inspirations
