OraFAQ Forum: Data Integration » Materialized View (Refresh on commit)

Home » Developer & Programmer » Data Integration » Materialized View (Refresh on commit) (Oracle 9i, Windows XP)

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

Materialized View (Refresh on commit) [message #435936]

Sat, 19 December 2009 03:40

NoraizOraDev
Messages: 11
Registered: September 2008

Junior Member

Dear Members,

I am facing a problem as under,

1. I have a table 'tbl_a' (with data) in schema 'ABC'.
2. MV_LOG on 'tbl_a' in same schema.
3. create a Materialized View in schema 'XYZ'
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH PRIMARY KEY
4. MV created Successfuly.

Problem:
When i commit any DML on 'tbl_a' it shows error
ORA-00942: table or view does not exist.

Report message to a moderator

Re: Materialized View (Refresh on commit) [message #435937 is a reply to message #435936]

Sat, 19 December 2009 03:45

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From SQL Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2063793

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.

Regards
Michel

Report message to a moderator

Re: Materialized View (Refresh on commit) [message #435940 is a reply to message #435937]

Sat, 19 December 2009 03:55

NoraizOraDev
Messages: 11
Registered: September 2008

Junior Member

Dear Michel,

If you read the post in detail then at # 4.

4. MV created Successfuly.

Its only possible if 'ON COMMIT REFRESH' system privilege has granted.
Moreover, If i create MV in same schema then it works very well.

Hope you understand my prob.

Report message to a moderator

Re: Materialized View (Refresh on commit) [message #435946 is a reply to message #435940]

Sat, 19 December 2009 09:29

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The fact you can create the MV does not mean you can refresh it because you lose a privilege.
You didn't post anything that contradict my answer.
You didn't post anything that prove what you're saying is true.

But
Quote:

If i create MV in same schema then it works very well.

tend to prove that I am correct.

Hope you undertsand my answer.

Regards
Michel

[Updated on: Sat, 19 December 2009 09:31]

Report message to a moderator

Previous Topic:	MOLAP doesn't get sequence
Next Topic:	one dimension level

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 27 18:17:50 CST 2025