프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.4 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 인터벌 파티션 테이블 주기적으로 삭제하는 스크립트

오라클에서 인터벌 파티션 테이블이 존재 할때 인터벌이 짧은 경우(1일) 오래된 파티션은 주기적으로 삭제해주는 작업이 필요할 수 있음

이때 파티션을 수동으로 조회해서 해당 파티션만 수동으로 지워줄수도 있지만 귀찮을수 있음

자동으로 일정 기간이 지난 파티션을 삭제하는 스크립트를 설명함

 

 

로깅용 테이블 생성(테이블 owner 변경가능)

1
2
3
4
5
6
7
8
9
10
11
SQL> 
CREATE TABLE system.tb_log (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  wrk_tbl_nm VARCHAR2(100),
  err_cd VARCHAR2(100),
  err_msg VARCHAR2(1000),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT pk_tb_err_log PRIMARY KEY (id)
);
 
Table created.

 

 

인터벌 파티션 제거 스크립트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SQL>
DECLARE 
  l_ddl_str VARCHAR2(1000);
  l_err_code VARCHAR2(100);
  l_err_msg VARCHAR2(1000);
  
  CURSOR c_del_inter_partition IS
    SELECT owner ow_nm, object_name AS tb_nm, subobject_name AS partition_nm
    FROM dba_objects
    WHERE owner = 'SYSTEM' -- 인터벌 파티션 owner 지정 
      AND object_type = 'TABLE PARTITION'
      AND generated = 'Y'   -- 인터벌 파티션은 system 에 의해 생성되기 때문에 이값이 Y 임
      AND (object_name = 'INTERVAL_TEST'  -- 인터벌 파티션 이름 지정
           AND created < TRUNC(SYSDATE) - 5);  -- 몇일지난 파티션을 지울것인지 설정
 
BEGIN
  FOR c IN c_del_inter_partition LOOP   
    l_ddl_str := 'ALTER TABLE ' || c.ow_nm || '.' || c.tb_nm || ' DROP PARTITION ' || c.partition_nm;
 
    BEGIN
    -- Output the DDL string for debugging
    --DBMS_OUTPUT.PUT_LINE('DDL String: ' || l_ddl_str);
   
      -- Execute the DDL statement
      EXECUTE IMMEDIATE l_ddl_str;
      
      -- Insert success log into tb_err_log
      INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
      VALUES (c.tb_nm, 'No Error', 'Job Succeeded', CURRENT_TIMESTAMP);
      COMMIT;
      
    EXCEPTION
      WHEN OTHERS THEN
        l_err_code := '' || SQLCODE;
        l_err_msg := SQLERRM || ' ( ' || c.partition_nm || ' ) ';
        
        -- Output the error details for debugging
        --DBMS_OUTPUT.PUT_LINE('Error Code: ' || l_err_code);
        --DBMS_OUTPUT.PUT_LINE('Error Message: ' || l_err_msg);
        
        -- Log the error to the tb_err_log table
        INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
        VALUES (c.tb_nm, l_err_code, l_err_msg, CURRENT_TIMESTAMP);
        COMMIT;
    END;
  END LOOP;
END;
/

 

 

실행 전 테스트

현재 날짜 확인

1
2
3
4
5
SQL> select to_char(sysdate, 'yyyy/mm/dd') from dual;
 
