[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：
--//標識為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

相關文章