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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
