| dbms_mview [message #283438] |
Tue, 27 November 2007 02:40  |
raji.s
Messages: 52
Registered: February 2005
|
Member |
|
|
Hi,
I have created a procedure in SYS user and then granted execute privilege to public. Then i have created a public synonym of this procedure.
SQL> sho user
USER is "SYS"
1 create or replace procedure prc_mv_refresh(list varchar2, method varchar2) as
2 begin
3 dbms_mview.refresh(list,method);
4 exception
5 when others then
6 raise_application_error(-20202,sqlerrm);
7* end;
SQL> /
Procedure created.
I have a materialized view and materialized view log in scott schema. when i try to refresh using dbms_mview, it is working fine but when i try to refresh it through user defined proc prc_mv_refresh, it is giving me insufficeint privileges issue.
SQL> exec dbms_mview.refresh('MV_EMP','F')
PL/SQL procedure successfully completed.
SQL> exec prc_mv_refresh('MV_EMP','F')
BEGIN prc_mv_refresh('MV_EMP','F'); END;
*
ERROR at line 1:
ORA-20202: ORA-01031: insufficient privileges
ORA-06512: at "SYS.PRC_MV_REFRESH", line 6
ORA-06512: at line 1
I am unable to undestand what is the issue here, can someone please help.
|
|
|
|
|
|
|
|
| Re: dbms_mview [message #283446 is a reply to message #283438] |
Tue, 27 November 2007 03:10  |
raji.s
Messages: 52
Registered: February 2005
|
Member |
|
|
I know i am using SYS which is not the correct way but needed to try this option....
I have modified the procedure a bit.
SQL> sho user
USER is "SYS"
SQL> create or replace procedure prc_mv_refresh(v_user varchar2,list varchar2, method varchar2) as
2 begin
3 dbms_mview.refresh(v_user||'.'||list,method);
4 end;
5 /
Procedure created.
SQL> sho user
USER is "SYS"
SQL> exec prc_mv_refresh('scott','MV_EMP','F')
PL/SQL procedure successfully completed.
SQL> sho user
USER is "SCOTT"
SQL> exec prc_mv_refresh('scott','MV_EMP','F')
BEGIN prc_mv_refresh('scott','MV_EMP','F'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 814
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 872
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 852
ORA-06512: at "SYS.PRC_MV_REFRESH", line 3
ORA-06512: at line 1
What would be the issue here ????
|
|
|
|
|
|
| Re: dbms_mview [message #283451 is a reply to message #283446] |
Tue, 27 November 2007 03:37 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: |
I know i am using SYS which is not the correct way but needed to try this option....
|
No you don't need it.
It is the wrong way.
Full stop.
| Quote: |
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.
|
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
