This page was saved using WebZIP 7.0.3.1030 offline browser (Unregistered) on 09/04/11 14:29:53.
Address: http://home2oracle.narod.ru/block_internal/6f.html
Title: 6.Trans data-table (LOB)  •  Size: 265309  •  Last Modified: Thu, 04 Feb 2010 15:48:32 GMT
6   TRANS DATA (table with LOB) extract data
 
  0 1 2 3 4 5 6 7 8 9 A B C D E F   Start dump data blocks tsn: 12 file#: 11 minblk 183394 maxblk 183394
00 061 022 003 004 62 CC C2 025 6D EA 06 00 00 006 027 048   buffer tsn: 12 rdba: 0x02c2cc62 (11/183394)
10 3D 109 00 0010 0111 00 00 00 8D 76 00 0012 6D EA 06 00   Transaction scn: 0x0000.0006ea6d seq: 0x02 flg: 0x04 tail: 0xea6d0602
20 00 0013 00 00 0214 00 0315 0016 00 00 00 0017 1218 0019 01 0020    header frmt: 0x02 chkval: 0x103d type: 0x06=trans data
30 04 00 00 0021 E322 01 C0 0323 01 0024 0725 00 00 8026 00 0027   Block header dump:  0x02c2cc62
40 30 97 06 0028 10 00 00 00 05 00 00 00 63 01 C0 03   ITL  Object id on Block? Y
50 01 00 0F 00 01 00 00 00 00 00 00 00 0029 0130 01 0031    seg/obj: 0x768d  csc: 0x00.6ea6d  itc: 2  flg: O  typ: 1 - DATA
60 FF FF32 14 0033 E0 0534 09 0635 09 0636 00 0037 01 0038 E039 0540   Tab.directory      fsl: 0  fnx: 0x0 ver: 0x01
70 0041 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   Row directory  
80 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
90 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   0x01   0x0012.001.00000004  0x03c001e3.0001.07  C---    0  scn 0x0000.00069730
A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   0x02   0x0010.000.00000005  0x03c00163.0001.0f  ----    1  fsc 0x0000.00000000
B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   data_block_dump,data header at 0x71f305c
D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   ===============
E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   tsiz: 0x7a0
F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   hsiz: 0x14
100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   pbl: 0x071f305c
110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   bdba: 0x02c2cc62
120 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00        76543210
130 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   flag=--------
140 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   Free space ntab=1
150 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   nrow=1
160 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   frre=-1
170 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   fsbo=0x14
180 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   fseo=0x5e0
190 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   avsp=0x609
1A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   tosp=0x609
1B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   0xe:pti[0] nrow=1 offs=0
1C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   0x12:pri[0] offs=0x5e0
1D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   block_row_dump:
1E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   tab 0, row 0, @0x5e0
1F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   tl: 387 fb: --H-FL-- lb: 0x2  cc: 2
200 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   col  0: [360]
210 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 52 09 01 54 09 00 00
220 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    00 00 00 01 44 00 00 00 00 00 01 4c 4f 42 20 41 72 63 68 69 74 65 63 74 75
230 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    72 65 0a 95 20 41 20 4c 4f 42 20 63 61 6e 20 62 65 20 65 69 74 68 65 72 20
240 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    73 74 6f 72 65 64 20 69 6e 6c 69 6e 65 20 77 69 74 68 20 72 6f 77 0a 96 20
250 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    65 6e 61 62 6c 65 20 73 74 6f 72 61 67 65 20 69 6e 20 72 6f 77 20 6f 70 74
260 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    69 6f 6e 0a 96 20 57 69 6c 6c 20 62 65 20 61 75 74 6f 6d 61 74 69 63 61 6c
270 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    6c 79 20 6d 6f 76 65 64 20 6f 75 66 20 6f 66 20 6c 69 6e 65 20 69 66 20 69
280 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    74 0a 67 72 6f 77 73 20 6c 61 72 67 65 0a 95 20 85 20 6f 72 20 63 61 6e 20
290 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    62 65 20 64 65 66 69 6e 65 64 20 74 6f 20 61 6c 77 61 79 73 20 72 65 6d 61
2A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    69 6e 20 6f 75 74 6f 66 2d 0a 6c 69 6e 65 0a 96 20 64 69 73 61 62 6c 65 20
2B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    73 74 6f 72 61 67 65 20 69 6e 20 72 6f 77 20 6f 70 74 69 6f 6e 0a 95 20 41
2C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    20 4c 4f 42 20 63 61 6e 20 61 6c 73 6f 20 72 65 73 69 64 65 20 6f 75 74 73
2D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    69 64 65 20 74 68 65 20 64 61 74 61 62 61 73 65 0a 28 42 46 49 4c 45 73 29
2E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    0a 96 20 43 61 6e 20 62 65 20 69 6e 64 65 78 65 64 20 75 73 69 6e 67 20 4f
2F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    72 61 63 6c 65 20 54 65 78 74
300 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   col  1: [20]  00 54 00 01 02 08 00 00 00 01 00 00 00 01 00 00 00 00 52 0b
310 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   end_of_block_dump
320 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   End dump data blocks tsn: 12 file#: 11 minblk 183394 maxblk 183394
330 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
340 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
350 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
360 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
370 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
380 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
390 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
3F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
400 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
420 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
430 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
440 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
450 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
460 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
470 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
480 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
490 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
4F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
500 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
510 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
520 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
530 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
540 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
550 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
560 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
570 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
580 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
590 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5B0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5C0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
5F0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
600 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
610 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
620 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00    
630 00 00 00 00 00 00 00 00 00 00 00 00 2C42 0243 0244 FE    
640 68 0145 0046 54 00 01 02 0C 00 00 00 01 00 00 00 0147    
650 00 00 00 00 52 09 01 54 09 00 00 00 00 00 01 44    
660 00 00 00 00 00 01 4C 4F 42 20 41 72 63 68 69 74    
670 65 63 74 75 72 65 0A 95 20 41 20 4C 4F 42 20 63    
680 61 6E 20 62 65 20 65 69 74 68 65 72 20 73 74 6F    
690 72 65 64 20 69 6E 6C 69 6E 65 20 77 69 74 68 20    
6A0 72 6F 77 0A 96 20 65 6E 61 62 6C 65 20 73 74 6F   Rows data  
6B0 72 61 67 65 20 69 6E 20 72 6F 77 20 6F 70 74 69    
6C0 6F 6E 0A 96 20 57 69 6C 6C 20 62 65 20 61 75 74    
6D0 6F 6D 61 74 69 63 61 6C 6C 79 20 6D 6F 76 65 64    
6E0 20 6F 75 66 20 6F 66 20 6C 69 6E 65 20 69 66 20    
6F0 69 74 0A 67 72 6F 77 73 20 6C 61 72 67 65 0A 95    
700 20 85 20 6F 72 20 63 61 6E 20 62 65 20 64 65 66    
710 69 6E 65 64 20 74 6F 20 61 6C 77 61 79 73 20 72    
720 65 6D 61 69 6E 20 6F 75 74 6F 66 2D 0A 6C 69 6E    
730 65 0A 96 20 64 69 73 61 62 6C 65 20 73 74 6F 72    
740 61 67 65 20 69 6E 20 72 6F 77 20 6F 70 74 69 6F    
750 6E 0A 95 20 41 20 4C 4F 42 20 63 61 6E 20 61 6C    
760 73 6F 20 72 65 73 69 64 65 20 6F 75 74 73 69 64    
770 65 20 74 68 65 20 64 61 74 61 62 61 73 65 0A 28    
780 42 46 49 4C 45 73 29 0A 96 20 43 61 6E 20 62 65    
790 20 69 6E 64 65 78 65 64 20 75 73 69 6E 67 20 4F    
7A0 72 61 63 6C 65 20 54 65 78 7448 1449 0050 54 00 01 02    
7B0 08 00 00 00 01 00 00 00 01 00 00 00 00 52 0B51 2C    
7C0 01 02 24 00 54 00 01 02 0C 00 00 00 01 00 00 00    
7D0 01 00 00 00 00 52 09 00 10 09 00 00 00 00 00 00    
7E0 00 00 00 00 00 00 00 14 00 54 00 01 02 08 00 00    
7F0 00 01 00 00 00 01 00 00 00 00 52 0A 02 06 6D EA52    
 
