Executing External Programs From Within Oracle - Page 2June 11, 2004 How to View Scheduler InformationTo 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.
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.
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 ExampleJust 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 JobIf 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
