[20201126]18c VPD的問題.txt

[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欄位在塊中。測試沒有遇到作者遇到的情況。