[20201126]11g VPD的問題.txt
[20201126]11g VPD的問題.txt
--//連結提到的問題在測試環境測試看看。
--//你可以檢視中文版本更加詳細的介紹10g,11g與12c增加列的一些操作方式上的變化:
--//
--//我僅僅測試11g環境。
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立測試:
SCOTT@book> create table t1 as select rownum n1, lpad('x',5) v1 from dual connect by level <=5;
Table created.
--//分析表略。
SCOTT@book> alter table t1 add c1 number default 42 not null;
Table altered.
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> select count(1) from t1 where c1=42;
COUNT(1)
----------
5
SCOTT@book> @ 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 |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.01 | 3 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 65 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------
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:
SCOTT@book> 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;
/
SCOTT@book> alter table t1 add c2 number default 43 not null;
Table altered.
SCOTT@book> select count(1) from t1 where c2=43;
COUNT(1)
----------
5
--//嗯,我的測試是ok的。
SCOTT@book> @ dpc '' ''
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 | 12 | | | 1 |00:00:00.01 | 2 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 12 | 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
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=43)
--//注意過濾條件"C2"=43。這樣就奇怪了,明明過濾條件是C2=43,按照前面的測試如果資料不在段中,應該count(1)是0才對啊。
4.繼續探究:
SCOTT@book> select rowid,t1.* from t1 where rownum=1;
ROWID N1 V1 C1 C2
------------------ ---------- ----- ---------- ----------
AAAXJRAAEAAAAILAAA 1 x 42 43
SCOTT@book> @ rowid AAAXJRAAEAAAAILAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
94801 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;
--//透過bbed觀察,注意執行一次重新整理資料快取,不然看到的可能不真實。
SCOTT@book> alter system flush buffer_cache;
System altered.
BBED> x /rncnn dba 4,523 *kdbr[1]
rowdata[48] @8096
-----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x02
cols@8098: 4
col 0[2] @8099: 2
col 1[5] @8102: x
col 2[0] @8108: *NULL*
col 3[2] @8109: 43
--//噢,注意看第4個欄位透過類似的方式加入表中,而rls的存在改變的操作模式直接插入到資料段中。
SCOTT@book> column BINARYDEFVAL format a20
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- --------------------
94801 3 C12B
--//啟用rls後,在資料欄位sys.ecol$,僅僅出現欄位3.
--//注42的oracle數字編碼就是C12B。
SCOTT@book> select dump(42,16),dump(43,16) from dual ;
DUMP(42,16) DUMP(43,16)
------------------ ------------------
Typ=2 Len=2: c1,2b Typ=2 Len=2: c1,2c
--//也就是這樣操作模式可能會導致表產生大量的redo,甚至影響前臺的操作,在工作中要引起注意。
--//作者的測試在19c上視乎遇到了bug,不過對方還提到sys使用者的一些情況我也測試看看。
SYS@book> show user
USER is "SYS"
SYS@book> alter table scott.t1 add c3 number default 44 not null;
Table altered.
SYS@book> select count(1) from scott.t1 where c3=44;
COUNT(1)
----------
5
--//OK,正確。
SYS@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 92qghqsahftp5, child number 0
-------------------------------------
select count(1) from scott.t1 where c3=44
Plan hash value: 3724264953
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 12 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
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("C3",44)=44)
--//注意看過濾條件。現在是filter(NVL("C3",44)=44)。
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- --------------------
94801 3 C12B
94801 5 C12D
--//也就是以sys使用者操作預設值記錄在sys.ecol$中,繞過了這個問題。
SCOTT@book> @ desc_proc sys dbms_rls DROP_POLICY
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
---------- -------------------- ----------- -------- ------------- --------- --------- ----------
SYS DBMS_RLS DROP_POLICY 3 POLICY_NAME VARCHAR2 IN N
1 OBJECT_SCHEMA VARCHAR2 IN Y
2 OBJECT_NAME VARCHAR2 IN N
SCOTT@book> exec dbms_rls.drop_policy (object_schema=> user,object_name=> 'T1', policy_name=> 'F_T1_POLICY');
PL/SQL procedure successfully completed.
SCOTT@book> alter table t1 add c4 number default 45 not null;
Table altered.
SCOTT@book> select * from sys.ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- --------------------
94801 6 C12E
94801 3 C12B
94801 5 C12D
--//這樣就不會出現前面遇到的情況。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2737068/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20201126]18c VPD的問題.txt
- [20201126]檔案相對號與絕對號問題.txt
- [20201210]11G ACS相關問題.txt
- [20180819]關於父子游標問題(11g).txt
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- [20201116]11g連線謂詞推入push_pred問題.txt
- [20190116]rman的老問題.txt
- [20181227]bbed的使用問題.txt
- 11g rac新增節點容易遇到的問題
- rac新增節點容易遇到的問題(11g)
- [20240807]數值累加的問題.txt
- [20231020]增加欄位的問題.txt
- [20191129]關於hugepages的問題.txt
- [20190314]使用strace注意的問題.txt
- [20201126]使用cursor_sharing_exact與給sql打補丁2.txtSQL
- [20201126]使用cursor_sharing_exact與給sql打補丁3.txtSQL
- [20201130]11g or_expand提示.txt
- [20190306]11g health monitor.txt
- [20190219]那個更快(11g).txt
- [20210812]windows xcopy問題.txtWindows
- [20190221]sql patch 問題.txtSQL
- [20181217]strace使用問題.txt
- [20181204]bbed修改問題.txt
- [20190313]備份問題.txt
- [20180619]bbed verify問題.txt
- [20190416]11g下那些latch是Exclusive的.txt
- [20190415]11g下那些latch是共享的.txt
- [20231207]ls -f的顯示問題.txt
- [20221125]設定hugepages遇到的問題.txt
- [20211214]ezconnect輸入口令的問題.txt
- [20211210]優化遇到的奇怪問題.txt優化
- [20190116]詭異的問題2.txt
- [20181229]關於字串的分配問題.txt字串
- [20180417]奇怪的grep過濾問題.txt
- [20211025]11g sequemce nocahe測試.txt
- [20221203]sqlplus set trimspool 問題.txtSQL
- [20221202]sqlplus set trimout 問題.txtSQL
- [20221107]除錯crontab問題.txt除錯