[20160414]分析drop column.txt
--昨天看了惜分飞关于删除字段的测试,自己也重复测试看看.
--原链接:http://www.xifenfei.com/2016/04/%E5%88%86%E6%9E%90drop-col%E5%AF%B9%E4%BA%8E%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8%E5%9D%97%E5%81%9A%E4%BA%86%E4%BB%80%E4%B9%88.html
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table dept1 tablespace tea as select * from dept ;
Table created.
SCOTT@book> @ &r/desc dept1
Name Null? Type
------- -------- -------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SCOTT@book> select rowid,dept1.* from dept1;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWglAAHAAAACTAAA 10 ACCOUNTING NEW YORK
AAAWglAAHAAAACTAAB 20 RESEARCH DALLAS
AAAWglAAHAAAACTAAC 30 SALES CHICAGO
AAAWglAAHAAAACTAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAWglAAHAAAACTAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- ------- ----------------------------------------
92197 7 147 0 7,147 alter system dump datafile 7 block 147 ;
SCOTT@book> alter system checkpoint ;
System altered.
2.使用bbed观察:
BBED> set dba 7,147
DBA 0x01c00093 (29360275 7,147)
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
3.删除中间的字段:
SCOTT@book> alter table dept1 drop column dname;
Table altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> select rowid,dept1.* from dept1;
ROWID DEPTNO LOC
------------------ ---------- -------------
AAAWglAAHAAAACTAAA 10 NEW YORK
AAAWglAAHAAAACTAAB 20 DALLAS
AAAWglAAHAAAACTAAC 30 CHICAGO
AAAWglAAHAAAACTAAD 40 BOSTON
SCOTT@book> @ &r/rowid AAAWglAAHAAAACTAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
92197 7 147 0 7,147 alter system dump datafile 7 block 147 ;
SCOTT@book> select * from dba_objects where owner=user and object_name='DEPT1';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT DEPT1 92197 92197 TABLE 2016-04-15 14:48:19 2016-04-15 14:53:01 2016-04-15:14:53:01 VALID N N N 1
--从这里还可以看出一个特点,drop column字段,data_object_id并没有发生变化.
4.再通过bbed观察:
BBED> set dba 7,147
DBA 0x01c00093 (29360275 7,147)
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
--如果你对比上面的显示,可以发现行目录的偏移量没有发生变化.
BBED> set dba 7,147
DBA 0x01c00093 (29360275 7,147)
BBED> p *kdbr[0]
rowdata[66]
-----------
ub1 rowdata[66] @8162 0x2c
BBED> x /rnc
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x02
cols@8164: 2
col 0[2] @8165: 10
col 1[8] @8168: NEW YORK
BBED> p *kdbr[1]
rowdata[44]
-----------
ub1 rowdata[44] @8140 0x2c
BBED> x /rnc
rowdata[44] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142: 2
col 0[2] @8143: 20
col 1[6] @8146: DALLAS
--因为数据从数据块底部开始插入.先看deptno=20.占用空间3+1+2+1+6=13 个字节. 8140+13=8153 ,而实际上下一条记录偏移在8162,
--中间出现9个字节的空隙.
--而原来的deptno=20,dname='RESEARCH',占用8个字符,加上长度指示器正好9个字符.如果你检查数据块:
SCOTT@book> @ &r/bbvi 7 147
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1204224 -s 8192 /mnt/ramdisk/book/tea01.dbf
00127F90 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00127FA0 2C 02 02 02 C1 29 06 42 4F 53 54 4F 4E 49 4F 4E ,....).BOSTONION
00127FB0 53 06 42 4F 53 54 4F 4E 2C 02 02 02 C1 1F 07 43 S.BOSTON,......C
00127FC0 48 49 43 41 47 4F 48 49 43 41 47 4F 2C 02 02 02 HICAGOHICAGO,...
~~~~~~~~~~~
00127FD0 C1 15 06 44 41 4C 4C 41 53 43 48 06 44 41 4C 4C ...DALLASCH.DALL
~~~~~ ~~~~~~~~~~~~~~~~~~~~
00127FE0 41 53 2C 02 02 02 C1 0B 08 4E 45 57 20 59 4F 52 AS,......NEW YOR
00127FF0 4B 4E 47 08 4E 45 57 20 59 4F 52 4B 01 06 9D B1 KNG.NEW YORK....
00128000
--你可以发现oracle仅仅将后面的字段向前移动,行目录的偏移并没有变动.你开可以发现第3个字段原来的信息还在.
--你可以发现drop column后第2个字段信息被覆盖,无法通过导出数据块的方式找到原来的记录.除非一种特殊的情况就是删除最后的字段
5.再做删除最后字段的测试:
SCOTT@book> drop table dept1 purge ;
Table dropped.
SCOTT@book> create table dept1 tablespace tea as select * from dept ;
Table created.
SCOTT@book> select rowid,dept1.* from dept1;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWgqAAHAAAACTAAA 10 ACCOUNTING NEW YORK
AAAWgqAAHAAAACTAAB 20 RESEARCH DALLAS
AAAWgqAAHAAAACTAAC 30 SALES CHICAGO
AAAWgqAAHAAAACTAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAWgqAAHAAAACTAAA
OBJECT FILE BLOCK ROW DBA TEXT
------ ---------- ---------- ---------- ----- ----------------------------------------
92202 7 147 0 7,147 alter system dump datafile 7 block 147 ;
SCOTT@book> alter system checkpoint ;
System altered.
BBED> set dba 7,147
DBA 0x01c00093 (29360275 7,147)
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
SCOTT@book> alter table dept1 drop column loc;
Table altered.
SCOTT@book> alter system checkpoint ;
System altered.
BBED> set dba 7,147
DBA 0x01c00093 (29360275 7,147)
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
BBED> p *kdbr[0]
rowdata[66]
-----------
ub1 rowdata[66] @8162 0x2c
BBED> x /rnc
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x02
cols@8164: 2
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
BBED> p *kdbr[1]
rowdata[44]
-----------
ub1 rowdata[44] @8140 0x2c
BBED> x /rnc
rowdata[44] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142: 2
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
SCOTT@book> @ &r/bbvi 7 147
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1204224 -s 8192 /mnt/ramdisk/book/tea01.dbf
$ bvi -b 1204224 -s 8192 /mnt/ramdisk/book/tea01.dbf
...
00127F90 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00127FA0 2C 02 02 02 C1 29 0A 4F 50 45 52 41 54 49 4F 4E ,....).OPERATION
00127FB0 53 06 42 4F 53 54 4F 4E 2C 02 02 02 C1 1F 05 53 S.BOSTON,......S
00127FC0 41 4C 45 53 07 43 48 49 43 41 47 4F 2C 02 02 02 ALES.CHICAGO,...
00127FD0 C1 15 08 52 45 53 45 41 52 43 48 06 44 41 4C 4C ...RESEARCH.DALL
00127FE0 41 53 2C 02 02 02 C1 0B 0A 41 43 43 4F 55 4E 54 AS,......ACCOUNT
00127FF0 49 4E 47 08 4E 45 57 20 59 4F 52 4B 01 06 D0 C1 ING.NEW YORK....
00128000
--这样是可以发现最后的字段,这样仅仅修改cols=2.理论将这种情况还是可以恢复,因为没有覆盖.
