Tuesday, February 26, 2013

A demonstration of Oracle row movement within a block.

The following is an investigation of what happens in the Oracle block when a record does not have room to grow.


SQL> create table TEST_TABLE(id integer, attribute1 varchar2(200)) tablespace test;

Table created.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------
         5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF

SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73516          73516

SQL> insert into test_table values (1,'AAAAA');

1 row created.

SQL> insert into test_table values (2,'BBBBB');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        160          8            5

We extract the block contents using the following program :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html


Block 163 Contains Object ID 73516
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971683
 SCN Base                    :- 1075559
 SCN Wrap                    :- 0
 Sequence                    :- 01 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 32492
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73516
 Cleanout SCN Base           :- 1075556
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971680
ITLSlot                     :- 1
 Undo Segment               :- 1
 Undo Segment Slot          :- 5
 Transaction Sequence       :- 633
 Undo Block Address         :- 12583566
 Undo Sequence              :- 165
 Undo Record Number         :- 23
 Spare 1                    :- 0
 Flag                       :- 8194
 _ktbitun                   :- 0
 Base                       :- 1075559
ITLSlot                     :- 2
 Undo Segment               :- 0
 Undo Segment Slot          :- 0
 Transaction Sequence       :- 0
 Undo Block Address         :- 0
 Undo Sequence              :- 0
 Undo Record Number         :- 0
 Spare 1                    :- 0
 Flag                       :- 0
 _ktbitun                   :- 0
 Base                       :- 0
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8064
 Available Space after Commit:- 8042
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8076(+100)
 Row Offset 2 :- 8064(+100)
1 Row Header start  :-8176
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
2 Row Header start  :-8164
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- BBBBB

From the above we can see that there are two rows in block 163, starting at bytes 8164 and 8176.  The first record inserted (AAAAA) is at the end of the block, with no room to grow.

If we update the record with more data :-


SQL> update test_table set attribute1 = '012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890' where attribute1 = 'AAAAA'
;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.


We can now see that :-

1. the block SCNs and sequence have been updated

2. the block CheckSum has been updated
3. the Available Space in the block has decreased
4. the record that previously started at byte 8176 has now moved to byte 8066 to allow it to grow.


Block 163 Contains Object ID 73516
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971683
 SCN Base                    :- 1075759
 SCN Wrap                    :- 0
 Sequence                    :- 02 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- -25092
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73516
 Cleanout SCN Base           :- 1075757
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971680
ITLSlot                     :- 1
 Undo Segment               :- 1
 Undo Segment Slot          :- 5
 Transaction Sequence       :- 633
 Undo Block Address         :- 12583566
 Undo Sequence              :- 165
 Undo Record Number         :- 23
 Spare 1                    :- 0
 Flag                       :- -32768
 _ktbitun                   :- 0
 Base                       :- 1075559
ITLSlot                     :- 2
 Undo Segment               :- 7
 Undo Segment Slot          :- 22
 Transaction Sequence       :- 605
 Undo Block Address         :- 12583559
 Undo Sequence              :- 222
 Undo Record Number         :- 49
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 0
 Base                       :- 1075759
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 7966
 Available Space after Commit:- 7956
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 7966(+100)
 Row Offset 2 :- 8064(+100)
1 Row Header start  :-8066
 Flags              :-  2C  Table Data
 Lock Status        :- 02 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 91 Data :- 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
2 Row Header start  :-8164
 Flags              :-  2C  Table Data
 Lock Status        :- 00 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- BBBBB