오라클 시퀀스 매일 초기화하는 방법 job 등록

게시판에 넣을 번호를 시퀀스로 만들었습니다..

새로 매번 증가되는 값을 넣는 방법 중에는 시퀀스보다 쉽고 정확한게 없죠..

그럼 매일 새롭게 증가되는 값을 넣는 방법은 뭐가 있을까 하다가..

당시 가장 쉽게 생각한 방법은..

번호와 날짜를 저장할 테이블을 만들고..

CREATE TABLE STUDY.SEQ_TABLE

(

  SEQ       NUMBER,

  REG_DATE  VARCHAR2(8 BYTE)

)

매일 새롭게 번호를 생성할 수 있는 쿼리를 만들어 줍니다..

SELECT NVL(EVERY_NO,0)+1 EVERY_NO

FROM STUDY.SEQ_TABLE

WHERE REG_DATE = TO_CHAR(SYSDATE,'YYYYMMDD')

그리고 번호가 생성될때 마다..

SEQ_TABLE 테이블에 값을 INSERT 그리고 그 다음부터는 UPDATE 로..

SEQ	REG_DATE
60	20161230
23	20161231
4	20170102
501	20170110
123	20170111

그럼 이런식으로 데이터가 쌓일겁니다..

해당 일자에 번호 생성 쿼리를 실행 안해주면 INSERT도 안될테니 그 일자로는 데이터가 없을것이고..

해당 일자에 번호 생성 쿼리를 계속 실행했다면.. UPDATE 될테니 SEQ 번호가 증가했겠죠..

하지만 저 방식은 시퀀스를 통해 번호를 증가하는 형식이아닌..

테이블의 값에 MAX + 1 이기 때문에..

어디서 문제가 생겨버리면.. 중복된 값이 발생할 수 있는 확률이 존재합니다..

그래서 안전한 시퀀스 방식으로.. 매일 초기화 하는 방법을 찾다보니..

많은 분들이 시퀀스 초기화 하는 방법을.. 작성해놓으셨더군요..

감사히 배우겠습니다!..

우선 매일매일 초기화할 시퀀스를 만들어줍니다..

CREATE SEQUENCE STUDY.EVERYDAY_SEQ

  START WITH 1

  MAXVALUE 99999

  MINVALUE 0

  NOCYCLE

  NOCACHE

  NOORDER;

그리고 시퀀스를 초기화를 해줄 프로시져도 만들어줍니다..

CREATE OR REPLACE procedure STUDY.PRC_RESET_DAY_SEQ( p_seq_name in varchar2 )

  is

      l_val number;

  begin

      execute immediate

      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

      execute immediate

      'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';

      execute immediate

      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

      execute immediate

      'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';

  end;

/

시퀀스를 변경해서 증가한 만큼 -1 을 해서 0이 될때까지 빼주고..

그다음 원래대로 다시 변경하는 방식으로 되어있습니다..

프로시져를 만들었으니.. 해당 프로시져를 매일 매일 실행해줄..

오늘의 메인! job 을 등록합니다..

저는 토드 포 오라클을 사용하는데..

버전마다 약간씩 틀리겠지만.. 비슷할겁니다..

언제부터 실행할껀지.. 일자를 선택해주고..

어떻게 반복 실행할건지 선택해주고..

어떤 작업을 할껀지 적어주면 끝입니다..

친절하게도.. 언제 실행할건지.. 목록으로 보여줘서 선택만 하면됩니다..

전 매일 초기화 할꺼니까 Every day at midnight 을 선택했습니다..

스크립트는 이렇게 나오네요..

DECLARE

  X NUMBER;

  user_name varchar2(30);

BEGIN

  select user into user_name from dual;

  execute immediate 'alter session set current_schema = STUDY';

  BEGIN

    SYS.DBMS_JOB.SUBMIT

    ( job       => X 

     ,what      => 'PRC_RESET_DAY_SEQ(''EVERYDAY_SEQ'');'

     ,next_date => to_date('11/01/2017 00:00:00','dd/mm/yyyy hh24:mi:ss')

     ,interval  => 'TRUNC(SYSDATE+1)'

     ,no_parse  => FALSE

    );

    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

    execute immediate 'alter session set current_schema = ' || user_name ;

  EXCEPTION

    WHEN OTHERS THEN 

      execute immediate 'alter session set current_schema = ' || user_name ;

      RAISE;

  END;

  COMMIT;

END;

/

수행주기인 interval 을 매일 밤 12:00:00 로 선택했기 때문에..

해당 job 이 정상적으로 동작하는지 알수없습니다;..

그래서 우선 수행주기를 바꾸고;.. 테스트 후에.. 정상이면 다시 원래대로 등록하는 방법으로..

하시는게 좋습니다..

SYSDATE+3/1440  으로 3분에 한번씩 실행하게 변경했고..

시작시간도 지금보다 조금 더 늦은시간으로 바꿨습니다..

그럼 그때부터 job 이 실행되겠죠..

우선 테스트를 해봐야하니까 시퀀스를 마구 실행해줍니다..

SELECT EVERYDAY_SEQ.NEXTVAL FROM DUAL

그리고 job 이 돌았나 확인해봤는데..

시퀀스도 초기화 안되고 그대로 있고..

잡도.. 실행될때마다 NEXT Date 값이 다음 수행주기일자로 바뀌고..

Last Date 도 찍히는데 아무것도 안찍혔네요..

기존에 job 이 등록되어 있는.. 이미 사용하고 있는 분들이라면.. 

아마 정상적으로 실행이 되셨을겁니다..

처음으로 job을 생성한 분들은 저 처럼 실패와 좌절을;..

우선 dba 권한이 있는 계정으로..

SELECT * FROM V$PARAMETER WHERE NAME = 'job_queue_processes' 로..

job_queue_processes 값이 얼마로 설정되어있나 보셔야합니다..

기존 설정값이 0 입니다.. 그래서 안되는거죠..

설정 값을 0 -> 10으로 변경해줍니다.. 최고 1000 까지 변경됩니다..

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10 ;

아!.. job_queue_processes 값을 변경하시기 전에!..

이미 job 을 만들어둔 상태이기 때문에..

job_queue_processes 의 값을 변경하는 순간 job이 실행되어버립니다..

운영서버 같은곳에 반영하실땐 주의 하셔야합니다..

그럼 시퀀스가 초기화 되고..

Job 도 3분 간격으로 실행되는걸 확인할 수 있습니다..

그럼 다시 interval 값을 바꾸면.. 시퀀스 매일 초기화 작업 끝!..