What client are you using to connect to the database ? How exactly are you connecting to "SYSTEM" ? Do you have SYSDBA granted to SYSTEM and connect SYSTEM AS SYSDBA ?
If possible, can you post the complete output of what you're trying to execute ?
Stefan
On 5/1/06, varciasz <varciasz@(protected)> wrote: > > Thanks for trying but this still doesn't work at all. > > > >>You're creating the materialized view in schema SYSTEM, but try to > refresh > >>an mview in schema SYS - that cannot work. > > At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears > in > SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how > its > work but it is like that. > > So after : > EXEC dbms_mview.refresh('SYSTEM.V1 '); > > I have error from Oracle: > * > ERROR at line 1: > ORA-23401 (See ORA-23401.ora-code.com): materialized view "SYSTEM"."V1" does not exist > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 794 > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 851 > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 832 > ORA-06512 (See ORA-06512.ora-code.com): at line 1 > > > And after > EXEC dbms_mview.refresh('sys.V1 '); > > I have the same old error: > * > ERROR at line 1: > ORA-01031 (See ORA-01031.ora-code.com): insufficient privileges > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 794 > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 851 > ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 832 > ORA-06512 (See ORA-06512.ora-code.com): at line 1 > > > > > > It looks like that this is not so simple ... > > > > > __ ____ ____ ____ ____ ____ ____ ____ __ > From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] > On Behalf Of Stefan Knecht > Sent: Sunday, April 30, 2006 11:00 PM > To: oracle-l@(protected) > Subject: Re: Refresh materialized view by other user then owner > > > From what I can see from your posting: > > > begin > DBMS_MVIEW.REFRESH('sys.My_View','c'); > end; > You're creating the materialized view in schema SYSTEM, but try to refresh > an mview in schema SYS - that cannot work. > > The error message you're getting doesn't seem to be accurate, though, you > should be getting ora-23401 (See ora-23401.ora-code.com) if that is the cause - I only tested on 10gr2, > so 9i might react differently but I cannot test this right now. > > system@(protected)> grant create session, alter any materialized view to user1 > identified by user1; > > Grant succeeded. > > system@(protected)> create table t1 (x int primary key); > > Table created. > > system@(protected)> create materialized view v1 as select * from t1 > 2 ; > > Materialized view created. > > system@(protected)> @(protected) user1/user1 > Connected. > user1@(protected)> exec dbms_mview.refresh('SYSTEM.V1 '); > > PL/SQL procedure successfully completed. > > user1@(protected)> > > > Either way, you really shouldn't be using SYS or SYSTEM for userdata. > > Stefan > > > > >
What client are you using to connect to the database ? How exactly are you connecting to "SYSTEM" ? Do you have SYSDBA granted to SYSTEM and connect SYSTEM AS SYSDBA ?<br><br>If possible, can you post the complete output of what you're trying to execute ? <br><br>Stefan<br><br><div><span class="gmail_quote">On 5/1/06, <b class="gmail _sendername">varciasz</b> <<a href="mailto:varciasz@(protected)">varciasz@(protected) .com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Thanks for trying but this still doesn't work at all.<br><br><br>>>You're creating the materialized view in schema SYSTEM, but try to refresh<br>>> ;an mview in schema SYS - that cannot work.<br><br>At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears in <br>SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how its<br>work but it is like that.<br><br>So after :<br> EXEC dbms_mview.refresh('SYSTEM.V1 '); <br><br>I have error from Oracle: <br> *<br> ERROR at line 1:<br>   ; ORA-23401 (See ORA-23401.ora-code.com): materialized view "SYSTEM". "V1" does not exist<br>   ; ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 794<br> ORA-06512 (See ORA-06512.ora-code.com): at " SYS.DBMS_SNAPSHOT", line 851<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 832<br> ORA-06512 (See ORA-06512.ora-code.com): at line 1<br><br><br>And after <br> EXEC dbms_mview.refresh('sys .V1 ');<br><br>I have the same old error: <br> *<br> ERROR at line 1:<br>   ; ORA-01031 (See ORA-01031.ora-code.com): insufficient privileges<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 794<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS .DBMS_SNAPSHOT", line 851 <br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS .DBMS_SNAPSHOT", line 832<br> ORA-06512 (See ORA-06512.ora-code.com): at line 1<br><br><br><br><br><br>It looks like that this is not so simple ...<br><br><br><br><br>__ ____ ____ ____ ____ ____ ____ ____ __ <br>From: <a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce @(protected)</a> [mailto:<a href="mailto:oracle-l-bounce@(protected)">oracle -l-bounce@(protected)</a>]<br>On Behalf Of Stefan Knecht<br>Sent: Sunday, April 30, 2006 11:00 PM <br>To: <a href="mailto:oracle-l@(protected)">oracle-l@(protected)</a><br >Subject: Re: Refresh materialized view by other user then owner<br><br><br>From what I can see from your posting:<br><br><br> begin<br>DBMS_MVIEW.REFRESH(' sys.My_View','c');<br>end;<br>You're creating the materialized view in schema SYSTEM, but try to refresh<br>an mview in schema SYS - that cannot work.<br><br >The error message you're getting doesn't seem to be accurate, though, you <br>should be getting ora-23401 (See ora-23401.ora-code.com) if that is the cause - I only tested on 10gr2, <br>so 9i might react differently but I cannot test this right now.<br><br >system@(protected)> grant create session, alter any materialized view to user1 <br>identified by user1;<br><br>Grant succeeded.<br><br>system@(protected)> create table t1 (x int primary key);<br><br>Table created.<br><br>system @(protected)> create materialized view v1 as select * from t1<br> 2 ;<br><br> Materialized view created.<br><br>system@(protected)> @(protected) user1/user1<br >Connected.<br>user1@(protected)> exec dbms_mview.refresh('SYSTEM.V1 ');<br><br >PL/SQL procedure successfully completed.<br><br>user1@(protected)><br> <br><br>Either way, you really shouldn't be using SYS or SYSTEM for userdata. <br><br>Stefan<br><br><br><br><br></blockquote></div><br>
