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
|
정상적으로 등록됨
'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 |