[20190531]lob类型pctversion 和 retention.txt

来源：这里教程网时间：2026-03-03 13:49:07 作者：

[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视图定义出了问题...

编辑推荐：

返回列表

[20190531]lob类型pctversion 和 retention.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推