291 lines
9.6 KiB
SQL
291 lines
9.6 KiB
SQL
--
|
|
-- $Id$
|
|
--
|
|
|
|
DEFINE thisfile = 'MAREON_JOBS.SQL'
|
|
DEFINE dbuser = '^MARX'
|
|
DEFINE custid = 'MARX'
|
|
|
|
SET ECHO ON
|
|
SET DEFINE ON
|
|
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
|
|
WHENEVER SQLERROR EXIT;
|
|
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
|
|
WHENEVER SQLERROR CONTINUE;
|
|
SPOOL &fcltlogfile
|
|
SET DEFINE OFF
|
|
|
|
|
|
------ payload begin ------
|
|
|
|
|
|
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
|
-- !! LET OP: EERST IN SYSTEM HANDMATIG RECHTEN UITDELEN: GRANT CREATE JOB TO MARX; !!
|
|
-- !! en na draaien van dit script in SYSTEM weer ontnemen: REVOKE CREATE JOB FROM MARX; !!
|
|
-- !! PS. In testomgeving 'MARX' vervangen in 'MARX_TEST' in bovenstaande 2 statements !!
|
|
-- !! PS. Indien grant permanent kan blijven, dan eenmalig: GRANT CREATE JOB TO PUBLIC; !!
|
|
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
|
|
|
-- Common procedures:
|
|
-- marx_add_job
|
|
-- marx_alter_job
|
|
-- marx_drop_job
|
|
--
|
|
-- marx_enable_job
|
|
-- marx_disable_job
|
|
-- marx_log_job
|
|
|
|
|
|
-- To enable a job/scheduled task:
|
|
CREATE OR REPLACE PROCEDURE marx_enable_job (p_job_name VARCHAR2)
|
|
AS
|
|
BEGIN
|
|
DBMS_SCHEDULER.ENABLE (p_job_name);
|
|
END;
|
|
/
|
|
|
|
-- To disable a job/scheduled task
|
|
CREATE OR REPLACE PROCEDURE marx_disable_job (p_job_name VARCHAR2)
|
|
AS
|
|
BEGIN
|
|
DBMS_SCHEDULER.DISABLE (p_job_name);
|
|
END;
|
|
/
|
|
|
|
-- To set the loglevel of a job/scheduled taks:
|
|
-- p_loglevel =
|
|
-- 0 (= no logging),
|
|
-- 1 (= a log entry is made only if the job fails)
|
|
-- 2 (= a log entry is made each time the job is run)
|
|
-- 3 (= a log entry is made every time the job runs and for every operation performed on a job, including create, enable/disable, update , stop, and drop)
|
|
-- NULL or any other number (not 0,1,2,3): (= no logging)
|
|
CREATE OR REPLACE PROCEDURE marx_log_job (p_job_name VARCHAR2, p_loglevel NUMBER)
|
|
AS
|
|
l_loglevel NUMBER;
|
|
BEGIN
|
|
SELECT decode(p_loglevel, 0, DBMS_SCHEDULER.LOGGING_OFF, 1, DBMS_SCHEDULER.LOGGING_FAILED_RUNS, 2, DBMS_SCHEDULER.LOGGING_RUNS, 3, DBMS_SCHEDULER.LOGGING_FULL, DBMS_SCHEDULER.LOGGING_OFF)
|
|
INTO l_loglevel
|
|
FROM DUAL;
|
|
DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name, 'logging_level', l_loglevel);
|
|
END;
|
|
/
|
|
|
|
|
|
-- marx_add_job: to insert a new (not existing) scheduled task
|
|
-- p_job_name: new, unique name of the job / scheduled task
|
|
-- p_proc_name: name of an existing stored (user) procedure
|
|
-- p_start_date: NULL (job will be scheduled rigth away), or a date/timestamp in the future
|
|
-- p_end_date: NULL (job without end date / timeless), or a date/timestamp in the future (with p_end_date >= p_start_date)
|
|
-- p_repeat_interval: string, e.g. see below...
|
|
-- p_comments: Your additional info / comments about the job
|
|
-- p_logging: 0 (= no logging), 1 (= a log entry is made only if the job fails), 2 (= a log entry is made each time the job is run), 3 (= a log entry is made every time the job runs and for every operation performed on a job, including create, enable/disable, update , stop, and drop)
|
|
|
|
-- Examples of p_repeat_interval (Calendaring Expressions):
|
|
-- The following examples illustrate simple repeat intervals. For simplicity, it is assumed that there is no contribution to the evaluation results by the start date.
|
|
|
|
-- Run every Friday. (All three examples are equivalent.)
|
|
-- FREQ=DAILY; BYDAY=FRI;
|
|
-- FREQ=WEEKLY; BYDAY=FRI;
|
|
-- FREQ=YEARLY; BYDAY=FRI;
|
|
|
|
-- Run every other Friday.
|
|
-- FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
|
|
|
|
-- Run on the last day of every month.
|
|
-- FREQ=MONTHLY; BYMONTHDAY=-1;
|
|
|
|
-- Run on the next to last day of every month.
|
|
-- FREQ=MONTHLY; BYMONTHDAY=-2;
|
|
|
|
-- Run on March 10th. (Both examples are equivalent)
|
|
-- FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
|
|
-- FREQ=YEARLY; BYDATE=0310;
|
|
|
|
-- Run every 10 days.
|
|
-- FREQ=DAILY; INTERVAL=10;
|
|
|
|
-- Run daily at 4, 5, and 6PM.
|
|
-- FREQ=DAILY; BYHOUR=16,17,18;
|
|
|
|
-- Run on the 15th day of every other month.
|
|
-- FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;
|
|
|
|
-- Run on the 29th day of every month.
|
|
-- FREQ=MONTHLY; BYMONTHDAY=29;
|
|
|
|
-- Run on the second Wednesday of each month.
|
|
-- FREQ=MONTHLY; BYDAY=2WED;
|
|
|
|
-- Run on the last Friday of the year.
|
|
-- FREQ=YEARLY; BYDAY=-1FRI;
|
|
|
|
-- Run every 50 hours.
|
|
-- FREQ=HOURLY; INTERVAL=50;
|
|
|
|
-- Run on the last day of every other month.
|
|
-- FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;
|
|
|
|
-- Run hourly for the first three days of every month.
|
|
-- FREQ=HOURLY; BYMONTHDAY=1,2,3;
|
|
|
|
-- Run on the last workday of every month (assuming that workdays are Monday through Friday).
|
|
-- FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1
|
|
|
|
-- Run elke 30 seconden.
|
|
-- Interval of "FREQ=SECONDLY;INTERVAL=30;"
|
|
|
|
-- A repeat interval of "FREQ=MINUTELY;BYHOUR=17;BYMINUTE=2;" will start each day at 17:02u
|
|
|
|
|
|
-- A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:
|
|
-- SUN 29-FEB-2004 17:02:00
|
|
-- SUN 29-FEB-2004 17:04:00
|
|
-- SUN 29-FEB-2004 17:50:00
|
|
-- MON 01-MAR-2004 17:02:00
|
|
-- MON 01-MAR-2004 17:04:00
|
|
-- MON 01-MAR-2004 17:50:00
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CREATE OR REPLACE PROCEDURE marx_add_job (p_job_name VARCHAR2, p_proc_name VARCHAR2, p_start_date DATE, p_end_date DATE, p_repeat_interval VARCHAR2, p_comments VARCHAR2, p_logging NUMBER)
|
|
AS
|
|
BEGIN
|
|
DBMS_SCHEDULER.CREATE_JOB (
|
|
job_name => p_job_name,
|
|
job_type => 'STORED_PROCEDURE',
|
|
job_action => p_proc_name,
|
|
start_date => p_start_date,
|
|
repeat_interval => p_repeat_interval,
|
|
end_date => p_end_date,
|
|
auto_drop => FALSE,
|
|
comments => p_comments);
|
|
marx_enable_job (p_job_name);
|
|
marx_log_job (p_job_name, p_logging);
|
|
END;
|
|
/
|
|
|
|
-- marx_alter_job: to change an existing scheduled task
|
|
-- p_job_name: existing name of the job / scheduled task
|
|
-- p_start_date: a date/timestamp in the future, NULL will have no effect/no alter
|
|
-- p_end_date: a date/timestamp in the future (with p_end_date >= p_start_date), NULL will have no effect/no alter
|
|
-- p_repeat_interval: string, e.g. see proc 'marx_add_job', NULL will have no effect/no alter
|
|
-- p_comments: Your additional info / comments about the job, NULL will have no effect/no alter
|
|
-- p_logging: 0 (= no logging), 1 (= a log entry is made only if the job fails), 2 (= a log entry is made each time the job is run), 3 (= a log entry is made every time the job runs and for every operation performed on a job, including create, enable/disable, update , stop, and drop), NULL will have no effect/no alter
|
|
|
|
CREATE OR REPLACE PROCEDURE marx_alter_job (p_job_name VARCHAR2, p_start_date DATE, p_end_date DATE, p_repeat_interval VARCHAR2, p_comments VARCHAR2, p_logging NUMBER)
|
|
AS
|
|
BEGIN
|
|
IF p_job_name IS NOT NULL
|
|
THEN
|
|
IF (NOT (p_start_date IS NULL AND p_end_date IS NULL AND p_repeat_interval IS NULL AND p_comments IS NULL AND p_logging IS NULL))
|
|
THEN
|
|
IF p_start_date IS NOT NULL
|
|
THEN
|
|
DBMS_SCHEDULER.SET_ATTRIBUTE (
|
|
name => p_job_name,
|
|
attribute => 'start_date',
|
|
value => p_start_date);
|
|
END IF;
|
|
IF p_end_date IS NOT NULL
|
|
THEN
|
|
DBMS_SCHEDULER.SET_ATTRIBUTE (
|
|
name => p_job_name,
|
|
attribute => 'end_date',
|
|
value => p_end_date);
|
|
END IF;
|
|
IF p_repeat_interval IS NOT NULL
|
|
THEN
|
|
DBMS_SCHEDULER.SET_ATTRIBUTE (
|
|
name => p_job_name,
|
|
attribute => 'repeat_interval',
|
|
value => p_repeat_interval);
|
|
END IF;
|
|
IF p_comments IS NOT NULL
|
|
THEN
|
|
DBMS_SCHEDULER.SET_ATTRIBUTE (
|
|
name => p_job_name,
|
|
attribute => 'comments',
|
|
value => p_comments);
|
|
END IF;
|
|
IF p_logging IS NOT NULL
|
|
THEN
|
|
marx_log_job (p_job_name, p_logging);
|
|
END IF;
|
|
END IF;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
-- To drop a job/scheduled task
|
|
CREATE OR REPLACE PROCEDURE marx_drop_job (p_job_name VARCHAR2)
|
|
AS
|
|
BEGIN
|
|
DBMS_SCHEDULER.DROP_JOB (p_job_name);
|
|
END;
|
|
/
|
|
|
|
|
|
CREATE OR REPLACE VIEW marx_v_user_jobs
|
|
AS
|
|
SELECT
|
|
job_name,
|
|
job_action,
|
|
to_date(to_char(start_date,'yyyy-mm-dd hh24:mi:ss')) start_date,
|
|
repeat_interval,
|
|
to_date(to_char(end_date,'yyyy-mm-dd hh24:mi:ss')) end_date,
|
|
enabled,
|
|
state,
|
|
job_priority,
|
|
run_count,
|
|
failure_count,
|
|
retry_count,
|
|
to_date(to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss')) last_start_date,
|
|
last_run_duration,
|
|
to_date(to_char(next_run_date,'yyyy-mm-dd hh24:mi:ss')) next_run_date,
|
|
max_run_duration,
|
|
logging_level,
|
|
comments
|
|
FROM user_scheduler_jobs;
|
|
|
|
CREATE OR REPLACE VIEW marx_v_user_scheduler_job_log
|
|
AS
|
|
SELECT log_id,
|
|
to_date(to_char(log_date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') log_date,
|
|
owner,
|
|
job_name,
|
|
job_subname,
|
|
error# error,
|
|
status,
|
|
to_date(to_char(req_start_date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') req_start_date,
|
|
to_date(to_char(actual_start_date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') actual_start_date,
|
|
run_duration,
|
|
instance_id,
|
|
session_id,
|
|
credential_owner,
|
|
credential_name,
|
|
slave_pid,
|
|
destination_owner,
|
|
cpu_used,
|
|
destination,
|
|
additional_info,
|
|
errors,
|
|
output
|
|
FROM user_scheduler_job_run_details;
|
|
|
|
|
|
------ payload end ------
|
|
|
|
SET DEFINE OFF
|
|
BEGIN adm.systrackscriptId ('$Id$', 1); END;
|
|
/
|
|
|
|
COMMIT;
|
|
SET ECHO OFF
|
|
SPOOL OFF
|
|
SET DEFINE ON
|
|
PROMPT Logfile of this upgrade is: &fcltlogfile
|