[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
.
.
. 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
-
lfree
2018-10-22 21:20:55 -
Oracle
- 原创