SQL> create table TEST_LOB  
(LOB_1 CLOB,  
 LOB_2 CLOB)  
tablespace TEST   
lob(lob_1) store as Lob_1 (enable storage in row)  
lob(lob_2) store as Lob_2 (disable storage in row);  
 
SQL>insert into  test_lob values ( 'LOB Architecture  
• A LOB can be either stored inline with row  
– enable storage in row option  
– Will be automatically moved ouf of line if it  
grows large  
• … or can be defined to always remain outof-  
line  
– disable storage in row option  
• A LOB can also reside outside the database  
(BFILEs)',  
 'LOB Architecture  
• A LOB can be either stored inline with row  
– enable storage in row option  
– Will be automatically moved ouf of line if it  
grows large  
• … or can be defined to always remain outof-  
line  
– disable storage in row option  
• A LOB can also reside outside the database  
(BFILEs)');  
 
SQL> update test_lob set lob_2=  
 'It describes an important piece of data warehouse infrastructure that is usually overlooked:  
SQL statement usage tracking and all of the benefits in performance tuning of the DW  
as well that become possible, as well as opportunities to detect the need for   
data model re-design due to changing end-user requirements.'  ;    
 
SQL> alter system checkpoint;  
SQL> select segment_name,segment_type,header_file,header_block  
 from dba_segments where tablespace_name='TEST';  
 
SEGMENT_NAME     SEGMENT_TYPE HEADER_FILE   HEADER_BLOCK  
TEST_LOB TABLE 11 183393  
SYS_IL0000030349C00001$$ LOBINDEX 11 183409  
SYS_IL0000030349C00002$$ LOBINDEX 11 183425  
LOB_1 LOBSEGMENT 11 183401  
LOB_2 LOBSEGMENT 11 183417  
 
SQL> alter system dump datafile 11  block 183394;  
1
Block types.
type:0x06
2
The format of the cache header was changed for Oracle8. Under Oracle8 and 9, the value is always 2. Previously, it was 1.
frmt:0x02
3
spare1
4
spare2
5
The tablespace relative database block address (RDBA). This is constructed from the tablespace relative file number (10 bits), and
the block number of the data block within that file (22 bits).
RDBA:0x02C2CC62
6
The SCN at which the block was last changed. The low-order 4 bytes are called the SCN base, and the high-order 2 bytes are called the SCN wrap.
scn: 0x0000.0006EA6D
7
A sequence number incremented for each change to a block at the same SCN. If the sequence number wraps, a new SCN must be allocated.  The value 0xff is
reserved. When present it indicates that the block has been marked as corrupt by Oracle.
seq:0x02
8
Flag.
1 - virgin block
2 - last change to the block was for a cleanout operation
4 - checksum value is set
8 - temporary data
flg:0x04
9
An optional checksum of the block contents. When a block is written, the checksum is either cleared or set depending on the
setting of the db_block_checksum parameter. When a block is read, the checksum is verified if present and if the parameter is
set to TRUE. Checksums are always calculated and checked for blocks in the SYSTEM tablespace. The checksum is the XOR of
all the other 2-byte pairs in the block. Thus when a block with a checksum is checked, the XOR of all the 2-byte words in the
block should be 0.
chkval:0x103D
10
spare3
11
Flag for data or index block
 1 - Data
 2 - Index
 type:01
12
The object number of the segment in OBJ$. 
seg/obj: 0x0000768D (30349)
13
Cleanout system change,used to show when block cleanout was last performed.
csc: 0x0006EA6D
14
Count of the ITLs on the block.
 itc:2
15
Flag (no ASSM) :
'-' means that the block is not on the freelist,
'O' - in freelist
Object id on Block? Y flg: - ver: 0x01
16
Index to the first slot on the ITL freelist.
fsl:0
17
The RDBA of the next block on the segment freelist. Null if this block is not on a freelist.
 fnx: 0x00000000
18
The ITL is used to mark the row locked until the transaction completes.
XID is  the  pointer to the rollback segment transaction table slot  -  Undoseg#.slot#.wrap
The  Undoseg# is the rollback segment, the slot# refers to  a slot in trunsaction  table, and  wrap refers to
the number of times that the slot has been used  in  a trunsaction.
Pointer to the rollback segment transaction table slot.
XID(undoseg#.slot#.wrap):0x0012.0001.00000004
19
Rollback segment.
XID:undoseg 18
20
Refers to a slot in the transaction table.
 XID:slot# 1
21
Refers to the number of times that the slot has been used (i.e. incarnation or version) in a transaction.
XID:wrap 0x00000004
22
The UBA is the pointer to the tail of the undo chain for the block.
UBA:(undodatablockaddr.sequence#.record#): 0x03C001E3.0001.07
23
UBA:DBA of the rollback segment block file- 15 block- 483
24
UBA:sequence 1
25
UBA:record 07
26
Lck Flag
---- transaction is active, or committed pending cleanout    
C--- transaction has been committed and locks cleaned out  
-B-- this undo record contains the undo for this ITL entry   
--U- transaction committed (maybe long ago); SCN is an upper bound   
---T = transaction was still active at block cleanout SCN  
Lck Flag: C---
Lck
The number of row-level locks held in the block by this transaction
Lck: 0
(1000 - 0000 0000 0000)
27
If the transaction has been cleaned out, this is the commit SCN or an upper bound thereof. Otherwise the leading two
bytes contain the free space credit for the transaction - that is, the number of bytes freed in the block by the transaction
(free space credit) Fsc:0
28
SCN or freespace credit.
If the transaction has been cleaned out, this is the commit SCN or an upper bound thereof. Otherwise the
leading two bytes contain the free space credit for the transaction - that is, the number of bytes freed in
the block by the transaction.
(SCN of commited TX ) Scn: 0x00932305
29
Flag  
n=pctfree hit (clusters),
f=don ' t put on freelist,
k=flushable cluster keys
flag:------
30
Number of tables. (>1 so this is a cluster)
ntab:1
31
Number of rows.
nrow:1
32
First free row index entry (-1 - you have to add one)
free: -1
33
Free space begin offset.
fsbo:0014
34
Free space end offset.
fseo:05E0
35
Available space in the block.
avsp:1545
36
Total available space when all TXs commit.
tosp:1545
37
Table offset.
 offs:0
38
Rows in table.
nrow :1
39
 Begin data row address...
40
offset @0x5e0 (1504)
41
Begin free space.
42
table  0,  row   15 (offset @0x14d (333))
K - Cluster Key (Flags may change meaning if this is set to show HASH cluster) 
C - Cluster table member    
H - Head piece of row     
D - Deleted row    
F - First data piece     
L - Last data piece   
P - First column continues from previous piece  
N - Last column continues in next piece 
fb: --H-FL--
( 0010  1100 )
 
43
Lock byte.
Corresponds with above ITL lck expresses this line whether by lock
lb: 0x2
44
column count
cc: 2
45
len 360
46
Enable storage in row inline LOB...
47

00 54 00 01 02 0c 00 00 00 01
48
LOB locator is a pointer to LOB instances physical location
– Locator works the same way for both persistent and temporary LOBs
– 20 bytes in size
– Contains 10 byte LOB ID (2B+8B LOB OID)
– Includes some metainformation
LOB locator column dump and structure:
len(2), vsn(2), flg(4), bytl(2), lobid(10), inode(16), data
00 54 00 01 02 0c 00 00 00 01
00 00 00 01 00 00 00 00 52 09  - LOB  ID
01 54 09 00 00 00 00 00 01 44 00 00 00 00 00 01   -kdlinode
data:
"LOB Architecture
• A LOB can be either stored inline with row
– enable storage in row option
– Will be automatically moved ouf of line if it
grows large
• … or can be defined to always remain outof-
line
– disable storage in row option
• A LOB can also reside outside the database
(BFILEs)"

49
len 20
50
Disable storage in row...
51
LOB locator.
00 54 00 01 02 08 00 00 00 01
00 00 00 01 00 00 00 00 52 0B  - LOB ID

52
Tail is comprised of the low-order two bytes of the SCN base followed by the block type and the sequence number. The consistency of the
 header and tail is checked whenever a block is read. This detects most block corruptions, in particular split blocks from hot backups.
tail:0xEA6D0602