Tuesday, May 1, 2018

ORA-01548: active rollback segment ‘_SYSSMU1_xxxx’ found, terminate dropping tablespace while deleting UNDOTBS1


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> 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
...
...
...
_______________________________________________________________________


No comments:

Post a Comment