Re: Refresh materialized view by other user then owner

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_at_gmail.com> 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: materialized view "SYSTEM"."V1" does not exist
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
> ORA-06512: at line 1
>
>
> And after
> EXEC dbms_mview.refresh('sys.V1 ');
>
> I have the same old error:
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
> ORA-06512: at line 1
>
>
>
>
>
> It looks like that this is not so simple ...
>
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Stefan Knecht
> Sent: Sunday, April 30, 2006 11:00 PM
> To: oracle-l_at_freelists.org
> 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 if that is the cause - I only tested on 10gr2,
> so 9i might react differently but I cannot test this right now.
>
> system_at_CENTRAL> grant create session, alter any materialized view to user1
> identified by user1;
>
> Grant succeeded.
>
> system_at_CENTRAL> create table t1 (x int primary key);
>
> Table created.
>
> system_at_CENTRAL> create materialized view v1 as select * from t1
> 2 ;
>
> Materialized view created.
>
> system_at_CENTRAL> @conn user1/user1
> Connected.
> user1_at_CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');
>
> PL/SQL procedure successfully completed.
>
> user1_at_CENTRAL>
>
>
> Either way, you really shouldn't be using SYS or SYSTEM for userdata.
>
> Stefan
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l