Re: Error when creating materialized view

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 3 May 2012 19:53:13 -0700 (PDT)
Message-ID: <1336099993.54000.YahooMailNeo_at_web160904.mail.bf1.yahoo.com>

Since you didn't provide a table creation script for ch_tract_f or a description of it I can only guess that the geometry column is of type SDO_GEOMETRY; this makes the materialized view unable to set the ON COMMIT attribute:
 

SQL> create table ch_tract_f(
  2          id      number,
  3          show_subfeature varchar2(20),
  4          scale_factor number,
  5          feature_type varchar2(3),
  6          text    clob,
  7          geometry sdo_geometry,
  8          rotation number,
  9          anchor_x number,
 10          anchor_y number,
 11          ch_tract_id varchar2(40)
 12  );

Table created.
SQL>
SQL> create materialized view log on  CH_TRACT_F with rowid; Materialized view log created.
SQL>
SQL> CREATE MATERIALIZED VIEW VW_ALL_SOIL_F
  2  BUILD IMMEDIATE
  3  refresh fast on commit with rowid
  4  AS
  5  SELECT CH_TRACT_F.ID
  6  ,      rowid  AS ROW_ID
  7  ,      'allSoil'
  8  ,      CH_TRACT_F.SHOW_SUBFEATURE
  9  ,      CH_TRACT_F.SCALE_FACTOR
 10  ,      CH_TRACT_F.TEXT
 11  ,      CH_TRACT_F.GEOMETRY
 12  ,      CH_TRACT_F.ROTATION
 13  ,      CH_TRACT_F.ANCHOR_X
 14  ,      CH_TRACT_F.ANCHOR_Y
 15  ,      CH_TRACT_F.CH_TRACT_ID
 16  FROM  CH_TRACT_F
 17  WHERE  CH_TRACT_F.FEATURE_TYPE = 'Soil';
FROM  CH_TRACT_F
      *

ERROR at line 16:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view SQL>
 

Since SDO_GEOMETRY is an object type the ON COMMIT attribute cannot be set as it's a reported restriction in the documentation:
 "Restrictions on Refreshing ON COMMIT

• This clause is not supported for materialized views containing object types or Oracle-supplied types.
• This clause is not supported for materialized views with remote tables.
• If you specify this clause, then you cannot subsequently execute a distributed transaction on any master table of this materialized view. For example, you cannot insert into the master by selecting from a remote table. The ON DEMAND clause does not impose this restriction on subsequent distributed transactions on master tables" David Fitzjarrell

  ---

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Thursday, May 3, 2012 6:00 PM
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 Thu May 03 2012 - 21:53:13 CDT