Sign up ×

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
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. –  Niall Litchfield Sep 1 '11 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. –  Leigh Riffel Sep 1 '11 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 '11 at 18:33
@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. –  Leigh Riffel Sep 2 '11 at 19:34

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

share|improve this answer

Your Answer


