This forum is currently locked. You can't register or post questions at this time. (read more)

VPD with materialized view

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

me_lucky
Senior Member
Posts: 183
Joined: Tue Jun 02, 2009 11:40 am

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests