Managing Job Queues

☞ DBMS_JOB 패키지 ● JOB QUEUE의 JOB을 스케줄링하고 관리 ● DBMS_JOB 패키지 프로시저 설명 SUBMIT job을 job queue에 submit REMOVE job을 job queue에서 remove CHANGE job queue의 job을 변경(작업실행시간, 작업실행간격) WHAT 지정된 job에 대한 설명 변경 NEXT_DATE job에 대한 다음 실행시간 변경 INTERVAL job에 대한 실행간격 변경 BROKEN job을 중단 RUN 지정된 job을 강제로 실행 ☞ Submitting a job to the job queue ▶ DBMS_JOB 패키지의 SUBMIT 프로시저 사용 ▶ 구문형식 o. DBMS_JOB.SUBMIT(job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT FALSE) ▶ SUBMIT 프로시저의 매개변수 매개변수 설명 job 작성한 job에 할당된 식별자 job number를 사용하여 job을 변경, 제거 what 실행할 PL/SQL 코드 next_date job이 실행될 다음 날짜(default : SYSDATE) interval job을 실행할 다음 시기를 계산하는 data 함수(default : NULL) null이면 job이 성공적으로 완료된 후 queue에서 job을 삭제 no_parse flag(defalt : FALSE) no_parse가 FALSE이면 job과 연관된 table들을 생성하기 미리 생성한 후 job을 job queue에 submit, No_parse가 ture이면 job을 job queue에 submit한 후 job과 연관된 table들을 생성가능 ▶ 예] job_test table에 현재 시각을 1분마다 insert하는 job을 job queue에 submit하는 예 sql> VARIABLE jobno number; sql> begin 2> DBMS_JOB.SUBMIT ( :jobno, 3> 'INSERT INTO job_test VALUES(seq_test_id.nextval,SYSDATE);COMMIT;' 4> SYSDATE, 'SYSDATE+1/1440'); 5> end; 6> / sql> print jobno JOBNO ----------- 21 ♣ JOB Environment ▶job queue에 job을 보내거나, job의 정의를 변경시 Oracle7은 다음과 같은 환경특징을 기록 현재 사용자 JOB을 보내거나 변경한 사용자 현재 Schema MAC 권한(해당하는 경우) Oracle은 또한 다음과 같은 NLS(National Language Support) parameter를 기록 NLS_LANGUAGE NLS_TERRITORY NLS_CURRENCY NLS_ISO_CURRENCY NLS_NUMERIC_CHARACTERS NLS_DATE_FORMAT NLS_DATE_LANGUAGE NLS_SORT ▶ Oracle은 job이 실행되는 매시간 이 환경특성을 재저장한다. ▶ 작업환경 변경 o. DBMS_SEQ package와 alter session 명령어를 사용하여 작업환경 변경

♣ JOB 및 EXPORT/IMPORT

▶job은 export/import 가능 o. 하나의 database에서 job을 정의한다면 또 다른 database에 job을 전송 가능 ▶job의 export/import시 job 번호, 환경, 정의는 변경되지 않음 ♨import시 job의 번호가 database에 이미 존재하는 job은 import할 수 없고 이때는 database에 새로운 job을 submit

♣ JOB Owner

▶job queue에 Job을 보낸 사람 ▶job의 소유자는 job의 변경, 강제 실행, queue로부터 job의 삭제 가능 ▶현재 자신이 만든 job을 볼려면 SQL>SELECT * FROM user_jobs;

♣ 작업 번호

▶job queue안에 있는 job을 식별자 ▶SYS.JOBSEQ sequence로부터 자동적으로 생성 ▶할당된 job번호는 변경되지 않음(job이 export/import시 job 번호는 유지) SQL>SELECT job FROM user_jobs;

♣ 작업 정의

