Home » Server Options » Replication » Materialized View Refresh (Oracle 9.2.0.6)
Materialized View Refresh [message #275911] Tue, 23 October 2007 06:02 Go to next message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

Hi,

I've got a materialized view MV1 in schema A1, which should be refreshed by B1. But the catch is I can't give ALTER ANY MATERIALIZED VIEW to B1 user to refresh A1.MV1

So, my question is, without having the ALTER ANY MATERIALIZED VIEW privilege, is there anyway one can refresh someone elses materialized view.

I';ve tried giving GRANT ALL ON A1.MV1 TO B1; and GRANT EXECUTE ON DBMS_SNAPSHOT TO B1;, but it didn't work. Everytime I try to
execute dbms_mview.refresh('A1.MV1','c'); I get: ORA-01031: insufficient privileges.


TIA.
Re: Materialized View Refresh [message #275950 is a reply to message #275911] Tue, 23 October 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the refresh statement in a procedure own by A1 and grant execute on this procedure to B1.

Regards
Michel
Re: Materialized View Refresh [message #276118 is a reply to message #275950] Wed, 24 October 2007 01:14 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Yes it's possible.

In mviewadmin@mv site

SQL > CREATE OR REPLACE PROCEDURE PRO_TRANSDATA AS
2 BEGIN
3 DBMS_REFRESH.REFRESH('MVIEWADMIN.us_rep');
4* END;
SQL> /

SQL> GRANT EXECUTE ON PRO_TRANSDATA TO SCOTT;

Grant succeeded.

SQL> CREATE SYNONYM SCOTT.PRO_TRANSDATA FOR PRO_TRANSDATA;

Synonym created.

In SCOTT@MV SITE

SQL> EXEC PRO_TRANSDATA

PL/SQL procedure successfully completed.


Re: Materialized View Refresh [message #276131 is a reply to message #276118] Wed, 24 October 2007 01:36 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Help needed regarding Materialized view
Next Topic: MV
Goto Forum:
  


Current Time: Thu Mar 28 13:54:23 CDT 2024