分析drop col对于数据存储块做了什么

老书虫 2016-06-07 02:53:21
oracle 的alter table drop col具体内部是对于数据存储块操作的,如果drop col之后dul之类的工具是否可以恢复,这里我通过具体测试,结合bbed,dump block等方法来说明该问题

1.创建测试表,并写入硬盘
SQL> create table xff.t_xifenfei as select object_id,owner,object_name from dba_objects;


Table created.


SQL> desc xff.t_xifenfei

Name Null? Type

----------------------------------------- -------- ----------------------------

OBJECT_ID NUMBER

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)



SQL> alter system checkpoint;


System altered.


SQL> alter system checkpoint;


System altered.


2.找出来测试表一个block分析drop col对于存储的影响
SQL> select rowid,

2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,

3 dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno,object_id

4 5 from xff.t_xifenfei where rownum<5;


ROWID REL_FNO BLOCKNO ROWNO OBJECT_ID

------------------ ---------- ---------- ---------- ----------

AAAZ9wAAEAAAJojAAA 4 39459 0 20

AAAZ9wAAEAAAJojAAB 4 39459 1 46

AAAZ9wAAEAAAJojAAC 4 39459 2 28

AAAZ9wAAEAAAJojAAD 4 39459 3 15


3. dump block,并且记录该block 1,2,和最后一条记录
SQL> oradebug setmypid

Statement processed.

SQL> alter system dump datafile 4 block 39459;


System altered.


SQL> oradebug TRACEFILE_NAME

/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14069.trc



block_row_dump:

tab 0, row 0, @0x1f70

tl: 16 fb: --H-FL-- lb: 0x0 cc: 3

col 0: [ 2] c1 15

col 1: [ 3] 53 59 53

col 2: [ 5] 49 43 4f 4c 24

tab 0, row 1, @0x1f5e

tl: 18 fb: --H-FL-- lb: 0x0 cc: 3

col 0: [ 2] c1 2f

col 1: [ 3] 53 59 53

col 2: [ 7] 49 5f 55 53 45 52 31

…………

tab 0, row 288, @0x589

tl: 22 fb: --H-FL-- lb: 0x0 cc: 3

col 0: [ 3] c2 03 5b

col 1: [ 3] 53 59 53

col 2: [10] 49 5f 4a 4f 42 5f 4e 45 58 54


4. 使用bbed查看该block 1,2,和最后一条记录
[oracle@localhost ~]$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf


BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:25:28 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


************* !!! For Oracle Internal Use only !!! ***************


BBED> set block 39459

BLOCK# 39459


BBED> map

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Dba:0x00000000

------------------------------------------------------------

KTB Data Block (Table/Cluster)


struct kcbh, 20 bytes @0


struct ktbbh, 96 bytes @20


struct kdbh, 14 bytes @124


struct kdbt[1], 4 bytes @138


sb2 kdbr[289] @142


ub1 freespace[821] @720


ub1 rowdata[6647] @1541


ub4 tailchk @8188



BBED> p *kdbr[0]

rowdata[6631]

-------------

ub1 rowdata[6631] @8172 0x2c


BBED> x /rncc

rowdata[6631] @8172

-------------

flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8173: 0x00

cols@8174: 3


col 0[2] @8175: 20

col 1[3] @8178: SYS

col 2[5] @8182: ICOL$



BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 8172 to 8191 Dba:0x00000000

------------------------------------------------------------------------

2c000302 c1150353 59530549 434f4c24 02067576


<32 bytes per line>


BBED> p *kdbr[1]

rowdata[6613]

-------------

ub1 rowdata[6613] @8154 0x2c


BBED> x /rncc

rowdata[6613] @8154

-------------

flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8155: 0x00

cols@8156: 3


col 0[2] @8157: 46

col 1[3] @8160: SYS

col 2[7] @8164: I_USER1



BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 8154 to 8191 Dba:0x00000000

------------------------------------------------------------------------

2c000302 c12f0353 59530749 5f555345 52312c00 0302c115 03535953 0549434f

4c240206 7576


<32 bytes per line>


BBED> p *kdbr[288]

rowdata[0]

----------

ub1 rowdata[0] @1541 0x2c


BBED> x /rncc

rowdata[0] @1541

----------

flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1542: 0x00

cols@1543: 3


col 0[3] @1544: 290

col 1[3] @1548: SYS

col 2[10] @1552: I_JOB_NEXT



BBED> set count 32

COUNT 32


BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 1541 to 1572 Dba:0x00000000

------------------------------------------------------------------------

2c000303 c2035b03 5359530a 495f4a4f 425f4e45 58542c00 0303c203 5a035359


<32 bytes per line>


5. 删除中间列,并且写入硬盘
SQL> ALTER TABLE XFF.T_XIFENFEI DROP COLUMN owner;


Table altered.


SQL> alter system checkpoint;


System altered.


SQL> /


System altered.


6. 查询确定相同行所在block没有发生改变
SQL> select rowid,

2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,

3 dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno,object_id

4 5 from xff.t_xifenfei where rownum<5;


