Home » Developer & Programmer » Data Integration » Materialized View (Refresh on commit) (Oracle 9i, Windows XP)
Materialized View (Refresh on commit) [message #435936] Sat, 19 December 2009 03:40 Go to next message
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.
Re: Materialized View (Refresh on commit) [message #435937 is a reply to message #435936] Sat, 19 December 2009 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Materialized View (Refresh on commit) [message #435940 is a reply to message #435937] Sat, 19 December 2009 03:55 Go to previous messageGo to next message
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.
Re: Materialized View (Refresh on commit) [message #435946 is a reply to message #435940] Sat, 19 December 2009 09:29 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Fri Mar 29 09:37:46 CDT 2024