| DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314575] |
Thu, 17 April 2008 03:45  |
sarwagya
Messages: 4
Registered: February 2008
Location: nepal |
Junior Member |
|
|
Hi,
I am trying to use the following pl/sql code block to refresh all mviews in a schema.
DECLARE
v_failures NUMBER(12) := 0;
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_failures,'C','', TRUE, FALSE);
END;
But, it shows the following error:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 265
ORA-06512: at "SYS.DBMS_SCHEDULER", line 731
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1854
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2955
ORA-06512: at line 4
What privilege should be granted to do it?
Thanks in advance.
Sarwagya.
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314583 is a reply to message #314575 ] |
Thu, 17 April 2008 03:59  |
rleishman
Messages: 2563
Registered: October 2005
Location: Melbourne, Australia |
Senior Member |
|
|
That refreshes all MVs on the database. Probably need to be a DBA - eg. SYSTEM.
If you run a SQL Trace, you will be able to see the exact statement that is failing in the trace file. eg. It may fail on a TRUNCATE, so you could grant DROP ANY TABLE.
Ross Leishman
|
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314603 is a reply to message #314583 ] |
Thu, 17 April 2008 04:56 |
sarwagya
Messages: 4
Registered: February 2008
Location: nepal |
Junior Member |
|
|
Okay,
If I don't need to do complete refresh and want to do fast refresh, then I think, that privilege needn't be granted, right?
I tried with 'F' instead of 'C', but the problem still persists.
And, please tell me why the errors:
ORA-06512: at "SYS.DBMS_ISCHED", line 265
ORA-06512: at "SYS.DBMS_SCHEDULER", line 731
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1854
are coming?
Thank you.
Regards,
Sarwagya
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314633 is a reply to message #314603 ] |
Thu, 17 April 2008 05:52 |
Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
As you are an expert, I don't answer the question because you obviously already knows it.
Regards
Michel
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314640 is a reply to message #314633 ] |
Thu, 17 April 2008 06:03 |
JRowbottom
Messages: 2663
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
That's not a terribly helpful post, Michel. By your argument no-one could ever post in this forum as either they're not an expert, or they already know the answer and don't need to ask the question.
@sarwagya
I think you are confusing the concept of refreshing all the MVIEWS at one with the idea of FAST/COMPLETE refreshes.
Whether a MVIEW is suitable for FAST or COMPLETE refreshes is determined at the time it is created, and any refresh of the Mview will use the method specified in the initial Create statement.
DBMS_MVIEW.REFRESH_ALL_MVIEWS simple generates some Sql for each Mview that will perform a refresh on that Mview. It will try to refresh all the Mviews in the database, so if you don't have the permission to refresh them all, you will get this error.
The DBMS_SCHEDULER and DBMS_ISCHED packages are used by DBMS_MVIEW to handle the task of refreshing all the Mviews.
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314670 is a reply to message #314640 ] |
Thu, 17 April 2008 07:52 |
Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | That's not a terribly helpful post, Michel
|
I know it and this is deliberate. As I said I don't answer.
| Quote: | By your argument no-one could ever post in this forum as either they're not an expert, or they already know the answer and don't need to ask the question.
|
Maybe I badly express myself.
I meant either he is an expert and obviously knows the answer so I don't see the reason why he posted the question. Either he is not an expert and this question should be in newbie forum.
I don't know if we understand the same way OP's last question, for me he asked why there are:
ORA-06512: at "SYS.DBMS_ISCHED", line 265
ORA-06512: at "SYS.DBMS_SCHEDULER", line 731
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1854
in addition to "ORA-27486: insufficient privileges"
and this is a newbie question.
Regards
Michel
|
|
|
| Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #315216 is a reply to message #314640 ] |
Sun, 20 April 2008 01:55 |
sarwagya
Messages: 4
Registered: February 2008
Location: nepal |
Junior Member |
|
|
Thanks JRowbottom,
Now, I remember that I got this problem since I had granted the user with CREATE JOB & CREATE EXTERNAL JOB.
Previously, it used to run smoothly.
Now, when I revoked these privileges, it again gives desired result.
Please suggest why is this happening.
Also, I have to grant these privileges so that the user can run a scheduler job.
I can't grant DROP ANY TABLE too. So, is there any suitable way to do this?
Regards,
Sarwagya.
|
|
|
]
Members
Search
Help
Register
Login
Home
