3

Very new to this topic - using 11g.

When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler.

Am I doing something wrong or am I totally misunderstanding what's going on?

3 Answers 3

1

You are not doing anything wrong and your understanding is correct. Materialized views that are created or altered to have a refresh schedule use DBMS_JOB rather than DBMS_SCHEDULER. This would be unexpected given the following wording from the 11.2 Administrators Guide (emphasis mine):

DBMS_JOB is a PL/SQL package that you use to schedule jobs. It is replaced by Oracle Scheduler, which is more powerful and flexible. Although Oracle recommends that you switch from DBMS_JOB to Oracle Scheduler, DBMS_JOB is still supported for backward compatibility.

I agree with Jack Douglas and the comments he linked to. DBMS_JOB is unlikely to go away anytime soon.

4
  • Leigh, I'm not sure that I'd read the extract in the same way as you. In prior versions scheduled MVIEW refreshes were accomplished by creating a dbms_job. I would not expect that code to be removed from the oracle kernel because dbms_scheduler was created. I'd say that fell under the backward compatibility banner. Sep 1, 2011 at 12:57
  • You may be correct. Oracle's statement isn't quite as strong as their statment on LONGs for example: Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead. LONG columns are supported only for backward compatibility. That being said, it would go against the conventional meaning of replaced and still supported for backward compatibility to not remove it at some point. Sep 1, 2011 at 20:22
  • In your opinion should I leave the job as is or replace it with a manually created dbms_scheduler job that does a refresh?
    – engil
    Sep 2, 2011 at 18:33
  • 1
    @engil Leave it. The feature hasn't been removed yet and if/when it gets removed you can change it as part of the migration process. In the end you may never have to do this and save yourself the trouble. Sep 2, 2011 at 19:34
1

dbms_scheduler, while being much more powerful/flexible than dbms_job, is not a simple/complete replacement for it. dbms_job is still widely used as you have discovered and is unlikely to go away

0

It seams that in Oracle Release 19.3 a DBMS_SCHEDULER job is created for a MV with start with ... next

select JOB_NAME, JOB_ACTION from USER_SCHEDULER_JOBS;

JOB_NAME             JOB_ACTION                              
-------------------- ----------------------------------------
MV_RF$J_0_S_2932     dbms_refresh.refresh('"LAB"."SIM_JOB"');

The refresh group is created in the same way, but the JOB is zero and the JOB_NAME is populated pointing in USER_SCHEDULER_JOBS.

select owner, name, rname, REFGROUP, job,JOB_NAME, interval, next_date 
from DBA_REFRESH_CHILDREN;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.