sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【学习笔记】清除回滚段之更改数据字典删除回滚段和undo tablespace

时间:2016-11-15 19:55   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库回滚段操作的笔记,详细介绍在遇到Oracle回滚段异常时手动清除回滚段操作的方法通过更改数据字典表来删除回滚段和undo tablespace。

SQL> SHOW parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       INTEGER     900
undo_tablespace                      string      UNDOTBS3

SQL> conn roger/roger
Connected.

SQL> SELECT COUNT(*) FROM ht_01;

  COUNT(*)
----------
         3

SQL> DELETE FROM ht_01 WHERE rownum <2;

1 ROW deleted.

SQL> -- 不提交

SQL> SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2          5        310        345          7          6

SQL> SELECT usn,name FROM v$rollname WHERE usn=2;

       USN NAME
---------- --------------------------------------------------
         2 _SYSSMU2$

SQL> ALTER system SET undo_tablespace=undotbs4;

System altered.

SQL> ALTER DATABASE datafile 7 offline;

DATABASE altered.


SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.
DATABASE opened.

SQL> DROP tablespace undotbs3 including contents AND datafiles;
DROP tablespace undotbs3 including contents AND datafiles
*
ERROR at line 1:
ORA-01548: active ROLLBACK segment '_SYSSMU2$' found, terminate dropping tablespace

SQL> SELECT ts# FROM ts$ WHERE name='UNDOTBS3';

       TS#
----------
         7

SQL> SELECT file#,block#,TYPE#,TS# FROM seg$ WHERE ts#=7;

     FILE#     BLOCK#      TYPE#        TS#
---------- ---------- ---------- ----------
         7          9         10          7
         7         25         10          7
         7         41         10          7
         7         57         10          7
         7         73         10          7
         7         89         10          7
         7        105         10          7
         7        121         10          7
         7        137         10          7
         7        153         10          7
         7        265          3          7
         7        281         10          7

12 ROWS selected.

SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
  2  FROM dba_rollback_segs
  3  WHERE file_id=7;

OWNER  SEGMENT_NAME  SEGMENT_ID    FILE_ID   BLOCK_ID  STATUS
------ ------------- ---------- ---------- ----------  ----------------
PUBLIC _SYSSMU2$              2          7        281  NEEDS RECOVERY
PUBLIC _SYSSMU21$            21          7          9  OFFLINE
PUBLIC _SYSSMU22$            22          7         25  OFFLINE
PUBLIC _SYSSMU23$            23          7         41  OFFLINE
PUBLIC _SYSSMU24$            24          7         57  OFFLINE
PUBLIC _SYSSMU25$            25          7         73  OFFLINE
PUBLIC _SYSSMU26$            26          7         89  OFFLINE
PUBLIC _SYSSMU27$            27          7        105  OFFLINE
PUBLIC _SYSSMU28$            28          7        121  OFFLINE
PUBLIC _SYSSMU29$            29          7        137  OFFLINE
PUBLIC _SYSSMU30$            30          7        153  OFFLINE

11 ROWS selected.

SQL> UPDATE seg$ SET TYPE# = 3 WHERE ts#=7 AND file#=7 AND BLOCK#=281;
1 ROW updated.

SQL> commit;
Commit complete.

SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.
DATABASE opened.                                                                      

SQL> SELECT US# ,NAME,FILE#,BLOCK# FROM undo$ WHERE file#=7;

       US# NAME               FILE#     BLOCK#
---------- ---------------  ------- ----------
         1 _SYSSMU1$              7        265
         2 _SYSSMU2$              7        281
        21 _SYSSMU21$             7          9
        22 _SYSSMU22$             7         25
        23 _SYSSMU23$             7         41
        24 _SYSSMU24$             7         57
        25 _SYSSMU25$             7         73
        26 _SYSSMU26$             7         89
        27 _SYSSMU27$             7        105
        28 _SYSSMU28$             7        121
        29 _SYSSMU29$             7        137
        30 _SYSSMU30$             7        153

12 ROWS selected.         

SQL> DELETE FROM undo$ WHERE ts#=7 AND US#=2;
1 ROW deleted.

SQL> DELETE FROM seg$ WHERE ts#=7 AND file#=7 AND block#=281;
1 ROW deleted.

SQL> commit;
Commit complete.

SQL> DROP ROLLBACK segment "_SYSSMU2$";
DROP ROLLBACK segment "_SYSSMU2$"
*
ERROR at line 1:
ORA-01545: ROLLBACK segment '_SYSSMU2$' specified NOT available

-- 由于我们已经从几个数据字典表中将该段清除了,所以需要用包检查下

SQL> EXECUTE hcheck.FULL
PL/SQL PROCEDURE successfully completed.

SQL> DROP ROLLBACK segment "_SYSSMU2$";
ROLLBACK segment dropped.

SQL> DROP tablespace undotbs3 including contents AND datafiles;
DROP tablespace undotbs3 including contents AND datafiles
*
ERROR at line 1:
ORA-01561: failed TO remove ALL objects IN the tablespace specified

对于该错误,处理起来就非常容易了,如下:

SQL> UPDATE seg$ SET TYPE#=3 WHERE ts#=7;
11 ROWS updated.

SQL> commit;
Commit complete.

SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.
DATABASE opened.

SQL> DROP tablespace undotbs3 including contents AND datafiles;
Tablespace dropped.

-- Drop Tablespace 成功

简单的总结下,其实我们只要通过如下步骤就能轻易的删除:
1. 将回滚段更改为临时段
2. 重启实例
3. 从seOracleоg$中删除记录
4. 从undo$中删除记录


需要注意一下的是,如果不使用hcheck.full 那么直接drop tablespace可能遇到如下错误:

SQL> DROP tablespace undotbs2 including contents AND datafiles;
DROP tablespace undotbs2 including contents AND datafiles
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [5], [6], [25], [], [], [], []

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】清除回滚段之更改数据字典删除回滚段和undo tablespace

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1280.html

Oracle研究中心

关键词:

通过更改数据字典表来删除回滚段和undo tablespace

手动清除回滚段案例

遇到Oracle回滚段异常如何手动清除

ORA-01548