Skip to content
August 15, 2016 / doganay

ORA-01548 WHEN DROPPING UNDO TBS

SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_2842692639$' found, terminate dropping tablespace

SQL> select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1';

SQL> drop rollback segment "_SYSSMU11_2842692639$";
drop rollback segment "_SYSSMU11_2842692639$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU11_2842692639$' (in undo tablespace) not allowed

SQL> alter system set "_offline_rollback_segments"='_SYSSMU11_2842692639$','_SYSSMU12_3631842673$','_SYSSMU13_543390606$','_SYSSMU14_1141270304$','_SYSSMU15_4256630628$','_SYSSMU16_1706077410$','_SYSSMU17_2943159071$','_SYSSMU18_4202619447$','_SYSSMU19_437051883$','_SYSSMU20_3541319746$' scope=spfile;

SQL> shu immediate
SQL> startup

SQL> select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name='UNDOTBS1';

SQL> drop tablespace UNDOTBS1;

SQL> create undo tablespace UNDOTBS2;

SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile;
SQL> alter system set undo_management=AUTO  scope=spfile;

SQL> shu immediate
SQL> startup

SQL> alter system reset "_offline_rollback_segments";
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: