VPD with materialized view

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

VPD with materialized view

Postby me_lucky » Fri Nov 08, 2013 7:03 am

Dear Tim,

I have created a materialized with a complex query which is referring few table . One among them is having Row Level VPD policy.

The same query when executed as normal view, VPD functionality is working fine, but the same is not working when it is created as materialized.

Can u pl let me know what I need to check to work VPD with materialized.

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

Re: VPD with materialized view

Postby Tim... » Fri Nov 08, 2013 8:25 am

Hi.

What do you mean here?

1) VPD is not working when the materialized view is being populated.
2) VPD is not working when you query the materialized view, after it has been populated.

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: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD with materialized view

Postby me_lucky » Fri Nov 08, 2013 9:14 am

Dear Tim,

I mean to say that, VPD is not working when the materialized view is being populated.
It is not filtering the data when it is populating.

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

Re: VPD with materialized view

Postby Tim... » Fri Nov 08, 2013 9:25 am

Hi.

OK. So read the second paragraph here:

http://docs.oracle.com/cd/B28359_01/ser ... m#BADJACCJ

Specifically,

"The materialized view owner must either have no such VPD policies, or any such policy must return NULL. This is because VPD would transparently modify the defining query of the materialized view such that the set of rows contained by the materialized view would not match the set of rows indicated by the materialized view definition."

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: 17935
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