Oracle introduced the
utl_mail
package, which provides a simpler and more intuitive email API.
The package is loaded by running the following scripts as the
SYS user.
CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
GRANT EXECUTE ON UTL_MAIL TO test_user;
Before the package can be used, the SMTP gateway must be specified by
setting the
smtp_out_server
parameter. The parameter
is dynamic, but the instance must be restarted before an email can be
sent.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.mycompany.com';
SHUTDOWN IMMEDIATE
STARTUP
With the configuration complete, it is now possible to send an email
using the
send procedure.
BEGIN
UTL_MAIL.send(sender
=> 'me@mycompany.com',
recipients => 'you@mycompany.com',
subject
=> 'Test UTL_MAIL.SEND Procedure',
message
=> 'If you are reading this it worked!');
END;
/
As with the
utl_smtp
example, the code related to the job needs to be placed into a
database procedure which captures errors using an exception handler
and sends the appropriate email.
The following procedure is the Oracle equivalent of the one
used in the
utl_smtp example.
automated_email_alert_10g.sql
CREATE OR REPLACE PROCEDURE
automated_email_alert_10g AS
l_mail_host
VARCHAR2(50) := 'smtp.mycompany.com';
l_from
VARCHAR2(50) := 'jobs@mycompany.com';
l_to
VARCHAR2(50) := 'tim@mycompany.com';
BEGIN
DBMS_STATS.gather_database_stats(cascade => TRUE,
options => 'GATHER AUTO');
UTL_MAIL.send(sender
=> l_from,
recipients => l_to,
subject
=> 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Success',
message
=> 'AUTOMATED_EMAIL_ALERT_10G (MYSID) completed
successfully!');
EXCEPTION
WHEN OTHERS THEN
UTL_MAIL.send(sender
=> l_from,
recipients => l_to,
subject
=> 'AUTOMATED_EMAIL_ALERT_10G (MYSID): Error',
message
=> 'AUTOMATED_EMAIL_ALERT_10G (MYSID) failed with the
following error:' || SQLERRM);
END automated_email_alert_10g;
/
SHOW ERRORS
Next, a mechanism for running operating system commands and scripts
from within PL/SQL will be introduced.
If combining these techniques with the error logging method described
previously, one may wish to send additional information in the email
(prefix, start and end timestamps) to help pinpoint the errors in the
error_logs table
Monitoring Oracle Job Execution
This section will introduce how Oracle jobs can be monitored using
both database views and Oracle Enterprise Manager
(OEM). The scheduler
available in Oracle is radically different from the one available in
previous versions of Oracle.
For this reason, it will be dealt with separately. Explanations
of the
dbms_application_info
and
dbms_system
packages are included as they can simplify the identification and
monitoring of sessions related to scheduled jobs.
The list of currently scheduled jobs is displayed using the
dba_scheduler_jobs
view. This view provides a list of job names and the basic schedule
information. The
scheduled_jobs.sql
script shows how this view is used.
set linesize 200
column owner format a15
column next_run_date format a25
select
job_name,
owner,
nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'),
schedule_name) as next_run_date,
to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as
last_run_date,
job_class,
run_count
from
dba_scheduler_jobs
;
The output generated from the
scheduled_jobs.sql
script is shown below.
JOB_NAME
OWNER
NEXT_RUN_DATE
LAST_RUN_DATE
JOB_CLASS
RUN_COUNT
------------------------------ ---------------
------------------------- --------------------
------------------------------ ----------
GATHER_STATS_JOB
SYS
MAINTENANCE_WINDOW_GROUP
24-JUN-2004 08:09:39
AUTO_TASKS_JOB_CLASS
4
PURGE_LOG
SYS
24-JUN-2004 03:00:00
23-JUN-2004 03:00:01 DEFAULT_JOB_CLASS
19
TEST_FULL_JOB_DEFINITION
JOB_USER
24-JUN-2004 08:52:00
24-JUN-2004 08:52:00 DEFAULT_JOB_CLASS
281
TEST_PROG_SCHED_CLASS_JOB_DEF
JOB_USER
24-JUN-2004 09:00:16
24-JUN-2004 08:09:39 TEST_JOB_CLASS
16
ARGUMENT_JOB_DEFINITION
JOB_USER
24-JUN-2004 09:00:16
24-JUN-2004 08:09:39 DEFAULT_JOB_CLASS
16
TEST_SCHED_JOB_DEFINITION
JOB_USER
24-JUN-2004 09:00:16
24-JUN-2004 08:09:37 DEFAULT_JOB_CLASS
16
TEST_PROG_JOB_DEFINITION
JOB_USER
24-JUN-2004 09:00:09
24-JUN-2004 08:09:38 DEFAULT_JOB_CLASS
16
TEST_PROG_SCHED_JOB_DEFINITION
JOB_USER
24-JUN-2004 09:00:16
24-JUN-2004 08:09:39 TEST_JOB_CLASS
16
This information is also available from OEM on the Scheduler Jobs
(Scheduled) page (Administration > Jobs) shown in Figure 11.28.
Figure 11.28 –
OEM DB Control: Scheduler Jobs (Scheduled)
The
dba_scheduler_running_jobs
view is the real starting point for job monitoring as it displays a
list of the currently running jobs.
Using this view, the user is able to identify the session that
is actually executing the job, thereby giving the ability to monitor
session level information.
The
scheduled_jobs_running.sql
script uses this view to identify the currently running jobs.
The
extract function is used to retrieve the elapsed time in
seconds from the interval returned by the view.
scheduled_jobs_running.sql
set linesize 200
column owner format a15
column next_run_date format a20
select
rj.job_name,
rj.owner,
to_char(j.next_run_date, 'DD-MON-YYYY HH24:MI:SS') as
next_run_date,
extract(second from rj.elapsed_time) as elapsed_time,
rj.cpu_used,
rj.session_id,
rj.resource_consumer_group,
j.run_count
from
dba_scheduler_running_jobs rj,
dba_scheduler_jobs j
where
rj.job_name = j.job_name
order by
rj.job_name
;
The output generated from the
scheduled_jobs_running.sql
script is shown below.
SQL> @scheduled_jobs_running
JOB_NAME
OWNER
NEXT_RUN_DATE
ELAPSED_TIME
CPU_USED SESSION_ID RESOURCE_CONSUMER_GROUP
RUN_COUNT
------------------------------ ---------------
-------------------- ------------ ----------
TEST_FULL_JOB_DEFINITION
JOB_USER
24-JUN-2004
09:22:00
20.69
0
272
296
This information is also available from OEM on the Scheduler Jobs
(Running) page (Administration > Jobs) shown in Figure 11.29.
Figure 11.29 –
OEM DB Control: Scheduler Jobs (Running)
