OraFAQ Forum: SQL & PL/SQL » Creating and refreshing materialized view group from another schema

Home » SQL & PL/SQL » SQL & PL/SQL » Creating and refreshing materialized view group from another schema

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Creating and refreshing materialized view group from another schema [message #571594]

Tue, 27 November 2012 16:01

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

Report message to a moderator

Re: Creating and refreshing materialized view group from another schema [message #571599 is a reply to message #571594]

Tue, 27 November 2012 17:12

BlackSwan
Messages: 20245
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;

Report message to a moderator

Re: Creating and refreshing materialized view group from another schema [message #571607 is a reply to message #571594]

Wed, 28 November 2012 01:27

Michel Cadot
Messages: 54712
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

infinitenumbers
Messages: 33
Registered: November 2010

Member

Thanks Blackswan! That was helpful, appreciate it!

Report message to a moderator

Previous Topic:	Handling Exceptions!
Next Topic:	missing values in database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 22:45:35 CDT 2013

Total time taken to generate the page: 0.13582 seconds