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延遲應用等方法解決
uj5u.com熱心網友回復：
把資料塊dump 出來，也可以查看效果。uj5u.com熱心網友回復：
你研究這搞毛，
uj5u.com熱心網友回復：
轉帖我的文章，最好注明下分析drop col對于資料存盤塊做了什么
uj5u.com熱心網友回復：
抓了個現行，哈哈uj5u.com熱心網友回復：
倆月以前的帖子，你在翻老賬
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/112126.html
標籤：認證與考試