Understanding dbms_scheduler code

user10472047

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;
/

Paulzip

I'm not sure that is doing anything as it's not a PL/SQL block, it should be something like the following. Disable the job and try :

,job_action     => q'[begin dbms_refresh.refresh('"USER1"."USER1_TABLES"'); end;]'

then look at dba_scheduler_job_log, you might see a different status other than SUCCESS.

USER1_TABLES might be a synonym to an MView, so check that.

user10472047

Thank you so much.

I didn't not find such object(with name user1_tables) in dba_objects if it would have been synonym, we would have seen it in dba_objects view. But then i see below doc, and as we have used dbms_refresh.refresh rather dbms_mview.refresh function used in job_action, so it must be a materialized view group consisting up of multiple materialized views which were expected to be refreshed through this job. Now the only thing is we need to find out what was the exact definition of this materialized view group to make it proper again.

https://docs.oracle.com/cd/B19306_01/server.102/b14227/rarmviewgroup.htm#i15751

Solomon Yakobson

Now the only thing is we need to find out what was the exact definition of this materialized view group to make it proper again

So issue:

SELECT  *
  FROM  DBA_REFRESH_CHILDREN
  WHERE ROWNER = 'USER1'
    AND RNAME = 'USER1_TRAN_TABLES'
/

SY.