Hello, Its version 19.9.0.0 of Oracle. We got one issue in which some of the materialize view not getting refreshed recently. Its a legacy system and we are trying to understand from where these materialize view were getting refreshed before. And we got a scheduler job and in dba_scheduler_job_run_details the status of the job is showing as succeeded every day, but i am unable to understand the code which is written in its job_action parameter.
As per below code, i am expecting a materialize view named as 'USER1_TRAN_TABLES' which is getting refreshed through this job everyday. But we don't see any such objects/mviews present in DBA_MVIEWS data dictionary. And the materialized views which are in DBA_MVIEWS(those are not getting refreshed) are having different names. So wanted to understand as we don't see any objects named as USER1_TRAN_TABLES, so how-come this job/scheduler getting succeeded in daily basis? Or is it possible that , this(user1.USER1_TRAN_TABLES) could be a wrapper objects to the existing materialized views and somehow dropped? Can you please help me understand this below job logic?
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'XXXX'
,start_date => TO_TIMESTAMP_TZ('2021/05/10 07:00:00.000000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'trunc(sysdate+1) +7/24'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'dbms_refresh.refresh(''"USER1"."USER1_TRAN_TABLES"'');'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'USER1.DBMS_JOB$_123888'
,attribute => 'AUTO_DROP'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'USER1.DBMS_JOB$_123888');
END;
/