ITPub博客

首页 > 数据库 > Oracle > [20160304]奇怪的回滚段3.txt

[20160304]奇怪的回滚段3.txt

原创 Oracle 作者:lfree 时间:2016-03-04 15:23:19 0 删除 编辑

[20160304]奇怪的回滚段3.txt

--相关链接:
http://blog.itpub.net/267265/viewspace-2036568/
http://blog.itpub.net/267265/viewspace-2022969/

--自己将其中2个offline的回滚段online看看:

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select * from dba_rollback_segs ;
SEGMENT_NAME           OWNER  TABLESPACE_NAME SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS  RELATIVE_FNO
---------------------- ------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------- ------------
SYSTEM                 SYS    SYSTEM                   0          1        128         114688       57344           1       32765              ONLINE             1
_SYSSMU12_3785813942$  PUBLIC UNDOTBS1                12          3        296         131072       65536           2       32765              OFFLINE            3
_SYSSMU11_1701842685$  PUBLIC UNDOTBS1                11          3        248         131072       65536           2       32765              OFFLINE            3
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                10          3        272         131072       65536           2       32765              ONLINE             3
_SYSSMU9_1650507775$   PUBLIC UNDOTBS1                 9          3        256         131072       65536           2       32765              ONLINE             3
_SYSSMU8_517538920$    PUBLIC UNDOTBS1                 8          3        240         131072       65536           2       32765              ONLINE             3
_SYSSMU7_2070203016$   PUBLIC UNDOTBS1                 7          3        224         131072       65536           2       32765              ONLINE             3
_SYSSMU6_1263032392$   PUBLIC UNDOTBS1                 6          3        208         131072       65536           2       32765              ONLINE             3
_SYSSMU5_898567397$    PUBLIC UNDOTBS1                 5          3        192         131072       65536           2       32765              ONLINE             3
_SYSSMU4_1254879796$   PUBLIC UNDOTBS1                 4          3        176         131072       65536           2       32765              ONLINE             3
_SYSSMU3_1723003836$   PUBLIC UNDOTBS1                 3          3        160         131072       65536           2       32765              ONLINE             3
_SYSSMU2_2996391332$   PUBLIC UNDOTBS1                 2          3        144         131072       65536           2       32765              ONLINE             3
_SYSSMU1_3724004606$   PUBLIC UNDOTBS1                 1          3        128         131072       65536           2       32765              ONLINE             3
13 rows selected.

SYS@book> column PARTITION_NAME noprint
SYS@book> select * from DBA_extents where segment_name in ('_SYSSMU11_1701842685$','_SYSSMU12_3785813942$');
OWNER  SEGMENT_NAME           SEGMENT_TYPE       TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ---------------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS    _SYSSMU11_1701842685$  TYPE2 UNDO         UNDOTBS1                 0          3        248      65536          8            3
SYS    _SYSSMU11_1701842685$  TYPE2 UNDO         UNDOTBS1                 1          3        152      65536          8            3
SYS    _SYSSMU11_1701842685$  TYPE2 UNDO         UNDOTBS1                 2          3       1280    1048576        128            3
SYS    _SYSSMU11_1701842685$  TYPE2 UNDO         UNDOTBS1                 3          3        512    1048576        128            3
SYS    _SYSSMU12_3785813942$  TYPE2 UNDO         UNDOTBS1                 0          3        296      65536          8            3
SYS    _SYSSMU12_3785813942$  TYPE2 UNDO         UNDOTBS1                 1          3        184      65536          8            3
SYS    _SYSSMU12_3785813942$  TYPE2 UNDO         UNDOTBS1                 2          3        768    1048576        128            3
7 rows selected.


SYS@book> alter rollback segment "_SYSSMU11_1701842685$" online;
Rollback segment altered.

SYS@book> alter rollback segment "_SYSSMU12_3785813942$" online;
Rollback segment altered.

SYS@book> select * from dba_rollback_segs where SEGMENT_ID in (11,12);
SEGMENT_NAME           OWNER  TABLESPACE_NAME  SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS           RELATIVE_FNO
---------------------- ------ ---------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ------------
_SYSSMU11_1701842685$  PUBLIC UNDOTBS1                 11          3        248         131072       65536           2       32765              OFFLINE                     3
_SYSSMU12_3785813942$  PUBLIC UNDOTBS1                 12          3        296         131072       65536           2       32765              OFFLINE                     3

--真见鬼!提示是成功了,实际上根本没有修改。

