RE: Newly inserted record into MV(updatable MV) got deleted when

From: Sami <saminathans_at_myrealbox.com>
Date: Sat, 08 Nov 2003 14:09:24 -0800
Message-ID: <F001.005D619E.20031108140924@fatcity.com>

I figured out myself. MV grop was missing.

-----Original Message-----

Saminathan
Sent: Saturday, November 08, 2003 3:59 PM To: Multiple recipients of list ORACLE-L refresh occurs

Hi All,

Newly inserted record into MV(updatable MV) got deleted when refresh occurs. How to push the changes made into actual base table. Please see below.

SQL> create materialized view log on dept with primary key;

Materialized view log created.

SQL> create materialized view dept_mv refresh fast for update as select * from dept;

Materialized view created.

SQL> select count(*) from dept_mv;

  COUNT(*)

---

         4

SQL> insert into dept_mv values(90,'From DEPT_MV','NJ');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from dept_mv;

  COUNT(*)

---

         5

SQL> select * from USLOG$_DEPT_MV;

    DEPTNO SNAPTIME$$ DML OLD
---------- --------------- --- ---

        90 01-JAN-00 I N SQL> BEGIN

  2     DBMS_REFRESH.MAKE(
  3       name => '"SCOTT"."SCOTT_REFGRP1"',
  4       list => '',
  5       next_date => SYSDATE,
  6       interval => '/*1:Hr*/ sysdate + 1/192',
  7       implicit_destroy => FALSE,
  8       lax => FALSE,
  9       job => 0,
 10       rollback_seg => NULL,
 11       push_deferred_rpc => TRUE,
 12       refresh_after_errors => TRUE,
 13       purge_option => NULL,
 14       parallelism => NULL,
 15       heap_size => NULL);

 16 END;
 17 /

PL/SQL procedure successfully completed.

SQL> BEGIN

  2     DBMS_REFRESH.ADD(
  3       name => '"SCOTT"."SCOTT_REFGRP1"',
  4       list => '"SCOTT"."DEPT_MV"',
  5       lax => TRUE);

  6 END;
  7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from dept;

  COUNT(*)

---

         4

SQL> select count(*) from dept_mv;

  COUNT(*)

---

         5

SQL> exec dbms_refresh.refresh('scott_refgrp1');

PL/SQL procedure successfully completed.

SQL> select count(*) from dept;

  COUNT(*)

---

         4

SQL> select count(*) from dept_mv;

  COUNT(*)

---

         4

Thanks
Sami

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Saminathan
  INET: saminathans_at_myrealbox.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sami
  INET: saminathans_at_myrealbox.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat Nov 08 2003 - 16:09:24 CST