목적: DBMS_SCHEDULER를 사용한 job 설정 및 구동

참고 자료
Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)
Oracle® Database Administrator’s Guide 11g Release 2 (11.2)

DBMS_JOB: Oracle 모든 버전
DBMS_SCHEDULER: Oracle 10g 이상

1. JOB_QUEUE_PROCESSES(환경 확인 및 수정)
　JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, … J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package.

　Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

SELECT * FROM v$parameter WHERE name LIKE '%job%';

Value가 없는 경우

ALTER SYSTEM SET job_queue_processes= 10;

2. CREATE_JOB Procedure(JOB 생성)
　This procedure creates a single job (regular or lightweight). If you create the job enabled by setting the enabled attribute to TRUE, the Scheduler automatically runs the job according to its schedule. If you create the job disabled, the job does not run until you enable it with the SET_ATTRIBUTE Procedure.

　The procedure is overloaded. The different functionality of each form of syntax is presented along with the syntax declaration.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
	job_name	=>  'JOB01',
	job_type	=>  'STORED_PROCEDURE',
	job_action	=>  '${OWNER_NAME}.${PROCEDUR_NAME}',
	start_date	=>  systimestamp,
	repeat_interval	=>  'FREQ=MINUTELY;INTERVAL=10'),
	auto_drop	=>   FALSE;
END;

3. SET_ATTRIBUTE Procedure(JOB 속성 수정)
　This procedure changes an attribute of an object. It is overloaded to accept values of the following types: VARCHAR2, TIMESTAMP WITH TIMEZONE, BOOLEAN, PLS_INTEGER, and INTERVAL DAY TO SECOND. To set an attribute to NULL, the SET_ATTRIBUTE_NULL procedure should be used. What attributes can be set depends on the object being altered. With the exception of the object name, all object attributes can be changed.

　SET_ATTRIBUTE is overloaded.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
	name        => 'JOB01',
	attribute    => 'logging_level',
	value        => DBMS_SCHEDULER.LOGGING_RUNS);
END;

4. ENABLE Procedure(JOB 활성화)
　This procedure enables a program, job, chain, window, or window group. When an object is enabled, the enabled flag is set to TRUE. By default, jobs, chains, and programs are created disabled and windows and window groups are created enabled.

　If a job was disabled and you enable it, the Scheduler begins to automatically run the job according to its schedule.

　Validity checks are performed before enabling an object. If the check fails, the object is not enabled, and an appropriate error is returned. This procedure does not return an error if the object was already enabled.

EXEC DBMS_SCHEDULER.ENABLE('JOB1');

5. RUN_JOB Procedure(JOB 실행)
　This procedure runs a job immediately.

　It is not necessary to call RUN_JOB to run a job according to its schedule. Provided that that job is enabled, the Scheduler runs it automatically. Use RUN_JOB to run a job outside of its normal schedule.

EXEC DBMS_SCHEDULER.RUN_JOB('JOB1');

6. DBA_SCHEDULER_JOBS(JOB 확인)
　DBA_SCHEDULER_JOBS displays information about all Scheduler jobs in the database. Its columns are the same as those in ALL_SCHEDULER_JOBS.

SELECT * FROM DBA_SCHEDULER_JOBS

SELECT * FROM USER_SCHEDULER_JOBS

‘=

7. USER_SCHEDULER_JOB_LOG(JOB 로그 확인)
　USER_SCHEDULER_JOB_LOG displays log information for the Scheduler jobs owned by the current user. Its columns are the same as those in ALL_SCHEDULER_JOB_LOG.

SELECT * FROM USER_SCHEDULER_JOB_LOG;

Object Attributes
　’참고 자료’ Table 114-3 JOB Object Type Attributes를 참조.

repeat_interval

frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )
predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | 
   "HOURLY" | "MINUTELY" | "SECONDLY"
user_defined_frequency = named_schedule

interval_clause = "INTERVAL" "=" intervalnum
   intervalnum = 1 through 99
bymonth_clause = "BYMONTH" "=" monthlist
   monthlist = monthday ( "," monthday)*
   month = numeric_month | char_month
   numeric_month = 1 | 2 | 3 ...  12
   char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" |
   "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC"
byweekno_clause = "BYWEEKNO" "=" weeknumber_list
   weeknumber_list = weeknumber ( "," weeknumber)*
   weeknumber = [minus] weekno
   weekno = 1 through 53
byyearday_clause = "BYYEARDAY" "=" yearday_list
   yearday_list = yearday ( "," yearday)*
   yearday = [minus] yeardaynum
   yeardaynum = 1 through 366
bydate_clause = "BYDATE" "=" date_list
   date_list = date ( "," date)*
   date = [YYYY]MMDD [ offset | span ]
bymonthday_clause = "BYMONTHDAY" "=" monthday_list
   monthday_list = monthday ( "," monthday)*
   monthday = [minus] monthdaynum
   monthdaynum = 1 through 31
byday_clause = "BYDAY" "=" byday_list
   byday_list = byday ( "," byday)*
   byday = [weekdaynum] day
   weekdaynum = [minus] daynum
   daynum = 1 through 53 /* if frequency is yearly */
   daynum = 1 through 5  /* if frequency is monthly */
   day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"
byhour_clause = "BYHOUR" "=" hour_list
   hour_list = hour ( "," hour)*
   hour = 0 through 23
byminute_clause = "BYMINUTE" "=" minute_list
   minute_list = minute ( "," minute)*
   minute = 0 through 59
bysecond_clause = "BYSECOND" "=" second_list
   second_list = second ( "," second)*
   second = 0 through 59
bysetpos_clause = "BYSETPOS" "=" setpos_list
   setpos_list = setpos ("," setpos)*
   setpos = [minus] setpos_num
   setpos_num = 1 through 9999

include_clause = "INCLUDE" "=" schedule_list
exclude_clause = "EXCLUDE" "=" schedule_list
intersect_clause = "INTERSECT" "=" schedule_list
schedule_list = schedule_clause ("," schedule_clause)*
schedule_clause = named_schedule [ offset ]
named_schedule = [schema "."] schedule
periods_clause = "PERIODS" "=" periodnum
byperiod_clause = "BYPERIOD" "=" period_list
period_list = periodnum ("," periodnum)*
periodnum = 1 through 100

offset = ("+" | "-") ["OFFSET:"] duration_val
span = ("+" | "-" | "^") "SPAN:" duration_val
duration_val = dur-weeks | dur_days
dur_weeks = numofweeks "W"
dur_days = numofdays "D"
numofweeks = 1 through 53
numofdays = 1 through 376
minus = "-"