Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view [message #185165] Mon, 31 July 2006 05:36 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Im trying to create a materialized view with the ON COMMIT option, and getting the ORA-12054 error. I can create it just fine without the ON COMMIT option though. Any insight would be appreciated.

SQL> CREATE MATERIALIZED VIEW banners_mv
2 BUILD IMMEDIATE
3 REFRESH FORCE
4 NEXT (sysdate + 1/1440)
5 WITH PRIMARY KEY
6 AS
7 SELECT * from banners
8 where banner_status = 1 and
9 banner_name not like '%#%' and
10 banner_startdate <= sysdate and
11 ((banner_enddate >= sysdate) or (banner_enddate IS NULL));

Materialized view created.

But with the ON COMMIT option it gives a ORA-12054 error:

SQL> CREATE MATERIALIZED VIEW banners_mv
2 BUILD IMMEDIATE
3 REFRESH FORCE ON COMMIT
4 WITH PRIMARY KEY
5 AS
6 SELECT * from banners
7 where banner_status = 1 and
8 banner_name not like '%#%' and
9 banner_startdate <= sysdate and
10 ((banner_enddate >= sysdate) or (banner_enddate IS NULL));
SELECT * from banners
*
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Tried creating a mat. view log as well:

1* create materialized view log on banners
SQL> /

Materialized view log created.

Still getting the ORA-12054 when i try to create the mat. view. Any ides what I'm missing here? Thanks!
Re: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view [message #185177 is a reply to message #185165] Mon, 31 July 2006 07:04 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Documentation says
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.

Does this help?

[Updated on: Mon, 31 July 2006 07:04]

Report message to a moderator

Previous Topic: Scope of variables
Next Topic: date problem
Goto Forum:
  


Current Time: Mon Dec 05 23:48:17 CST 2016

Total time taken to generate the page: 0.19327 seconds