Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alex.

Asked: April 25, 2025 - 3:20 pm UTC

Last updated: April 29, 2025 - 5:44 am UTC

Version: 19

Viewed 1000+ times

You Asked

After using dbms_scheduler since its introduction I was surprised to learn that it is not the way application development should go.
I was explained that having scheduling outside of the database is better than having it inside the database.
And the replacement are crontab, complicated self-made scheduler using Perl, Python, favorite language, preferred third party application, or refreshable materialized views.
I did not get the answer to my question why using developed app stored code or other Oracle supplied packages inside the database is good practice and using dbms_scheduler is bad.
What is your view on that issue?


and Connor said...

I was surprised to learn that it is not the way application development should go.

I am equally surprised because that statements sounds like nonsense to me :-)

I was explained that having scheduling outside of the database is better than having it inside the database.

Also nonsense, but I stress, this is NOT me saying that inside the database is better than outside.

As always, it depends.

For example, consider something like APEX. It is a application framework that runs completely within the database. It hence makes 100% perfect sense for its scheduled tasks to be run by the scheduler, because if the database is down, then the fact that your scheduler jobs are not running is of no consequence, because (by definition) APEX is also not running.

At the other end of the spectrum, what if you have hundreds or thousands of non-database tasks across hundreds of servers that you need scheduled. Maybe for that kind of extreme need, you would opt for a 3rd party solution to provide a single enterprise-wide view of the scheduled tasks in your organization.

In reality, most people are somewhere in-between these two. For things which are database-dependent (eg materialized views, PLSQL jobs etc etc), the (database) scheduler is an ideal place for them to be, because there are no external dependencies, and similar, if your database was down you would NOT want the jobs to run (which they wont).

For things that sit "above" the database, you might then opt for cron or similar. eg, it would make no sense to have OS metrics (disk space, CPU etc) to be monitored from a database scheduler task - because it unlikely to have any relationship to the database.

My simple rule of thumb is this: Whenever someone tells you "You should not use tool X in 100% of cases", they're generally talking nonsense, because there are almost never any absolutes in the IT space.

Rating

  (2 ratings)

Comments

Thank you Conor.

Alex, April 28, 2025 - 6:49 pm UTC

I use the same approach - it depends on the task to accomplish. I would also add the security consideration no need to keep password outside of the database if job owner unauthenticated schema.
DBA can create dedicated views for supporting teams to monitor jobs status, executions etc.
There is no need to have login having code executed by job instrumented. And quite a few other benefits - fir example sending notification if job failed.
Connor McDonald
April 29, 2025 - 5:44 am UTC

Nice input

Thank you Conor.

Alex, April 28, 2025 - 7:04 pm UTC

I use the same approach - it depends on the task to accomplish. I would also add the security consideration no need to keep password outside of the database if job owner unauthenticated schema.
DBA can create dedicated views for supporting teams to monitor jobs status, executions etc.
There is no need to have login having code executed by job instrumented and using dbms_sceduler_job _run_details.output. There are quite a few other benefits - for example, sending notification if job failed, loading files using external tables, unloading data, etc. I think that scheduler is one of the best and most reliabke Oracle features.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library