Admin 2. 14장 Automating Tasks with the Scheduler

업무 자동화.

 plsql로 작동 가능한 것만. 대부분 다 됨...ㅋㅋ

 

 

 10초 마다 insert 되는 job scheduler 만들기.

 argument 사용

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."MYJOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
   insert into x values(xs.nextval,''sqlplus test'',systimestamp);
   commit;
end;',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=10',
start_date => systimestamp at time zone '+9:00',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;

 

 

 

에러남

ORA-06550: line ORA-06550: line 2, column 16: PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here ORA-06550: line 2, column 4: PL/SQL: SQL Statement ignored , column :

 

 

sys 입장이기 때문에 테이블명이나 시퀀스 명앞에 hr 붙여줘야 함.

 

BEGIN
sys.dbms_scheduler.disable( '"SYS"."MYJOB"' );
sys.dbms_scheduler.set_attribute( name => '"SYS"."MYJOB"', attribute => 'job_action', value => 'begin
   insert into hr.x values(hr.xs.nextval,''myJob done'',systimestamp);
   commit;
end;');
sys.dbms_scheduler.enable( '"SYS"."MYJOB"' );
END;

 

 

 

SQL> select * from x;

        ID DATA                           TIME
---------- ------------------------------ ------------------------------
         1 sqlplus test                   02-JUL-09 03.44.21.475493 PM
         2 myJob done                     02-JUL-09 03.49.14.350463 PM
         3 myJob done                     02-JUL-09 03.49.24.123067 PM
         4 myJob done                     02-JUL-09 03.49.34.129646 PM
         5 myJob done                     02-JUL-09 03.49.44.119253 PM
         6 myJob done                     02-JUL-09 03.49.54.116829 PM
         7 myJob done                     02-JUL-09 03.50.04.119862 PM
         8 myJob done                     02-JUL-09 03.50.14.122385 PM

8 rows selected.

 

 

 

 

 

스케줄러 만들기.

BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=SECONDLY;INTERVAL=5',
start_date => systimestamp at time zone '+9:00',
schedule_name => '"SYS"."MYSCHEDULER"');
END;

 

 

job 끄기

BEGIN
sys.dbms_scheduler.disable( '"SYS"."MYJOB"' );
END;

 

job 켜기

BEGIN
sys.dbms_scheduler.enable( '"SYS"."MYJOB"' );
END;

 

 

--Monitoring a Job

SQL> @fv
Enter value for key: dba%sche%job
old   2: where view_name like upper('%&KEY%')
new   2: where view_name like upper('%dba%sche%job%')

VIEW_NAME
------------------------------
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAILS
DBA_SCHEDULER_RUNNING_JOBS

6 rows selected.

 

 

 

-Chain 만들고 Chain rules

 

BEGIN
sys.dbms_scheduler.create_chain(
chain_name => '"SYS"."MYCHAIN"');
sys.dbms_scheduler.define_chain_step(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's1',
program_name => 'xx');
sys.dbms_scheduler.alter_chain(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's1',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's1',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_step(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's2',
program_name => 'xx2');
sys.dbms_scheduler.alter_chain(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's2',
attribute => 'pause',
value => FALSE);
sys.dbms_scheduler.alter_chain(
chain_name => '"SYS"."MYCHAIN"',
step_name => 's2',
attribute => 'skip',
value => FALSE);
sys.dbms_scheduler.define_chain_rule(
chain_name => '"SYS"."MYCHAIN"',
condition => 'true',
action => 'start s1');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"SYS"."MYCHAIN"',
condition => 's1 completed',
action => 'start s2');
sys.dbms_scheduler.define_chain_rule(
chain_name => '"SYS"."MYCHAIN"',
condition => 's2 completed',
action => 'end');
sys.dbms_scheduler.enable('"SYS"."MYCHAIN"');
END;

 

job에 chain 연결

 

BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."MYJOB"', attribute => 'program_name');
sys.dbms_scheduler.set_attribute( name => '"SYS"."MYJOB"', attribute => 'job_type', value => 'CHAIN');
sys.dbms_scheduler.set_attribute( name => '"SYS"."MYJOB"', attribute => 'job_action', value => 'SYS.MYCHAIN');
sys.dbms_scheduler.set_attribute( name => '"SYS"."MYJOB"', attribute => 'number_of_arguments', value => 0);
sys.dbms_scheduler.enable( '"SYS"."MYJOB"' );
END;

 

 

view chain

SQL> select step_name,chain_name from dba_scheduler_chain_steps where owner='SYS' order by chain_name;

STEP_NAME                      CHAIN_NAME
------------------------------ ------------------------------
S2                             MYCHAIN
S1                             MYCHAIN

 

 

- window  기간 바꾸기.

 

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'SYS.WEEKNIGHT_WINDOW',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.WEEKNIGHT_WINDOW',
attribute=>'RESOURCE_PLAN',
value=>'SYSTEM_PLAN');
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.WEEKNIGHT_WINDOW',
attribute=>'DURATION',
value=>numtodsinterval(240, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.WEEKNIGHT_WINDOW',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=7;BYMINUTE=0;BYSECOND=0');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'SYS.WEEKNIGHT_WINDOW');
END;