RE: Error when creating materialized view

From: Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
Date: Fri, 4 May 2012 04:39:49 -0500
Message-ID: <8C0087E93F6A9A459051869F5D574891B775EE_at_LISL-XBCK-C1-V2.snaponglobal.com>



For fast refresh, the columns supporting your predicates must also be in the MV log. See
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6003.ht m#i2064649

Try
create materialized view log on CH_TRACT_F with rowed, sequence(feature_type) including new values;

Mike

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta Sent: Thursday, May 03, 2012 8:01 PM
To: ORACLE-L
Subject: Error when creating materialized view

Hi Friends,
I am getting an error when creating a materialized view:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

The schema has the following sys privs.

CREATE MATERIALIZED VIEW
ON COMMIT REFRESH DROP ANY DIRECTORY
CREATE SESSION
DELETE ANY TABLE
QUERY REWRITE
CREATE DATABASE LINK
CREATE SEQUENCE
DROP ANY TABLE
CREATE ANY TABLE
DROP USER
CREATE TYPE
CREATE ANY DIRECTORY
ALTER USER
CREATE USER
UNLIMITED TABLESPACE
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
CREATE TABLE
CREATE TRIGGER
SELECT ANY TABLE
CREATE PROCEDURE
CREATE SYNONYM
ALTER SESSION
CREATE INDEXTYPE
ANALYZE ANY
CREATE VIEW I created the materialized view log as:

create materialized view log on CH_TRACT_F with rowid;

CREATE MATERIALIZED VIEW VW_ALL_SOIL_F
BUILD IMMEDIATE
refresh fast on commit with rowid

AS
SELECT CH_TRACT_F.ID
, rowid AS ROW_ID
, 'allSoil'

, CH_TRACT_F.SHOW_SUBFEATURE

, CH_TRACT_F.SCALE_FACTOR

, CH_TRACT_F.TEXT

, CH_TRACT_F.GEOMETRY

, CH_TRACT_F.ROTATION

, CH_TRACT_F.ANCHOR_X

, CH_TRACT_F.ANCHOR_Y

, CH_TRACT_F.CH_TRACT_ID
FROM CH_TRACT_F WHERE CH_TRACT_F.FEATURE_TYPE = 'Soil';

What is wrong:

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 04 2012 - 04:39:49 CDT

Original text of this message