How to View Scheduler Information
To take a look at the jobs that have run and their
success or failure, you can run the following SQL, found in Listing 4. If you are concerned with just the status of a job and whether it is
scheduled to run or not you can issue the SQL in Listing 5.
Listing 4.
Show status of previously ran jobs
SQL> SELECT JOB_NAME, STATUS, ERROR#
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'VMSTAT_JOB';
JOB_NAME STATUS ERROR#
--------------- ---------------- ------
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
VMSTAT_JOB SUCCEEDED 0
Listing 5.
Status of scheduled jobs
SQL> SELECT JOB_NAME, STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'VMSTAT_JOB';
JOB_NAME STATE
------------------- ---------------
VMSTAT_JOB SCHEDULED
Looky mom, I can delete from a DBA view.
SQL> DELETE FROM dba_scheduler_job_run_details;
SQL> COMMIT;
Output From Our Example
Just to show that we actually generated some
output from our external job call from within Oracle take a look at Listing 6.
Listing 6.
[oracle@bier oracle]$ cat /tmp/vmstat.LST
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 97268 187756 588084 0 0 87 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 97268 187792 588048 0 0 87 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 97140 187840 588000 0 0 87 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 98612 187864 587976 0 0 87 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 98612 187920 587920 0 0 86 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 98612 187968 587872 0 0 86 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 98548 188016 587824 0 0 86 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 0 98548 188056 588044 0 0 86 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 98612 188096 588004 0 0 86 102 533 98 6 1 89 4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 97012 188124 587976 0 0 86 102 533 98 6 1 89 4
Droping the Program and Job
If you should ever want to drop the newly
created program and job you can use the following DBMS_SCHEDULER drop
procedures.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM ('vmstat_pgm');
END;
/
BEGIN
DBMS_SCHEDULER.DROP_JOB ('vmstat_job');
END;
/
The ability for us as DBAs to extend internal database
scheduling to call external applications is invaluable. Personally, I no longer
need to rely upon cron job entries and their limited ability to schedule my
external procedures and DBA tasks. Now ALL scheduled database tasks can be
scheduled within my database and be controlled by me where I have control. This
is a great day.
»
See All Articles by Columnist James Koopmann
» 
IE 7
Firefox 2.0
Comment and Contribute
