Chinaunix首页 | 论坛 | 博客
  • 博客访问: 92792
  • 博文数量: 25
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 10
  • 用 户 组: 普通用户
  • 注册时间: 2016-06-12 13:45
文章分类
文章存档

2016年(25)

我的朋友

分类: Oracle

2016-06-07 14:57: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延迟应用等方法解决



更多Oracle精彩  内容请关注我:
                           
阅读(1760) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~