ORASOA side:

 

create table tab1(c1 char(10),c2 char(10),c3 char(10));

 

begin

for i in 1..10000 loop

insert into tab1 values(i,’A’,’B’);

end loop;

end;

 

alter table tab1 add constraint pk_tab1 primary key(c1);

 

create materialized view log on tab1;—– Default is Primary Key

 

SQL> select * from mlog$_tab1 where rownum<5;

 

C1         SNAPTIME$ D O

———- ——— – –

CHANGE_VECTOR$$

———————————————-

 

429        01-JAN-00 I N

FE

 

430        01-JAN-00 I N

FE

 

431        01-JAN-00 I N

FE

 

create table tab2(c1 char(10),c2 char(10),c3 char(10));

 

begin

for i in 1..10000 loop

insert into tab2 values(i,’A’,’B’);

end loop;

end;

 

create materialized view log on tab1 with rowid;

 

SQL> select * from mlog$_tab2 where rownum<5;

 

M_ROW$$

——————————————————————————–

 

SNAPTIME$ D O

——— – –

CHANGE_VECTOR$$

——————————————————————————–

 

AAALv8AAEAAABZuACR

01-JAN-00 I N

FE

 

AAALv8AAEAAABZuACS

01-JAN-00 I N

FE

 

CREATE MATERIALIZED VIEW mv_fast_commit_tab1
build immediate
REFRESH FAST ON commit
AS SELECT * from tab1;

 

ORCL  side:

 

CREATE DATABASE LINK ORASOA_LINK CONNECT TO scott IDENTIFIED BY tiger USING ‘ORASOA’;
select * from dual@ORASOA_LINK

 

SQL> select * from dual@ORASOA_LINK;

 

D

–

X

 

create materialized view mv_comp_tab1
build immediate
refresh complete
as select * from tab1@orasoa_link

SQL>select count(1) from mlog$_tab1;

 

count(1)

——–

0

 

create materialized view mv_fast_demand_tab1
build immediate
refresh fast on demand
as select * from tab1@orasoa_link

 

SQL> create or replace procedure p_ref is

2  begin

3  dbms_snapshot.refresh(‘MV_FAST_DEMAND_TAB1′,’F’);

4  end;

5  /

 

Procedure created.

 

SQL> CREATE OR REPLACE PROCEDURE job_call AS

2   JobNo user_jobs.job%TYPE;

3  BEGIN

4    dbms_job.submit(JobNo, ‘begin p_ref;end;’, SYSDATE,’SYSDATE + 1/1440′);

5    COMMIT;

6  END;

7  /

 

Procedure created.

 

SQL> exec job_call;

 

PL/SQL procedure successfully completed.

 

SQL> commit;

 

Commit complete.

 

CREATE MATERIALIZED VIEW mv_fast_demand_scheduled_tab1
REFRESH FAST ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS
SELECT  *
FROM tab1@orasoa_link;

 

create materialized view mv_count_tab2
build immediate
refresh complete
as select count(1) from tab2@orasoa_link

 

 

CREATE MATERIALIZED VIEW mv_comp_tab2
refresh fast on demand
with rowid
AS
SELECT  *
FROM tab2@orasoa_link;

 

BEGIN Dbms_Stats.Gather_Table_Stats( ownname => ‘SCOTT’, tabname => ‘mv_comp_tab2’); END;

 

BEGIN
Dbms_Refresh.Make(
name => ‘SCOTT.MINUTE_REFRESH’,
list => ”,
next_date => SYSDATE,
interval => ‘/*1:Mins*/ sysdate + 1/(60*24)’,
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

 

 

BEGIN
Dbms_Refresh.Add(
name => ‘SCOTT.MINUTE_REFRESH’,
list => ‘SCOTT.mv_comp_tab2’,
lax => TRUE);
END;
/

 

Cleaning Up

 

DROP MATERIALIZED VIEW emp_mv;
DROP DATABASE LINK TSH1.WORLD;

BEGIN
Dbms_Refresh.Destroy(name => ‘SCOTT.MINUTE_REFRESH’);
END;
/

DROP MATERIALIZED VIEW LOG ON scott.emp;