ROWID REL_FNO BLOCKNO ROWNO OBJECT_ID

------------------ ---------- ---------- ---------- ----------

AAAZ9wAAEAAAJojAAA 4 39459 0 20

AAAZ9wAAEAAAJojAAB 4 39459 1 46

AAAZ9wAAEAAAJojAAC 4 39459 2 28

AAAZ9wAAEAAAJojAAD 4 39459 3 15


7. drop col之后dump block继续分析
SQL> alter system dump datafile 4 block 39459;


System altered.


SQL> select value from v$diag_info where name='Default Trace File';


VALUE

--------------------------------------------------------------------------------

/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14784.trc


SQL>


tab 0, row 0, @0x1f70

tl: 12 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 2] c1 15

col 1: [ 5] 49 43 4f 4c 24

tab 0, row 1, @0x1f5e

tl: 14 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 2] c1 2f

col 1: [ 7] 49 5f 55 53 45 52 31

…………

tab 0, row 288, @0x589

tl: 18 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 3] c2 03 5b

col 1: [10] 49 5f 4a 4f 42 5f 4e 45 58 54


8. 使用bbed查看drop col后的数据存储情况
$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf


BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:31:37 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


************* !!! For Oracle Internal Use only !!! ***************


BBED> set block 39459

BLOCK# 39459


BBED> map

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Dba:0x00000000

------------------------------------------------------------

KTB Data Block (Table/Cluster)


struct kcbh, 20 bytes @0


struct ktbbh, 96 bytes @20


struct kdbh, 14 bytes @124


struct kdbt[1], 4 bytes @138


sb2 kdbr[289] @142


ub1 freespace[821] @720


ub1 rowdata[6647] @1541


ub4 tailchk @8188



BBED> p *kdbr[0]

rowdata[6631]

-------------

ub1 rowdata[6631] @8172 0x2c


BBED> x /rncc

rowdata[6631] @8172

-------------

flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8173: 0x02

cols@8174: 2


col 0[2] @8175: 20

col 1[5] @8178: ICOL$



BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 8172 to 8191 Dba:0x00000000

------------------------------------------------------------------------

2c020202 c1150549 434f4c24 434f4c24 0106de78


<32 bytes per line>


BBED> p *kdbr[1]

rowdata[6613]

-------------

ub1 rowdata[6613] @8154 0x2c


BBED> x /rncc

rowdata[6613] @8154

-------------

flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8155: 0x02

cols@8156: 2


col 0[2] @8157: 46

col 1[7] @8160: I_USER1



BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 8154 to 8191 Dba:0x00000000

------------------------------------------------------------------------

2c020202 c12f0749 5f555345 52315345 52312c02 0202c115 0549434f 4c24434f

4c240106 de78


<32 bytes per line>


BBED> p *kdbr[288]

rowdata[0]

----------

ub1 rowdata[0] @1541 0x2c


BBED> set count 32

COUNT 32


BBED> x /rncc

rowdata[0] @1541

----------

flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1542: 0x02

cols@1543: 2


col 0[3] @1544: 290

col 1[10] @1548: I_JOB_NEXT



BBED> d

File: /usr/local/oradata/qsng/users01.dbf (0)

Block: 39459 Offsets: 1541 to 1572 Dba:0x00000000

------------------------------------------------------------------------

2c020203 c2035b0a 495f4a4f 425f4e45 58544e45 58542c02 0203c203 5a09495f


<32 bytes per line>


通过上述测试可以得出如下结论:
1. drop col是真的把对应列存储在block中的内容除掉,而且把后面的列的内容前移了,并且以前多于的内容(因为一行内容前移,后面就出现空闲记录不设置为空,而就是最初内容,下次如果行长度发生改变的时候使用,就和类似update把列修改短了一样)
2. drop col只是导致一行的长度变短,但是每行的偏移量未发生改变,也就是说,每行所在的偏移量没有改变,drop col之后,每行后面多了一些空闲空间
3. 根据上面分析的原理,drop col 是真的从block内部把这一列的数据使用后面列的数据覆盖了,因此从原理上而言,dul无法恢复drop col的数据(最后一列有可能可以恢复,因为他不会被覆盖),对于drop col,只能是通过备份不完全恢复,全库闪回,dg延迟应用等方法解决
...全文
2875 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
jdsnhan 2016-11-28
  • 打赏
  • 举报
回复
引用 4 楼 sych888 的回复:
抓了个现行,哈哈
俩月以前的帖子,你在翻老账
sych888 2016-11-26
  • 打赏
  • 举报
回复
抓了个现行,哈哈
惜分飞 2016-09-27
  • 打赏
  • 举报
回复
转帖我的文章,最好注明下 分析drop col对于数据存储块做了什么
smallcrocodile 2016-09-27
  • 打赏
  • 举报
回复
你研究这搞毛,
卖水果的net 2016-06-07
  • 打赏
  • 举报
回复
把数据块dump 出来,也可以查看效果。

2,668

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 认证与考试
社区管理员
  • 认证与考试社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