
ISBN
0-9744486-6-4
ISBN 13
978-0974448664 |
Library of Congress Number: 2005901259 |
288 Pages:
Perfect Bind - 9x7 |
Published 2014 |
Shelving:
Databases/Oracle |
Oracle In-Focus
Series # 13 |
|
|
Oracle Job Scheduling
Creating Robust Task Management Oracle
dbms_scheduler
Dr. Timothy S. Hall
Retail
Price $29.95
/ £19.95
Order now and
get immediate online access to the
code depot! |
Only $9.95
(30% off) |
 |
Get the
Oracle Tuning Library
Save $100 - All four books for only $118.95 |
 |
|
|
This unique book
focuses on the use of internal utilities to schedule complex
Oracle database tasks. The book compares external job
scheduling with UNIX cron and Windows AT command, and shows the
differences between the dbms_job and dbms_scheduler utilities.
This indispensable
reference shows working examples of how to schedule complex
time-based jobs, creating streams of multiple jobs, scheduling job
streams with intermediate error checking, scheduling of
event-based jobs, and much, much more.
|
* See the differences between
cron, AT, dbms_job and dbms_scheduler
* See step-by-step instructions for implementing dbms_job
and dbms_scheduler
* Learn how to create complex time-based job invocation.
* See real-world techniques for job coordination with
dbms_job and dbms_scheduler
* Get working examples from the Oracle 10g dbms_scheduler
utility
* Protect your Oracle job-stream integrity with
error-checking code
* Create e-mails alerts for aborted job steps
* Get working scripts to display upcoming job status
|
About the Author:

Dr. Timothy Hall
 |
