Home » SQL & PL/SQL » SQL & PL/SQL » Creating and refreshing materialized view group from another schema
Creating and refreshing materialized view group from another schema [message #571594] Tue, 27 November 2012 16:01 Go to next message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Hi all,

I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.

I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges. I want to know is there any alternative method to this.

I did lot of Google search on this topic but all the options I found need SYS privileges.

Your help is much appreciated.

Thanks,
shilpa
Re: Creating and refreshing materialized view group from another schema [message #571599 is a reply to message #571594] Tue, 27 November 2012 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I want to know is there any alternative method to this.
write a PL/SQL procedure, called REFRESH_A, that is owned by SchemaA that does the appropriate DDL.
then from SchemaA issue the following SQL

GRANT EXECUTE ON REFRESH_A TO SCHEMAB;
Re: Creating and refreshing materialized view group from another schema [message #571607 is a reply to message #571594] Wed, 28 November 2012 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did lot of Google search on this topic but all the options I found need SYS privileges.


You would only read stuff from B...'s site? Twisted Evil
You (almost) NEVER need SYS privileges to maintain database non-SYS objects.

The second link of my Google search gives the answer:
http://www.google.com/#hl=fr&tbo=d&output=search&sclient=psy-ab&q=oracle+privilege+refresh+materialized+view

The privileges needed are described in the documentation:
Database SQL Reference, CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW (a refresh is an alter) pages.

Anyway, BlackSwan solution is the best one.

Regards
Michel

[Updated on: Wed, 28 November 2012 01:28]

Report message to a moderator

Re: Creating and refreshing materialized view group from another schema [message #571680 is a reply to message #571607] Wed, 28 November 2012 20:39 Go to previous message
infinitenumbers
Messages: 33
Registered: November 2010
Member
Thanks Blackswan! That was helpful, appreciate it!
Previous Topic: Handling Exceptions!
Next Topic: missing values in database
Goto Forum:
  


Current Time: Mon Jul 28 15:50:12 CDT 2014

Total time taken to generate the page: 0.10442 seconds