SYS@book> alter rollback segment "_SYSSMU10_1197734989$" offline;
Rollback segment altered.

SYS@book> select * from dba_rollback_segs where SEGMENT_ID in (11,12,10);
SEGMENT_NAME           OWNER  TABLESPACE_NAME                SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS           RELATIVE_FNO
---------------------- ------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ------------
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                               10          3        272         131072       65536           2       32765              ONLINE                      3
_SYSSMU11_1701842685$  PUBLIC UNDOTBS1                               11          3        248         131072       65536           2       32765              OFFLINE                     3
_SYSSMU12_3785813942$  PUBLIC UNDOTBS1                               12          3        296         131072       65536           2       32765              OFFLINE                     3

-- SEGMENT_ID = 10 依旧是online。

2.换一种方式:
SYS@book> alter system set"_smu_debug_mode" = 4 scope=memory;
System altered.

SYS@book> alter rollback segment "_SYSSMU11_1701842685$" online;
Rollback segment altered.

SYS@book> alter rollback segment "_SYSSMU12_3785813942$" online;
Rollback segment altered.

SYS@book> select * from dba_rollback_segs where SEGMENT_ID in (11,12,10);
SEGMENT_NAME           OWNER  TABLESPACE_NAME SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS           RELATIVE_FNO
---------------------- ------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ------------
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                10          3        272         131072       65536           2       32765              ONLINE                      3
_SYSSMU11_1701842685$  PUBLIC UNDOTBS1                11          3        248         131072       65536           2       32765              ONLINE                      3
_SYSSMU12_3785813942$  PUBLIC UNDOTBS1                12          3        296         131072       65536           2       32765              ONLINE                      3

--重新启动看看:
SYS@book> column SEGMENT_NAME format a22
SYS@book> select * from dba_rollback_segs where SEGMENT_ID in (11,12,10);
SEGMENT_NAME           OWNER  TABLESPACE_NAME SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS           RELATIVE_FNO
---------------------- ------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ------------
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                10          3        272         131072       65536           2       32765              ONLINE                      3
_SYSSMU11_1701842685$  PUBLIC UNDOTBS1                11          3        248         131072       65536           2       32765              OFFLINE                     3
_SYSSMU12_3785813942$  PUBLIC UNDOTBS1                12          3        296         131072       65536           2       32765              OFFLINE                     3

--再次见鬼! 还是offline。

3.既然这样删除算了。
SYS@book> alter system set"_smu_debug_mode" = 4 scope=memory;
System altered.

SYS@book> alter rollback segment "_SYSSMU11_1701842685$" offline;
Rollback segment altered.

SYS@book> alter rollback segment "_SYSSMU12_3785813942$" offline;
Rollback segment altered.

SYS@book> drop rollback segment "_SYSSMU11_1701842685$" ;
Rollback segment dropped.

SYS@book> drop rollback segment "_SYSSMU12_3785813942$" ;
Rollback segment dropped.

--如果没有设置alter system set"_smu_debug_mode" = 4 scope=memory;直接删除报错。

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


SYS@book> drop rollback segment "_SYSSMU12_3785813942$" ;
drop rollback segment "_SYSSMU12_3785813942$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU12_3785813942$' (in undo tablespace) not allowed
====================================================================================

SYS@book> column SEGMENT_NAME format a22
SYS@book> select * from dba_rollback_segs where SEGMENT_ID in (11,12,10);
SEGMENT_NAME           OWNER  TABLESPACE_NAME SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS            RELATIVE_FNO
---------------------- ------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------------  ------------
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                10          3        272         131072       65536           2       32765              ONLINE                       3

--重新启动看看:
SYS@book> column INSTANCE_NUM noprint
SYS@book> select * from dba_rollback_segs ;
SEGMENT_NAME           OWNER  TABLESPACE_NAME SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS           RELATIVE_FNO
---------------------- ------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ------------
SYSTEM                 SYS    SYSTEM                   0          1        128         114688       57344           1       32765              ONLINE                      1
_SYSSMU10_1197734989$  PUBLIC UNDOTBS1                10          3        272         131072       65536           2       32765              ONLINE                      3
_SYSSMU9_1650507775$   PUBLIC UNDOTBS1                 9          3        256         131072       65536           2       32765              ONLINE                      3
_SYSSMU8_517538920$    PUBLIC UNDOTBS1                 8          3        240         131072       65536           2       32765              ONLINE                      3
_SYSSMU7_2070203016$   PUBLIC UNDOTBS1                 7          3        224         131072       65536           2       32765              ONLINE                      3
_SYSSMU6_1263032392$   PUBLIC UNDOTBS1                 6          3        208         131072       65536           2       32765              ONLINE                      3
_SYSSMU5_898567397$    PUBLIC UNDOTBS1                 5          3        192         131072       65536           2       32765              ONLINE                      3
_SYSSMU4_1254879796$   PUBLIC UNDOTBS1                 4          3        176         131072       65536           2       32765              ONLINE                      3
_SYSSMU3_1723003836$   PUBLIC UNDOTBS1                 3          3        160         131072       65536           2       32765              ONLINE                      3
_SYSSMU2_2996391332$   PUBLIC UNDOTBS1                 2          3        144         131072       65536           2       32765              ONLINE                      3
_SYSSMU1_3724004606$   PUBLIC UNDOTBS1                 1          3        128         131072       65536           2       32765              ONLINE                      3

