Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in Materialized View
Help needed in Materialized View [message #274183] Sun, 14 October 2007 23:00 Go to next message
jobsushil
Messages: 7
Registered: October 2007
Location: India
Junior Member
Hi,
I am getting the following error when i try to refresh the materialized view.

execute DBMS_SNAPSHOT.REFRESH( 'mv_stored_proc_3','C');

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1



Kindly help me out.


regards
Job
Re: Help needed in Materialized View [message #274184 is a reply to message #274183] Sun, 14 October 2007 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do not apply within PL/SQL procedure.

You don't have sufficient privs to do what you want to do.
Re: Help needed in Materialized View [message #274186 is a reply to message #274184] Sun, 14 October 2007 23:13 Go to previous messageGo to next message
jobsushil
Messages: 7
Registered: October 2007
Location: India
Junior Member
Thank very much for responding...

I can't understand can make a bit clear on that.

I can create a materialized view. How can i not have to refresh it?.
I have checked with my admin , he has confirmed that he has given me full access to DBMS_REFRESH package.

Can kindly give the that i need to refresh the mv.

regards
Job

[mod-edit] removed illiterate IM speak.

[Updated on: Mon, 15 October 2007 07:53] by Moderator

Report message to a moderator

Re: Help needed in Materialized View [message #274187 is a reply to message #274184] Sun, 14 October 2007 23:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A complete refresh of an MV must have the privs to insert/update/delete and TRUNCATE the Materialized View Logs on all of the base tables.

Are all of the base tables in your current schema (or the CONNECT TO schema of a DB Link? If not, you will need Ins/Upd/Del privs on the MV Log as well as DROP ANY TABLE.

Ross Leishman
Re: Help needed in Materialized View [message #274243 is a reply to message #274187] Mon, 15 October 2007 03:38 Go to previous messageGo to next message
jobsushil
Messages: 7
Registered: October 2007
Location: India
Junior Member
I have got the privs on the mv logs on all the base tables involved in the mv but still i am getting the error.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
Re: Help needed in Materialized View [message #274251 is a reply to message #274243] Mon, 15 October 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64101
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post all what you did, giving with which you did it.
Reproduce it with a small test case that we can reproduce.
Post your Oracle version with 4 decimals. Always post this in your FIRST post.

Regards
Michel
Re: Help needed in Materialized View [message #274382 is a reply to message #274243] Mon, 15 October 2007 21:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
jobsushil wrote on Mon, 15 October 2007 18:38

I have got the privs on the mv logs on all the base tables involved in the mv but still i am getting the error.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


You have "privs", or do you OWN the base tables and MV logs? There's a difference.

If you OWN them (ie. they reside in the same schema to which you are connected), fair enough. Otherwise, you need:
- DROP ANY TABLE system privilege, and
- SELECT, INSERT, UPDATE, DELETE on the MV Logs
- SELECT on the base tables

Furthermore - as Ana pointed out, these privileges need to be granted directly to your User ID (or to PUBLIC), NOT via a role.

Ross Leishman
Re: Help needed in Materialized View [message #274411 is a reply to message #274382] Tue, 16 October 2007 00:10 Go to previous messageGo to next message
jobsushil
Messages: 7
Registered: October 2007
Location: India
Junior Member
Thank u very much for responding...

I dont own the base tables. The base tables and mv logs are in another schema.

I am given privs to the base tables and mv logs via user.
Like this,

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON MLOG$_TABLE TO USER;

I do have the drop any table and the select from the base table privs.

regards,
Job
Re: Help needed in Materialized View [message #274427 is a reply to message #274411] Tue, 16 October 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64101
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You surely missed something that is why we I ask you to provided the complete steps you did as long as the list of your privileges.
If you don't provide them the only thing we can say is "you have insufficient privileges".

Regards
Michel
Re: Help needed in Materialized View [message #274467 is a reply to message #274427] Tue, 16 October 2007 03:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't believe any of those privs gives you permission to TRUNCATE the MV Log. The only privilege (to my knowlege) that gives you TRUNCATE on another user's schema is DROP ANY TABLE.

Why don't you run SQL*Trace when you refresh the MV. The trace file will tell you the SQL statment that caused the failure. If I'm right, it will be a TRUNCATE command, otherwise it will give a clue as to what privilege you lack.

Ross Leishman
Re: Help needed in Materialized View [message #275901 is a reply to message #274183] Tue, 23 October 2007 05:00 Go to previous message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

If you are trying to refresh some other user's materialized view then I guess you are missing ALTER ANY MATERIALIZED VIEW privilege.
Previous Topic: Problem Involving Trigger
Next Topic: Query required
Goto Forum:
  


Current Time: Fri Dec 02 13:49:57 CST 2016

Total time taken to generate the page: 0.20725 seconds