Re: materialized view rewrite

Volker Hetzer schrieb:
> Hi!
> I got a simple view, an inner join on four tables with only the equality
> condition but
> oracle (10.2.0.1, linux) gives me an ORA-30353.
> I've checked the doc and and uid, user, rownum,sysdate,... don't apply,
> except that the doc says etc and I don't know what falls under this.
> Here's what I'm trying to do:
>
> drop table dxdb_cmsattrib;
> CREATE TABLE dxdb_cmsattrib (
> fsc_snr VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_fsc_snr NOT NULL,
> vorzug VARCHAR2(1) CONSTRAINT NN_dxdb_cmsattrib_vorzug NOT NULL,
> zulassung VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_zulassung NOT
> NULL,
> rohs VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_rohs NOT NULL,
> CONSTRAINT PK_dxdb_cmsattrib PRIMARY KEY (fsc_snr)
> );
>
> drop table dxdb_cms_vorzug_enum;
> CREATE TABLE dxdb_cms_vorzug_enum (
> Textstring VARCHAR2(1) CONSTRAINT NN_cmsvorz_Textstring NOT NULL,
> Value NUMBER(2),
> CONSTRAINT PK_dxdb_cms_vorzug_enum PRIMARY KEY (Textstring)
> );
>
> drop table dxdb_cms_zulassung_enum;
> CREATE TABLE dxdb_cms_zulassung_enum (
> Textstring VARCHAR2(128) CONSTRAINT NN_cmszul_Textstring NOT NULL,
> Value NUMBER(2),
> CONSTRAINT PK_dxdb_cms_zulassung_enum PRIMARY KEY (Textstring)
> );
>
> drop table dxdb_cms_rohs_enum;
> CREATE TABLE dxdb_cms_rohs_enum (
> Textstring VARCHAR2(128) CONSTRAINT NN_cmsrohs_Textstring NOT NULL,
> Value NUMBER(2),
> CONSTRAINT PK_dxdb_cms_rohs_enum PRIMARY KEY (Textstring)
> );
>
> drop materialized VIEW dxdb_cms_attvalues_MV;
> CREATE materialized VIEW dxdb_cms_attvalues_MV
> build immediate refresh complete next sysdate + 1/144
> enable query rewrite
> AS
> select
> fsc_snr,
> dxdb_cms_vorzug_enum.value vorzugvalue,
> dxdb_cms_zulassung_enum.value zulassungvalue,
> dxdb_cms_rohs_enum.value rohsvalue
> from
> dxdb_cmsattrib,
> dxdb_cms_vorzug_enum,
> dxdb_cms_zulassung_enum,
> dxdb_cms_rohs_enum
> where
> dxdb_cmsattrib.vorzug=dxdb_cms_vorzug_enum.textstring
> and dxdb_cmsattrib.zulassung=dxdb_cms_zulassung_enum.textstring
> and dxdb_cmsattrib.rohs=dxdb_cms_rohs_enum.textstring
> ;
>
> Does anyone know what's wrong?
>
> Lots of Greetings and thanks!
> Volker

If you omit "next sysdate + 1/144" part of refresh clause, view will be created. You can then manually setup job to refresh it automatically.

Best regards

Maxim Received on Mon Aug 28 2006 - 10:20:07 CDT