Re: Materialized view refresh fast without Primary key

From: Chirag DBA <chiragdba_at_gmail.com>
Date: Thu, 19 Apr 2007 22:15:33 -0400
Message-ID: <1a3629600704191915j18e2d78fj52e2a89fb02c9146@mail.gmail.com>

I just run yr queries and still the same error.

SQL> create table t(no number);

Table created.

SQL> create materialized view log on t with rowid (no) including new values;

Materialized view log created.

SQL> grant select, insert,update on t to mv2;

Grant succeeded.

SQL>

• another schema SQL> select * from mv1.t;

no rows selected

SQL> create materialized view mv_t
  2 refresh fast on demand
  3 with rowid as
  4 select * From mv1.t;
select * From mv1.t

                  *

ERROR at line 4:
ORA-12018: following error encountered during code generation for "MV2"."MV_T"
ORA-00942: table or view does not exist

Wondering how come I am not able to, or How can you simple create it ?

On 4/19/07, Alexander Fatkulin <afatkulin_at_gmail.com> wrote:
>
> Well, now it's saying what there is now such table?
>
> It works.
>
> SQL> create table t (n number);
>
> Table created.
>
> SQL> create materialized view log on t with rowid (n) including new
> values;
>
> Materialized view log created.
>
> SQL> create materialized view mv_t
> 2 refresh fast on demand
> 3 with rowid as
> 4 select * from t;
>
> Materialized view created.
>
> On 4/20/07, Chirag DBA <chiragdba_at_gmail.com> wrote:
> > No Luck.
> >
> > ERROR at line 1:
> > ORA-12018: following error encountered during code generation for
> > "MV2"."TEMP"
> > ORA-00942: table or view does not exist
> >
> >
> > On 4/19/07, Alexander Fatkulin <afatkulin_at_gmail.com> wrote:
> > > Try using this instead:
> > >
> > > create materialized view temp
> > > refresh fast on demand
> > > with rowid
> > > as select * from mv1.temp ;
> > >
> > > On 4/20/07, Chirag DBA <chiragdba_at_gmail.com > wrote:
> > > > Can I refresh materialized view FAST when a primary table doesnt
> have a
> > > > primary key ?
> > > >
> > > > As per oracle it should work with ROWID option while creating MV
> logs.
> > > >
> > > > SQL> create table temp(no number);
> > > > Table created.
> > > >
> > > > SQL> create materialized view log on temp with rowid(no) including
> new
> > > > values;
> > > > Materialized view log created.
> > > >
> > > > -- On secondary site (Same db)
> > > >
> > > > SQL> create materialized view temp refresh on demand as select *
> from
> > > > mv1.temp ;
> > > > create materialized view temp refresh on demand as select * from
> > mv1.temp
> > > >
> > > > *
> > > > ERROR at line 1:
> > > > ORA-12014: table 'TEMP' does not contain a primary key constraint
> > > >
> > > >
> > > > SQL> create materialized view temp as select * from mv1.temp;
> > > > create materialized view temp as select * from mv1.temp
> > > > *
> > > > ERROR at line 1:
> > > > ORA-12014: table 'TEMP' does not contain a primary key constraint
> > > >
> > > >
> > > > Anyone can tell me what I am doing wrong ?
> > > >
> > > > --
> > > > Cheers,
> > > > ------------------------
> > > > Chirag Majmundar
> > > >
> > > > --
> > > > Cheers,
> > > > ------------------------
> > > > Chirag Majmundar
> > >
> > >
> > > --
> > > Alexander Fatkulin
> > >
> >
> >
> >
> > --
> > Cheers,
> > ------------------------
> > Chirag Majmundar
>
>
> --
> Alexander Fatkulin
>

-- 
Cheers,
------------------------
Chirag Majmundar

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

Received on Thu Apr 19 2007 - 21:15:33 CDT