[20190531]lob型別pctversion 和 retention.txt
[20190531]lob型別pctversion 和 retention.txt
--//昨天看Secrets of the oracle database 電子書,發現lob型別的pctversion 和 retention測試,在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
SCOTT@test01p> SHOW PARAMETER undo_retention
NAME TYPE VALUE
-------------- ------- ------
undo_retention integer 3600
2.測試:
CREATE TABLE blog ( username VARCHAR2(30), date_time DATE, text CLOB, img BLOB)
LOB (text) STORE AS blog_text_clob (RETENTION),
LOB (img) STORE AS blog_img_blob (PCTVERSION 10);
SCOTT@test01p> SELECT COLUMN_NAME,pctversion, retention FROM user_lobs WHERE table_name='BLOG';
COLUMN_NAME PCTVERSION RETENTION
-------------------- ---------- ----------
TEXT
IMG 10
SCOTT@test01p> SELECT COLUMN_NAME,pctversion, retention FROM dba_lobs WHERE table_name='BLOG';
COLUMN_NAME PCTVERSION RETENTION
-------------------- ---------- ----------
TEXT
IMG 20
--//顯示與書測試不符合.是不是可以這麼認為PCTVERSION不顯示就是採用RETENTION,還是12c的pdb導致的情況呢?
SELECT object_name, flags
FROM sys.lob$ l, dba_objects o
WHERE l.lobj# = o.object_id
AND o.object_name IN ('BLOG_TEXT_CLOB', 'BLOG_IMG_BLOB');
OBJECT_NAME FLAGS
-------------------- ----------
BLOG_IMG_BLOB 1089
BLOG_TEXT_CLOB 1121
--//這個測試也是與書測試不符合.
--//書中執行結果如下:
OBJECT_NAME FLAGS
-------------------- ----------
BLOG_IMG_BLOB 65
BLOG_TEXT_CLOB 97
--//1089-65 = 1024
--//1121-97 = 1024
--//我估計lob 12c使用SECUREFILE的緣故. flag=1024表示SECUREFILE型別.
SCOTT@test01p> @ ddl scott.blog
C100
--------------------------------------------------------------------
CREATE TABLE "SCOTT"."BLOG"
( "USERNAME" VARCHAR2(30),
"DATE_TIME" DATE,
"TEXT" CLOB,
"IMG" BLOB
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
LOB ("TEXT") STORE AS SECUREFILE "BLOG_TEXT_CLOB"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES )
LOB ("IMG") STORE AS SECUREFILE "BLOG_IMG_BLOB"(
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES ) ;
--//從書中摘要:
There's the missing piece of information: if retention is specified, then LOB$.FLAGS, which is obviously a bit vector,
is incremented by 32. So the bit that represents 25 is set if RETENTION is used. Leveraging our finding, we can write
the following query, which uses the function BITAND to detect whether RETENTION is enabled:
SELECT owner, object_name,
CASE WHEN bitand(l.flags, 32) = 0 THEN l.pctversion$
ELSE NULL
END AS pctversion,
CASE WHEN bitand(l.flags, 32) = 32 THEN l.retention
ELSE NULL
END AS retention
FROM sys.lob$ l, dba_objects o
WHERE l.lobj# = o.object_id
AND o.object_type = 'LOB'
AND OWNER = 'SCOTT';
OWNER OBJECT_NAME PCTVERSION RETENTION
----- -------------------- ---------- ----------
SCOTT BLOG_IMG_BLOB 10
SCOTT BLOG_TEXT_CLOB 3600
--//感覺oracle到12c這樣一些細節都沒有解決好不應該.不過注意看這樣查詢RETENTION有數值,說明dba_lobs檢視定義有問題.
SCOTT@test01p> SELECT owner,object_name,object_id,data_object_id FROM dba_objects where owner='SCOTT' and object_type='LOB';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
----- -------------------- ---------- --------------
SCOTT BLOG_IMG_BLOB 24443 24443
SCOTT BLOG_TEXT_CLOB 24441 24441
SCOTT@test01p> select * from sys.lob$ where lobj# in (24441,24443);
OBJ# COL# INTCOL# LOBJ# PART# IND# TS# FILE# BLOCK# CHUNK PCTVERSION$ FLAGS PROPERTY RETENTION FREEPOOLS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- -------- ---------- ---------- ---------- ---------- -------
24440 3 3 24441 0 24442 4 0 0 1 10 1121 6146 3600 0
24440 4 4 24443 0 24444 4 0 0 1 20 1089 6146 4 0
--//很明顯RETENTION定義是有值的,很奇怪的地方是BLOG_IMG_BLOB的RETENTION=4.
--//按照道理僅僅透過flags標識來確定定義時採用pctversion還是retention.
3.繼續測試:
SCOTT@test01p> select text_vc from dba_views where view_name like 'DBA_LOBS';
TEXT_VC
----------------------------------------------------------------------
select u.name, o.name,
decode(bitand(c.property, 1), 1, ac.name, c.name), lo.name,
decode(bitand(l.property, 8),
8, decode(l.ts#, 2147483647, ts1.name, ts.name), ts.name),
io.name,
l.chunk * decode(bitand(l.property, 8), 8, ts1.blocksize,
ts.blocksize),
decode(bitand(l.flags, 32), 0, l.pctversion$, to_number(NULL)),
decode(bitand(l.flags, 32), 32,
decode(bitand(l.property, 2048), 2048, to_number(NULL),
l.retention), to_number(NULL)),
decode(l.freepools, 0, to_number(NULL), 65534, to_number(NULL),
65535, to_number(NULL), l.freepools),
decode(bitand(l.flags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
16, 'CACHEREADS', 256, 'YES',
512, 'YES', 'YES'),
decode(bitand(l.flags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512,
'YES', 'YES'),
decode(bitand(l.flags, 4096), 4096, 'YES',
decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
decode(bitand(l.flags, 57344), 8192, 'LOW', 16384, 'MEDIUM', 32768,
'HIGH',
decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
decode(bitand(l.flags, 458752), 65536, 'LOB', 131072, 'OBJECT',
327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE',
decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
decode(bitand(l.property, 2), 2, 'YES', 'NO'),
decode(c.type#, 113, 'NOT APPLICABLE ',
decode(bitand(l.property, 512), 512,
'ENDIAN SPECIFIC', 'ENDIAN NEUTRAL ')),
decode(bitand(ta.property, 32), 32, 'YES', 'NO'),
decode(bitand(l.property, 2048), 2048, 'YES', 'NO'),
decode(bitand(l.property, 4096), 4096, 'NO',
decode(bitand(ta.property, 32), 32, 'N/A', 'YES')),
decode (bitand(l.property, 2048),
2048,
decode(bitand(ta.property, 17179869184), 17179869184,
decode(ds.lobret_stg, to_number(NULL), 'DEFAULT',
0, 'NONE', 1, 'AUTO',
2, 'MIN', 3, 'MAX',
4, 'DEFAULT', 'INVALID'),
decode(s.lists, 0, 'NONE', 1, 'AUTO',
2, 'MIN', 3, 'MAX',
4, 'DEFAULT', 'INVALID')),
decode(bitand(l.flags, 32), 32, 'YES', 'NO')),
decode (bitand(l.property, 2048),
2048,
decode(bitand(ta.property, 17179869184), 17179869184,
decode(ds.lobret_stg, 2, ds.mintim_stg, to_number(NULL)),
decode(s.lists, 2, s.groups, to_number(NULL))))
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.tab$ ta, sys.lob$ l,
sys.obj$ lo, sys.obj$ io, sys.user$ u, sys.ts$ ts, sys.ts$ ts1,
sys.seg$ s, sys.deferred_stg$ ds
where o.owner# = u.user#
and bitand(o.flags, 128) = 0
and o.obj# = c.obj#
and c.obj# = l.obj#
and c.intcol# = l.intcol#
and l.lobj# = lo.obj#
and l.ind# = io.obj#
and l.ts# = ts.ts#(+)
and u.tempts# = ts1.ts#
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)
and bitand(c.property,32768) != 32768 /* not unused column */
and o.obj# = ta.obj#
and bitand(ta.property, 32) != 32 /* not partitioned table */
and l.file# = s.file#(+)
and l.block# = s.block#(+)
and l.ts# = s.ts#(+)
and l.lobj# = ds.obj#(+)
union all
select u.name, o.name,
decode(bitand(c.property, 1), 1, ac.name, c.name),
lo.name,
NVL(ts1.name,
(select ts2.name
from ts$ ts2, partobj$ po
where o.obj# = po.obj# and po.defts# = ts2.ts#)),
io.name,
plob.defchunk * NVL(ts1.blocksize, NVL((
select ts2.blocksize
from sys.ts$ ts2, sys.lobfrag$ lf
where l.lo
--//暈!!竟然顯示不全.完整可以檢視D:\app\oracle\product\12.2.0\dbhome_1\rdbms\admin\cdcore.sql,不再貼出.
--//decode(bitand(l.flags, 32), 0, l.pctversion$, to_number(NULL)) 對應欄位 PCTVERSION.
--//decode(bitand(l.flags, 32), 32,decode(bitand(l.property, 2048), 2048, to_number(NULL),l.retention), to_number(NULL)) 對應欄位RETENTION.
--//直接帶入值看看:
SCOTT@test01p> select decode(bitand(97, 32), 32,decode(bitand(6146, 2048), 2048, to_number(NULL),3600), to_number(NULL)) n10 from dual ;
N10
---------------------
--//6146=0x1802, 2048=0x800 1024=0x400 32=0x20
--//看dcore.bsq中lob$表定義:
flags number not null, /* 0x0000 = CACHE */
/* 0x0001 = NOCACHE LOGGING */
/* 0x0002 = NOCACHE NOLOGGING */
/* 0x0008 = CACHE READS LOGGING */
/* 0x0010 = CACHE READS NOLOGGING */
/* 0x0020 = retention is specified */
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/* 0x0040 = Index key holds timestamp */
/* 0x0080 = need to drop the freelists */
/* 0x0100 = CACHE NOLOGGING */
/* 0x0200 = CACHE LOGGING */
/* 0x0400 = SYNC */
/* 0x0800 = ASYNC */
/* 0x1000 = Encryption */
/* 0x2000 = Compression - Low */
/* 0x4000 = Compression - Medium */
/* 0x8000 = Compression - High */
/* 0x10000 = Sharing: LOB level */
/* 0x20000 = Sharing: Object level */
/* 0x40000 = Sharing: Validate */
property number not null, /* 0x00 = user defined lob column */
/* 0x01 = kernel column(s) stored as lob */
/* 0x02 = user lob column with row data */
/* 0x04 = partitioned LOB column */
/* 0x0008 = LOB In Global Temporary Table */
/* 0x0010 = Session-specific table */
/* 0x0020 = lob with compressed header */
/* 0x0040 = lob using shared segment */
/* 0x0080 = first lob using shared segment */
/* 0x0100 = klob and inline image coexist */
/* 0x0200 = LOB data in little endian format */
/* 0x0800 = 11g LOCAL lob */
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~什麼意思??
/* 0x1000 = Delayed Segment Creation */
/*0x2000 = 32K inline lobs */
4.不指定看看:
CREATE TABLE blog ( username VARCHAR2(30), date_time DATE, text CLOB, img BLOB)
LOB (text) STORE AS blog_text_clob ,
LOB (img) STORE AS blog_img_blob ;
SCOTT@test01p> SELECT owner,object_name,object_id,data_object_id FROM dba_objects where owner='SCOTT' and object_type='LOB';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- -------------------- ---------- --------------
SCOTT BLOG_IMG_BLOB 24448 24448
SCOTT BLOG_TEXT_CLOB 24446 24446
SCOTT@test01p> select * from sys.lob$ where lobj# in (24446,24448);
OBJ# COL# INTCOL# LOBJ# PART# IND# TS# FILE# BLOCK# CHUNK PCTVERSION$ FLAGS PROPERTY RETENTION FREEPOOLS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- -------- ---------- ---------- ---------- ---------- -------
24445 3 3 24446 0 24447 4 0 0 1 10 1121 6146 4 0
24445 4 4 24448 0 24449 4 0 0 1 10 1121 6146 4 0
SCOTT@test01p> SELECT COLUMN_NAME,pctversion, retention FROM user_lobs WHERE table_name='BLOG';
COLUMN_NAME PCTVERSION RETENTION
-------------------- ---------- ----------
TEXT
IMG
--//明顯12c dba_lobs檢視定義出了問題...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2647058/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 值型別和引用型別型別
- JavaScript值型別和引用型別JavaScript型別
- 型別預設和any型別型別
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- js基本型別和引用型別區別JS型別
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- Java的基本型別和引用型別Java型別
- 【LOB】Oracle lob管理常用語句Oracle
- typeScript 型別斷言、聯合型別和交叉型別（七）TypeScript型別
- [20190531]Timestamp Oddity.txt
- java基本型別和包裝型別的“==”和equals()方法Java型別
- c#中值型別和引用型別的區別C#型別
- JS篇-基本型別和引用型別、typeofJS型別
- TypeScript型別系統和基礎型別TypeScript型別
- 區別值型別資料和引用型別資料型別
- mysql bigint型別和datetime型別的轉換MySql型別
- Java基礎-基本型別和包裝型別Java型別
- js資料型別之基本資料型別和引用資料型別JS資料型別
- sizeof和strlen計算陣列型別和指標型別字串陣列型別指標字串
- 從賦值看基本型別和引用型別的區別賦值型別
- rust trait 關聯型別和泛型的區別RustAI型別泛型
- Python語法--可變型別和不可變型別Python型別
- TypeScript 基本型別和泛型的使用TypeScript型別泛型
- TypeScript Null和Undefined 型別TypeScriptNullUndefined型別
- JS專題之資料型別和型別檢測JS資料型別
- JAVA中基本資料型別和引用資料型別Java資料型別
- [20190531]建立job與commit.txtMIT
- 關於 Go 中 Map 型別和 Slice 型別的傳遞Go型別
- 《JavaScript物件導向精要》之一：基本型別和引用型別JavaScript物件型別
- 強型別語言變數和資料型別的理解變數資料型別
- Solidity-變數和資料型別[複合型別_1]Solid變數資料型別
- Python：多型、協議和鴨子型別Python多型協議型別
- Java中基本資料型別和包裝型別有什麼區別？Java資料型別
- 你不知道的JavaScript--Item4 基本型別和基本包裝型別（引用型別）JavaScript型別
- JS 的型別（null 和 undefined 的區別）JS型別NullUndefined
- JavaScript的String和Boolean型別JavaScriptBoolean型別
- 型別轉換（int 和 String）型別
- 資料型別和運算子資料型別