文档章节

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

我就叫你女王吧
 我就叫你女王吧
发布于 2016/06/07 14:51
字数 1536
阅读 8
收藏 0

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内容  请关注我:

© 著作权归作者所有

共有 人打赏支持
我就叫你女王吧
粉丝 3
博文 34
码字总数 25430
作品 0
海淀
私信 提问
mysql学习笔记(5-DDL命令)

服务器端命令: DDL:数据定义语言,主要用于管理数据库组件,例如表、索引、视图、用户、存储过程 CREATE、ALTER、DROP DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查...

黎晨
2017/05/15
0
0
mysql基本语法及使用

环境:centos6.8 软件:mysaql 5.6.20 1.mysql交互式基本指令 ;mysql交互式默认的语句结束符, 如果没有结束会出现‘->’这个符号来告知你语句没有结束 show databases;查看有那些数据库存在...

doudou___9958
2016/11/09
5
0
oracle 体系结构及内存管理 15_存储结构

1、Oracle物理存储结构 控制文件:ctl 数据库名字,物理结构信息,字符集,SCN,检查点,状态信息,归档日志历史等,一般会有镜像 show parameter control; 数据文件:dbf 数据和临时文件百万...

PeakFang-BOK
10/13
0
0
MariaDB/Mysql的用法

要使用MariaDB/Mysql,先要利用yum安装 [root@localhost ~]# yum install -y mariadb-* //利用这条命令能把所有关于MariaDB的软件工具安装上 若本来Linux(CentOS)已经安装,可用 [root@loca...

Vayne36
07/23
0
0
mariadb常用的客服端及其服务端命令

一、安装MariaDB及其配置步骤 1)创建存放数据目录及其用户 mkdir -pv /mydata/data groupadd -r mysql useradd -g mysql -r mysql 2)授权数据存放目录 chown -R /mydata/data 3)解压mariadb...

秦时明月路
2016/10/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

容器之Zookeeper的使用

我们使用zookeeper时,都是在Linux上安装zookeeper,之后启动时要加入配置文件。 使用docker之后,我们可以直接使用镜像运行容器,镜像可以从docker.hub上下载,地址是https://hub.docker.co...

克虏伯
21分钟前
0
0
esxi 更换ssl证书

概述 就是想换一个证书而已,你可以通过下面的途径去申请一个泛解析域名的证书之后再esxi上安装上 使用阿里云域名api申请Let’s Encrypt泛域名免费ssl证书 申请完成证书之后进行下一步 操作 ...

bboysoulcn
34分钟前
1
0
PLC编程入门:梯形图

梯形图(LAD)是PLC编程的最佳可视化语言,它看起来非常类似于继电器电路图,因此如果 你对继电器控制和电子电路有所了解的话,那么学起来会非常容易! 在这个教程中,我们将学习关于使用梯形...

汇智网教程
36分钟前
1
0
Kubernetes 1.13.0的快速升级

Kubernetes 1.13.0已经正式发布,快速升级(含国内镜像快速下载链接)包括升级kubeadm/kubectl/kubelet版本、拉取镜像、升级Kubernetes集群三个主要步骤。注意Kubernetes 1.13.0版本暂时不支...

openthings
50分钟前
2
0
go的卸载和环境变量配个人.bashrc

若是用安装包直接解压 http://download.csdn.net/detail/u010026901/7592581 cd /usr/local tar -zxvf go1.1.2.linux-386.tar.gz(先把安装包移到这个目录) 3.安装 $ cd go/src,$ ./all.b......

dragon_tech
55分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部