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… 😉