Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Job with materialized view not working anymore

user22208Sep 20 2010 — edited Sep 20 2010
I'm on windows 2008 server with 10.2.0.4

I have a job that was running at every hour in the database that was refreshing some materialized view (refresh group) the sql query on that MV goes by dblink to another database.

Le atrget database had crashed last thursday since then the job on my first database do not execute.

I tried to start the job in TOAD but it does nothing.... Le last refresh date still on thurday.

Here are my script :

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'dbms_refresh.refresh(''"EREGROUPEMENT_TEMP"."VM_CANTOR_CREDENTIALS"'');'
,next_date => to_date('20-09-2010 13:17:21','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate + 60/(60*24) '
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/



DECLARE
SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;
JobNo Number;
BEGIN
SnapArray(1) := 'EREGROUPEMENT_TEMP.VM_CANTOR_CREDENTIALS';
SnapArray(2) := 'EREGROUPEMENT_TEMP.VM_CANTOR_CV';
SnapArray(3) := 'EREGROUPEMENT_TEMP.VM_CANTOR_DIS_CV';
SnapArray(4) := 'EREGROUPEMENT_TEMP.VM_CANTOR_FINANCEMENT';
SnapArray(5) := 'EREGROUPEMENT_TEMP.VM_CANTOR_FORM_ETUD_STAG';
SnapArray(6) := 'EREGROUPEMENT_TEMP.VM_CANTOR_MOTS_CLES';
SnapArray(7) := 'EREGROUPEMENT_TEMP.VM_CANTOR_OBR_CV';
SnapArray(8) := 'EREGROUPEMENT_TEMP.VM_CANTOR_ORGANISME';
SnapArray(9) := 'EREGROUPEMENT_TEMP.VM_CANTOR_SCHA_CV';
SnapArray(10) := NULL;
SYS.DBMS_REFRESH.MAKE (
name => 'EREGROUPEMENT_TEMP.VM_CANTOR_CREDENTIALS'
,tab => SnapArray
,next_date => TO_DATE('01/01/4000 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
,interval => 'SYSDATE + 60/(60*24)'
,implicit_destroy => TRUE
,lax => TRUE
,job => 0
,rollback_seg => NULL
,push_deferred_rpc => TRUE
,refresh_after_errors => FALSE
,purge_option => 1
,parallelism => 0
,heap_size => 0
);

select job
into JobNo
from all_refresh
where rowner = 'EREGROUPEMENT_TEMP'
and rname = 'VM_CANTOR_CREDENTIALS';

SYS.DBMS_JOB.BROKEN(JobNo,TRUE);

Commit;
END;
/


Info on the MV :
Last Refresh 2010-09-16 17:39:25
Next Refresh SYSDATE + 60/(60*24)
Refresh Type FORCE
Refresh Mode Refresh Mode

Start With 2010-09-16 18:39:25

Do I have to refresh COMPLETE all the VIEWS MANUALLY and the recreate the refresh group or something wrong?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2010
Added on Sep 20 2010
13 comments
2,417 views