TO_CHAR(SY
----------
2023/12/30

테스트 날짜 기준 12월 30일임

 

 

기존 인터벌 파티션 테이블의 파티션 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL>
set lines 200 pages 1000
col table_name for a15
col partition_name for a15
col high_value for a80
select table_name, partition_name, high_value
from dba_tab_partitions
where table_name = 'INTERVAL_TEST';
 
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------------------------------------------------------------------
INTERVAL_TEST   P_INIT          TO_DATE(' 2023-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14661      TO_DATE(' 2023-12-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14662      TO_DATE(' 2023-12-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14663      TO_DATE(' 2023-12-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14664      TO_DATE(' 2023-12-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14665      TO_DATE(' 2023-12-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14666      TO_DATE(' 2023-12-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14667      TO_DATE(' 2023-12-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14668      TO_DATE(' 2023-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14669      TO_DATE(' 2023-12-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14670      TO_DATE(' 2023-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14671      TO_DATE(' 2023-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 
12 rows selected.

12월 31일까지의 파티션이 존재함

 

 

위 스크립트에서 실제 실행되는 ddl과 insert 문 주석 후 디버깅용 스크립트 주석해제 후 실행

5일 이상된 파티션을 제거하게끔 되어있음(15번째 줄)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SQL>
set serveroutput on
DECLARE 
  l_ddl_str VARCHAR2(1000);
  l_err_code VARCHAR2(100);
  l_err_msg VARCHAR2(1000);
  
  CURSOR c_del_inter_partition IS
    SELECT owner ow_nm, object_name AS tb_nm, subobject_name AS partition_nm
    FROM dba_objects
    WHERE owner = 'SYSTEM' -- 인터벌 파티션 owner 지정 
      AND object_type = 'TABLE PARTITION'
      AND generated = 'Y'   -- 인터벌 파티션은 system 에 의해 생성되기 때문에 이값이 Y 임
      AND (object_name = 'INTERVAL_TEST'  -- 인터벌 파티션 이름 지정
           AND created < TRUNC(SYSDATE) - 5);  -- 몇일지난 파티션을 지울것인지 설정
 
BEGIN
  FOR c IN c_del_inter_partition LOOP   
    l_ddl_str := 'ALTER TABLE ' || c.ow_nm || '.' || c.tb_nm || ' DROP PARTITION ' || c.partition_nm;
     
    BEGIN
      -- Output the DDL string for debugging
      DBMS_OUTPUT.PUT_LINE('DDL String: ' || l_ddl_str);
 
      -- Execute the DDL statement
      --EXECUTE IMMEDIATE l_ddl_str;
      
      -- Insert success log into tb_err_log
      --INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
      --VALUES (c.tb_nm, 'No Error', 'Job Succeeded', CURRENT_TIMESTAMP);
      COMMIT;
      
    EXCEPTION
      WHEN OTHERS THEN
        l_err_code := '' || SQLCODE;
        l_err_msg := SQLERRM || ' ( ' || c.partition_nm || ' ) ';
        
        -- Output the error details for debugging
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || l_err_code);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || l_err_msg);
        
        -- Log the error to the tb_err_log table
        --INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
        --VALUES (c.tb_nm, l_err_code, l_err_msg, CURRENT_TIMESTAMP);
        COMMIT;
    END;
  END LOOP;
END;
/
 
DDL String: ALTER TABLE SYSTEM.INTERVAL_TEST DROP PARTITION SYS_P14661
DDL String: ALTER TABLE SYSTEM.INTERVAL_TEST DROP PARTITION SYS_P14662
DDL String: ALTER TABLE SYSTEM.INTERVAL_TEST DROP PARTITION SYS_P14663
DDL String: ALTER TABLE SYSTEM.INTERVAL_TEST DROP PARTITION SYS_P14664
DDL String: ALTER TABLE SYSTEM.INTERVAL_TEST DROP PARTITION SYS_P14665
 
PL/SQL procedure successfully completed.

실제 실행될 ddl 구문들이 정상적으로 표시됨

SYS_P14665 파티션까지 삭제함(high value가 12월 25일인 파티션으로 12월 24일에 생성된 데이터가 들어있음)

 

 

실제 스크립트 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SQL>
DECLARE 
  l_ddl_str VARCHAR2(1000);
  l_err_code VARCHAR2(100);
  l_err_msg VARCHAR2(1000);
  
  CURSOR c_del_inter_partition IS
    SELECT owner ow_nm, object_name AS tb_nm, subobject_name AS partition_nm
    FROM dba_objects
    WHERE owner = 'SYSTEM' -- 인터벌 파티션 owner 지정 
      AND object_type = 'TABLE PARTITION'
      AND generated = 'Y'   -- 인터벌 파티션은 system 에 의해 생성되기 때문에 이값이 Y 임
      AND (object_name = 'INTERVAL_TEST'  -- 인터벌 파티션 이름 지정
           AND created < TRUNC(SYSDATE) - 5);  -- 몇일지난 파티션을 지울것인지 설정
 
BEGIN
  FOR c IN c_del_inter_partition LOOP   
    l_ddl_str := 'ALTER TABLE ' || c.ow_nm || '.' || c.tb_nm || ' DROP PARTITION ' || c.partition_nm;
 
    BEGIN
    -- Output the DDL string for debugging
    --DBMS_OUTPUT.PUT_LINE('DDL String: ' || l_ddl_str);
   
      -- Execute the DDL statement
      EXECUTE IMMEDIATE l_ddl_str;
      
      -- Insert success log into tb_err_log
      INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
      VALUES (c.tb_nm, 'No Error', 'Job Succeeded', CURRENT_TIMESTAMP);
      COMMIT;
      
    EXCEPTION
      WHEN OTHERS THEN
        l_err_code := '' || SQLCODE;
        l_err_msg := SQLERRM || ' ( ' || c.partition_nm || ' ) ';
        
        -- Output the error details for debugging
        --DBMS_OUTPUT.PUT_LINE('Error Code: ' || l_err_code);
        --DBMS_OUTPUT.PUT_LINE('Error Message: ' || l_err_msg);
        
        -- Log the error to the tb_err_log table
        INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
        VALUES (c.tb_nm, l_err_code, l_err_msg, CURRENT_TIMESTAMP);
        COMMIT;
    END;
  END LOOP;
END;
/
 
PL/SQL procedure successfully completed.

 

 

인터벌 파티션 테이블의 파티션 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>
set lines 200 pages 1000
col table_name for a15
col partition_name for a15
col high_value for a80
select table_name, partition_name, high_value
from dba_tab_partitions
where table_name = 'INTERVAL_TEST';
 
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------------------------------------------------------------------
INTERVAL_TEST   P_INIT          TO_DATE(' 2023-12-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14666      TO_DATE(' 2023-12-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14667      TO_DATE(' 2023-12-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14668      TO_DATE(' 2023-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14669      TO_DATE(' 2023-12-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14670      TO_DATE(' 2023-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TEST   SYS_P14671      TO_DATE(' 2023-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 
7 rows selected.

정상적으로 해당 파티션들이 삭제됨

 

 

tb_log 테이블 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>
set lines 200 pages 1000
col id for 99999
col wrk_tbl_nm for a20
col err_cd for a20
col err_msg for a20
col created_at for a30
select * from system.tb_log;
 
    ID WRK_TBL_NM           ERR_CD               ERR_MSG              CREATED_AT
------ -------------------- -------------------- -------------------- ------------------------------
     1 INTERVAL_TEST        No Error             Job Succeeded        30-DEC-23 10.19.20.968052 AM
     2 INTERVAL_TEST        No Error             Job Succeeded        30-DEC-23 10.19.20.970547 AM
     3 INTERVAL_TEST        No Error             Job Succeeded        30-DEC-23 10.19.20.979574 AM
     4 INTERVAL_TEST        No Error             Job Succeeded        30-DEC-23 10.19.20.985874 AM
     5 INTERVAL_TEST        No Error             Job Succeeded        30-DEC-23 10.19.20.992756 AM

정상적으로 작업이 완료됨

 

 

스케줄러에 등록

위 스크립트를 프로시저로 생성

여기서는 dba_objects사용시 에러가 발생해 all_objects를 사용했음

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL>
CREATE OR REPLACE PROCEDURE drop_inter_partition_proc AS
  l_ddl_str VARCHAR2(1000);
  l_err_code VARCHAR2(100);
  l_err_msg VARCHAR2(1000);
  
  CURSOR c_del_inter_partition IS
    SELECT owner ow_nm, object_name AS tb_nm, subobject_name AS partition_nm
    FROM all_objects
    WHERE owner = 'SYSTEM'
      AND object_type = 'TABLE PARTITION'
      AND generated = 'Y'
      AND (object_name = 'INTERVAL_TEST'
           AND created < TRUNC(SYSDATE) - 5);
BEGIN
  FOR c IN c_del_inter_partition LOOP   
    l_ddl_str := 'ALTER TABLE ' || c.ow_nm || '.' || c.tb_nm || ' DROP PARTITION ' || c.partition_nm;
 
    BEGIN
      -- Execute the DDL statement
      EXECUTE IMMEDIATE l_ddl_str;
      
      -- Insert success log into tb_err_log
      INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
      VALUES (c.tb_nm, 'No Error', 'Job Succeeded', CURRENT_TIMESTAMP);
      COMMIT;
      
    EXCEPTION
      WHEN OTHERS THEN
        l_err_code := '' || SQLCODE;
        l_err_msg := SQLERRM || ' ( ' || c.partition_nm || ' ) ';
        
        -- Log the error to the tb_err_log table
        INSERT INTO system.tb_log (wrk_tbl_nm, err_cd, err_msg, created_at)
        VALUES (c.tb_nm, l_err_code, l_err_msg, CURRENT_TIMESTAMP);
        COMMIT;
    END;
  END LOOP;
END drop_inter_partition_proc;
/
 
Procedure created.

 

 

프로시저 테스트 수행

1
2
3
4
5
6
7
SQL>
BEGIN
 drop_inter_partition_proc;
END;
/
 
PL/SQL procedure successfully completed.

정상적으로 수행됨

 

 

dbms_scheduler로 매일 새벽 1시에 실행되게끔 등록

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'DAILY_DROP_INTER_PARTITION_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DROP_INTER_PARTITION_PROC; END;',
    start_date      => TRUNC(SYSDATE + 1) + INTERVAL '1' HOUR,
    repeat_interval => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE
  );
END;
/
 
PL/SQL procedure successfully completed.

 

 

등록된 스케줄러 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL>
set lines 200 pages 1000
col job_name for a30
col job_action for a40
col start_date for a30
col repeat_interval for a30
col enabled for a10
SELECT job_name, job_action, start_date, repeat_interval, enabled
FROM DBA_SCHEDULER_JOBS
where job_name = 'DAILY_DROP_INTER_PARTITION_JOB';
 
JOB_NAME                       JOB_ACTION                               START_DATE                     REPEAT_INTERVAL                ENABLED
------------------------------ ---------------------------------------- ------------------------------ ------------------------------ ----------
DAILY_DROP_INTER_PARTITION_JOB BEGIN DROP_INTER_PARTITION_PROC; END;    31-DEC-23 01.00.00.000000 AM + FREQ=DAILY; BYHOUR=1; BYMINUTE TRUE
                                                                        09:00                          =0; BYSECOND=0

정상적으로 등록됨

 

 

참조 : https://bintech.tistory.com/13

https://positivemh.tistory.com/963

 

오라클 19c 인터벌 파티션 테이블 생성

OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c 인터벌 파티션 테이블 생성 오라클 19c에서 인터벌 파티션 테이블을 생성하는 방법에 대해 설명함 먼저 7번 라인까지는

positivemh.tistory.com

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 19c sqlplus 에서의 rollback 커맨드 신기한점  (0) 2023.12.26
오라클 19c OCP, OCM 자격증 취득 관련 내용  (12) 2023.12.20
오라클 19c cpu_count 파라미터 인스턴스 케이징  (0) 2023.12.14
오라클 19c 파티션 테이블 high_value 컬럼 잘라서 보는 방법  (0) 2023.12.13
오라클 19c 테이블 생성 시 unrecoverable 옵션  (0) 2023.12.11

티스토리툴바