--//链接:http://www.itpub.net/thread-2105833-1-1.html的讨论.
--//在讨论前先看看lob字段的索引段.
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
CREATE TABLE Tx
( ID NUMBER,
IMAGE cLOB
)
LOB (IMAGE) STORE AS basicfile ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE ) ;
SCOTT@test01p> select table_name,index_name,tablespace_name from user_indexes where table_name ='TX' ;
TABLE_NAME INDEX_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
TX SYS_IL0000022836C00002$$ USERS
SCOTT@test01p> select table_name,column_name,segment_name,tablespace_name from USER_LOBS where table_name ='TX' ;
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------ --------------------
TX IMAGE SYS_LOB0000022836C00002$$ USERS
SCOTT@test01p> select * from dba_extents where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
no rows selected
--//延迟段建立，导致没有段的分配。
2.插入数据看看:
SCOTT@test01p> insert into tx values (1,lpad('a',4000,'a'));
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> column PARTITION_NAME noprint
SCOTT@test01p> select * from dba_extents where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------- ------------------------------ ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT SYS_IL0000022836C00002$$ LOBINDEX USERS 0 11 192 65536 8 11
SCOTT SYS_LOB0000022836C00002$$ LOBSEGMENT USERS 0 11 184 65536 8 11
SCOTT@test01p> select segment_type,segment_name,header_file,header_block from dba_segments
where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
SEGMENT_TYPE SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------ ------------------------------ ----------- ------------
LOBINDEX SYS_IL0000022836C00002$$ 11 194
LOBSEGMENT SYS_LOB0000022836C00002$$ 11 186
SCOTT@test01p> alter system checkpoint;
System altered.
--//转储lob索引段的root节点看看.
SCOTT@test01p> alter system dump datafile 11 block 195;
System altered.
--//你可以发现转储索引没有信息：
Block header dump: 0x02c000c3
Object id on Block? Y
seg/obj: 0x5936 csc: 0x00000000002c93e1 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2c000c0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x00000000002c93e1
Leaf block dump
===============
header address 32313444=0x1ed1064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8036=0x1f64
kdxcoavs 8000
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
kdxlebksz 8036
*** dummy key ***
row#0[8000] flag: -------, lock: 0, len=36, data:(32):
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 0; (0):
col 1; len 0; (0):
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 11 minblk 195 maxblk 195
--//看来即使lob段有数据,lob索引段未必有信息.插入大的lob看看.
3.插入大一些lob:
CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION;
D:\tmp\expdp>ls -l 1.txt
-rw-rw-rw- 1 user group 418209 Oct 20 20:08 1.txt
$ cat c3.txt
DECLARE
b_file BFILE;
b_lob CLOB;
src_offset INT := 1;
dest_offset INT := 1;
csid INT := 0;
lc INT := 0;
warning INT;
BEGIN
INSERT INTO tx
VALUES (2, EMPTY_CLOB ())
RETURN image
INTO b_lob;
b_file := BFILENAME ('TMP_EXPDP', '1.txt');
DBMS_LOB.open (b_file, DBMS_LOB.file_readonly);
DBMS_LOB.loadclobfromfile
(
b_lob
,b_file
,DBMS_LOB.getlength (b_file)
,dest_offset
,src_offset
,csid
,lc
,warning
);
DBMS_LOB.close (b_file);
COMMIT;
END;
/
SCOTT@test01p> @ c3.txt
PL/SQL procedure successfully completed.
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 11 block 195;
System altered.
--//检查转储：
Block header dump: 0x02c000c3
Object id on Block? Y
seg/obj: 0x5936 csc: 0x00000000002c93e1 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2c000c0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0003.017.0000021e 0x01802279.0054.41 --U- 12 fsc 0x0000.002c9464
Leaf block dump
===============
header address 32313444=0x1ed1064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 12
kdxcofbo 60=0x3c
kdxcofeo 7436=0x1d0c
kdxcoavs 7376
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
--//参看链接：http://blog.itpub.net/267265/viewspace-2124744/=>[20160908]唯一索引与非唯一索引.txt
kdxlebksz 8036
row#0[7986] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 d1 02 c0 00 d5 02 c0 00 d6 02 c0 00 d2 02 c0 00 d3 02 c0 00 d4 02
c0 00 df 02 c0 00 d9
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
col 1; len 4; (4): 00 00 00 0c
~~~~~~~~~~~
row#1[7936] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 dd 02 c0 00 de 02 c0 00 da 02 c0 00 db 02 c0 00 dc 02 c0 00 e7 02
c0 00 e1 02 c0 00 e5
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 14
row#2[7886] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 e6 02 c0 00 e2 02 c0 00 e3 02 c0 00 e4 02 c0 00 ef 02 c0 00 e9 02
c0 00 ed 02 c0 00 ee
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 1c
row#3[7836] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 ea 02 c0 00 eb 02 c0 00 ec 02 c0 00 f7 02 c0 00 f1 02 c0 00 f5 02
c0 00 f6 02 c0 00 f2
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 24
row#4[7786] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 f3 02 c0 00 f4 02 c0 00 ff 02 c0 00 f9 02 c0 00 fd 02 c0 00 fe 02
c0 00 fa 02 c0 00 fb
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 2c
row#5[7736] flag: -------, lock: 2, len=50, data:(32):
02 c0 00 fc 02 c0 01 07 02 c0 01 01 02 c0 01 05 02 c0 01 06 02 c0 01 02 02
c0 01 03 02 c0 01 04
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 34
row#6[7686] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 0f 02 c0 01 09 02 c0 01 0d 02 c0 01 0e 02 c0 01 0a 02 c0 01 0b 02
c0 01 0c 02 c0 01 17
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 3c
row#7[7636] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 11 02 c0 01 15 02 c0 01 16 02 c0 01 12 02 c0 01 13 02 c0 01 14 02
c0 01 1f 02 c0 01 19
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 44
row#8[7586] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 1d 02 c0 01 1e 02 c0 01 1a 02 c0 01 1b 02 c0 01 1c 02 c0 01 27 02
c0 01 21 02 c0 01 25
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 4c
row#9[7536] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 26 02 c0 01 22 02 c0 01 23 02 c0 01 24 02 c0 01 2f 02 c0 01 29 02
c0 01 2d 02 c0 01 2e
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 54
row#10[7486] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 2a 02 c0 01 2b 02 c0 01 2c 02 c0 01 37 02 c0 01 31 02 c0 01 35 02
c0 01 36 02 c0 01 32
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 5c
row#11[7436] flag: -------, lock: 2, len=50, data:(32):
02 c0 01 33 02 c0 01 34 02 c0 01 3f 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4): 00 00 00 64
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 11 minblk 195 maxblk 195
--//可以发现索引插入12条键值。
--//索引键值col 0; len 10; (10): 00 00 00 01 00 00 00 34 a6 54 都是一样的.标识lobid.
--//http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals标识为lobid,如何得来呢?
Basic Files LOB ID
. LOB ID is a 10 byte number identifying individual instance of a LOB
. Allocated when LOB value is created including EMPTY_CLOB() etc
. Format is
.
.
. For example:
SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_size FROM v$_sequences where sequence_name='IDGEN1$' ;
Sequence Owner Sequence Name Next Value Increment By Cache Size
SYS IDGEN1$ 37401 50 20
--//索引键值col 1表示first chunk number.00 00 00 0c表示12,也就是第12chunk(从0开始记数,块内已经保存12个chunk了)
3.转储lob段看看：
SCOTT@test01p> select rowid,tx.id from tx;
ROWID ID
------------------ ----------
AAAFk0AALAAAAC1AAA 1
AAAFk0AALAAAAC1AAB 2
SCOTT@test01p> @ rowid AAAFk0AALAAAAC1AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
22836 11 181 0 0x2C000B5 11,181 alter system dump datafile 11 block 181
SCOTT@test01p> alter system dump datafile 11 block 181;
System altered.
--//检查转储：
Block header dump: 0x02c000b5
Object id on Block? Y
seg/obj: 0x5934 csc: 0x00000000002c93e8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2c000b0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.002.000002cc 0x01800698.005b.22 --U- 1 fsc 0x0000.002c93ea
0x02 0x0003.017.0000021e 0x01802279.0054.42 --U- 1 fsc 0x0000.002c9464
bdba: 0x02c000b5
data_block_dump,data header at 0x1ed1064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x01ed1064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1ee3
avsp=0x1ef8
tosp=0x1ef8
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f69
0x14:pri[1] offs=0x1ee3
block_row_dump:
tab 0, row 0, @0x1f69
tl: 47 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [40]
00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 34 a6 53 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 02 c0 00 bd
~~~~~~~~~~~
tab 0, row 1, @0x1ee3
tl: 91 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 03
col 1: [84]
00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 34 a6 54 00 40 05 00 00
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
00 00 66 1b 2a 00 00 00 00 00 68 02 c0 00 be 02 c0 00 bf 02 c0 00 bb 02 c0
~~~~~~~~~~~ ~~~~~~~~
00 bc 02 c0 00 cf 02 c0 00 c9 02 c0 00 cd 02 c0 00 ce 02 c0 00 ca 02 c0 00
cb 02 c0 00 cc 02 c0 00 d7
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 181 maxblk 181
--//注意看下划线内容明显表示dba地址。@@@@@@下的内容表示键值之类的信息(看看前面索引段的转储字段col 0)
--//0x2c000bd=46137533
SCOTT@test01p> @ dfb16 0x2c000bd
RFILE# BLOCK# TEXT
---------- ---------- -----------------------------------------------------
11 189 alter system dump datafile 11 block 189 ;
SCOTT@test01p> select segment_type,segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$','TX');
SEGMENT_TYPE SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------ ------------------------------ ----------- ------------
TABLE TX 11 178
LOBINDEX SYS_IL0000022836C00002$$ 11 194
LOBSEGMENT SYS_LOB0000022836C00002$$ 11 186
--//第2条数据,(84-36)/4 = 12,12块根本放不下1.txt内容(1.txt大小418209)。418209*2/(8192-56-4) = 102.85
--//注：字符集影响，1个英文字符占2个字节(中文1个字占2个字节)。另外1个lob段的数据块有1个头占56字节，tail占4个字节。
--//我个人建议不要使用clob类型,最好使用blob类型,保持原样存储.
--//如果你注意前面的索引条目，可以发现1个键值最多保存8个块地址，这样
--//8*11+3 = 91,加上表段看到12。 91+12 = 103，与猜测一致。
--//如果想了解更多lob内容看链接：
http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals
--//以前看过，现在应该重新再看1遍^_^。
总结：
--//1.如果这样ZALBB兄如果保存的lob字段，使用临时表，lob 索引段即使保存在system表空间，如果lob很小估计问题也不是太大。
--//8192-56-4 = 8132,8132*12 = 97584,97584/2 = 48792,也就是小于48792英文字节(48792/1024 = 47.6484375K)，不会使用lob段索引。
--//当然如果多个用户都建立lob索引段(lob很大的情况下)，这样消耗也是很可观的，毕竟放在system表空间不是很好。
--//2.另外我个人建议不要使用clob类型,而是使用blob类型,也许在一定程度节约磁盘空间.
--//3.chunk大小是定义表是可以指定的,最大32K,比如像我们应用一个lob字段一般平均lob占用2XXK,这样采用32K chunk,一定程度减少索引段使用.
--//4.lob的索引段还有维持读一致的作用,当修改lob时,会在索引段记录修改前后的块.具体一些细节看
--// http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals,里面有演示.