Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view -- insufficient privileges
Materialized view -- insufficient privileges [message #316011] Wed, 23 April 2008 08:47 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

Quote:


connect scott/tiger

connected.

select * from contest.evfrz;

Name
-----
Simulation

CREATE MATERIALIZED VIEW scott_evfrz
PARALLEL
BUILD IMMEDIATE
REFRESH ON COMMIT
AS
select * from contest.evfrz;

error occurred..

ORA-01031: insufficient privileges.

Can anyone let me know, why this is happening? Will the above command gets refreshed once the contest.evfrz's content changed?




Thanks in advance.
Yashora
Re: Materialized view -- insufficient privileges [message #316014 is a reply to message #316011] Wed, 23 April 2008 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have the privilege to do this.
Do you satisfy the prerequisite for this action?

Regards
Michel
Re: Materialized view -- insufficient privileges [message #316017 is a reply to message #316011] Wed, 23 April 2008 09:01 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi Michel,

I do not understand your point. But i am able to access the contest's view from scott environment. But for the materialized view stated below, i am finding problem. What is the pre-requisite for that? Can you please let me know?

Regards,
Yashora

Re: Materialized view -- insufficient privileges [message #316022 is a reply to message #316017] Wed, 23 April 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In CREATE MATERIALIZED VIEW page of SQL Reference, there is a prerequisite section, did you read it? Did you verify you satisfy all the conditions that are listed?

Regards
Michel
Re: Materialized view -- insufficient privileges [message #316069 is a reply to message #316011] Wed, 23 April 2008 11:38 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi Michel,

I contacted my DBA, he said, all the privileges were available in both the schemas. When i tried to create with the normal materialized view without the options, it is allowing me to create. Say, if i give the command like

create materialized view scott_evfrz
AS
select * from contest.evfrz;

--> materialized view is created.

But for the command which i gave earlier, it is not working.
Can you please throw some light on this?

Regards
Jagdish
Re: Materialized view -- insufficient privileges [message #316072 is a reply to message #316069] Wed, 23 April 2008 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again you or your DBA didn't read the prerequisite.
Quote:
To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

It is simple as just reading.

Regards
Michel
Re: Materialized view -- insufficient privileges [message #316080 is a reply to message #316011] Wed, 23 April 2008 12:41 Go to previous message
yashora
Messages: 39
Registered: August 2006
Member
Thanks a ton Michel. This way, i will ask him to give me the permissions.

Regards,
Jagdish
Previous Topic: SQL join problem
Next Topic: Birth Date Range
Goto Forum:
  


Current Time: Tue Dec 06 12:17:40 CST 2016

Total time taken to generate the page: 0.08944 seconds