Skip to Main Content

SQL & PL/SQL

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!

Understanding dbms_scheduler code

user10472047Sep 23 2021 — edited Sep 23 2021

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

Comments

Post Details

Added on Sep 23 2021
3 comments
67 views