오라클 자동 스케줄러 등록하기(feat. 프로시저, 잡, 스케줄 등록/삭제)

어떻게 구현..?

• 푸시 발송 실패 건수가 3만건 이상일 경우, DEVICE_INFO 테이블에서 발송 실패한 토큰 전부 삭제
  
  • 매주 금요일 밤 11시 45분 스케줄러 실행

 

 

DB 자동 스케줄러 등록하기

1. 프로시저 생성

-- (프로시저 삭제)
DROP PROCEDURE delete_failed_push_tokens;

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE delete_failed_push_tokens
IS
    v_failed_token_cnt NUMBER;  -- 실패 토큰 개수 저장할 변수
BEGIN

    -- 1. 실패 토큰 개수 가져와 변수에 저장
    SELECT COUNT(*) INTO v_failed_token_cnt
    FROM 사용자계정.push_history
    WHERE send_status = '0';
    
    -- 2. 실패한 토큰 3만개 이상이면, 실패한 토큰 모두 삭제
    IF v_failed_token_cnt >= 30000 THEN
        DELETE FROM device_info@db_link_name
        WHERE device_token IN (
            SELECT device_token 
            FROM 사용자계정.push_history
            WHERE send_status = '0'
        );
        COMMIT;
    END IF;
    
END delete_failed_push_tokens;

• 필자는 푸시 보낼 디바이스 token이 쌓이는 DB와 푸시 발송 후 성공/실패 history가 담기는 DB가 다르므로, 2번에서와 같이 오라클 db link를 사용했습니다.

 

• 프로시저 내용 확인

SELECT * FROM user_source 
WHERE type = ‘PROCEDURE’ 
AND name = ‘DELETE_FAILED_PUSH_TOKENS’;

 

 

2. 스케줄 생성

-- (스케줄 삭제)
BEGIN
    dbms_scheduler.drop_schedule('DELETE_FAILED_TOKENS_SCHEDULE');
END;
/

-- 스케줄 등록
BEGIN
     DBMS_SCHEDULER.CREATE_SCHEDULE (
        schedule_name   => 'DELETE_FAILED_TOKENS_SCHEDULE',
        repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=45');
END;
/

• 매주 금요일 밤 11시 45분 실행

 

3. 잡 생성 및 속성 추가

-- (잡 삭제)
BEGIN
    dbms_scheduler.drop_job('DELETE_FAILED_TOKENS_JOB');
END;
/

-- 잡 등록
BEGIN
    dbms_scheduler.create_job(
            job_name     => 'DELETE_FAILED_TOKENS_JOB',
            job_type     => 'STORED_PROCEDURE',
            job_action   => 'SYS.DELETE_FAILED_PUSH_TOKENS',
            schedule_name => 'DELETE_FAILED_TOKENS_SCHEDULE',	-- 해당 잡을 스케줄에 연결
            auto_drop    =>   FALSE,
            enabled      => TRUE
    );
END;
/

-- 잡 속성 추가
BEGIN
  DBMS_SCHEDULER.set_attribute (
   name      => 'DELETE_FAILED_TOKENS_JOB',    
   attribute => 'restartable',
   value => TRUE
   );
   
   DBMS_SCHEDULER.set_attribute (
   name      => 'DELETE_FAILED_TOKENS_JOB',    
   attribute => 'logging_level',
   value => SYS.DBMS_SCHEDULER.LOGGING_RUNS
   );   
END;
/

 

 

주의

• 잡 생성하더라도 job_queue_processes 가 0이면, 실행되지 않음

# 조회
show parameter processes;

# 수정 (0이 아닌 값. 최대 1000)
alter system set job_queue_processes = 1000;

• 잡 - 스케줄 둘 연결되어 있을 경우, 삭제 순서
  • 잡 → 스케줄
  • 스케줄 → 잡: 해당 경우는 종속성 문제로 스케줄이 삭제되지 않음.