Anyone seeing policy predicate errors after a bounce of OTM?
We are seeing these errors and it looks as though it's related to the 10g policy changes to use:SHARED_CONTEXT_SENSITIVE
Same as CONTEXT_SENSITIVE except that the server first looks for a
cached predicate generated by the same policy function of the same
policy type within the same database session. If the predicate is
found in the session memory, the policy function is not re-executed and
the cached value is valid until session private application context
changes occur. *** Shared across multiple objects. ***
We see the following trace dump:
Error information for ORA-28113:
Logon user : TGF_REPORTS
Table/View : GLOGOWNER.COUNTRY_CODE
Policy name : SXR_COUNTRY_CODE226
Policy function: GLOGOWNER.VPDWREXT_PREDICATE_READ226
RLS predicate :
(not exists (select 'X' from report_restrict_from_level rl,groupmembers gm where rl.report_gid = report.report_gid and gm.gm_type= 'L' and gm.gm_group = rl.gl_level and gm.gm_member = 'ur_'|| sys_context('gl_user_ctx','user_role_gid')))
ORA-00904: "REPORT"."REPORT_GID": invalid identifier
*** 2009-10-27 14:55:25.135
You can see via the trace dump that we are looking at the country_code table but the predicate
that it's referencing is for the report table. It looks as though it's reusing a cached predicate.
We are on 5.5, 10.2.0.3 on Linux.
We are seeing these errors and it looks as though it's related to the 10g policy changes to use:SHARED_CONTEXT_SENSITIVE
Same as CONTEXT_SENSITIVE except that the server first looks for a
cached predicate generated by the same policy function of the same
policy type within the same database session. If the predicate is
found in the session memory, the policy function is not re-executed and
the cached value is valid until session private application context
changes occur. *** Shared across multiple objects. ***
We see the following trace dump:
Error information for ORA-28113:
Logon user : TGF_REPORTS
Table/View : GLOGOWNER.COUNTRY_CODE
Policy name : SXR_COUNTRY_CODE226
Policy function: GLOGOWNER.VPDWREXT_PREDICATE_READ226
RLS predicate :
(not exists (select 'X' from report_restrict_from_level rl,groupmembers gm where rl.report_gid = report.report_gid and gm.gm_type= 'L' and gm.gm_group = rl.gl_level and gm.gm_member = 'ur_'|| sys_context('gl_user_ctx','user_role_gid')))
ORA-00904: "REPORT"."REPORT_GID": invalid identifier
*** 2009-10-27 14:55:25.135
You can see via the trace dump that we are looking at the country_code table but the predicate
that it's referencing is for the report table. It looks as though it's reusing a cached predicate.
We are on 5.5, 10.2.0.3 on Linux.
Comment