The following is an examination of the redo generated for an insert to a new table in a new datafile. It includes a comparison of the redo log dump, and actual byte contents of the log. To generate this output I ran :-
create table BBBBB (attribute1 char(5));
alter system switch logfile;
insert into BBBBB values ('AAAAA');
commit;
select vlf.member from v$log vl, v$logfile vlf where vl.group#=vlf.group# and vl.status = 'CURRENT';
alter system switch logfile;
ALTER SYSTEM DUMP LOGFILE 'D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG';
In the comparison the dump file output will be red, and the log byte value will be blue.
I ran the example on Windows, so the redo block size is 512 bytes.
I skipped the first block of the redo log, which contains the file header, and the second block, which contains the redo header. Output starts with the 3rd block, which contains the first redo record.
You can see in the output in red that the RBA (Redo Byte Address) where the first redo record starts is log sequence 9, block 2 (not including file header block) offset 16. The total length of the redo record is 668 bytes (not including the 16 byte block headers). The record also includes the VLD which defines redo record header size, SCN, SUBSCN, and Timestamp.
REDO RECORD - Thread:1 RBA: 0x000009.00000002.0010 LEN: 0x029c VLD: 0x0d
SCN: 0x0000.00110ad8 SUBSCN: 1 02/28/2013 16:19:07
# Each block starts with a 16 byte block header containing signature, block number sequence, offset and checksum.
01 22 Signature (the same in every block)
00 00
02 00 00 00 Block Number (2)
09 00 00 00 Sequence Number
10 Offset to redo record (16)
80
66 B2 CheckSum
# At offset 16 the Redo Record Header starts. For VLD 13 the header is 68 bytes containing VLD, SCN, and Timestamp.
9C 02 Redo Record Size (668)
00 00
0D 00 VLD (13)
00 00
D8 0A 11 00 SCN
01 00 00 00 Sub SCN
00 00 00 00
00 00 00 00
00 00 01 00
02 00 00 00
02 00 00 00
08 00 00 00
D7 0A 11 00 SCN
00 00 00 00
00 00 00 00
00 00 00 00
D9 0A 11 00 SCN
00 00 00 00
7B 1A 32 30 Timestamp
# The first change after the redo record header relates to a change to object 73524 in DBA (Database Block Address) 0x00415129. The block Class (CLS) is 1 (data block) and the Absolute File Number (AFN) is 1. The change is Operation (OP) 13.5 which is a "block format" prior to the insert.
CHANGE #1 TYP:1 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad6 SEQ:1 OP:13.5 ENC:0 RBL:0
KTSFRBFMT (block format) redo: Segobjd: 0x00011f34 type: 1 itls: 2 cscn: 0x0000.00110ad6
# change record for OP 13.5 is 48 bytes
0D 05 OP (13.5)
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D6 0A 11 00 00 00 SCN
DC 58
01 SEQ
01 TYP
34 1F
06 00
08 00 08 00
00 00
34 1F 01 00 SegObjd
01 00 type
02 00 itls
D6 0A 11 00 00 00 cscn
00 00
# The second change in the redo record is to the same object and block, consisting of operation 13.6 which is a "block link modify" "lock set" following the format.
CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:1 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo: Opcode: LSET (lock set)
Next dba: 0x0041512a itli: 0
# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
0D 00
01 SEQ
00 TYP
34 1F
04 00
0C 00 02 00
00 00
2A 51 41 00 Next dba
00 00 00 00 itli
# The third change in the redo records is operation 13.6 which is a "block link modify" "lock write"
CHANGE #3 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:2 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo: Opcode: LWRT (lock write)
Next dba: 0x00000000 itli: 0
# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
DC 58
02 SEQ
00 TYP
34 1F
04 00
0C 00 03 00
00 00 00 00 Next dba
00 00 00 00 itli
00 00
# The fourth change in the redo record is operation 13.7 to maintain the block linked list (freelist) and high water mark (hwm).
CHANGE #4 TYP:0 CLS:4 AFN:1 DBA:0x00415128 OBJ:73524 SCN:0x0000.00110ac8 SEQ:1 OP:13.7 ENC:0 RBL:0
KTSFRGRP (fgb/shdr modify freelist) redo:
Opcode: HWMMV (move hwm)
NBK: 1
Opcode: LUPD_LLIST (link a list)
Slot no: 0, Count: 1
Flag: = 1 xid or slot0 ccnt: 0x0000.000.00000001 Head: 0x00415129 Tail: 0x00415129
# change record for OP 13.7 is 72 bytes
0D 07 OP
04 00 CLS
01 00 AFN
01 00
28 51 41 00 DBA
C8 0A 11 00 00 00 SCN
4C 4C
01 SEQ
00 TYP
34 1F
06 00
08 00
20 00 38 34
01 00 00 00
01 00 00 00
05 00 00 00
01 00 00 00
00 00 00 00
01 00 00 00
00 00 00 00
01 00 00 00
29 51 41 00 head
29 51 41 00 tail
# The fifth change in the redo record is OP 11.2 which is the actual redo for the insert to the newly formatted block. It contains details on the System Change Number (SCN), Transaction ID (xid), Undo Block Address (uba), table column and data.
# The xid is 8 bytes composed of Undo segment number(0x006) , Undo segment header transaction table slot (0x009), and sequence number wrap (0x0000032d).
# The uba is 8 bytes composed of DBA of undo block (0x00c00182), Sequence number (0x00ca), and Record number in block (0x20).
CHANGE #5 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:3 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0006.009.0000032d uba: 0x00c00182.00ca.20
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00415129 hdba: 0x00415128
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) size/delt: 9
fb: --H-FL-- lb: 0x1 cc: 1
null: -
col 0: [ 5] 41 41 41 41 41
# change record for OP 11.2 is 112 bytes (will vary depending on table and data)
0B 02 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
00 00
03 SEQ
00 TYP
34 1F
08 00
14 00 31 00
05 00 01 05
DC 58
06 00 09 00 2D 03 00 00 xid
82 01 C0 00 CA 00 20 00 uba
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
02 01
01 00 itli
00 00
2C 01
01 00
00 00
00 00 slot
00 00
00 00
00 00
00 00
00 00
00 00
00 00
00 00
09 00 size
00 00 lb
00 00
00 00
00 00
00 00
41 41 41 41 41 00 00 00 DATA (AAAAA)
# The sixth change is OP 5.2 containing SCN, uba.
CHANGE #6 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x0000032d flg: 0x0012 siz: 108 fbi: 0
uba: 0x00c00182.00ca.20 pxid: 0x0000.000.00000000
# change record for OP 5.2 is 60 bytes
05 02 OP
1B 00 CLS
03 00 AFN
FF FF
D0 00 C0 00 DBA
95 0A 11 00 00 00 SCN
DC 58
02 SEQ
00 TYP
FF FF
04 00 20 00
09 00 slt
3A 00
2D 03 00 00 sqn
82 01 C0 00 CA 00 20 uba
00
12 00 flg
6C siz (108)
00
00 00 00 00
00 00 00 00
00 00 00 00
# The seventh change is OP 5.4 is the final commit, containing SCN, uba.
CHANGE #7 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110ad8 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0009 sqn: 0x0000032d srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00182.00ca.20 ext: 3 spc: 4250 fbi: 0
# change record for OP 5.4 is 72 bytes
05 04 OP
1B 00 CLS
03 00 AFN
FF FF
D0 00 C0 00 DBA
D8 0A 11 00 00 00 SCN
DC 58
01 SEQ
00 TYP
FF FF
08 00 14 00
10 00 04 00
09 00 00 00
2D 03 00 00 sqn
00 00
00 00 srt
09 00 sta
00 00
02 00 flg
00 00
82 01 C0 00 uba part 1
# The redo record did not fit into one 512 byte block, so now continues in block 3, which starts with a 16 byte header including offset.
# block header = 16 bytes
01 22 Signature
00 00
03 00 00 00 Block Number
09 00 00 00 Sequence Number
BC Offset
80
EE 58 CheckSum
# continuation of change 7 in new block
CA 00 20 00 uba part 2
03 00 ext
9A 10 spc
00 07 00 00
FB 12 2F 51
# The eighth and final change is OP 5.1 which is the undo record.
CHANGE #8 TYP:0 CLS:28 AFN:3 DBA:0x00c00182 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 108 spc: 4360 flg: 0x0012 seq: 0x00ca rec: 0x20
xid: 0x0006.009.0000032d
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 73524 objd: 73524 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00182.00ca.1d
prev ctl max cmt scn: 0x0000.00110540 prev tx cmt scn: 0x0000.00110544
txn start scn: 0x0000.00110ad1 logon user: 0 prev brb: 12583297 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00415129 hdba: 0x00415128
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0)
END OF REDO DUMP
# change record for OP 5.1 is 156 bytes
05 01 OP
1C 00 CLS
03 00 AFN
FF FF
82 01 C0 00 DBA
95 0A 11 00 00 00 SCN
00 00
03 SEQ
00 TYP
FF FF
0A 00 14 00
4C 00 02 00
14 00
00 00
6C siz (108)
00
08 11
12 00 flg
00 00
06 00 09 00 2D 03 00 00 xid
CA 00 seq
20 00 rec
34 1F 01 00 objn
34 1F 01 00 objd
00 00 00 00
00 00 00 00
0B 01 09 00
08 0C 01 00
00 00 00 00
82 01 C0 00 DBA
CA 00
1D 00
40 05 11 00 00 00 prev ctl max cmt scn
C0 00
44 05 11 00 00 00 prev tx cmt scn
00 00
00 00
00 00
D1 0A 11 00 00 00 txn start scn
00 00
81 01
C0 00
00 00 00 00
00 00 00 00
03 05
FF FF
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
03 01
01 00
00 00
00 00
00 58
Monday, March 4, 2013
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
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
What happens to Oracle data blocks during Truncate.
The following is an investigation of what happens during an Oracle table truncate. First we create a table and insert two records.
SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) 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
---------- --------------
73514 73514
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
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 segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 152 8 5
We can find the data for the table in file 5 in an extent starting at block 152. When we scan for the data, we find that the records we inserted are in block 155, with empty blocks 156,7,8,9. The following output is from :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Now we truncate the table. Selecting from the data dictionary table we can see that the data blocks listed in DBA_EXTENTS did not change, but in DBA_OBJECTS the DATA_OBJECT_ID was updated.
SQL> select * from test_table;
no rows selected
If we again scan for blocks associated with the old DATA_OBJECT_ID 73514 we can see that they are unchanged and still have the original data in them. This means that even after a table is truncated, the data remains in the blocks on disk, and could be extracted (until the blocks are overwritten).
Block 155 Contains Object ID 73514
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971675
SCN Base :- 1074483
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 14737
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73514
Cleanout SCN Base :- 1074478
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 :- 20971672
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 14
Transaction Sequence :- 632
Undo Block Address :- 12583042
Undo Sequence :- 162
Undo Record Number :- 51
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1074483
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 :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514
Block 157 Contains Object ID 73514
Block 158 Contains Object ID 73514
Block 159 Contains Object ID 73514
SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) 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
---------- --------------
73514 73514
SQL> insert into test_table values (1,'AAAAA','BBB');
1 row created.
SQL> insert into test_table values (2,'CCCCC','DDDDD');
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 segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 152 8 5
We can find the data for the table in file 5 in an extent starting at block 152. When we scan for the data, we find that the records we inserted are in block 155, with empty blocks 156,7,8,9. The following output is from :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
Block 155 Contains Object ID 73514
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971675
SCN Base :- 1074483
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 14737
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73514
Cleanout SCN Base :- 1074478
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 :- 20971672
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 14
Transaction Sequence :- 632
Undo Block Address :- 12583042
Undo Sequence :- 162
Undo Record Number :- 51
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1074483
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 :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514
Block 157 Contains Object ID 73514
Block 158 Contains Object ID 73514
Block 159 Contains Object ID 73514
SQL> truncate table test_table;
Table truncated.
SQL> alter system checkpoint;
System altered.
no rows selected
SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
73514 73515
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';
FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
5 152 8 5
Block 155 Contains Object ID 73514
Block Header start :- 0
Block Type 06-DATA :- 06
Block Format :- A2
Spare1 :- 00
Spare2 :- 00
Relative Block Address :- 20971675
SCN Base :- 1074483
SCN Wrap :- 0
Sequence :- 01
Flag 01-NEW :- 06
CheckSum :- 14737
Spare3 :- 0
Transaction Header start :- 20
Type 01-DATA 02-INDEX :- 01
Spare 1 :- 00
Spare 2 :- 00
Spare 3 :- 00
Object ID :- 73514
Cleanout SCN Base :- 1074478
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 :- 20971672
ITLSlot :- 1
Undo Segment :- 1
Undo Segment Slot :- 14
Transaction Sequence :- 632
Undo Block Address :- 12583042
Undo Sequence :- 162
Undo Record Number :- 51
Spare 1 :- 0
Flag :- 8194
_ktbitun :- 0
Base :- 1074483
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 :- 8054
Available Space after Commit:- 8032
Table Directory start :-114
Table :- 1
Offset :- 0
Number of Rows :- 2
Total Number of Rows :- 2
Row Directory start :-118
Row Offset 1 :- 8072(+100)
Row Offset 2 :- 8054(+100)
1 Row Header start :-8172
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start :-8154
Flags :- 2C Table Data
Lock Status :- 01
Number of Columns :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514
Block 157 Contains Object ID 73514
Block 158 Contains Object ID 73514
Block 159 Contains Object ID 73514
Subscribe to:
Posts (Atom)