Job scheduling
775803Feb 18 2011 — edited Feb 21 2011Hi, I have requirement to extract the data from remote database to local database that contain one clob column. So I created materialized view .
Now my requirement is I want real time data, means if one row is inserted into remote table then it should display in local database within minimum time without affecting remote database transition time. so can’t create trigger or on commit materialized view. So I create "REFRESH FORCE ON DEMAND" and created one job that refresh it. Below is definition
CREATE MATERIALIZED VIEW test_mview
TABLESPACE USERS
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE USERS
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT * from emp;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'DBMS_MVIEW.REFRESH("test_mview",''F'');'
,next_date => to_date('18/02/2011 22:43:56','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+(1/720)'
,no_parse => TRUE
);
end
Is my approach correct? Is there any side effects as job is running with the such a short interval of time i.e. 'SYSDATE+(1/720)'
Is there any more better solution?
Please help.
Thanks..