[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字段在块中。测试没有遇到作者遇到的情况。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2737350/，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20210125]完善hide.sql脚本.txt
- [20210120]in list与绑定变量个数.txt
- [20210120]提示加入注解.txt
- [20210119]sqlplus 12c LOBPREFETCH.txt
- [20210119]看执行计划可以使用hash_value.txt
- [20210115]sqlnet.ora设置sqlnet.expire_time断开时oracle如何探测.txt
- [20210115]sqlnet.ora设置sqlnet.expire_time与tcp_keepalive_time关系以及一些总结
- [20210114]理解DBMS_SESSION.set_identifier.txt
- [20210114]toad查看真实执行计划问题.txt
- [20210113]给PB开发人员的一个建议.txt
