SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 16:35:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONN STLBAS/STLBAS@TEST107
Connected.
SQL> drop MATERIALIZED VIEW LOG ON stchrtac;

Materialized view log dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON stchrtac
  2  WITH SEQUENCE, ROWID (headcode, acctname, dbcrclas)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL> drop MATERIALIZED VIEW LOG ON sttrndtl;

Materialized view log dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON sttrndtl
  2  WITH SEQUENCE, ROWID (compcode, docttype,acctcode,doctdate,jvlcamnt)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>
SQL> CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS
  7  SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  8           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  9         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
 10           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 11           b.acctname glname, b.dbcrclas, SYSDATE - 1 timstamp,SYSDATE jobstamp
 12      FROM sttrndtl a, stchrtac b
 13     WHERE a.doctdate <= SYSDATE - 1
 14     AND   a.acctcode = b.acctcode
 15  GROUP BY a.compcode,
 16           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 17           b.headcode,
 18           a.acctcode,
 19           a.doctdate,
 20           b.acctname,
 21           b.dbcrclas;
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 12:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL>

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 17:05:58 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn stlbas/stlbas@test107
Connected.
SQL>
SQL> CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  7           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  8         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
  9           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 10           b.acctname glname, b.dbcrclas, SYSDATE - 1 timstamp,SYSDATE jobstamp,count(*) num
 11      FROM sttrndtl a, stchrtac b
 12     WHERE a.doctdate <= SYSDATE - 1
 13     AND   a.acctcode = b.acctcode
 14  GROUP BY a.compcode,
 15           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 16           b.headcode,
 17           a.acctcode,
 18           a.doctdate,
 19           b.acctname,
 20           b.dbcrclas;
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 11:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> ed
Wrote file afiedt.buf

  1  CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  7           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  8         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
  9           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 10           b.acctname glname, b.dbcrclas ---, SYSDATE - 1 timstamp,SYSDATE jobstamp,count(*) num
 11      FROM sttrndtl a, stchrtac b
 12     WHERE ---a.doctdate <= SYSDATE - 1
 13        a.acctcode = b.acctcode
 14  GROUP BY a.compcode,
 15           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 16           b.headcode,
 17           a.acctcode,
 18           a.doctdate,
 19           b.acctname,
 20*          b.dbcrclas
SQL> /
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 11:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL>

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 18:06:08 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/tiger@beftn
Connected.
SQL> drop MATERIALIZED VIEW LOG ON scott.emp;
drop MATERIALIZED VIEW LOG ON scott.emp
*
ERROR at line 1:
ORA-12002: there is no materialized view log on table "SCOTT"."EMP"


SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON scott.emp
  2  WITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>
SQL> drop MATERIALIZED VIEW LOG ON scott.dept
  2
SQL>  CREATE MATERIALIZED VIEW LOG ON scott.dept
  2  WITH SEQUENCE, ROWID (DEPTNO)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>  DROP MATERIALIZED VIEW scott.sal_dept_mv;
 DROP MATERIALIZED VIEW scott.sal_dept_mv
*
ERROR at line 1:
ORA-12003: materialized view "SCOTT"."SAL_DEPT_MV" does not exist

SQL>
SQL>  CREATE MATERIALIZED VIEW scott.sal_dept_mv
  2             NOLOGGING
  3             PARALLEL
  4             BUILD IMMEDIATE
  5             REFRESH fast ON DEMAND
  6           ENABLE QUERY REWRITE
  7             AS
  8            SELECT e.job, e.deptno, sum(e.sal)
  9            FROM emp e, dept d
 10            WHERE e.deptno=d.deptno
 11            GROUP BY e.job, e.deptno;

Materialized view created.

SQL>