[20120906]alter table set unused column后的恢复.txt
我们知道表在alter table 表 set unused column 字段名 后的恢复，数据并没有真正的删除，昨天开发问如果出现误操作是否能够恢复(概率也太小了)。
大家知道在执行以上操作后，执行很快，对应字段的数据并没有真正删除，自己觉得好奇，测试看看。
1.测试环境：
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--建立表T，3个字段，插入1条记录。
SQL> create table t as select rownum id1 , mod(rownum,100)+1 id2, 'test' name from dual where 1=1;
SQL> column object_name format a10
SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name='T';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
T 106722 106722
SQL> select * from t;
ID1 ID2 NAME
---------- ---------- ----
1 2 test
SQL> select obj#,col#,segcol#,offset,name,intcol#,PROPERTY from sys.col$ where obj#=106722;
OBJ# COL# SEGCOL# OFFSET NAME INTCOL# PROPERTY
---------- ---------- ---------- ---------- ------------------------------ ---------- ----------
106722 1 1 0 ID1 1 0
106722 2 2 0 ID2 2 0
106722 3 3 0 NAME 3 0
2.删除id2:
SQL> alter table t set unused column id2;
Table altered.
SQL> select obj#,col#,segcol#,offset,name,intcol#,PROPERTY from sys.col$ where obj#=106722;
OBJ# COL# SEGCOL# OFFSET NAME INTCOL# PROPERTY
---------- ---------- ---------- ---------- ------------------------------ ---------- ----------
106722 1 1 0 ID1 1 0
106722 0 2 0 SYS_C00002_12090615:03:53$ 2 32800
106722 2 3 0 NAME 3 0
SQL> select sysdate from dual ;
SYSDATE
-------------------
2012-09-06 15:04:08
--可以发现 unused column id2变成了COL#＝0,后面顺序的col# -1 ,NAME变成了SYS_C00002_12090615:03:53$,大概可以猜出
--00002表示第2列，后面应该是时间（无20）。PROPERTY=32800
3.很明显要恢复ID2的显示，首先要恢复sys.col$的内容，修改sys.col$内容要小心，我这里是测试环境，不要在生产系统上做!!!
update (select col#,segcol#,name,intcol#,PROPERTY from sys.col$ where obj#=106722 and col#=0 ) set col#=intcol# ,name='ID2',PROPERTY=0;
update (select col#,segcol#,name,intcol#,PROPERTY from sys.col$ where obj#=106722 and intcol#> 2 ) set col#=intcol# ;
SQL> select obj#,col#,segcol#,offset,name,intcol#,PROPERTY from sys.col$ where obj#=106722;
OBJ# COL# SEGCOL# OFFSET NAME INTCOL# PROPERTY
---------- ---------- ---------- ---------- ------------------------------ ---------- ----------
106722 1 1 0 ID1 1 0
106722 2 2 0 ID2 2 0
106722 3 3 0 NAME 3 0
SQL> commit ;
Commit complete.
4.切换原回话，执行：
SQL> select * from t;
ID1 NAME
---------- ----
1 test
--不行，估计要刷新share pool。
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5510
Session ID: 135 Serial number: 5213
--挂起！退出
自己做了一次10046跟踪，步骤忽略，发现要执行N多系统表的操作，仔细检查发现需要修改sys.tab$中的cols。
SQL> select cols from sys.tab$ where obj#=106722;
COLS
----------
2
SQL> update ( select cols from sys.tab$ where obj#=106722 ) set cols=3;
1 row updated.
SQL> commit ;
Commit complete.
SQL> select * from t;
ID1 ID2 NAME
---------- ---------- ----
1 2 test
--OK成功!补充一点，我按照以上修改后，执行老是报错，多次alter system flush shared_pool;也没用，重启后在执行select * from t;正常！
--看来这种非常规操作要小心小心再小心，而且要做好备份！！！！
4.我执行alter table t set unused column id2;后，并没有插入数据，如果插入数据后，是否在能恢复操作吗？
自己接着测试看看:
SQL> alter table t set unused column id2;
Table altered.
SQL> insert into t values (2,'BBBB');
1 row created.
SQL> commit ;
Commit complete.
-- 打开另外的回话，插入数据，这样可以表空间是assm的话，可以讲数据插入不同的块中。
SQL> insert into t values (3,'CCCC');
1 row created.
SQL> commit ;
Commit complete.
SQL> column y format 9999
SQL> column z format 9999
SQL> SELECT ROWID x, DBMS_ROWID.rowid_relative_fno (ROWID) y, DBMS_ROWID.rowid_block_number (ROWID) z, t.* FROM t;
X Y Z ID1 NAME
------------------ ----- ---------- ---------- ----
AAAaDiAAEAAAACjAAA 4 163 1 test
AAAaDiAAEAAAACkAAA 4 164 3 CCCC
AAAaDiAAEAAAACnAAA 4 167 2 BBBB
--可以确定在不同的块中。
再重复上面的修改：
update (select col#,segcol#,name,intcol#,PROPERTY from sys.col$ where obj#=106722 and col#=0 ) set col#=intcol# ,name='ID2',PROPERTY=0;
update (select col#,segcol#,name,intcol#,PROPERTY from sys.col$ where obj#=106722 and intcol#> 2 ) set col#=intcol# ;
update ( select cols from sys.tab$ where obj#=106722 ) set cols=3;
commit ;
SQL> alter system flush shared_pool;
System altered.
SQL>
X ID1 ID2 NAME
------------------ ---------- ---------- ----
AAAaDiAAEAAAACjAAA 1 2 test
AAAaDiAAEAAAACkAAA 3 CCCC
AAAaDiAAEAAAACnAAA 2 BBBB
--发现可以正常输出显示！做一个块dump:
SQL> insert into t values (4,NULL,'DDDD');
1 row created.
SQL> commit ;
Commit complete.
SQL> SELECT ROWID x, DBMS_ROWID.rowid_relative_fno (ROWID) y, DBMS_ROWID.rowid_block_number (ROWID) z, t.* FROM t;
X Y Z ID1 ID2 NAME
------------------ ----- ---------- ---------- ---------- --------------------
AAAaDiAAEAAAACjAAA 4 163 1 2 test
AAAaDiAAEAAAACkAAA 4 164 3 CCCC
AAAaDiAAEAAAACnAAA 4 167 2 BBBB
AAAaDiAAEAAAACnAAB 4 167 4 DDDD
SQL> alter system checkpoint ;
System altered.
SQL> alter system dump datafile 4 block 167 ;
System altered.
Block header dump: 0x010000a7
Object id on Block? Y
seg/obj: 0x1a0e2 csc: 0x00.1117ab8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.015.00001e13 0x00c02535.1008.24 --U- 1 fsc 0x0000.01117aca
0x02 0x0008.005.00001f6d 0x00c007b9.0fd9.30 --U- 1 fsc 0x0000.01117df7
bdba: 0x010000a7
data_block_dump,data header at 0x2a971d8264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2a971d8264
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f80
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f8c
0x14:pri[1] ffs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: *NULL*
col 2: [ 4] 42 42 42 42
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 05
col 1: *NULL*
col 2: [ 4] 44 44 44 44
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 167 maxblk 167
SQL> alter system dump datafile 4 block 163 ;
System altered.
Block dump from disk:
buffer tsn: 4 rdba: 0x010000a3 (4/163)
scn: 0x0000.01115092 seq: 0x02 flg: 0x04 tail: 0x50920602
frmt: 0x02 chkval: 0x543b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A971D8200 to 0x0000002A971DA200
2A971D8200 0000A206 010000A3 01115092 04020000 [.........P......]
2A971D8210 0000543B 00000001 0001A0E2 01115090 [;T...........P..]
2A971D8220 00000000 00320003 010000A0 0000FFFF [......2.........]
2A971D8230 00000000 00000000 00000000 00008000 [................]
2A971D8240 01115090 00000000 00000000 00000000 [.P..............]
2A971D8250 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
2A971D8270 00000000 00000000 00000000 00010100 [................]
2A971D8280 0014FFFF 1F5E1F72 00001F5E 1F720001 [....r.^.^.....r.]
2A971D8290 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
2A971DA1E0 00000000 00000000 00000000 002C0000 [..............,.]
2A971DA1F0 02C10203 0403C102 74736574 50920602 [........test...P]
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x1a0e2 csc: 0x00.1115090 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.01115090
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000a3
data_block_dump,data header at 0x2a971d827c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x2a971d827c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f72
avsp=0x1f5e
tosp=0x1f5e
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f72
block_row_dump:
tab 0, row 0, @0x1f72
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c1 03
col 2: [ 4] 74 65 73 74
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 163 maxblk 163
时间： 2024-10-23 08:44:55