2009/03/12 09:30 in Database/Oracle
[출처] http://ntalbs.tistory.com/3
예전에 database.sarang.net의 오라클 게시판에 DBMS_JOB을 이용해 원하는 작업을 08시, 14시, 20시에 실행시키는 방법을 묻는 질문이 올라왔다. 작업 간격이 규칙적일 때는 문제가 간단하지만 원하는 시간 간격이 불규칙하므로 그냥 JOB을 세 개 등록하면 어떻겠냐고 답했더니 이번에는 이 작업을 평일에만 실행시키게 하고 싶다고 했다. 즉 평일 08시, 14시, 20시에 작업이 실행되도록 하고 싶다는 것이었다. 그럼 문제를 풀기 전에 DBMS_JOB.SUBMIT 프로시저를 살펴보자. DBMS_JOB을 이용해 JOB을 등록시키려면 SUBMIT 프로시저를 사용해야 한다. 파라미터 중 next_date와 interval를 통해 작업 실행 시각을 조절할 수 있다.
1.DBMS_JOB.SUBMIT (
2. job OUT BINARY_INTEGER,
3. what IN VARCHAR2,
4. next_date IN DATE DEFAULT sysdate, -- 실행할 시각
5. interval IN VARCHAR2 DEFAULT 'null', -- 다음 실행될 시점을 계산할 수식
6. no_parse IN BOOLEAN DEFAULT FALSE,
7. instance IN BINARY_INTEGER DEFAULT any_instance,
8. force IN BOOLEAN DEFAULT FALSE);
next_date의 디폴트 값은 sysdate이므로 값을 주지 않으면 등록 즉시 실행된다. 그 다음 실행 시각은 JOB이 실행되기 직전 interval에 지정된 수식을 이용해 계산한다. (interval이 NULL일 경우는 작업이 한 번만 실행된다.) 파라미터 이름이 interval이기는 하지만 실제 의미는 interval이 아니라 "다음 실행될 시점을 계산할 수식"인 것이다. 만약 어떤 작업을 1시간에 1번씩 실행시키고 싶다면 interval을 'sysdate+1/24'로 주면 된다. 작업을 시작하기 전에 sysdate+1/24를 통해 다음 실행할 시각을 구하면 작업 시작 시간으로부터 1시간 후인 시각이 된다. 다음 작업 시작 시각을 알고 싶으면 ALL_JOBS의 NEXT_DATE 컬럼을 조회해 확인할 수 있다.
interval | 작업 주기 |
'sysdate + 1/24' | 1시간에 1번 |
'sysdate + 1' | 1일에 1번 |
'sysdate + 7' | 7일(일주일)에 한번 |
그런데 위와 같이 하면 작업 주기만 지정한 것일 뿐이다. 특정 시각에 JOB을 실행시키려면 다음과 같이 하면 된다.
interval | 작업 시각 |
'trunc(sysdate) + 1 + 1/24' | 매일 01시에 작업 실행 |
'trunc(sysdate, ''D'') + 7' | 매주 일요일 00시에 작업 실행 |
interval 파라미터는 문자열로 주어야 하므로 수식 내에 따옴표(single quotation)이 있으면 따옴표를 두 개 써줘야 하는 것에 유의해야 한다. interval 수식이 복잡할 때는 확인하기가 어려울 수 있는데, 그럴 때는 interval 수식으로 직접 쿼리를 작성해 확인할 수 있다.
1.select trunc(sysdate, 'D') + 7 from dual;
이제 다음과 같이 다양한경우에 대한 interval을 구해보자.
- 매주 토요일 새벽 1시에 실행
- 매월 1일 새벽 0시에 실행
- 매월 말일 밤 11시에 실행
- 평일(월화수목금) 밤 10시에 실행
- 불규칙한 시각, 8시, 14시, 20시에 한번씩
1....
2.next_date=>to_date('2007102701','YYYYMMDDHH24'),
3.interval=>'sysdate + 7'
4....
월초나 월말의 경우는 add_months나 last_day를 이용해 구하면 된다.
01.-- 매월1일 새벽 0시 작업 실행
02....
03.next_date=>add_months(trunc(sysdate,'MM'),1),
04.interval=>'add_months(trunc(sysdate,''MM''),1)'
05.
06.
07.-- 매월 말일 밤 11시에 작업 실행
08....
09.next_date=>last_day(trunc(sysdate))+23/24,
10.interval=>'last_day(trunc(sysdate)+1)+23/24' -- 말일+1일은 다음달 1일
11....
평일만 실행되도록 하기 위해서는 interval이 좀더 복잡해진다.
1....
2.interval=>'trunc(sysdate) + decode(to_char(sysdate,''D''), ''6'', 3, ''7'', 2, 1) + 22/24'
3....
요일을 구한 다음 토요일(to_char(sysdate,'D')='6')에는 작업 후 3일 후에, 일요일(to_char(sysdate,'D')='7')에는 작업 후 2일 후에, 평일에는 자업 후 1일 후에 작업이 다시 시작되도록 하면 된다. 이를 위해 DECODE 함수를 활용했다.
불규칙한 시간 간격일 경우에도 작업 시각을 기반으로 DECODE를 활용하면 가능할 것 같다. 그러나 하루 수행 횟수가 서너 번 정도라면 그냥 각 시각마다 실행되도록 서너 개의 JOB을 등록시켜주는 것도 생각해볼 수 있다.
원래 문제는 불규칙한 시각+평일 조건을 만족해야 하므로 하나의 interval 수식으로 해결하려면 수식이 무척 복잡해질 것 같다. interval 수식이 복잡해지면 이해가기도 어려워 진고, 나중에 수정하고 싶을 때 문제가 생길 수도 있다.
참고로 10g부터는 DBMS_JOB 대신 DBMS_SCHEDULER을 쓰도록 권고하고 있다.
[출처] http://blog.naver.com/gnpado/40017478314
.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다.. JOB을 스케줄링 하기 위해.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다..
JOB을 스케줄링 하기 위해서 ORACLE의 DBMS_JOB 패키지를 이용합니다..
JOB QUEUE PROCESS가 JOB QUEUE 안의 잡을 수행합니다..
JOB으로 등록될 수 있는 것은 PL/SQL 프로시저 또는 패키지가 되며
예를 들어소스 디비의 테이블들에서 타겟 테이블로 데이터를 적재하는 프로시저를 생성했는데
1분단위로 데이터를 타겟 테이블로 적재를 해야 할 때 DBMS_JOBS에 등록하여
스케줄링 할 수 있습니다.
.JOB_QUEUE_PROCESSES 파라미터가 이와 관련된 초기화 파라미터로 0으로 설정되면
JOB QUEUE PROCESS는 시작되지 않으며 JOB QUEUE의 어느 잡도 수행되지 않습니다..
JOB_QUEUE_PROCESSES 파라미터의 MAX값이 설정되어야 오라클 인스턴스 위에서
동시에 잡을 수행할 수 있다. 설정할 수 있는 최고 값은 1000입니다..
JOB_QUEUE_PROCESSES=60 과 같이 설정할 수 있습니다..
.등록되거나 수행되는 잡에 대해서는 DBA_JOBS 또는 USER_JOBS 딕셔너리 뷰를 통해
확인 할 수 있다.
.JOB_QUEUE_PROCESSES 는 다이나믹 하게 DB를 SHUTDOWN하지 않고 ALTER SYSTEM
명령을 이용해서 설정할 수 있습니다.
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
.JOB QUEUE안의 JOB을 스케줄링 하기 위해서는 DBMS_JOBS패키지를 사용할 수 있으며
JOB_QUEUE를 사용하기 위해 관련된 DB 권한은 없다.
.다음은 DBMS_JOBS의 패키지를 사용하기 위한 패키지의 프로시져들입니다.
SUBMIT - 잡큐의 잡을 등록합니다.
REMOVE - 잡큐의 잡을 제거합니다.
CHANGE - 잡큐의 잡을 변경합니다.
NEXT_DATE - 잡의 다음 수행시간을 변경합니다.
INTERVAL - 잡 수행 주기를 변경합니다.
WHAT - 잡으로 등록된 프로시저 또는 패키지를 변경합니다.
RUN - 잡을 수동으로 강제로 수행합니다.
.새 JOB을 JOB QUEUE에 등록하기 위해 사용되는 파라미터로 DBMS_JOB.SUBMIT()에 들어가느
파라미터 입니다.
JOB - OUTPUT 파라미터로 생성한 잡에 의해 할당되는 식별자 입니다.
WHAT - JOB QUEUE에 등록되는 PL/SQL 프로시저 또는 패키지 입니다.
NEXT_DATE - 잡이 수행되는 다음 시간입니다.
INTERVAL - 잡이 수행되는 주기로 초 단위까지 지정 가능합니다.
JOB_QUEUE에 등록하는 예제입니다.
01.VARIABLE jobno NUMBER
02.BEGIN
03. DBMS_JOB.SUBMIT
04. (
05. :jobno,
06. 'SP_IN_EMP_SAL;',
07. SYSDATE,
08. 'SYSDATE + 1'
09. );
10.COMMIT;
11.END;
12./ <SPAN style="FONT-FAMILY: 굴림; WHITE-SPACE: normal" class=Apple-style-span>
13.</SPAN>
위의 PL/SQL문을 SQL PLUS에서 수행합니다.
첫번째 파라미터가 JOB NUMBER가 부여되는 부분이고
두번째 파라미터가 WHAT으로 SP_IN_EMP_SAL이라는 프로시저를 등록했습니다.
세번째 파라미터가 NEXT_DATE이며 4번째 파라미터가 잡 수행 주기로 하루에 한번씩
수행하라는 의미입니다.
DBMS_JOB을 이용하면 특정시간, 특정요일, 특정일, 30초단위, 매분 정각, 매시정각
등 다양하게 잡을 스케줄링 하는 것이 가능합니다.
ETL 수행 할 때도 유용하게 사용할 수 있습니다.
잡 수행 간격 조정의 예
.SYSDATE+ 7 : 7일에 한번씩 잡이 수행됩니다.
.SYSDATE+1/24 : 1시간에 한번씩 잡이 수행됩니다.
.SYSDATE+30/86400 : 30초에 한번씩 잡이 수행됩니다.
.최초 잡 수행시간이 14:02분일 경우 매시 14:02분에 잡을 수행해야 될 경우
=>trunc(SYSDATE,'MI')+1/24
.최조 잡 수행시간이 06시 이고 매 8시간마다 정각에 잡이 수행되어야 될 경우
=>trunc(SYSDATE,'MI')+8/24
.매주 월요일 정각 3시에 잡이 수행되어야 할 경우
=>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25
.각 분기마다 첫번째 월요일에 잡이 수행되어야 될 경우
=>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY')
주의> dbms_job으로 잡을 스케줄링하게 될 경우 예를 들어 잡 최초 수행시간이 22일 14:00시이고
매시 정각에 잡이 수행되어야 할 경우 SYSDATE+1/24로 간격을 주게 되면 매 정시에 도는 것이
아니라 잡 수행 시간에 따라 약간씩 늦어지게 되어 14:00:04 => 15:00:07 => 16:00:10 이런식으로
잡 수행시간이 잡히게 됩니다. 따라서 정각에 수행되게 하려면 trunc함수를 이용해서 무조건 분에서
잘라내여 00으로 만들어 준다. trunc(SYSDATE,'MI')
잡의 삭제
1.
2.BEGIN
3. DBMS_JOB.REMOVE(14443);
4.END;
5./
14443은 잡 번호 이다. USER_JOBS 데이터 딕셔너리 뷰를 보면 잡 번호를 알 수 있습니다.
잡의 변경
1.
2.BEGIN
3. DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
4. END;
5. /
잡으로 등록된 프로시저/패키지 변경
1.
2.BEGIN
3. DBMS_JOB.WHAT(14144,
4. 'SP_IN_EMP_SAL;');
5.END;
6./
잡 다음 수행시간 변경
1.
2.BEGIN
3. DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4);
4.END;
5./
잡 수행 간격 변경
1.
2.BEGIN
3. DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440);
4.END;
5./
잡 수행 정지 잡이 BROKEN되면 잡은 수행되지 않으며 강제로 수행 할 시에는 DBMS_JOB.RUN()을 통해 수행합니다..
1.
2.BEGIN
3. DBMS_JOB.BROKEN(14144, TRUE);
4.END;
5./
잡큐 정보 VIEWING DBA_JOBS, USER_JOBS, ALL_JOBS를 이용합니다.
01.
02.SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
03.FROM DBA_JOBS;
04.
05.JOB NEXT_DATE NEXT_SEC FAILURES B
06.------- --------- -------- -------- -
07.9125 01-JUN-01 00:00:00 4 N
08.14144 24-OCT-01 16:35:35 0 N
09.9127 01-JUN-01 00:00:00 16 Y
10.3 rows selected.
DBMS_JOB의 활용예제를 주기적으로 소스테이블에서 타켓 테이블로 적재할 시 어떻게 사용할 수 있는지 간단하게 예를 만들었습니다
001.
002.--수행될 잡의 목록이 들어갈 테이블
003.CREATE TABLE JOB_LIST
004.(
005. JOB_ID VARCHAR2(2),
006. JOB_TYPE VARCHAR2(1),
007. JOB_NAME VARCHAR2(30),
008. JOB_EXEC_HOUR VARCHAR(2),
009. JOB_PARENTID VARCHAR2(2),
010. CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID)
011. USING INDEX
012. TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0) NOLOGGING)
013. TABLESPACE CYS_DATA PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0
014.);
015./
016.
017.CREATE UNIQUE INDEX JOB_LIST_IDX01 ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR)
018.TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0);
019./
020.
021.-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다.
022.CREATE TABLE JOB_LOG
023.(
024. JOB_ID VARCHAR2(2),
025. JOB_EXEC_DATE VARCHAR2(8),
026. JOB_START_TIME DATE,
027. JOB_END_TIME DATE,
028. JOB_DATASTART_TIME DATE,
029. JOB_DATAEND_TIME DATE,
030. JOB_STATUS VARCHAR2(1),
031. JOB_ERR_MSG VARCHAR2(100),
032. CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE) USING INDEX
033. TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0) NOLOGGING,
034. CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID) REFERENCES JOB_LIST(JOB_ID))
035. TABLESPACE CYS_DATA STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
036.
037. --JOB_ID를 부여하기 위한 시퀀스
038.CREATE SEQUENCE JOB_NUM START WITH 1 INCREMENT BY 1 ;
039.
040.--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져
041.--다음날 수행할 JOB을 LOG로 넣는다.
042.CREATE OR REPLACE PROCEDURE SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL)
043.AS
044.BEGIN
045. INSERT INTO JOB_LOG SELECT JOB_ID,
046. NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')),
047. NULL,
048. NULL,
049. NULL,
050. NULL,
051. 'N',
052. NULL
053. FROM JOB_LIST;
054.
055. COMMIT;
056.END SP_IN_JOB_LOG;
057.
058.--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다.
059.DECLARE
060. JOB_NUMBER NUMBER;
061.BEGIN
062. DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB번호
063. 'SP_IN_JOB_LOG;', --프로시저명
064. TO_DATE('20050208180000','YYYYMMDDHH24MISS'), --NEXT_DATE
065. 'TRUNC(SYSDATE,''MI'')+1'); --잡 수행 간격(매일 정각 6시)
066.END;
067.
068.--JOB_LIST에 수행할 프로시져(JOB)을 등록
069.--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용
070.INSERT INTO JOB_LIST
071.SELECT JOB_NUM.NEXTVAL JOB_ID,
072. JOB_TYPE,
073. JOB_NAME,
074. B.CNT JOB_EXEC_HOUR,
075. NULL
076.FROM(
077. SELECT NULL JOB_ID,
078. 'F' JOB_TYPE,
079. 'SP_IN_F_SALE_SUM' JOB_NAME,
080. NULL JOB_EXEC_HOUR,
081. NULL
082. FROM DUAL) A,
083. ( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES
084. WHERE ROWNUM<25) B
085.
086.COMMIT;
087.
088.--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌)
089.EXEC SP_IN_JOB_LOG('20050208');
090.
091. --제대로 들어갔는지 확인
092.SELECT * FROM JOB_LOG;
093.
094. --SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져
095.--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의
096.--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌
097.CREATE OR REPLACE PROCEDURE SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL, V_ENDTIME IN VARCHAR2 DEFAULT NULL)
098.AS
099. D_STARTTIME DATE;
100. D_ENDTIME DATE;
101. V_ERR_MSG VARCHAR2(100);
102.BEGIN
103.
104. --프로시저가 파라미터 값이 없이 수행될 경우
105. IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN
106. SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24'))
107. INTO D_STARTTIME
108. FROM JOB_LOG
109. WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
110. WHERE JOB_NAME='SP_IN_F_SALE_SUM'
111. AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24'))
112. AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD')
113. AND JOB_STATUS='Y';
114.
115. D_ENDTIME:=TRUNC(SYSDATE,'HH24');
116. ELSE
117. SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS'))
118. INTO D_STARTTIME
119. FROM JOB_LOG
120. WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
121. WHERE JOB_NAME='SP_IN_F_SALE_SUM'
122. AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
123. AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8)
124. AND JOB_STATUS='Y';
125.
126. D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS');
127. END IF;
128.
129. --수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시
130. UPDATE JOB_LOG
131. SET JOB_START_TIME=SYSDATE,
132. JOB_STATUS='R'
133. WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
134. WHERE JOB_NAME='SP_IN_F_SALE_SUM'
135. AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2))
136. AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
137.
138. -- DML------
139.
140. --수행되어질 INSERT문
141.
142. -- DML-----
143.
144. COMMIT;
145.
146. --프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌
147. UPDATE JOB_LOG
148. SET JOB_END_TIME=SYSDATE,
149. JOB_DATASTART_TIME=D_STARTTIME,
150. JOB_DATAEND_TIME=D_ENDTIME,
151. JOB_STATUS='Y'
152. WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
153. WHERE JOB_NAME='SP_IN_F_SALE_SUM'
154. AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
155. AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
156.
157. COMMIT;
158.
159.EXCEPTION
160. WHEN OTHERS THEN
161. V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80);
162.
163. UPDATE JOB_LOG
164. SET JOB_END_TIME=SYSDATE,
165. JOB_STATUS='E',
166. JOB_ERR_MSG=V_ERR_MSG
167. WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST
168. WHERE JOB_NAME='SP_IN_F_SALE_SUM'
169. AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24'))
170. AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8);
171.
172. COMMIT;
173.END SP_IN_F_SALE_SUM;
174./
175.
176. --SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다.
177.DECLARE
178. JOB_NUMBER NUMBER;
179.BEGIN
180. DBMS_JOB.SUBMIT(JOB_NUMBER, --JOB번호
181. 'SP_IN_F_SALE_SUM;', --프로시저명
182. TO_DATE('20050209000000','YYYYMMDDHH24MISS'), --NEXT_DATE
183. 'TRUNC(SYSDATE,''MI'')+1/24'); --잡 수행 간격(매시간 정각)
184.END;
JOB의 시간이나 간격 등록된 프로시저등을 변경하고자 할 때 DBMS_JOB의 다른 프로시져를 이용해서 변경합니다. 2번째 정리 부분에 설명되어 있습니다.
'DATABASE > Oracle' 카테고리의 다른 글
| 「Tools」DBeaver - Oracle 무료 툴 (0) | 2020.12.31 |
|---|---|
| Oracle SQL Developer 클릭시 객체 정보 안보이게... (0) | 2018.04.03 |
| Oracle Table, Column 정보 조회 쿼리 (0) | 2015.01.05 |
| 오라클 잡 관리(Oracle job manage) (0) | 2010.01.13 |
| How to: Bind an Array to an ODP.NET Database Command (0) | 2009.11.11 |
