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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment