[stoganv]

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.

[chrisplough]

No - I haven't seen these errors. I'd recommend opening an SR with Oracle. Also - assuming you've already resolved - could you post the resolution here?

--Chris

[stoganv]

Chris,

We've still got the SR open with Oracle and we are trying to run through some test cases and get them some more information.

We haven't resolved it yet but since we only see it when we do a bounce we can mitigate around the problem for now.

[chrisplough]

I've seen your different posts all related to this error. Sad that it hasn't been resolved yet!

Just to be sure, I assume you've involved both the DB and OTM support teams?

--Chris

[stoganv]

The OTM support team has just palmed it off to the DB support team today. They are claiming it's not an OTM problem but possibly a DB one.

I manged to reproduce it on a consistent basis yesterday so at least I was able to give them a few test cases in how to hit the bug.

It seems to rear it's head when you switch vpd users and one of the users has a number of custom external predicates with sys_context variables.

What's the usage out in the world with custom external predicates? ..... and ones that have custom context variables?

It's a bit of a worry because the way in which I finally reproduced it was through basic selects and running through them a few times while switching vpd's.

What I also noticed is that if I don't use the vpd.set_lite_vpd function in my script then the error does not occur. Do you know anything about this function?

[stoganv]

Bug fixed in the critical patch july 2009 for 10.2.0.4.

It took a while but got the answer I needed. I just wonder why it took so long

Put in a workaround with a dummy context variable until the upgrade.