[20181022]lob欄位的lobid來之那裡.txt
[20181022]lob欄位的lobid來之那裡.txt
--//這陣子探究lob欄位,遇到一個問題就是lob中的lobid來之那裡,
--//按照文件的介紹,
--//lobid來自SYS.IDGEN1$ ,我的測試遇到一些問題,專門研究看看.
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
--//按照連結,摘要如下
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.IDGEN1$
--//注:文件這裡標識SYS.IDGEN$ ,而PPT下面顯示的是IDGEN1$ ,我估計作者筆誤.而且我在12c沒有SYS.IDGEN$,找到SYS.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 3950401
2.測試:
--//session 1:
CREATE TABLE T
( ID NUMBER,
IMAGE BLOB
)
LOB (IMAGE) STORE AS securefile (ENABLE STORAGE IN ROW CHUNK 32768 RETENTION NOCACHE) ;
--//注:我建立CHUNK 32768,資料塊大小是8192.型別是securefile.
SCOTT@test01p> @ ddl scott.t
C100
------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"IMAGE" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
LOB ("IMAGE") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 32768
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
--//session 2.順便取一個看看:
SYS@test01p> select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;
NEXTVAL
----------
3950401
--//session 1:
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;
/
@ 10046on 12
insert into t values(2,'aa') ;
commit ;
@ 10046off
SCOTT@test01p> @ c1.txt
PL/SQL procedure successfully completed.
SCOTT@test01p> select rowid,id from t;
ROWID ID
------------------ ----------
AAAFlCAALAAAAC0AAA 2
AAAFlCAALAAAAC0AAB 1
SCOTT@test01p> @ rowid AAAFlCAALAAAAC0AAB
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
22850 11 180 1 0x2C000B4 11,180 alter system dump datafile 11 block 180
3.探究:
--//很奇怪跟蹤檔案並沒有IDGEN1$字串。查詢nextval也沒有對應字串。
SYS@test01p> alter system checkpoint;
System altered.
SCOTT@test01p> alter system dump datafile 11 block 180;
System altered.
Block header dump: 0x02c000b4
Object id on Block? Y
seg/obj: 0x5942 csc: 0x000000000030988c 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 0x0006.008.00000218 0x01801b36.0050.10 --U- 1 fsc 0x0000.0030988d
0x02 0x0006.01e.00000215 0x01801b36.0050.15 --U- 1 fsc 0x0000.003098b4
bdba: 0x02c000b4
data_block_dump,data header at 0x1a81064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x01a81064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f19
avsp=0x1f28
tosp=0x1f28
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f72
0x14:pri[1] offs=0x1f19
block_row_dump:
tab 0, row 0, @0x1f72
*** 2018-10-22T20:05:40.177470+08:00 (TEST01P(3))
tl: 38 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [31]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 73 00 0b 48 90 00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
05 00 00 01 01 aa
tab 0, row 1, @0x1f19
tl: 52 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [45]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 74 00 19 40 90 00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
13 22 00 06 61 a1 01 01 01 02 c0 00 c3 05 02 02 c0 01 37 2f
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 180 maxblk 180
--//注意看下劃線就是lobid，可以發現是線性增長的。
SCOTT@test01p> @ 16to10 3c4773
16 to 10 DEC
------------
3950451
--//0x3c4773=3950452.
--//如果對比前面的select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;的結果
--//3950401 加上 50 就是 3950451，正好與lobid的最後部分對上。
--//另外可以發現一個會話再插入lob欄位時，並沒有再從sys.IDGEN1$取順序號。而是在原來基礎上+1.
--//我估計一個會哈用完50個，再從sys.IDGEN1$取。這個操作不驗證了。
4.繼續探究：
--//在開啟一個會話，session 3:
SCOTT@test01p> insert into t values (3,'bb');
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select rowid,id from t where id=3;
ROWID ID
------------------ ----------
AAAFlCAALAAAAC2AAA 3
SCOTT@test01p> @ rowid AAAFlCAALAAAAC2AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
22850 11 182 0 0x2C000B6 11,182 alter system dump datafile 11 block 182
SCOTT@test01p> alter system checkpoint;
System altered.
SCOTT@test01p> alter system dump datafile 11 block 182;
System altered.
--//檢查轉儲：
Block header dump: 0x02c000b6
Object id on Block? Y
seg/obj: 0x5942 csc: 0x000000000030988c 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 0x0006.00a.00000217 0x01801b36.0050.16 --U- 1 fsc 0x0000.00309ad2
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02c000b6
data_block_dump,data header at 0x23231064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x23231064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f72
avsp=0x1f5e
tosp=0x1f5e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f72
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [31]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3d 0a 91 00 0b 48 90 00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
05 00 00 01 01 bb
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 182 maxblk 182
SCOTT@test01p> @ 16to10 3d0a91
16 to 10 DEC
------------
4000401
--//session 2:
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 4050401
SYS@test01p> select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;
NEXTVAL
----------
4000451
--//可以驗證確實如此。
5.再來看看chunk =32K 的情況：
tab 0, row 1, @0x1f19
tl: 52 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [45]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 74 00 19 40 90 00
13 22 00 06 61 a1 01 01 01 02 c0 00 c3 05 02 02 c0 01 37 2f
~~~~~~~~~~~--//檔案大小 418209=0x661a1
--//418209/8060 = 51.88697270471464019851,佔52塊
--//securefile的格式basic存在很大的不同。
--//參考的介紹：
01 01
01 02 c0 00 c3 05
02 02 c0 01 37 2f
SCOTT@test01p> @ dfb16 0x02c000c3
RFILE# BLOCK# TEXT
---------- ---------- -----------------------------------------------
11 195 alter system dump datafile 11 block 195 ;
SCOTT@test01p> @ dfb16 0x02c00137
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------
11 311 alter system dump datafile 11 block 311 ;
--//相當於dba=11,195,0x05表示# Blocks in extent
--// dba=11,311,0x2f=47.
--//這個chunk如何體會，不理解？
--//換systeminternals的Procmon.exe跟蹤看看，直接跟蹤tid。
"Time of Day","Process Name","PID","Operation","Path","Result","Detail","TID"
"21:00:28.2079556","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 1,597,440, Length: 40,960, I/O Flags: Non-cached, Priority: Normal","2956"
"21:00:28.2079975","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 2,547,712, Length: 385,024, I/O Flags: Non-cached, Priority: Normal","2956"
--//第2次：
"21:00:46.8923040","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 1,597,440, Length: 40,960, I/O Flags: Non-cached, Priority: Normal","2956"
"21:00:46.8924736","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 2,547,712, Length: 385,024, I/O Flags: Non-cached, Priority: Normal","2956"
--//1597440/8192 = 195 , 40960/8192 = 5
--//2547712/8192 = 311 , 385024/8192 = 47
--//都能對上。
6.總結：
--//不過為什麼跟蹤看不到取sequence的資訊有點奇怪。另外我掃描共享池也沒有發現痕跡，不知道為什麼...
--//看不出chunk的作用，也許對於securefile無用。
7.補充測試：
CREATE TABLE TX
( ID NUMBER,
IMAGE BLOB
)
LOB (IMAGE) STORE AS securefile (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE) ;
--//修改前面c1.txt指令碼 ，t => tx.
SCOTT@test01p> @ D:\tools\git_study\test1.git\c1.txt
PL/SQL procedure successfully completed.
SCOTT@test01p> select rowid,id from tx ;
ROWID ID
------------------ ----------
AAAFlFAALAAAADXAAA 1
SCOTT@test01p> @ rowid AAAFlFAALAAAADXAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
22853 11 215 0 0x2C000D7 11,215 alter system dump datafile 11 block 215
SCOTT@test01p> alter system dump datafile 11 block 215 ;
System altered.
Block header dump: 0x02c000d7
Object id on Block? Y
seg/obj: 0x5945 csc: 0x000000000030ac4f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2c000d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.011.00000218 0x01801b38.0050.0d --U- 1 fsc 0x0000.0030ac5d
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02c000d7
data_block_dump,data header at 0x9481064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x09481064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f3f
avsp=0x1f50
tosp=0x1f50
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f3f
block_row_dump:
tab 0, row 0, @0x1f3f
tl: 52 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [45]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3e 91 32 00 19 40 90 00
13 22 00 06 61 a1 01 01 01 02 c0 00 e3 05 02 02 c0 01 b7 2f
~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 215 maxblk 215
01 01
01 02 c0 00 e3 05
02 02 c0 01 b7 2f
--//可以看出chunk對於securefile基本無用，而且可以發現securefile的讀寫效率更高。
--//它儲存的是first chunk，然後是塊數。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2217166/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181020]lob欄位的索引段.txt索引
- [20210208]lob欄位與查詢的問題.txt
- [20181031]lob欄位與布隆過濾.txt
- ORACLE LOB大欄位維護Oracle
- [20181021]臨時表lob段建立在哪裡.txt
- [20231020]增加欄位的問題.txt
- [重慶思莊每日技術分享]-重建LOB欄位上的IndexIndex
- [20231008]bbed探究lob段.txt
- 【ORA-01555】Oracle LOB欄位匯出 報錯 ORA-01555Oracle
- [20180613]縮短欄位長度.txt
- proto裡的message欄位的編號
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- [20190227]簡單探究tab$的bojb#欄位.txt
- [20180905]lob與direct path read.txt
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- [20190810]如何索引一個超長欄位.txt索引
- [20210423]建立檢視以及欄位長度.txt
- MySQL 更新一個表裡的欄位等於另一個表某欄位的值MySql
- [20190531]lob型別pctversion 和 retention.txt型別
- 怎麼取的擴充套件模型裡表裡的欄位啊套件模型
- [20180408]那些函式索引適合欄位的查詢.txt函式索引
- Oracle-批量修改欄位裡面的值Oracle
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- SAP WM中階儲存型別裡的Full stk rmvl 欄位和Return Storage type欄位型別
- 從yjz那裡偷來的fread讀入掛
- [20201109]11.2.0.4增加欄位與預設值問題.txt
- 使用kubectl explain來了解可能的API物件欄位AIAPI物件
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- pydantic 欄位欄位校驗
- Django模型之欄位與約束Django模型
- SAP MM 物料主資料裡的‘Packaging Material Type'欄位
- PG裡常見的欄位有索引但未使用索引的原因索引
- -206 錯誤. 在表中找不到對應的資料欄位txt
- 【Mongo】mongo更新欄位為另一欄位的值Go
- SAP事務碼MM02裡的欄位屬性控制
- 預設文件模型裡的 pid欄位到底是什麼？模型
- Django之ORM常用欄位和引數DjangoORM
- 資料處理之欄位合併