ORA-28086: The data redaction policy expression has an error
Get link
Facebook
X
Pinterest
Email
Other Apps
Error:
ERROR at line 1: ORA-28086: The data redaction policy expression has an error. ORA-06512: at "SYS.DBMS_REDACT_INT", line 3 ORA-06512: at "SYS.DBMS_REDACT", line 42 ORA-06512: at line 1
When we try to run same as sys user it is working perfectly fine, but as a normal user it is failing with below errors.
No Privileges issue because AVO user had DBA Privilege ..
Creating a Data Redaction policy may fails with an
ORA-28086: The data redaction policy expression has an error.
when CURSOR_SHARING is set to FORCE using
alter system set cursor_sharing=force;
when using a simple Policy Expression such as '1=1', or a Policy Expression involving any constants.
Eg:
connect scott/tiger
create table tt (c1 number,c2 varchar2(50));
connect / as sysdba
alter system set cursor_sharing=force;
alter system flush buffer_cache;
alter system flush shared_pool;
begin
dbms_redact.add_policy(
object_schema=>'SCOTT'
,object_name=>'TT'
,column_name=>'C1'
,policy_name=>'P1'
,function_type=>DBMS_REDACT.FULL
,expression=>'1=1');
end;
/
^
ORA-28086: The data redaction policy expression has an error.
Workaround
None
If the cursor_sharing=FORCE, then this issue might occur. I am using Oracle database version -11.2.0.4. I have changed the cursor_sharing='EXACT' value and it works fine.
SQL> sho parameter cursor
NAME TYPE ------------------------------------ ------------------------- VALUE ------------------------------ cursor_bind_capture_destination string memory+disk cursor_sharing string FORCE cursor_space_for_time boolean FALSE open_cursors integer 5000 session_cached_cursors integer
NAME TYPE ------------------------------------ ------------------------- VALUE ------------------------------ 5000 SQL> alter session set cursor_sharing='EXACT';
Error: CDB$ROOT@EBTUPRDC> CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse; CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse * ERROR at line 1: ORA-65139: Mismatch between XML metadata file and data file /u05/odb/ORADATA_1/EBTUSPRDC/datafile/system.1531.807800993 for value of fcpsb (2230502002 in the plug XML file, 2230505492 in the data file) Fix: Don't open Non-CDB database until we complete pdb creation other wise we will get above error. Shutdown immediate; startup mount; alter database open read only; create xml file. shutdown immediate; Connect to CDB and Create Pluggable database .
If the cursor_sharing=FORCE, then this issue might occur. I am using Oracle database version -11.2.0.4. I have changed the cursor_sharing='EXACT' value and it works fine.
ReplyDeleteSQL> sho parameter cursor
NAME TYPE
------------------------------------ -------------------------
VALUE
------------------------------
cursor_bind_capture_destination string
memory+disk
cursor_sharing string
FORCE
cursor_space_for_time boolean
FALSE
open_cursors integer
5000
session_cached_cursors integer
NAME TYPE
------------------------------------ -------------------------
VALUE
------------------------------
5000
SQL> alter session set cursor_sharing='EXACT';
Session altered.