Forum Stats

  • 3,759,940 Users
  • 2,251,618 Discussions
  • 7,870,876 Comments

Discussions

Understanding dbms_scheduler code

user10472047
user10472047 Member Posts: 197 Red Ribbon
edited Sep 23, 2021 8:17PM in SQL & PL/SQL

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


Answers

  • Paulzip
    Paulzip Member Posts: 8,460 Blue Diamond
    edited Sep 23, 2021 8:29PM

    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
  • user10472047
    user10472047 Member Posts: 197 Red Ribbon

    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
    Solomon Yakobson Member Posts: 18,751 Red Diamond

    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.