새로운 job을 등록하는 테스트
job_test01 테이블 생성 후 1분에 1번씩 해당테이블에 데이터 insert 함
테스트용 테이블과 시퀀스, 프로시저를 생성함
SCOTT>create sequence seq_job_seq1;
Sequence created.
SCOTT>create table job_test01
2 (no number,
3 name varchar2(5));
Table created.
SCOTT>ed
Wrote file afiedt.buf
create or replace procedure insert_job_test01
is
begin
insert into scott.job_test01
values(seq_job_seq1.nextval, dbms_random.string('a',3));
end;
/
job을 등록할 프로시저를 생성
SCOTT>!vi job1.sql
begin
dbms_job.submit(:jno,
'scott.insert_job_test01;',
sysdate,
'sysdate + 1/24/60',
false);
end;
/
Procedure created.
job을 등록함
SCOTT>variable jno number
SCOTT>@job1.sql
PL/SQL procedure successfully completed.
번호는 랜덤으로 들어가는 것임
SCOTT>print jno;
JNO
----------
3
commit 이 시간부터 job이 수행됨, commit 가 안되면 수행되지 않음
SCOTT>commit;
Commit complete.
수행되고 있는 job 내역을 확인함
SCOTT>set line 200
SCOTT>col what for a50
SCOTT>select what, job, next_date, next_sec, failures, broken
2 from user_jobs
3 where what='scott.insert_job_test01;';
WHAT JOB NEXT_DATE NEXT_SEC FAILURES B
-------------------------------------------------- ---------- ------------ ---------------- ---------- -
scott.insert_job_test01; 3 23-OCT-14 18:54:30 0 N
실제 데이터가 입력되는지 확인함
SCOTT>select * from job_test01;
NO NAME
---------- -----
1 xJa
2 gMu
3 ucr
4 Rhw
등록되어 있는 job 삭제하기
SCOTT>exec dbms_job.remove(3);
PL/SQL procedure successfully completed.
삭제되고 없는 것을 확인할 수 있음
SCOTT>select what, job, next_date, next_sec, failures, broken
2 from user_jobs
3 where job=3;
no rows selected
등록되어 있는 job 수정하기
이 테스트를 위해 job을 한 번 더 등록함
SCOTT>variable jno number;
SCOTT>@job1.sql
PL/SQL procedure successfully completed.
SCOTT>print jno;
JNO
----------
4
SCOTT>commit;
Commit complete.
SCOTT>select what, job, next_date, next_sec, interval
2 from user_jobs
3 where job=4;
WHAT JOB NEXT_DATE NEXT_SEC
-------------------------------------------------- ---------- ------------ ----------------
INTERVAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
scott.insert_job_test01; 4 23-OCT-14 19:01:31
sysdate + 1/24/60
수행시간을 1분마다에서 5분 마다로 변경
SCOTT>exec dbms_job.change(:jno,'scott.insert_job_test01;',sysdate,'sysdate + 5/24/60');
PL/SQL procedure successfully completed.
SCOTT>set line 200
SCOTT>col interval for a30
SCOTT>select what, job, next_date, next_sec, interval
2 from user_jobs
3 where job=4;
WHAT JOB NEXT_DATE NEXT_SEC INTERVAL
-------------------------------------------------- ---------- ------------ ---------------- ------------------------------
scott.insert_job_test01; 4 23-OCT-14 19:02:56 sysdate + 5/24/60
Mview나 standby 서버, 원본과의 동기화 작업 등...
새로운 job을 등록하는 테스트
job_test1 이라는 테이블에 3초에 1회씩 데이터를 insert 하는 작업
작업을 위해서 create any job의 권한이 있어야 함
scott 계정에 먼저 이 권한을 주고 실행함
SCOTT>conn / as sysdba
Connected.
SYS>grant create any job to scott;
Grant succeeded.
SYS>conn scott/tiger
Connected.
SCOTT>create table job_test1
2 (no number,
3 name varchar2(5),
4 rdate date default sysdate);
Table created.
SCOTT>create sequence seq_job_test1_no;
Sequence created.
1 create or replace procedure insert_job_test
2 is
3 begin
4 insert into scott.job_test1(no, name)
5 values(seq_job_test1_no.nextval, dbms_random.string('a',2));
6 commit;
7* end;
SCOTT>/
Procedure created.
