Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Newly inserted record into MV(updatable MV) got deleted when 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);
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REFRESH.ADD( 3 name => '"SCOTT"."SCOTT_REFGRP1"', 4 list => '"SCOTT"."DEPT_MV"', 5 lax => TRUE);
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 servicesReceived on Sat Nov 08 2003 - 14:59:25 CST
---------------------------------------------------------------------
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).
![]() |
![]() |