Home » Server Options » Replication » ORA-12054 while creating materialized view
ORA-12054 while creating materialized view [message #75139] Sun, 03 February 2002 22:08 Go to next message
Sascha Quint
Messages: 1
Registered: February 2002
Junior Member
We are trying to create a materialized view with the following statements:

CREATE TABLE agg
(FELD1 NUMBER, FELD2 NUMBER, FELD3 NUMBER, FELD4 NUMBER, d NUMBER);

CREATE MATERIALIZED VIEW LOG ON TESTTABLE WITH ROWID(FELD1, FELD2, FELD3, FELD4) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW TESTMATVIEW
REFRESH FAST ON COMMIT
AS SELECT
feld4,
SUM(FELD2+FELD3),
COUNT(*)
FROM TESTTABLE
GROUP BY FELD4;

and get the error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

the following example from an oracle tutorial is working, but we can't see any difference to our example:

CREATE TABLE agg
(u NUMBER, a NUMBER, b NUMBER, c NUMBER, d NUMBER);

CREATE MATERIALIZED VIEW LOG ON agg
WITH ROWID (u,a,b,c,d)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sn0
REFRESH FAST ON COMMIT
AS SELECT SUM(b+c), COUNT(*), a, d, COUNT(b+c)
FROM agg
GROUP BY a,d;

What's wrong???

Sascha
Re: ORA-12054 while creating materialized view [message #75153 is a reply to message #75139] Fri, 15 March 2002 09:23 Go to previous messageGo to next message
LV
Messages: 1
Registered: March 2002
Junior Member
I am facing the same issue. Is it a known bug for materialized views ! Trying to figure out but not successful. Any insights would be greatly appreciated.

thanks
Re: ORA-12054 while creating materialized view [message #75172 is a reply to message #75139] Fri, 03 May 2002 17:05 Go to previous messageGo to next message
Siva Dirisala
Messages: 1
Registered: May 2002
Junior Member
One requirement is that any SUM(XYZ) should also
have COUNT(XYZ) in the select statement for the
MV to be fast refreshable.
Re: ORA-12054 while creating materialized view [message #75175 is a reply to message #75139] Tue, 14 May 2002 10:43 Go to previous messageGo to next message
BF
Messages: 1
Registered: May 2002
Junior Member
I have the exact same problem as well. I can get a materialized view to work, but the "ON COMMIT" is not accepted as valid.
Re: ORA-12054 while creating materialized view [message #75222 is a reply to message #75139] Tue, 23 July 2002 13:45 Go to previous messageGo to next message
Juan Calfucura
Messages: 1
Registered: July 2002
Junior Member
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.
Re: ORA-12054 while creating materialized view [message #75587 is a reply to message #75139] Fri, 10 December 2004 23:43 Go to previous message
Jonas Claesson
Messages: 1
Registered: December 2004
Junior Member
You cant use aggregate functions (AVG, SUM) with the fast refresh option. Fast refresh is used for simple replicas of the master table. Fast refresh updates the materialized view immediately for each change on the master table. It would be a performance killer to recalculate the aggregate functions after each insert, update on the master table.

Use another refresh option :)

Jonas
Previous Topic: why i can't call dbms_mview.refresh in my proc?
Next Topic: Advantage & Disadvantage of using snapshots
Goto Forum:
  


Current Time: Sat May 30 06:51:59 CDT 2020