Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12.2

Since its release in 10gR1, Oracle have consistently added neat functionality to the “new” scheduler with every database release. Yes, I still call it the new scheduler and you would be surprised how many people still insist on using DBMS_JOB rather than switching to DBMS_SCHEDULER.

Oracle Database 12c Release 2 (12.2) is no exception to this pattern, as once again we have some new stuff to play with.

In website housekeeping news, each scheduler article on the site was getting loaded down with links to other scheduler articles, so I’ve collected them all together into a section on one of my links pages, along with a feature list breakdown, so you don’t have to trawl through all the articles to get to the bit you need. You can see that section here.

For many people the simple example in the first article I wrote on the scheduler will be all they ever use, but it’s good to know what it’s capable of doing, especially when you see what some people get up to with CRON. ๐Ÿ™‚

Cheers

Tim…

Update: For theย person that just contacted me about the non-transactional nature of DBMS_SCHEDULER, I wrote about this here and finally raised an SR about it some time ago. That was converted to a bug, which was then altered to an enhancement request.

Bug 21247177 : DBMS_SCHEDULER JOB NOT DELETED WITH ROLLBACK, WHEREAS DBMS_JOB JOB DOES

This issue is still present in 12cR2, with a workaround of using DBMS_JOB. ๐Ÿ™‚

Scheduler Enhancements in Oracle Database 12c

I’ve spent the last couple of days playing around with the scheduler enhancements in Oracle 12c.

I guess the big news is the new “script jobs”, which are pretty cool. This kind-of passed me by until Brynn Llewellyn mentioned them at UKOUG in his Multitenant presentation, at which point I made a note to check them out.

I’ve been having some trouble with the “BACKUP_SCRIPT” jobs up until a few minutes ago. My problem was I couldn’t see what the stdout/stderr text was, so I couldn’t determine why there were not working. The “$ORACLE_HOME/scheduler/log” directory was empty and there were no messages in the trace files or alert log. Then I stumbled upon the new columns added to the ALL_SCHEDULER_JOB_RUN_DETAILS view. The OUTPUT column, not surprisingly, gives you the output from the scripts. Once I could see the error message it took me a few seconds to fix the issue andย Bingo! ๐Ÿ™‚

The new job types are a nice addition, allowing you to run file-based scripts or incline scripts much more easily that before.

Cheers

Tim…

 

SQL Developer 3.1 : Scheduler Support…

I had a play around with the scheduler support in SQL Developer 3.1 today. I’m late to the party because most of it has been there since 3.0, but what the hell.

It’s more of an opinion piece, so it started as a blog post, but it got too big. Not really what I usually put on the website, but I figured if I put it on the blog it would get in the way of the movie and book reviews and that simply wouldn’t do… ๐Ÿ™‚

I am still worried about feature creep turning SQL Developer into the new TOAD, but so far so good. I’m liking it more and more with each release.

Cheers

Tim…

New scheduler stuff in 11gR2…

There are some nice new features in the scheduler in 11gR2. I’ve written about them here:

Remember, there was already a bunch of new stuff added in llgR1, so there’s a lot of whiz-bang stuff if you are moving from 10g.

I do think there are some real security issues with some of this stuff if it is used unwisely though. The remote jobs (external and database) just strike me as a disaster waiting to happen in the wrong hands. ๐Ÿ™‚

Cheers

Tim…

A Tale of 4 Schedulers…

A long time ago in a galaxy far, far away, there was only one Oracle scheduler. It was managed using the DBMS_JOB package and the job information was stored in a single table.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'JOB$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
JOB$

1 row selected.

SQL>

Information about scheduled jobs was available from the “DBA_JOBS%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_JOBS%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_JOBS
DBA_JOBS_RUNNING

2 rows selected.

SQL>

Oracle 10g introduced a new scheduler that was managed using the DBMS_SCHEDULER package, whose job information was stored in the “SCHEDULER$%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'SCHEDULER$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
SCHEDULER$_CHAIN
.
.
.
SCHEDULER$_WINGRP_MEMBER

22 rows selected.

SQL>

Information about scheduled jobs for this new scheduler was available from the “DBA_SCHEDULER%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_SCHEDULER%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_SCHEDULER_CHAINS
.
.
.
DBA_SCHEDULER_WINGROUP_MEMBERS

19 rows selected.

SQL>

In addition to the two internal schedulers, the OEM 10g DBConsole had its own scheduler, which stored job information in the “MGMT_JOB%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

44 rows selected.

SQL>

To confuse matters further, the OEM 10g Grid Control repository had a similar scheduler to OEM 10g DBConsole, but with a few extra tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

53 rows selected.

SQL>

Faced with the choice of four ways to schedule a job, most DBAs called “Tim Hall” decided to pick a single scheduler and keep things simple ๐Ÿ™‚

Cheers

Tim…

PS. Don’t even get me started on OS schedulers and the Oracle Applications scheduler… ๐Ÿ˜‰