Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_MVIEW.REFRESH_ALL_MVIEWS
DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314575] Thu, 17 April 2008 03:45 Go to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #314588 is a reply to message #314575] Thu, 17 April 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How a question on "insufficient privileges" error can be an expert question at first sight?

Post in Newbie forum unless your answer YES to the 3 questions in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here

Regards
Michel
Re: DBMS_MVIEW.REFRESH_ALL_MVIEWS [message #314603 is a reply to message #314583] Thu, 17 April 2008 04:56 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
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.
Previous Topic: SQL using Aggregate COUNT
Next Topic: making the table distinct !
Goto Forum:
  


Current Time: Fri Dec 02 23:16:00 CST 2016

Total time taken to generate the page: 0.05349 seconds