Error while refreshing materialized View through DBMS_MVIEW [message #153120] |
Thu, 29 December 2005 05:17  |
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 #153243 is a reply to message #153123] |
Fri, 30 December 2005 05:57   |
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   |
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.
|
|
|
|