DROP COLUMN会从块级别清理掉数据

 

对于DELETE操作，oracle在块级别只是做了个删除的标记，数据还在，因此可以用恢复工具恢复出来数据。
但是DROP COLUMN会从数据块级别清理掉数据，因此恢复工具也很难恢复了。

for example:

SQL> create table scott.test (id int,name varchar2(20));

Table created.

SQL> insert into scott.test values(1,'abc');

1 row created.

SQL> insert into scott.test values(2,'htyansp');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
  2 dbms_rowid.rowid_block_number(rowid) block#
  3 from scott.test;

     FILE# BLOCK#
---------- ----------

         5 681440
         5 681440

SQL> select name from v$dbfile where file#=5;

NAME
--------------------------------------------------------------------------------

/oradata/testdb/users01.dbf




BBED> set block 681440
        BLOCK# 681440

BBED> p rowdata
ub1 rowdata[0] @8164 0x2c
ub1 rowdata[1] @8165 0x01
ub1 rowdata[2] @8166 0x02
ub1 rowdata[3] @8167 0x02
ub1 rowdata[4] @8168 0xc1
ub1 rowdata[5] @8169 0x03
ub1 rowdata[6] @8170 0x07
ub1 rowdata[7] @8171 0x68
ub1 rowdata[8] @8172 0x74
ub1 rowdata[9] @8173 0x79
ub1 rowdata[10] @8174 0x61
ub1 rowdata[11] @8175 0x6e
ub1 rowdata[12] @8176 0x73
ub1 rowdata[13] @8177 0x70
ub1 rowdata[14] @8178 0x2c
ub1 rowdata[15] @8179 0x01
ub1 rowdata[16] @8180 0x02
ub1 rowdata[17] @8181 0x02
ub1 rowdata[18] @8182 0xc1
ub1 rowdata[19] @8183 0x02
ub1 rowdata[20] @8184 0x03
ub1 rowdata[21] @8185 0x61
ub1 rowdata[22] @8186 0x62
ub1 rowdata[23] @8187 0x63

BBED> x /rnc 8164
rowdata[0] @8164
----------

flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x01
cols@8166: 2

col 0[2] @8167: 2
col 1[7] @8170: htyansp


BBED> x /rnc 8178
rowdata[14] @8178
-----------

flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x01
cols@8180: 2

col 0[2] @8181: 1
col 1[3] @8184: abc


BBED>

上面是列还没有被删除的时候，数据在块中的形式。

删掉NAME列

 

SQL> alter table scott.test drop column name;

Table altered.

SQL> alter system checkpoint;

System altered.

再次查询块中数据的情况：

 

BBED> x /rnc 8164
rowdata[0] @8164
----------

flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x02
cols@8166: 1

col 0[2] @8167: 2


BBED> x /rnc 8178
rowdata[14] @8178
-----------

flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x02
cols@8180: 1

col 0[2] @8181: 1


BBED>

DROP COLUMN将会导致数据被清理掉，这种情况下数据时很难恢复了。