Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 11, 2004

Executing External Programs From Within Oracle - Page 2

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM