[20120906]alter table set unused column后的恢复.txt

简介: [20120906]alter table set unused column后的恢复.txt我们知道表在alter table 表 set unused column 字段名 后的恢复,数据并没有真正的删除,昨天开发问如果出现误操作是否能够恢复(概率也太小了)。
[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

目录
相关文章
|
2月前
|
druid Java
Error attempting to get column ‘createTime‘ from result set的异常
Error attempting to get column ‘createTime‘ from result set的异常
171 0
|
2月前
|
XML Java 数据库连接
mybatis和mybatiplus中Error attempting to get column ‘xx‘ from result set
mybatis和mybatiplus中Error attempting to get column ‘xx‘ from result set
94 0
|
druid Java 数据库连接
java报错Error attempting to get column ‘XXX’ from result set. Cause: java.sql.怎么解决?
java报错Error attempting to get column ‘XXX’ from result set. Cause: java.sql.怎么解决?
2127 0
java报错Error attempting to get column ‘XXX’ from result set. Cause: java.sql.怎么解决?
|
22天前
|
Go
go语言map、实现set
go语言map、实现set
28 0
|
6天前
|
C++ 容器
【C++】map和set封装
【C++】map和set封装
11 2
|
6天前
|
存储 C++ 容器
【C++】map和set深度讲解(下)
【C++】map和set深度讲解(下)
14 2
|
6天前
|
存储 自然语言处理 Java
【C++】map和set深度讲解(上)
【C++】map和set深度讲解(上)
15 2
|
15天前
|
存储 JavaScript 前端开发
JavaScript编码之路【ES6新特性之 Symbol 、Set 、Map、迭代器、生成器】(二)
JavaScript编码之路【ES6新特性之 Symbol 、Set 、Map、迭代器、生成器】(二)
17 1