处理undo回滚段问题

最新推荐文章于 2021-04-09 12:53:19 发布

MissYouFY

最新推荐文章于 2021-04-09 12:53:19 发布

阅读量561

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/MissYouFY/article/details/78201542

删除undo表空间失败，显示有活动的回滚段

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_1401565358$' found, terminate dropping tablespace

要恢复的回滚段

SQL> select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         1 _SYSSMU1_1401565358$           NEEDS RECOVERY   UNDOTBS1
         2 _SYSSMU2_3125365238$           NEEDS RECOVERY   UNDOTBS1
         3 _SYSSMU3_1538315859$           NEEDS RECOVERY   UNDOTBS1
         4 _SYSSMU4_1640924022$           NEEDS RECOVERY   UNDOTBS1
         5 _SYSSMU5_2892967416$           NEEDS RECOVERY   UNDOTBS1
         6 _SYSSMU6_3276341082$           NEEDS RECOVERY   UNDOTBS1
         7 _SYSSMU7_387283697$            NEEDS RECOVERY   UNDOTBS1
         8 _SYSSMU8_2299136685$           NEEDS RECOVERY   UNDOTBS1
         9 _SYSSMU9_909303715$            NEEDS RECOVERY   UNDOTBS1
        10 _SYSSMU10_1695440836$          NEEDS RECOVERY   UNDOTBS1

尝试删除失败
SQL> drop  rollback segment "_SYSSMU10_1695440836$";
drop  rollback segment "_SYSSMU10_1695440836$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU10_1695440836$' (in undo tablespace) not allowed

修改参数文件pfile
其中_offline_rollback_segments参数中的回滚段的名字在上面查到过。

*.undo_management='MANUAL'
*._offline_rollback_segments=(_SYSSMU1_1401565358$,_SYSSMU2_3125365238$,_SYSSMU3_1538315859$,_SYSSMU4_1640924022$,_SYSSMU5_2892967416$,_SYSSMU6_3276341082$,_SYSSMU7_387283697$,_SYSSMU8_2299136685$,_SYSSMU9_909303715$,_SYSSMU10_1695440836$)

SQL> startup pfile='/tmp/pfile.ora';
SQL>  drop rollback segment "_SYSSMU10_1695440836$";
SQL> drop tablespace undotbs1 including contents and datafiles;
SQL>  select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

改完后，关闭数据库把undo_managment改为自动。