[20160414]分析drop column.txt
[20160414]分析drop column.txt
--昨天看了惜分飛關於刪除欄位的測試,自己也重複測試看看.
--原連結:
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.理論將這種情況還是可以恢復,因為沒有覆蓋.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2081924/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Lodash原始碼分析-drop.js原始碼JS
- 故障分析 | DROP 大表造成資料庫假死資料庫
- 閃回和drop原 undo tbs的一點分析
- drop asm disk、撤銷drop asm diskASM
- Drop DatabaseDatabase
- 資料包分析中Drop和iDrop的區別
- JavaScript drop 事件JavaScript事件
- SQL__DROPSQL
- windows drop emWindows
- oracle drop columnsOracle
- mysql drop keyMySql
- alter database drop datafile 與 drop tablespace file 的區別Database
- Oracle中drop user和drop user cascade的區別Oracle
- drag &drop 拖拽事件事件
- drag & drop 拖拽事件事件
- drop materialized view hung !!!ZedView
- drop、delete 與truncatedelete
- KEEP INDEX | DROP INDEXIndex
- torch--drop out
- alter database datafile offline drop 與 alter tablespace drop datafile 區別Database
- Drag & Drop 拖拽詳解
- HTML5 : Drag & DropHTML
- mysql恢復drop表MySql
- 快速drop mysql大表MySql
- truncate delete drop 區別delete
- 使用logmnr分析歸檔日誌恢復被drop掉的資料表
- 【Flashback】使用Flashback Drop技術閃回被DROP表的指定版本資料
- Quality Inspection in drop shipment process
- 簡述truncate、delete和dropdelete
- flashback drop/query/table/database/archiveDatabaseHive
- Truncate,Delete,Drop的比較.delete
- How To Efficiently Drop A Table With Many Extents
- How to drop Oracle RAC database manually?OracleDatabase
- flashback技術之---flashback drop
- oracle 誤刪表 drop tableOracle
- tarui drop失效,解決配置UI
- drop apply INSTANTIATION for one tableAPP
- HTML5 Drag和Drop 拖拽HTML