MVIEW REFRESH REMOTE USING DBLINK
Set the job queue process to 0 purposefully and donot set this in production system, since this may interrupt all scheduled jobs
kIsH@xHydra<>ALTER system set job_queue_processes=0;
System altered.
Create a database link from target to source. Before this, verify the tns connectivity and listener.
kIsH@xHydra<>conn kish/password
Connected.
kIsH@xHydra<>create database link mvremote connect to kish identified by "password" using 'db9zx';
Database link created.
kIsH@xHydra<>SELECT * from DUAL@mvremote;
D
-
X
Check the total records in base table from source
kIsH@Xhydra<>select count(*) from ytbl;
COUNT(*)
----------
16000000
Create materialized view in remote database using dblink
kIsH@xHydra<>CREATE MATERIALIZED VIEW mvremote
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT * FROM KISH.YTBL@mvremote;
Materialized view created.
kIsH@xHydra<>select count(*) from mvremote;
COUNT(*)
----------
1000000
Create a scheduler job for complete refresh of mview
kIsH@xHydra<>BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH(''MVREMOTE'');END;',
number_of_arguments => 0,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job refresh MVREMOTE every day'
);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
PL/SQL procedure successfully completed.
Verify the job
kIsH@xHydra<>SELECT owner,job_name,program_name,job_type,job_action FROM dba_scheduler_jobs WHERE job_name LIKE '%MVREMOTE%';
OWNER JOB_NAME PROGRAM_NAME JOB_TYPE JOB_ACTION
-------------------- -------------------- -------------------- -------------------- --------------------
KISH MV_MVREMOTE_REFRESH1 STORED_PROCEDURE REFRESH_MVREMOTEkIsH@xHydra<>col USERNAME for a20
kIsH@xHydra<>set lines 200 pages 1000
kIsH@xHydra<>select s.username,s.sid,s.serial#,s.program,p.spid from v$process p inner join v$session s on (s.paddr = p.addr) where s.username='KISH';
USERNAME SID SERIAL# PROGRAM SPID
-------------------- ---------- ---------- ------------------------------------------------ ------------------------
KISH 1 51465 sqlplus@staxhost (TNS V1-V3) 5182
Check dbms_scheduler functions which can be used to run a job manually
kIsH@xHydra<>col text for a80
kIsH@xHydra<>SELECT name,type,text FROM dba_source WHERE text LIKE '%DBMS_SCHEDULER%';
NAME TYPE TEXT
-------------------- ------------ --------------------------------------------------------------------------------
DBMS_SCHEDULER PACKAGE logging_level IN PLS_INTEGER DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
DBMS_WORKLOAD_CAPTUR PACKAGE -- Jobs (DBMS_SCHEDULER/DBMS_JOB) will not be captured, no matter how
E
DBMS_REFRESH PACKAGE -- or FALSE indicates that DBMS_SCHEDULER will be used to create the
DBMS_FEATURE_REGISTE PROCEDURE * DBMS_SCHEDULER HWM is number of jobs per day
R_ALLHWM
DBMS_FEATURE_REGISTE PROCEDURE HWM_DBMS_SCHEDULER_STR CONSTANT VARCHAR2(1000) :=
R_ALLHWM
DBMS_FEATURE_REGISTE PROCEDURE ('HWM_DBMS_SCHEDULER',
R_ALLHWM
DBMS_FEATURE_REGISTE PROCEDURE HWM_DBMS_SCHEDULER_STR,
R_ALLHWM
SYSLSBY_EDS_DDL_TRIG TRIGGER DBMS_SCHEDULER.CREATE_JOB(
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id'
,inst_id);
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);
MGMT_CONFIG PACKAGE BODY -- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_2_' || inst_id,'instan
ce_id',inst_id);
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.ENABLE (p_job_name || '_2_' || inst_id);
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.RUN_JOB(JOB_NAME);
MGMT_CONFIG PACKAGE BODY DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);
15 rows selected.
DBMS_SCHEDULER.RUN_JOB can trigger the mview refresh job immediately.
kIsH@xHydra<>BEGIN
DBMS_SCHEDULER.RUN_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
USE_CURRENT_SESSION => FALSE
);
END;
/
BEGIN
*
ERROR at line 1:
ORA-27492: unable to run job "KISH"."MV_MVREMOTE_REFRESH1": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2
Set the job queue process parameter to higher value and execute the procedure again in target.
kIsH@xHydra<>ALTER system set job_queue_processes=20;
System altered.
kIsH@xHydra<>BEGIN
DBMS_SCHEDULER.RUN_JOB
(
job_name => 'MV_MVREMOTE_REFRESH1',
use_current_session => FALSE
);
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Monitor the refresh from target database and all that can be seen is "SQL*Net more data from dblink" in general, unless there is a scarcity of any resource.kIsH@xHydra<>col CURRMVOWNER for a20
kIsH@xHydra<>col CURRMVNAME for a20
kIsH@xHydra<>select * from v$mvrefresh;
SID SERIAL# CURRMVOWNER CURRMVNAME CON_ID
---------- ---------- -------------------- -------------------- ----------
77 51278 KISH MVREMOTE 1
kIsH@xHydra<>col ACTION for a20
kIsH@xHydra<>col EVENT for a20
kIsH@xHydra<>col username for a20
kIsH@xHydra<>col module for a20
kIsH@xHydra<>SELECT username,sid,serial#,action,module,program,event FROM v$session WHERE action='MV_MVREMOTE_REFRESH1';
USERNAME SID SERIAL# ACTION MODULE PROGRAM EVENT
-------------------- ---------- ---------- -------------------- -------------------- -------------------- --------------------
KISH 77 51278 SQL*Net more data from dblink
Target mview is refreshed successfully in remote database.kIsH@xHydra<>select count(*) from mvremote;
COUNT(*)
----------
16000000