내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
2024
10.12
14:00
10.12
14:00
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.16.0.0
방법 : 오라클 19c 파티션 Exchange 방법
오라클에서 파티션 테이블의 기간이 오래되서 데이터를 지워야하거나,
어떤 이유에 의해 특정 파티션 데이터를 지워야 할 경우 파티션 drop 명령을 사용할 수 있음
하지만 drop 명령은 시간이 조금 소요되고, 부하가 있을수 있기 때문에
대용량 트랜젝션 환경에선 exchange를 사용하는것이 더 좋음
파티션 exchange 도 library cache lock 이 발생할수는 있지만 파티션 내 데이터를 제거하는 가장 나은 방법임
* 파티션 local 인덱스를 사용하고 있을때를 기준으로 작성한 글이며, 만약 파티션 global 인덱스를 가지고 있는 테이블의 경우 파티션 exchange 이후 인덱스가 Unusable 상태로 빠지기 때문에 인덱스 rebuild 가 필요함
본문에서 3가지 테스트를 진행함
1. 인덱스 존재하지 않는 환경에서의 exchange 테스트
2. 인덱스 존재하는 환경에서의 exchange 테스트
3. 다른 테이블스페이스간 exchange 테스트
1. 인덱스 존재하지 않는 환경에서의 exchange 테스트
파티션 테이블 생성
|
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;
drop table ntable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113'),
partition p_max values less than (maxvalue)
) nologging;
|
일반 테이블 생성
|
1
2
3
4
5
6
7
8
|
SQL>
create table ntable
(
col1 varchar2(8),
col2 number
)
tablespace users
nologging;
|
데이터 삽입
|
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..130 LOOP
FOR i IN 1..1000000 LOOP
w_ins(i).col1 := '202102';
w_ins(i).col2 := i||ceil(dbms_random.value(1, 1000000000000));
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4
OWNER TABLESPACE SEGMENT_NAME PARTITIO SEGMENT_TYPE MB
---------- ---------- -------------------- -------- ------------------ ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 8
IMSI USERS PTABLE P202102 TABLE PARTITION 3264
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 8
IMSI USERS PTABLE P202109 TABLE PARTITION 8
IMSI USERS PTABLE P202110 TABLE PARTITION 8
IMSI USERS PTABLE P202111 TABLE PARTITION 8
IMSI USERS PTABLE P202112 TABLE PARTITION 8
IMSI USERS PTABLE P_MAX TABLE PARTITION 8
13 rows selected.
|
exchange 시도
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.07
|
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO 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 8
IMSI USERS PTABLE P202109 TABLE PARTITION 8
IMSI USERS PTABLE P202110 TABLE PARTITION 8
IMSI USERS PTABLE P202111 TABLE PARTITION 8
IMSI USERS PTABLE P202112 TABLE PARTITION 8
IMSI USERS PTABLE P_MAX TABLE PARTITION 8
13 rows selected.
|
일반 테이블 용량확인
|
1
2
3
4
5
6
|
SQL>
select bytes/1024/1024 mb from dba_segments where segment_name = 'NTABLE';
MB
----------
3264
|
정상적으로 exchange 됨
exchange 재시도(역방향)
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.01
|
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO SEGMENT_TYPE MB
---------- ---------- -------------------- -------- ------------------ ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 8
IMSI USERS PTABLE P202102 TABLE PARTITION 3264
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 8
IMSI USERS PTABLE P202109 TABLE PARTITION 8
IMSI USERS PTABLE P202110 TABLE PARTITION 8
IMSI USERS PTABLE P202111 TABLE PARTITION 8
IMSI USERS PTABLE P202112 TABLE PARTITION 8
IMSI USERS PTABLE P_MAX TABLE PARTITION 8
13 rows selected.
|
일반 테이블 용량확인
|
1
2
3
4
5
6
|
SQL>
select bytes/1024/1024 mb from dba_segments where segment_name = 'NTABLE';
MB
----------
0
|
정상적으로 exchange 됨
추가 테스트를 위해 원상복구
|
1
2
3
4
|
SQL>
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
|
일반 테이블 용량확인
|
1
2
3
4
5
6
|
SQL>
select bytes/1024/1024 mb from dba_segments where segment_name = 'NTABLE';
MB
----------
3264
|
2. 인덱스 존재하는 환경에서의 exchange 테스트
파티션 테이블 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
CREATE INDEX imsi.ix_ptable_01
ON imsi.ptable (col1, col2)
LOCAL TABLESPACE users
NOLOGGING;
CREATE INDEX imsi.ix_ptable_02
ON imsi.ptable (col1)
LOCAL TABLESPACE users
NOLOGGING;
CREATE UNIQUE INDEX imsi.pk_ptable
ON imsi.ptable (col2, col1)
LOCAL TABLESPACE users
NOLOGGING;
ALTER TABLE imsi.ptable
ADD CONSTRAINT pk_ptable PRIMARY KEY (col2, col1);
|
일반 테이블 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
CREATE INDEX imsi.ix_ntable_01
ON imsi.ntable (col1, col2)
TABLESPACE users
NOLOGGING parallel 4;
CREATE INDEX imsi.ix_ntable_02
ON imsi.ntable (col1)
TABLESPACE users
NOLOGGING parallel 4;
CREATE UNIQUE INDEX imsi.pk_ntable
ON imsi.ntable (col2, col1)
TABLESPACE users
NOLOGGING parallel 4;
ALTER TABLE imsi.ntable
ADD CONSTRAINT pk_ntable PRIMARY KEY (col2, col1);
alter index imsi.ix_ntable_01 noparallel;
alter index imsi.ix_ntable_02 noparallel;
alter index imsi.pk_ntable noparallel;
|
exchange 시도
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.02
|
19.16, 19.11 환경에서 모두 빠름
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO SEGMENT_TYPE MB
---------- ---------- -------------------- -------- ------------------ ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 0
IMSI USERS PTABLE P202102 TABLE PARTITION 3264
IMSI USERS PTABLE P202103 TABLE PARTITION 0
IMSI USERS PTABLE P202104 TABLE PARTITION 0
IMSI USERS PTABLE P202105 TABLE PARTITION 0
IMSI USERS PTABLE P202106 TABLE PARTITION 0
IMSI USERS PTABLE P202107 TABLE PARTITION 0
IMSI USERS PTABLE P202108 TABLE PARTITION 0
IMSI USERS PTABLE P202109 TABLE PARTITION 0
IMSI USERS PTABLE P202110 TABLE PARTITION 0
IMSI USERS PTABLE P202111 TABLE PARTITION 0
IMSI USERS PTABLE P202112 TABLE PARTITION 0
IMSI USERS PTABLE P_MAX TABLE PARTITION 0
13 rows selected.
|
정상적으로 exchange 됨
exchange 재시도(역방향)
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.02
|
19.16, 19.11 환경에서 모두 빠름
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO 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 8
IMSI USERS PTABLE P202109 TABLE PARTITION 8
IMSI USERS PTABLE P202110 TABLE PARTITION 8
IMSI USERS PTABLE P202111 TABLE PARTITION 8
IMSI USERS PTABLE P202112 TABLE PARTITION 8
IMSI USERS PTABLE P_MAX TABLE PARTITION 8
13 rows selected.
|
정상적으로 exchange 됨
3. 다른 테이블스페이스간 exchange 테스트
파티션 테이블이 속한 테이블스페이스와 일반 테이블이 속한 테이블스페이스가 다른 경우 테스트
신규 테이블스페이스 생성
|
1
2
3
4
|
SQL>
create tablespace imsits datafile '+DATAC1' size 32500m;
Tablespace created.
|
quota 부여
|
1
2
3
4
|
SQL>
alter user imsi quota unlimited on imsits;
User altered.
|
일반 테이블2 생성
|
1
2
3
4
5
6
7
8
|
SQL>
create table ntable2
(
col1 varchar2(8),
col2 number
)
tablespace imsits
nologging;
|
데이터 삽입
|
1
2
3
4
|
SQL>
alter session enable parallel dml;
insert /*+ append full(a) parallel(a 4) */ into ntable2 a select /*+ parallel(b 4) full(b) */ * from ntable b;
commit;
|
일반 테이블2 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
CREATE INDEX imsi.ix_ntable2_01
ON imsi.ntable2 (col1, col2)
TABLESPACE imsits
NOLOGGING parallel 4;
CREATE INDEX imsi.ix_ntable2_02
ON imsi.ntable2 (col1)
TABLESPACE imsits
NOLOGGING parallel 4;
CREATE UNIQUE INDEX imsi.pk_ntable2
ON imsi.ntable2 (col2, col1)
TABLESPACE imsits
NOLOGGING parallel 4;
ALTER TABLE imsi.ntable2
ADD CONSTRAINT pk_ntable2 PRIMARY KEY (col2, col1);
alter index imsi.ix_ntable2_01 noparallel;
alter index imsi.ix_ntable2_02 noparallel;
alter index imsi.pk_ntable2 noparallel;
|
용량확인
|
1
2
3
4
5
6
|
SQL>
select bytes/1024/1024 mb from dba_segments where segment_name = 'NTABLE2';
MB
----------
3264
|
exchange 시도
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE2 INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.02
|
19.16, 19.11 환경에서 모두 빠름
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO SEGMENT_TYPE MB
---------- ---------- -------------------- -------- ------------------ ----------
IMSI USERS PTABLE P202101 TABLE PARTITION 0
IMSI IMSITS PTABLE P202102 TABLE PARTITION 3264
IMSI USERS PTABLE P202103 TABLE PARTITION 0
IMSI USERS PTABLE P202104 TABLE PARTITION 0
IMSI USERS PTABLE P202105 TABLE PARTITION 0
IMSI USERS PTABLE P202106 TABLE PARTITION 0
IMSI USERS PTABLE P202107 TABLE PARTITION 0
IMSI USERS PTABLE P202108 TABLE PARTITION 0
IMSI USERS PTABLE P202109 TABLE PARTITION 0
IMSI USERS PTABLE P202110 TABLE PARTITION 0
IMSI USERS PTABLE P202111 TABLE PARTITION 0
IMSI USERS PTABLE P202112 TABLE PARTITION 0
IMSI USERS PTABLE P_MAX TABLE PARTITION 0
13 rows selected.
|
정상적으로 exchange 됨
기존 IMSITS 테이블스페이스에 있던 NTABLE2의 데이터가 파티션 테이블인 PTABLE로 들어옴
하지만 해당 201202 파티션은 USERS 테이블스페이스가 아닌 IMSITS 테이블스페이스 그대로 파티션 테이블에 들어옴
exchange 재시도(역방향)
|
1
2
3
4
5
6
7
|
SQL>
set timing on
ALTER TABLE imsi.PTABLE EXCHANGE PARTITION p202102 WITH TABLE imsi.NTABLE2 INCLUDING INDEXES WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.01
|
19.16, 19.11 환경에서 모두 빠름
파티션 테이블 용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col owner for a10
col segment_name for a20
select OWNER, TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE, round(bytes/1024/1024) MB
from dba_segments
where SEGMENT_NAME =upper('PTABLE')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE SEGMENT_NAME PARTITIO 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 8
IMSI USERS PTABLE P202109 TABLE PARTITION 8
IMSI USERS PTABLE P202110 TABLE PARTITION 8
IMSI USERS PTABLE P202111 TABLE PARTITION 8
IMSI USERS PTABLE P202112 TABLE PARTITION 8
IMSI USERS PTABLE P_MAX TABLE PARTITION 8
13 rows selected.
|
정상적으로 exchange 됨
결론
exchange 명령어 수행시 인덱스 존재 여부와 관계없이
모두 1초 이내로 빠르게 exchange 가 되었고, 데이터도 정상적으로 옮겨짐
각 테이블이 속한 테이블스페이스가 다르더라도 빠르게 exchange가 수행됨
하지만 exchange 후에 데이터들이 exchange 대상 테이블스페이스로 이동되는 것이 아닌
테이블스페이스 그대로 옮겨진다는 점을 기억해야함
만약 특정 테이블스페이스의 용량을 줄일 목적으로 exchange 를 하는 경우
테이블스페이스만 다른 임시 비파티션 테이블을 만들어서 exchange만 해서는 용량이 줄어들지 않기 때문에
임시 비파티션 테이블을 만들어서 exchage 후 해당 임시 비파티션 테이블을 truncate 해줘야 용량 확보가 가능함
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 일반 dml 시 발생하는 lock 확인 (0) | 2023.06.02 |
|---|---|
| 오라클 19c ASM usable_file_mb -(음수) 됬을때 조치방법 (0) | 2023.05.23 |
| 오라클 19c 파티션 테이블 append insert 시 lock 확인 (0) | 2023.05.01 |
| 오라클 19c ORA-04036 에러를 발생시키는 방법 (0) | 2023.04.30 |
| 오라클 19c 파티션 테이블 멀티 truncate 테스트 (0) | 2023.03.06 |