▶SUBMIT procudure의 WHAT 매개변수에 기술된 PL/SQL code ♨job정의시 문자열 주위는 2개의 single quotation(')을 이용하고, job정의의 마지막 은 (;)을 포함한다. ▶작업 정의의 매개변수 매개변수 모드 설명 job IN 현재 작업번호 next_date IN/OUT 다음 작업 실행 날짜(default SYSDATE) broken IN/OUT 작업상태 ▶작업정의 예 'myproc(''10-JAN-82'', next_date, broken);' 'scott.emppackage.give_raise(''JFEE'', 3000.00);' 'dbms_job.remove(job);'

♣ 작업 실행간격

▶INTERVAL 날짜함수는 job이 실행되기 직전의 값을 계산하며, job이 성공적으로 완료 되면 INTERVAL로부터 계산된 날짜가 새 NEXT_DATE가 된다. INTERVAL 날짜함수의 결과가 NULL이고, job이 성공적으로 완료되면 queue에서 job을 Remove ▶ job이 정해진 간격으로 정기적으로 실행할 경우 o. INTERVAL 매개변수를 'SYSDATE+7'과 같은 시간 표현 o. 예] 월요일에 실행 간격을 'SYSDATE + 7'로 지정하면 network failure로 목요일까지 실행되지 않았 다면 'SYSDATE+7'에 의해 월요일이 아닌 목요일에 실행 ▶작업 실행간격의 일반적인 날짜표현식 날짜 표현식 계산 'SYSDATE + 7' 마지막 실행으로부터 7일마다 실행 'SYSDATE + 1/48' 30분마다 실행 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' 월요일 오후 3시마다 실행 'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' 매분기의 첫째 목요일에 실행 ♨NEXT_DATE나 INTERVAL을 지정시 data literal과 문자열은 작은 따옴표(')로 묶어야함

♣ 데이타베이스 링크 및 작업

▶데이타베이스 링크를 사용하여 job을 submit하는 경우 링크는 사용자명과 암호를 포함해야 함. anonymous 데이카베이스 링크는 안됨

☞ ☞How Job Execute

▶SNP process가 job을 실행, 작업 실행을 위해 프로세스는 작업을 실행할 세션을 생성 ▶job 실행시 작업은 보낸환경과 동일한 환경에서 소유자의 기본권한을 사용하여 실행 ▶DBMS_JOB.RUN procedure로 강제로 실행하면 사용자가 프로세스가 작업을 실행 가능

♣ Job Queue Locks

▶Oracle 7은 job queue lock를 사용하여 job이 한번에 하나의 session에 실행하도록 함. job이 실행될 때 그 session에 job queue lock를 얻는다. ▶ JQ lock 정보 해석 o. Server Manager Lock Monitor나 data dictionary내의 locking view를 이용하여 session이 보유한 locks에 대한 정보를 알 수 있다. ▶session 식별자, lock type, lock 식별자 SVRMGR> SELECT sid, type, id1, id2 FROM v$lock WHERE type='JQ'; SID TY ID1 ID2 ---- --- ----- ----- 12 JQ 0 13 ID1 lock identifier는 JQ Locks에서 항상 0이다. ID2 lock identifier는 session이 실행되는 job의 번호

♣ 작업 실행 오류

▶Queue 안에 있는 Job의 성공적인 실행할 수 없는 경우 - job을 실행하기 위한 SNP background process가 없을 때 - network나 instance failure - job 실행시 exception ▶Job이 실패하면 실패에 대한 정보는 trace file과 Alert log내에 기록된다. Oracle7은 에러 메시지(ORA-12012)에 실패된 job의 번호를 포함 Tue Oct 07 22:41:51 1997 Errors in file C:ORAWIN95RDBMS73traceorclSNP0.TRC: ORA-12012: error on auto execute of job 1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated ORA-06512: at line 1 ▶Job Failure와 실행시간 o. Job을 실행하기 위해 시도하는 동안 Job이 error를 return하면 재시도 함 첫 번째 시도는 1분후, 두 번째는 2분후, 세 번째는 4분후, 네 번째 8분후 등등 .... Job 재시도를 계속 만약 Job이 16번째 실패한다면 job을 broken을 True로하고 더 이상 Job을 실행을 시도하지 않는다.

☞ Removing a Job from the Job Queue

▶DBMS_JOB package 내의 REMOVE procedure를 이용 DBMS_JOB.REMOVE( job IN BINARY_INTEGER ) 다음 문장은 Job Queue로부터 job번호 13을 제거하는 것이다. SQL>execute DBMS_JOB.REMOVE(13); ▶제한사항 o. Job Queue로부터 현재 실행중인 Job을 제거 가능하나 Job은 중단되지 않고 현재의 실행을 완료 o. user 소유의 Job들만 제거 가능 만약 user 소유가 아닌 Job을 제거하려고 시도하면 에러발생(ORA-23421) ERROR at line 1: ORA-23421: job number 1 is not a job in the job queue ORA-06512: at "SYS.DBMS_SYS_ERROR", line 83 ORA-06512: at "SYS.DBMS_JOB", line 91 ORA-06512: at "SYS.DBMS_JOB", line 152 ORA-06512: at line 1

