RE: Refresh materialized view by other user then owner

From: varciasz <varciasz_at_gmail.com>
Date: Mon, 1 May 2006 02:36:49 +0200
Message-ID: <445556c8.41ee1ef5.3480.2d66@mx.gmail.com>

I'm using SQL *Plus Worksheet and logging into Database thru logging window after running "C:\oracle\ora92\bin\oemapp.bat worksheet" as SYSBDA (username: SYSTEM)  

In first mail I send whole code of creating "USER2", Table, Materialized View and refreshing it command used by "USER2"

The only output shown by SQL *Plus Worksheet is an error that also entirely I put to this mail, unless you mean any other output then please clarify this  

SYSTEM has granted roles: DBA, AQ_ADMINISTRATION_ROLE    

---

From: Stefan Knecht [mailto:knecht.stefan_at_gmail.com] Sent: Monday, May 01, 2006 2:01 AM
To: varciasz
Cc: oracle-l_at_freelists.org
Subject: 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

Received on Sun Apr 30 2006 - 19:36:49 CDT