Analyze what drop col in Oracle does for a data storage block

Oracle's ALTER TABLE Drop COL is specifically internal to the data storage block operation, and if a tool such as Dul is recoverable after the drop COL, I will illustrate the problem with a specific test, combined with bbed,dump block.

1. Create a test table and write to the hard drive

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. Find out to test the table a block analysis of the impact of drop col on storage


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, and record the blocks 1, 2, and last record


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] 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] 5f 55 53 45 52 31


............


tab 0, Row, @0x589


TL:22 FB:--h-fl--lb:0x0 Cc:3


Col 0: [3] C2 5b


Col 1: [3] 53 59 53


Col 2: [ten] 5f 4a 4f 5f 4e 45 58 54


4. Use bbed to view the Block 1, 2, and last record


[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, Oracle and/or its affiliates. All rights reserved.





************* !!! For Oracle Internal with 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, Bytes @0





struct KTBBH, Bytes @20





struct KDBH, 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. Delete the middle column and write to the hard drive


Sql> ALTER TABLE Xff. T_xifenfei DROP COLUMN owner;





Table altered.





Sql> alter system checkpoint;





System altered.





Sql>/





System altered.


6. The query determines that the same row block has not changed


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. After drop col, dump block continues to analyze


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] 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] 5f 55 53 45 52 31


............


tab 0, Row, @0x589


Tl:18 FB:--h-fl--lb:0x2 Cc:2


Col 0: [3] C2 5b


Col 1: [ten] 5f 4a 4f 5f 4e 45 58 54


8. Use bbed to view data storage after 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, Oracle and/or its affiliates. All rights reserved.





************* !!! For Oracle Internal with 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, Bytes @0





struct KTBBH, Bytes @20





struct KDBH, 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





<32 bytes per line>


Through the above tests, we can draw the following conclusions:


1. The drop col really removes the contents of the corresponding column stored in the block, and move the contents of the following column forward, and more than before (because a row of content is moved forward, the idle records are not set to empty, and the original content, the next time if the line length changes when used, Just like update to make the column changes short)


2. Drop col Only causes the length of a row to shorten, but the offset per line does not change, that is, the offset per line is unchanged, after drop COL, there is more free space behind each line


3. According to the above analysis principle, the drop col is true to overwrite the data in this column with the data in the following columns from within the block, so in principle, dul cannot recover the drop Col data (the last column may be recoverable because he will not be overwritten), for Drop Col, can only be solved by incomplete recovery of backup, Whole library flashback, DG Delay application, etc.