11 rows selected.
SYS@book> select rowid,us#,name,user#,file#,BLOCK#,SCNBAS,SCNWRP,XACTSQN,UNDOSQN,INST#,STATUS$,TS#,FLAGS,SPARE1 from sys.undo$ ;
ROWID                     US# NAME                       USER#      FILE#     BLOCK#     SCNBAS     SCNWRP    XACTSQN    UNDOSQN      INST#    STATUS$        TS#      FLAGS     SPARE1
------------------ ---------- --------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AAAAAPAABAAAADhAAA          0 SYSTEM                         0          1        128          0          0          0          0          0          3          0                     0
AAAAAPAABAAAADhAAB          1 _SYSSMU1_3724004606$           1          3        128  340927674          3       2645        797          0          3          2                     2
AAAAAPAABAAAADhAAC          2 _SYSSMU2_2996391332$           1          3        144  340927386          3       2896       1157          0          3          2                     2
AAAAAPAABAAAADhAAD          3 _SYSSMU3_1723003836$           1          3        160  340927648          3       2932       1534          0          3          2                     2
AAAAAPAABAAAADhAAE          4 _SYSSMU4_1254879796$           1          3        176  340927664          3       2793       1292          0          3          2                     2
AAAAAPAABAAAADhAAF          5 _SYSSMU5_898567397$            1          3        192  340927490          3       3021       1147          0          3          2                     2
AAAAAPAABAAAADhAAG          6 _SYSSMU6_1263032392$           1          3        208  340927586          3       3279        897          0          3          2                     2
AAAAAPAABAAAADhAAH          7 _SYSSMU7_2070203016$           1          3        224  340927686          3       4949       1203          0          3          2                     2
AAAAAPAABAAAADhAAI          8 _SYSSMU8_517538920$            1          3        240  340927688          3       8299       1351          0          3          2                     2
AAAAAPAABAAAADhAAJ          9 _SYSSMU9_1650507775$           1          3        256  340927676          3      11702       1104          0          3          2                     2
AAAAAPAABAAAADhAAK         10 _SYSSMU10_1197734989$          1          3        272  340927692          3      24969       4279          0          3          2                     2
AAAAAPAABAAAADhAAL         11 _SYSSMU11_1701842685$          1          3        248  340926721          3        557        362          0          1          2                     2
AAAAAPAABAAAADhAAM         12 _SYSSMU12_3785813942$          1          3        296  340927254          3       1748        624          0          1          2                     2
AAAAAPAABAAAADhAAN         13 _SYSSMU13_3860906822$          1          5        160     923661          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAO         14 _SYSSMU14_3319140121$          1          5        176     923323          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAP         15 _SYSSMU15_1436577151$          1          5        192     923332          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAQ         16 _SYSSMU16_1689093467$          1          5        208     923314          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAR         17 _SYSSMU17_1049158485$          1          5        224     923296          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAS         18 _SYSSMU18_1557221903$          1          5        240     923320          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAT         19 _SYSSMU19_2284825117$          1          5        256     923294          0          2          1          0          1          5                     2
AAAAAPAABAAAADhAAU         20 _SYSSMU20_2312497597$          1          5        272     923262          0          2          1          0          1          5                     2
21 rows selected.

--从这里可以发现drop rollback segment后,并没有从undo$表删除,仅仅标识 STATUS$=1.

SYS@book> select * from DBA_extents where segment_name in ('_SYSSMU11_1701842685$','_SYSSMU12_3785813942$');
no rows selected

--空间也回收了。估计这两个回滚段存在问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2039075/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2634
  • 访问量
    6397315