OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.18.0.0
방법 : 오라클 19c 파티션 테이블 멀티 truncate 테스트
일반적으로 오래된 파티션을 지울 경우 파티션을 하나씩 drop 하거나 truncate, exchange함
하지만 truncate 해야할 대상 파티션들이 많을 경우 명령어를 여러번 입력해줘야 하고
매 ddl 명령시 sql들이 무효화(invalidations)되는 단점이 있음
이때 파티션 멀티 truncate를 하면 명령어 하나로 여러 파티션을 truncate 시킬 수 있음(sql 무효화 1회)
물론 업무 부하가 많은 시간대에 해당명령어를 실행하게 되면
library cache lock 등 이벤트가 발생할수 있어 주의가 필요함
샘플 파티션 생성
|
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>
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20211300'),
partition p_max values less than (maxvalue)
)
;
|
샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF ptable%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..1000000 LOOP
w_ins(i).col1 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).col2 := i;
END LOOP;
FORALL i in 1..1000000 INSERT INTO ptable VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
|
파티션 별 용량 조회
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col tablespace_name for a15
col segment_name for a15
col partition_name for a15
col segment_type for a15
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
--select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024/1024) gb
from dba_segments
where segment_name = 'PTABLE'
and segment_type='TABLE PARTITION'
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- --------------- --------------- --------------- --------------- ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 16
IMSI USERS PTABLE P202102 TABLE PARTITION 24
IMSI USERS PTABLE P202103 TABLE PARTITION 24
IMSI USERS PTABLE P202104 TABLE PARTITION 24
IMSI USERS PTABLE P202105 TABLE PARTITION 24
IMSI USERS PTABLE P202106 TABLE PARTITION 24
IMSI USERS PTABLE P202107 TABLE PARTITION 24
IMSI USERS PTABLE P202108 TABLE PARTITION 24
IMSI USERS PTABLE P202109 TABLE PARTITION 24
IMSI USERS PTABLE P202110 TABLE PARTITION 24
IMSI USERS PTABLE P202111 TABLE PARTITION 24
IMSI USERS PTABLE P202112 TABLE PARTITION 16
12 rows selected.
|
파티션당 8mb씩 가지고 있음
단일 파티션 truncate 실행(202101)
|
1
2
3
|
SQL> alter table ptable truncate partition p202101;
Table truncated.
|
파티션 별 용량 재조회
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col tablespace_name for a15
col segment_name for a15
col partition_name for a15
col segment_type for a15
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
--select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024/1024) gb
from dba_segments
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION')
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- --------------- --------------- --------------- --------------- ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 8
IMSI USERS PTABLE P202102 TABLE PARTITION 24
IMSI USERS PTABLE P202103 TABLE PARTITION 24
IMSI USERS PTABLE P202104 TABLE PARTITION 24
IMSI USERS PTABLE P202105 TABLE PARTITION 24
IMSI USERS PTABLE P202106 TABLE PARTITION 24
IMSI USERS PTABLE P202107 TABLE PARTITION 24
IMSI USERS PTABLE P202108 TABLE PARTITION 24
IMSI USERS PTABLE P202109 TABLE PARTITION 24
IMSI USERS PTABLE P202110 TABLE PARTITION 24
IMSI USERS PTABLE P202111 TABLE PARTITION 24
IMSI USERS PTABLE P202112 TABLE PARTITION 16
12 rows selected.
|
202101 파티션의 데이터가 truncate 됨
멀티 truncate 실행(p202102, p202103, p202104, p202105, p202106, p202107)
|
1
2
3
|
SQL> alter table ptable truncate partition p202102, p202103, p202104, p202105, p202106, p202107;
Table truncated.
|
파티션 별 용량 재조회
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col tablespace_name for a15
col segment_name for a15
col partition_name for a15
col segment_type for a15
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
--select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024/1024) gb
from dba_segments
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION')
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- --------------- --------------- --------------- --------------- ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 8
IMSI USERS PTABLE P202102 TABLE PARTITION 8
IMSI USERS PTABLE P202103 TABLE PARTITION 8
IMSI USERS PTABLE P202104 TABLE PARTITION 8
IMSI USERS PTABLE P202105 TABLE PARTITION 8
IMSI USERS PTABLE P202106 TABLE PARTITION 8
IMSI USERS PTABLE P202107 TABLE PARTITION 8
IMSI USERS PTABLE P202108 TABLE PARTITION 24
IMSI USERS PTABLE P202109 TABLE PARTITION 24
IMSI USERS PTABLE P202110 TABLE PARTITION 24
IMSI USERS PTABLE P202111 TABLE PARTITION 24
IMSI USERS PTABLE P202112 TABLE PARTITION 16
|
정상적으로 여러 파티션이 truncate 됨
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 파티션 테이블 append insert 시 lock 확인 (0) | 2023.05.01 |
|---|---|
| 오라클 19c ORA-04036 에러를 발생시키는 방법 (0) | 2023.04.30 |
| 오라클 19c Memoptimize pool 테스트(memoptimize for read) (0) | 2023.02.14 |
| 오라클 19c db_files 파라미터 과다 설정시 pga 사용량 비교 (0) | 2023.02.10 |
| 오라클 19c invisible index 테스트 (0) | 2023.02.09 |