Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Refresh materialized view by other user then owner

Re: Refresh materialized view by other user then owner

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Sun, 30 Apr 2006 23:00:10 +0200
Message-ID: <486b2b610604301400k680a6e36jbf073455753ef51c@mail.gmail.com>


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 - 16:00:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US