ORA-30372:fine grain access policy conflicts with materiali

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ORA-30372:fine grain access policy conflicts with materiali

Postby me_lucky » Tue Aug 27, 2013 10:00 am

Dear Tim,

I have a VPD policy on a table. Based on this table one materialized view is created.

While refreshing the materialized view am getting the below error:

"ORA-30372: fine grain access policy conflicts with materialized view"

after googling,find the MOS Doc ID 386755.1 and modifed the policy function as suggested in the DOC.

But still not able to resolve the issue.
attaching the package for your reference.

--
FUNCTION vpd_function_test(p_schema in VARCHAR2,
p_obj in VARCHAR2)
RETURN VARCHAR2 as
d_predicate varchar2(2000);

BEGIN
IF dbms_snapshot.i_am_a_refresh
THEN
RETURN NULL;
ELSE
IF LOWER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')) = 'test' THEN
IF SYS_CONTEXT('CUST_CONTEXT', 'CUSTOMER') IS NOT NULL THEN

d_predicate := 'SOME... CONDITION';

return d_predicate;
ELSE
d_predicate := '1=1';
END IF;
ELSE
NULL;
END IF;
RETURN d_predicate;
END IF;
end;
---

Pl help me in identifying the issue.. And let me know if i can provide any other information?

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: ORA-30372:fine grain access policy conflicts with materi

Postby Tim... » Tue Aug 27, 2013 5:40 pm

Hi.

My first suggestion is, try with a blank function:

Code: Select all
FUNCTION vpd_function_test(p_schema in VARCHAR2,
p_obj in VARCHAR2)
RETURN VARCHAR2 as
BEGIN
RETURN NULL;
end;


If this works, you know it is the contents of the function that is the problem. If it still fails, you know the combination of VPD and your MV is just not going to work.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-30372:fine grain access policy conflicts with materi

Postby me_lucky » Thu Aug 29, 2013 10:39 am

Hi Tim,

As suggested, i have made the changes to return my function null. And with these changes am able to refresh the materialized view.

Thanks & Regards,
Sabhitha
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: ORA-30372:fine grain access policy conflicts with materi

Postby Tim... » Thu Aug 29, 2013 12:26 pm

Hi.

So there is not a fundamental incompatibility in the tech being used.

Now add pieces of the code back, one bit at a time, until you find the piece that is causing the problem.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-30372:fine grain access policy conflicts with materi

Postby me_lucky » Thu Sep 05, 2013 10:19 am

Hi Tim,

Thanks for the update.

I am able to work with my VPD.
Changes i did is only in declaration section, i have defaulted predicate to null and commented piece of code that's it.

Thanks & Regards,
Lucky.
me_lucky
Senior Member
 
Posts: 168
Joined: Tue Jun 02, 2009 11:40 am

Re: ORA-30372:fine grain access policy conflicts with materi

Postby Tim... » Thu Sep 05, 2013 12:37 pm

Good news.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests

cron