☞ Altering a Job

▶CHANGE, WHAT, NEXT_DATE,INTERVAL 프로시저를 사용하여 Job Queue의 Job을 변경 ▶제한사항 o. 자신이 소유한 작업만 변경가능.

♣ CHANGE 구문

▶DBMS_JOB.CHANGE procedure를 호출하여 Job에 할당된 사용자 정의 매개변수를 변경 가능 DBMS_JOB.CHANGE( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2) ▶change 구문에서 WHAT, NEXT_DATE, INTERVAL에 NULL이면 현재의 값이 유지 ▶change 프로시저에 WHAT 매개변수를 사용하여 작업 정의 변경하면 ORACLE7에 현재 환경을 기록하고 이것이 작업의 새로운 환경이다. 다음 문장은 Job Queue로부터 job번호 21인 작업을 1분마다 실행 sql>DBMS_JOB.CHANGE(21,NULL,NULL,'SYSDATE+1/2880');

♣ NEXT_DATE 구문

▶DBMS_JOB.NEXT_DATE procedure를 호출하여 Job을 실행할 다음 날짜 변경 DBMS_JOB.NEXT_DATE( job IN BINARY_INTEGER, next_date IN DATE) ▶예제 SQL> execute dbms_job.next_date(21,sysdate);

♣ INTERVAL 구문

▶DBMS_JOB.INTERVAL procedure를 호출하여 Job을 실행 간격을 변경 DBMS_JOB.INTERVAL( job IN BINARY_INTEGER, interval IN VARCHAR2) ▶예 SQL>execute dbms_job.interval(21,'sysdate+1/1440');

☞ Broken Jobs

▶job이 중단되는 경우 o. job의 실패후 16번 재시도한 후 작업을 성공적으로 실행하는데 실패한 경우 o. DBMS_JOB.BROKEN 프로시저를 사용하여 작업을 중단된 것으로 표시한 경우 ▶DBMS_JOB.BROKEN 프로시저 SVRMGR>DBMS_JOB.BROKEN(job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE); ▶job의 중단 여부 확인 SQL> select job,broken from user_jobs; broken이 True이면 작업이 중단된 것 임 ▶예] job 21를 중단되지 않은 것으로 표시하고 다음 실행날짜를 다음 월요일 설정 o. DBMS_JOB.BROKEN(21,FALSE,NEXT_DAY(SYSDATE,'MONDAY')); ▶ 중단된 작업은 DBMS_JOB.RUN 프로시저를 이용하여 재실행 가능 ▶제한사항 o. 자신이 소유한 작업만 중단가능

♣ 중단된 job 실행

▶job이 16번 실패하면 작업은 중단됨 ▶job의 재실행 방법 o. DBMS_JOB.RUN 을 호출하여 강제로 job 실행 o. DBMS_JOB.BROKEN을 호출하여 job의 broken을 False로 하면 Oracle이 job을 실행하도록 대기 ▶DBMS_JOB.RUN 프로시저로 job을 재실행하면 job이 성공적으로 완료되면 job을 broken을 false로하고 실패한 실행횟수를 재설정

☞ Forcing a job to Execute

▶job을 직접 실행하고자 할때 ▶DBMS_JOB.RUN 프로시저 SQL> DBMS_JOB.RUN(job IN BINARY_INTEGER) ▶예] SQL> DBMS_JOB.RUN(12) ▶제한사항 o. 자신이 소유한 job만 실행가능 o. Run 프로시저는 암시적 commit이므로 run을 사용한 경우 실행을 Rollback할 수 없음

☞ Terminating a Job

▶job 종료방법 1. job을 재실행하지 않도록 작업울 중단된것으로 표시 SQL>execute dbms_job.broken(12,True); 2. v$session으로 작업 섹션 id를 확인한 후 alter system을 사용하여 세션을 종료 Svrmgr>select sid,serial#,username# from v$session; svrmgr>alter system kill session '7,14'; 여기서 sid는 7, serial number는 14

 자료 출처 : http://www.koug.net/xe/3396

권한 부여 하는 것은 sys계정에서 grant execute on dbms_job to users;

[출처] http://blog.naver.com/kiyoun82?Redirect=Log&logNo=110076992957