[20201126]18c VPD的问题.txt

来源：这里教程网时间：2026-03-03 16:16:51 作者：

[20201126]18c VPD的问题.txt --//链接：http://blog.itpub.net/267265/viewspace-2737068/，在18c测试看看。 1.环境： TTT@192.168.2.7:1521/orcl> select banner_full from v$version; BANNER_FULL ---------------------------------------------------------------------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 2.建立测试： TTT@192.168.2.7:1521/orcl> create table t1 as select rownum n1, lpad('x',5) v1 from dual connect by level <=5; Table created. TTT@192.168.2.7:1521/orcl> alter table t1 add c1 number default 42 not null; Table altered. TTT@192.168.2.7:1521/orcl> alter session set statistics_level = all; Session altered. TTT@192.168.2.7:1521/orcl> select count(1) from t1 where c1=42; COUNT(1) ---------- 5 TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID a4v8hg2qxzp1g, child number 0 ------------------------------------- select count(1) from t1 where c1=42 Plan hash value: 3724264953 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 2 | 1 | | 1 | SORT AGGREGATE | | 1 | 1 | 9 | | | 1 |00:00:00.01 | 2 | 1 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 9 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 2 | 1 | ------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL("C1",42)=42) --//注意filter条件。是NVL("C1",42)=42。因为这个字段并不保存在数据段中。 3.建立VPD policy： TTT@192.168.2.7:1521/orcl> select sys_context('USERENV','LANG') from dual; SYS_CONTEXT('USERENV','LANG') ----------------------------- US create or replace function f_t1_policy(piv_schema in varchar2 ,piv_object in varchar2) return varchar2 is lv_return_value varchar2(4000); begin if sys_context('USERENV','LANG') = 'US' then lv_return_value := '1=1'; else lv_return_value := '1=0'; end if; return lv_return_value; end f_t1_policy; / --//也就是测试返回 lv_return_value := '1=1'; -- assign this policy to t1 table begin dbms_rls.add_policy (object_schema => user, object_name => 'T1', policy_name => 'F_T1_POLICY', function_schema => user, policy_function => 'F_T1_POLICY', statement_types => 'SELECT' ); end; / TTT@192.168.2.7:1521/orcl> alter table t1 add c2 number default 43 not null; Table altered. TTT@192.168.2.7:1521/orcl> select count(1) from t1 where c2=43; COUNT(1) ---------- 5 TTT@192.168.2.7:1521/orcl> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 6vk08skyq9v43, child number 0 ------------------------------------- select count(1) from t1 where c2=43 Plan hash value: 3724264953 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 9 | | | 1 |00:00:00.01 | 2 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 9 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 2 - SEL$F5BB74E1 / T1@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('18.1.0') DB_VERSION('18.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$F5BB74E1") MERGE(@"SEL$2" >"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") FULL(@"SEL$F5BB74E1" "T1"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C2"=43) ~~~~~~~~~~~~~~~~~~~~~~~~ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (rowset=1019) --//注意过滤条件是filter("C2"=43)，说明修改段的数据。 TTT@192.168.2.7:1521/orcl> column BINARYDEFVAL format a20 TTT@192.168.2.7:1521/orcl> select * from sys.ecol$; TABOBJ# COLNUM BINARYDEFVAL GUARD_ID ---------- ---------- -------------------- ---------- 225887 3 C12B 4.做一个转储就可以验证: TTT@192.168.2.7:1521/orcl> select rowid from t1 where rownum=1; ROWID ------------------ AAA3JfAAMAAAACDAAA TTT@192.168.2.7:1521/orcl> @ rowid AAA3JfAAMAAAACDAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- ------------------------------ -------------------- ---------------------------------------- 225887 12 131 0 0x3000083 12,131 alter system dump datafile 12 block 131 TTT@192.168.2.7:1521/orcl> alter system dump datafile 41 block 131; System altered. TTT@192.168.2.7:1521/orcl> select * from v$dbfile where name like '%user%'; FILE# NAME CON_ID ----- ------------------------------------------------------------------------------------------ ------------ 41 +DATA/ORCLCDB/74A69DC145F5662BE0558253DD747177/DATAFILE/users.276.985549575 3 --//注意file=41,不是12. Block header dump: 0x03000083 --//0x03000083 = set dba 12,131 = alter system dump datefile 12 block 131 = 50331779 Object id on Block? Y seg/obj: 0x3725f csc: 0x00000000575b9d99 itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x3000080 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x00000000575b9d99 0x02 0x0006.00d.0000cc1d 0x02400223.21b2.13 --U- 5 fsc 0x0000.575b9e0f 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x03000083 data_block_dump,data header at 0x7f55b259f07c =============== tsiz: 0x1f80 hsiz: 0x1c pbl: 0x7f55b259f07c 76543210 flag=-------- ntab=1 nrow=5 frre=-1 fsbo=0x1c fseo=0x1ef4 avsp=0x1f14 tosp=0x1f14 0xe:pti[0] nrow=5 offs=0 0x12:pri[0] offs=0x1f34 0x14:pri[1] offs=0x1f24 0x16:pri[2] offs=0x1f14 0x18:pri[3] offs=0x1f04 0x1a:pri[4] offs=0x1ef4 block_row_dump: tab 0, row 0, @0x1f34 tl: 16 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [ 2] c1 02 col 1: [ 5] 20 20 20 20 78 col 2: *NULL* col 3: [ 2] c1 2c tab 0, row 1, @0x1f24 tl: 16 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [ 2] c1 03 col 1: [ 5] 20 20 20 20 78 col 2: *NULL* col 3: [ 2] c1 2c tab 0, row 2, @0x1f14 tl: 16 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [ 2] c1 04 col 1: [ 5] 20 20 20 20 78 col 2: *NULL* col 3: [ 2] c1 2c tab 0, row 3, @0x1f04 tl: 16 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [ 2] c1 05 col 1: [ 5] 20 20 20 20 78 col 2: *NULL* col 3: [ 2] c1 2c tab 0, row 4, @0x1ef4 tl: 16 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [ 2] c1 06 col 1: [ 5] 20 20 20 20 78 col 2: *NULL* col 3: [ 2] c1 2c end_of_block_dump --//说明c4字段在块中。测试没有遇到作者遇到的情况。

编辑推荐：

返回列表

[20201126]18c VPD的问题.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推