Home » SQL & PL/SQL » SQL & PL/SQL » Error while refreshing materialized View through DBMS_MVIEW
Error while refreshing materialized View through DBMS_MVIEW [message #153120] Thu, 29 December 2005 05:17 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Experts,
I am facing some problem while REFRESHING the materialized view.

I have one schema FPD_OBJ, on which all the tables are created.
Second schema FPD_SP I have on which database packages (procedure/functions) are created. On this schema I have created Private synonyms pointing to the tables creates on FPD_OBJ.
GRANTS (SELECT, INSERT, UPDATE & DELETE) have been given to FPD_SP user.
Third schema I have FPD_USER, through which I am executing the stored procedure. EXECUTE privilege has been given to FPD_USER from FPD_SP.

I have a Materialized View (FPD_ACTIVITY_SUMMARY_MV) which is again created on FPD_OBJ schema. Now one of my stored procedure (in FPD_SP) executes DBMS_MVIEW.REFRESH('FPD_ACTIVITY_SUMMARY_MV','C');
To refresh the materialized view, after some data processing. This stored procedure is being executed from FPD_USER schema. All the other procedure works fine except for this one and it gives error “ORA-01031: insufficient privileges”. I have given all the privileges (SELECT, INSERT, DELETE, UPDATE) on this Materialized View to FPD_SP schema, but still it gives me error. If I comment this code then the stored procedure works fine.

Is there any other privilege has to be given to FPD_USER or FPD_SP, so as the REFRESH happens through the procedure.

Thanks in advance.
Re: Error while refreshing materialized View through DBMS_MVIEW [message #153123 is a reply to message #153120] Thu, 29 December 2005 05:40 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I have got a way around, which is that I created the Materialized View in FPD_SP schema (reference tables of FPD_OBJ) schema and then the procedure EXECUTES fine. But just wanted to know whether the approach is correct or is there any other way to REFRESH the view through procedure keeping it in FPD_OBJ schema.

Thanks in advance.

Regards
Himanshu
Re: Error while refreshing materialized View through DBMS_MVIEW [message #153243 is a reply to message #153123] Fri, 30 December 2005 05:57 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A complete refresh will issue a TRUNCATE table statement, which is not covered by SELECT, INSERT, UPDATE, DELETE.

The manual says:

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.


So, either grant DROP ANY TABLE priv to FPD_SP, or move the proc and the table to the same schema (which is what you have done).

_____________
Ross Leishman
Re: Error while refreshing materialized View through DBMS_MVIEW [message #189013 is a reply to message #153243] Tue, 22 August 2006 15:14 Go to previous messageGo to next message
praving
Messages: 1
Registered: August 2006
Location: India
Junior Member
This not true.
Even if you have drop any table, you can not refresh a materialized view owned by other user. I am not sure about the reason.
But I have created a materialized view owned by user1 and granted all on that view to user2 and user2 had "drop any table" priv. But user2 is still not able to refresh the view.
I am sure there is more than "drop any table".
If you have a method of refreshing a materialized view owned by other user then please reply to this message.


Re: Error while refreshing materialized View through DBMS_MVIEW [message #275348 is a reply to message #153120] Fri, 19 October 2007 10:17 Go to previous message
sivarams
Messages: 1
Registered: October 2007
Junior Member
Just wanted to post a solution for this query even though it is more than a year old. Please try ALTER ANY SNAPSHOT privilege from the owner of the Materialized View to the user that is refreshing the Materialized View. This should resolve this issue.

Thanks.
Previous Topic: How to do bulk Data Extraction in single time
Next Topic: How to merge data from two temp tables in 'with' clause
Goto Forum:
  


Current Time: Fri Feb 14 18:43:52 CST 2025