[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
--空间也回收了。估计这两个回滚段存在问题。