[20181021]临时表lob段建立在哪里.txt

来源：这里教程网时间：2026-03-03 12:08:39 作者：

[20181021]临时表lob段建立在哪里.txt --//链接:http://www.itpub.net/thread-2105833-1-1.html的讨论. --//才知道全局临时表中的lob字段的索引，是建在SYSTEM表空间上的,而12c没有指明，看看具体在哪里。 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 2.测试: CREATE GLOBAL TEMPORARY TABLE T ( ID NUMBER, IMAGE BLOB ) ON COMMIT PRESERVE ROWS LOB (IMAGE) STORE AS securefile (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE) ; * ERROR at line 6: ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "" --//如果指定securefile报错,也就是临时表的lob仅仅支持basicfile.因为临时表空间不是assm的。 CREATE GLOBAL TEMPORARY TABLE T ( ID NUMBER, IMAGE BLOB ) ON COMMIT PRESERVE ROWS LOB (IMAGE) STORE AS basicfile (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE) ; --//看看如何定义: SCOTT@test01p> @ ddl scott.t C100 -------------------------------------------------------- CREATE GLOBAL TEMPORARY TABLE "SCOTT"."T" ( "ID" NUMBER, "IMAGE" CLOB ) ON COMMIT PRESERVE ROWS ; --//看到的内容很少。也说明临时表支持的内容很少。 3.插入数据看看： --//链接http://blog.itpub.net/267265/viewspace-2217009/=>[20181020]lob字段的索引段.txt --//里面提到lob要达到一定的程度大于12块才会使用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 c1.txt declare b_file bfile; b_lob blob; begin insert into t values(1,empty_blob()) return image into b_lob; b_file:=bfilename('TMP_EXPDP','1.txt'); dbms_lob.open(b_file,dbms_lob.file_readonly); dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file)); dbms_lob.close(b_file); commit; end; / SCOTT@test01p> @ c1.txt PL/SQL procedure successfully completed. SCOTT@test01p> select table_name,index_name,tablespace_name from user_indexes where TABLE_NAME='T'; TABLE_NAME INDEX_NAME TABLESPACE_NAME -------------------- ------------------------------ -------------------- T SYS_IL0000022846C00002$$ --//lob的索引段没有指定表空间。 SCOTT@test01p> select table_name,column_name,segment_name,tablespace_name from USER_LOBS; TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME -------------------- -------------------- ------------------------------ -------------------- T IMAGE SYS_LOB0000022846C00002$$ TEMP --//lob段在temp临时表空间。那么lob的索引段在哪里呢? 4.继续测试： --//测试前准备： SCOTT@test01p> select object_name,object_id,data_object_id from dba_objects where owner=user and object_name in ('SYS_IL0000022846C00002$$','SYS_LOB0000022846C00002$$'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID ------------------------------ ---------- -------------- SYS_IL0000022846C00002$$ 22848 22848 SYS_LOB0000022846C00002$$ 22847 22847 SCOTT@test01p> select rowid,id from t; ROWID ID ------------------ ---------- AAQAYHAABAAAAYIAAA 1 SCOTT@test01p> @ rowid AAQAYHAABAAAAYIAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 4195847 1 1544 0 0x400608 1,1544 alter system dump datafile 1 block 1544 --//注意这里的1指的是临时表数据文件。另外datafile改写为tempfile. SCOTT@test01p> alter system dump tempfile 'D:\app\oracle\oradata\test\test01p\TEMP01.DBF' block 1544; System altered. Block header dump: 0x00400608 Object id on Block? Y seg/obj: 0x400607 csc: 0x00000000002ea9b3 itc: 2 flg: O typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.000.000002d4 0x018006bc.005b.14 ---- 1 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x00400608 data_block_dump,data header at 0x1daa105c =============== tsiz: 0x1fa0 hsiz: 0x14 pbl: 0x1daa105c 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1eef avsp=0x1f31 tosp=0x1f31 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1eef block_row_dump: tab 0, row 0, @0x1eef tl: 91 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [84] 00 70 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 00 38 76 b2 00 40 05 00 00 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>lobid 00 00 33 0d 95 00 00 00 00 00 02 00 40 05 8a 00 40 05 8b 00 40 05 8c 00 40 ~~~~~~~~~~~ ~~~~~~~~~~~=>chunk 05 8d 00 40 05 8e 00 40 05 8f 00 40 05 90 00 40 05 91 00 40 05 92 00 40 05 93 00 40 05 94 00 40 05 95 end_of_block_dump End dump data block from file D:\APP\ORACLE\ORADATA\TEST\TEST01P\TEMP01.DBF minblk 1544 maxblk 1544 --//0x40058a=4195722 --//4195722= alter system dump datafile 1 block 1418. --//补充说明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 <X><Y> where . <X> is a currently unknown 4-byte number (always 1) . <Y> is a 6-byte number generated from sequence SYS.IDGEN$ . For example: --//12c我没有找到SYS.IDGEN$ ，找到SYS.IDGEN1$,不过原始文档下面显示的是 IDGEN1$ 。 SELECT sequence_owner ,sequence_name ,increment_by ,cache_size ,last_number FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = 'IDGEN1$' AND sequence_owner = 'SYS'; SEQUENCE_OWNER SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER -------------------- -------------------- ------------ ---------- ----------- SYS IDGEN1$ 50 1000 3800401 --//3800401=0x39fd51 SYS@test01p> select sys.IDGEN1$.nextval from dual; NEXTVAL ---------- 3800401 SYS@test01p> select sys.IDGEN1$.nextval from dual; NEXTVAL ---------- 3800451 --//0x3876b2 =3700402,好像对不上，先放一下。 --//使用10046跟踪。 alter system flush buffer_cache; @10046on 12 set feedback only select * from t; @10046off set feedback on --//检查转储： ===================== PARSING IN CURSOR #911805216 len=15 dep=0 uid=81 oct=3 lid=81 tim=6640685435 hv=1134051363 ad='7ff1560b970' sqlid='89km4qj1thh13' select * from t END OF STMT PARSE #911805216:c=93601,e=375624,p=23,cr=262,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=6640685434 EXEC #911805216:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=6640685678 WAIT #911805216: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=22846 tim=6640685775 WAIT #911805216: nam='SQL*Net message from client' ela= 8613 driver id=1413697536 #bytes=1 p3=0 obj#=22846 tim=6640694472 WAIT #911805216: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=22846 tim=6640694715 FETCH #911805216:c=0,e=143,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1601196873,tim=6640694770 WAIT #911805216: nam='SQL*Net message from client' ela= 123 driver id=1413697536 #bytes=1 p3=0 obj#=22846 tim=6640694957 WAIT #0: nam='db file sequential read' ela= 12308 file#=203 block#=1288 blocks=1 obj#=22848 tim=6640707474 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//从 obj#=22848看这个段是临时表数据文件，file#=203也说明问题。 WAIT #0: nam='direct path read temp' ela= 11740 file number=203 first dba=1418 block cnt=2 obj#=22847 tim=6640719494 WAIT #0: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=22847 tim=6640719698 WAIT #0: nam='SQL*Net more data to client' ela= 125 driver id=1413697536 #bytes=8137 p3=0 obj#=22847 tim=6640719887 ..... WAIT #0: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=8136 p3=0 obj#=22847 tim=6640727319 LOBREAD: type=PERSISTENT LOB,bytes=418209,c=0,e=32315,p=53,cr=54,cu=0,tim=6640727393 WAIT #0: nam='SQL*Net message from client' ela= 4721 driver id=1413697536 #bytes=1 p3=0 obj#=22847 tim=6640732285 FETCH #911805216:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=6640732447 STAT #911805216 id=1 cnt=1 pid=0 pos=1 obj=22846 op='TABLE ACCESS FULL T (cr=3 pr=0 pw=0 str=1 time=75 us cost=2 size=2015 card=1)' WAIT #911805216: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=22847 tim=6640732735 *** 2018-10-21T21:30:14.755645+08:00 (TEST01P(3)) WAIT #911805216: nam='SQL*Net message from client' ela= 4182356 driver id=1413697536 #bytes=1 p3=0 obj#=22847 tim=6644915142 CLOSE #911805216:c=0,e=45,dep=0,type=0,tim=6644915548 ===================== --//换systeminternals的Procmon.exe跟踪看看，直接跟踪tid。 "Time of Day","Process Name","PID","Operation","Path","Result","Detail","TID" "21:45:52.0534365","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 12,640,256, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.0728711","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 12,648,448, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.0747488","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 10,551,296, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1048336","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,616,256, Length: 16,384, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1048984","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,632,640, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1049395","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,665,408, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1049772","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,698,176, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1172490","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,730,944, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1176973","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,763,712, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1181074","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,796,480, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1184863","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,829,248, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1188550","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,862,016, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1192458","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,894,784, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1202438","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,927,552, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1206736","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,960,320, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1210636","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 11,993,088, Length: 32,768, I/O Flags: Non-cached, Priority: Normal","5688" "21:45:52.1214959","ORACLE.EXE","5548","ReadFile","D:\app\oracle\oradata\test\test01p\TEMP01.DBF","SUCCESS","Offset: 12,025,856, Length: 16,384, I/O Flags: Non-cached, Priority: Normal","5688" --//可以发现全部读临时文件。 12640256/8192 = 1543 => 临时表段头 12648448/8192 = 1544 => 临时表段 10551296/8192 = 1288 => 临时表的索引段。 11616256/8192 = 1418 => lob段 --//很奇怪的是10046跟踪仅仅看到1个,cnt=2,也就是16384可以对上。 WAIT #0: nam='direct path read temp' ela= 11740 file number=203 first dba=1418 block cnt=2 obj#=22847 tim=6640719494 5.转储lob索引段看看： SYS@test01p> alter system dump tempfile 'D:\app\oracle\oradata\test\test01p\TEMP01.DBF' block 1288; System altered. Block header dump: 0x00400508 Object id on Block? Y seg/obj: 0x400507 csc: 0x00000000002eaa4e itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0009.000.000002d4 0x018006bc.005b.13 C--- 0 scn 0x00000000002ea9e7 Leaf block dump =============== header address 484905052=0x1ce7105c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 5 kdxcofbo 46=0x2e kdxcofeo 7786=0x1e6a kdxcoavs 7740 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 0=0x0 kdxledsz 32 kdxlebksz 8036 *** 2018-10-21T21:52:29.558991+08:00 (TEST01P(3)) row#0[7986] flag: -------, lock: 0, len=50, data:(32): 00 40 05 96 00 40 05 97 00 40 05 98 00 40 05 99 00 40 05 9a 00 40 05 9b 00 40 05 9c 00 40 05 9d col 0; len 10; (10): 00 00 00 01 00 00 00 38 76 b2 col 1; len 4; (4): 00 00 00 0c row#1[7936] flag: -------, lock: 0, len=50, data:(32): 00 40 05 9e 00 40 05 9f 00 40 05 a0 00 40 05 a1 00 40 05 a2 00 40 05 a3 00 40 05 a4 00 40 05 a5 col 0; len 10; (10): 00 00 00 01 00 00 00 38 76 b2 col 1; len 4; (4): 00 00 00 14 row#2[7886] flag: -------, lock: 0, len=50, data:(32): 00 40 05 a6 00 40 05 a7 00 40 05 a8 00 40 05 a9 00 40 05 aa 00 40 05 ab 00 40 05 ac 00 40 05 ad col 0; len 10; (10): 00 00 00 01 00 00 00 38 76 b2 col 1; len 4; (4): 00 00 00 1c row#3[7836] flag: -------, lock: 0, len=50, data:(32): 00 40 05 ae 00 40 05 af 00 40 05 b0 00 40 05 b1 00 40 05 b2 00 40 05 b3 00 40 05 b4 00 40 05 b5 col 0; len 10; (10): 00 00 00 01 00 00 00 38 76 b2 col 1; len 4; (4): 00 00 00 24 row#4[7786] flag: -------, lock: 0, len=50, data:(32): 00 40 05 b6 00 40 05 b7 00 40 05 b8 00 40 05 b9 00 40 05 ba 00 40 05 bb 00 40 05 bc 00 40 05 bd col 0; len 10; (10): 00 00 00 01 00 00 00 38 76 b2 col 1; len 4; (4): 00 00 00 2c ----- end of leaf block Logical dump ----- ----- end of leaf block dump ----- End dump data block from file D:\APP\ORACLE\ORADATA\TEST\TEST01P\TEMP01.DBF minblk 1288 maxblk 1288 --//418209/(8192-56-4) = 51.42757009345794392523,占52块。 --//表块内占12chunk。lob index 5条，每条8个chunk，12+5*8 = 52。能对上，可以发现blob比clob节省磁盘空间。总结： --//可以发现12c，临时表的lob索引段使用临时表空间。

编辑推荐：

返回列表

[20181021]临时表lob段建立在哪里.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推