Dr. Tim Hall is an Oracle Certified Professional
DBA (7.3, 8, 8i, 9i, 10g) and has been involved in DBA, design
and development work with Oracle databases since graduating
from college in 1994 with a PhD in Molecular Genetics.
Tim Hall has gained a wide knowledge of the Oracle software
stack and has worked as a consultant for several
multi-national companies on projects ranging from real-time
control systems to OLTP web applications. Since 2000 he has
published over 200 articles on his website
www.oracle-base.com
covering a wide range of Oracle features.
Tim has been a Karate black belt since 1993 and is a
practitioner and qualified teacher of Hatha and Ashtanga yoga.
In addition, he enjoys running and completed the 2002 London
Marathon.
|
Table of Contents:
Chapter
1 - External Oracle job scheduling
Introduction
External Oracle job scheduling
Using cron and crontab in UNIX and Linux
Using the Windows job schedulers
AT.EXE
Scheduled Tasks Wizard
SCHTASKS.EXE
Internal Oracle job Scheduling
Oracle 9i Scheduler Overview
Oracle 10g Scheduler Overview
Features comparison between dbms_job and dbms_scheduler
Migrating from dbms_job to dbms_scheduler
Summary
Chapter 2 - Configuring Oracle Job
Scheduling
Introduction
Setting up a test environment
Overview and examples of dbms_job functions
Data dictionary views related to dbms_job
dba_jobs
dba_jobs_running
Overview and examples of dbms_scheduler functions
Scheduler Privileges
Programs
Schedules
Jobs
Job Classes
Windows
Window Groups
Enabling, disabling and setting attributes of scheduler
objects
Data dictionary views related to dbms_scheduler
Summary
Chapter 3 - Time-Based Job Scheduling
Introduction
Dates
Timestamps
Intervals and Interval Literals
Calendar Syntax in Oracle 10g
Complex Date Rules for Job Execution
Summary
|
Chapter 4 - Chaining Oracle Jobs
Together
Introduction
Creating a job chain
Conditional job creation
Conditional job enabling
Conditional job runs using Oracle Advanced Queuing
Conditional job runs using a custom table solution
Implementing error checking routines
Implementing conditional job execution
Sending email notifications of job errors
Using UTL_SMTP
Using UTL_MAIL in Oracle 10g
Running OS commands and scripts from PL/SQL
Summary
Chapter 5 - Monitoring Oracle Job
Execution
Introduction
Monitoring Jobs (pre 10g)
Monitoring Jobs in 10g
Monitoring Sessions
Killing Oracle Sessions
DBMS_APPLICATION_INFO
DBMS_SYSTEM
Summary
Chapter 6 - Advanced Scheduling
Topics
Introduction
Setting Scheduler Attributes
current_open_window
default_timezone
log_history
max_job_slave_processes
Job Priorities
Scheduler Logging
Job Logs
Job Run Details
Window Logs
Purging Logs
Resource Manager
Export/Import and the Scheduler
Services and Instance Stickiness
Security
Setting Scheduler Object Attributes
Summary |
Reviews:
Author references:
Editor Review:
As
the editor for this book, I was honored to be able to
observe and recommend important areas for this definitive
book on Oracle Job Scheduling.
Oracle job scheduling is critical to
any Oracle shop and the DBA must be able to create complete
DBA job streams for backups, redo log archiving and other
database activities that require interfacing with the
external environment.
Dr. Hall notes that with Oracle10g
there is no need to use UNIX/Linux crontab files any longer,
and he also notes some of the important shortcoming of
scheduling Oracle jobs in Linux, like the fact that
dbms_scheduler will re-start a missed job (when the database
has been down) while a crontab will not.
Oracle job scheduling has always been
super-complex, as we see within Oracle’s own applications
products where a “concurrent manager” is used to interface
with the dbms_job utility to schedule complex job streams.
It’s even more confounding when vendor applications (such as
the ever-popular SAP) dispatch their own tasks independently
from Oracle.
Dr. Hall has an excellent section on
setting-up an Oracle scheduling environment and he show the
sophisticated features of Oracle scheduling including
establishing job classes and granting scheduler privileges.
But this handy book goes far beyond the
basics, and Hall shared his real-world experience in
chapters dedicated to time-based job scheduling (i.e. run
this job every second Wednesday at 3:00 PM) and he shows
working example that will save the reader many hours of
manual computations.
The real meat of “Oracle Job
Scheduling” is where Dr. Hall explains the process of
chaining jobs together with dbms_job and dbms_scheduler.
Hall covers the most complicated aspects of Oracle job
chaining including the conditional execution of Oracle
tasks, and working examples that show how to create a robust
jobstream with built-in error checking. He also includes
details for job failure notification and shows how to alert
the staff using the utl_smtp protocol package.
But Hall does not stop there. He goes
far beyond the Oracle documentation and shows working
examples of using the new Oracle job monitoring features and
the reader can see exactly how to use the Oracle job
monitoring functions to monitor and kill scheduled Oracle
tasks.
Halls opens-up his personal experience
in chapter 6, advanced scheduling Topics, and show
never-before seen techniques for setting scheduler object
attributes and the concept of “Instance Stickiness”.
Dr. Hall has a long history of explain
complex Oracle concepts with clear, easy-to-understand
examples, and this book is no exception. Hall is provided
an online code depot, replete with working examples of
complex job streams, an invaluable time-saver for the Oracle
professional.
I also like the price, as I’m getting
weary of shelling-out $50 for mediocre Oracle books and it’s
refreshing to see that Rampant can offer a high-quality
288-page Oracle book for only pennies on the dollar.
My copy of Oracle Job Scheduling is
already filling-up with dog-eared pages and post-it notes, a
sure sign that other Oracle professionals are going to find
this book to be an addition to their Oracle libraries that
they will actually use everyday.
Index Topics:
A
action_name
active_plan.sql
Add Scheduled Task
add_months
add_window_group_member
Advanced Queuing
ALL
all_%
ALTER
AQ
aq_administrator_role
aq_user_role
argument_name
argument_position
argument_type
argument_value
AT
auto_drop
automated_email_alert.sql
automated_email_alert_g.sql
B
BATCH
broken
byday
byhour
byminute
bymonth
bymonthday
bysecond
byweekno
byyearday
C
calendar_string
cjq
close_window
Cluster Ready Services
column_comments.sql
comments
Conditional job creation
Conditional job enabling
Conditional job runs
consumer_group_usage.sql
consumer_groups.sql
copy_job
CREATE ANY JOB
CREATE JOB
create_g_job_failure.sql
create_g_job_max_runs.sql
create_consumer_groups.sql
create_job
create_job_class
create_job_classes.sql
create_job_failure.sql
create_plan_directive
create_queue
create_queue_table
create_schedule
create_window
create_window_group
cron
CRON
cron.allow
cron.deny
crond
crontab
CRS
current_date
current_open_window
current_timestamp
D
Data Definition Language
Database Configuration Assistant
Database Control
datapump
DATE
DAY TO SECOND
day_plan.sql
day_precision
dba_%
dba_jobs
dba_jobs_running
dba_rsrc_%
dba_rsrc_consumer_groups
dba_rsrc_plan_directives
dba_scheduler_%
dba_scheduler_job_args
dba_scheduler_job_classes
dba_scheduler_job_log
dba_scheduler_job_run_details
dba_scheduler_jobs
dba_scheduler_program_args
dba_scheduler_programs
dba_scheduler_running_jobs
dba_scheduler_schedules
dba_scheduler_window_details
dba_scheduler_window_groups
dba_scheduler_window_log
dba_scheduler_windows
dba_scheduler_wingroup_members
dbca
DBCA
dbms_application_info
dbms_aq
dbms_aqadm
dbms_java
dbms_job
dbms_jobs
dbms_monitor
dbms_pipe
dbms_resource_manager
dbms_resource_manager_privs
dbms_scheduler
dbms_service
dbms_sheduler
dbms_system
dbtimezone
DDL
DEFAULT_JOB_CLASS
default_timezone
DEFAULT_TIMEZONE
default_value
define_metadata_argument
define_program_argument
delete_consumer_group
delete_plan
delete_plans.sql
disabled_jobs.sql
DML
drop_job
drop_job_class
drop_job_classes.sql
drop_program
drop_program_argument
drop_schedule
drop_window
drop_window_group
dump
duration
|
E
EM
enable
enabled
end_date
Enterprise Manager
evaluate_calendar_string
exception_job_proc_.sql
EXECUTABLE
EXECUTE
EXECUTE ANY CLASS
EXECUTE ANY PROGRAM
exp
expdp
extract
F
force
fraction_second_precision
Freq
from_tz
G
generate_job_name
get_scheduler_attribute
Global Services Daemon
grant_permission
grant_switch_consumer_group
group_name
GSD
I
imp
impdp
instance
instance_stickiness
interval
Intervals
isubmit
J
job
job coordinator
job run history
job slave
job table
job$
job_action
job_and_window_log
job_chain
job_chain_aq.sql
job_chain_aq_jobs.sql
job_chain_aq_query.sql
job_chain_aq_setup.sql
job_chain_create.sql
job_chain_create_job.sql
job_chain_custom_jobs.sql
job_chain_enable_jobs.sql
job_chain_locks
job_chain_locks_query.sql
job_chain_query.sql
job_chain_queue_tab
job_chain_table.sql
job_class
job_class_name
job_class_resource_privileges.sql
job_classes.sql
job_failures.sql
job_log
job_log_lifecycle.sql
job_name
job_priority
job_queue_processes
job_queue_query.sql
job_run_details
job_run_details.sql
job_run_details_api
job_run_details_api.sql
job_run_details_schema.sql
job_run_details_template_job.sql
job_run_details_template_proc.sql
job_run_failures_g.sql
job_run_history.sql
job_type
job_user
job_weight
jobs_g.sql
jobs_running.sql
jobs_running_g.sql
JServer
Julian dates
L
last_day
localtimestamp
log_history
logging_full
logging_level
logging_off
logging_runs
M
MANAGE SCHEDULER
max_failures
max_job_slave_processes
max_runs
metadata
module_name
my_schedule_function.sql
myinst
N
name
new_job
new_time
next_date
next_day
next_run_date
night_plan.sql
no_parse
number_of_arguments
numtodsinterval
numtoyminterval
O
OEM GUI
old_job
OLTP
oltp_job_class
OLTP_SERVICE
open_window
out_argument
P
p_mail_host
PLSQL_BLOCK
program_action
program_args.sql
program_name
program_type
programs.sql
R
RAC
raise_application_error
Real Application Cluster
Red Hat Linux
remove_window_group_member
repeat_interval |
resource_consumer_group
resource_manager_plan
resource_plan
resource_plan_directives.sql
restartable
return_after_date
round
run_job
running_job_consumer_groups.sql
S
schedule_limit
schedule_name
Scheduled Tasks Wizard
scheduled_job_details.sql
scheduled_jobs.sql
scheduled_jobs_running.sql
SCHEDULER_ADMIN
SCHTASKS
send_mail.sql
service
session_locks.sql
session_open_cursors.sql
session_waits.sql
session_waits_g.sql
sessions.sql
sessiontimezone
set_attribute
set_attribute_null
set_scheduler_attribute
show_scheduler_attribute.sql
smtp_out_server
SQL*Plus
sqlfile
srcvtl
srvctl
start_date
start_queue
stop_job
stop_on_window_close
STORED_PROCEDURE
switch_group
switch_time
sys_extract_utc
sysdate
systimestamp
T
table_comments.sql
test_calendar_string.sql
test_resource_manager_job_.sql
test_stored_procedure_prog
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE
TKPROF
to_char
to_date
to_dsinterval
to_timestamp
to_timestamp_tz
to_yminterval
trcsess
trunc
tst
U
use_current_session
user_%
user_export
user_jobs.sql
utl_mail
utl_smtp
V
v$lock
v$open_cursors
v$process
v$rsrc_consumer_group
v$rsrc_plan
v$session
v$session_longops
v$session_wait_history
v$session_waits
v$sesstat
v$sql
v$statname
W
what
which_log
window_groups.sql
window_list
window_log
window_logs.sql
window_name
window_priority
windows.sql
write_to_alert_log.sql
Y
year_precision |
Errata:
|