OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 파티션 인덱스의 테이블스페이스 변경
오라클에서 파티션 인덱스의 테이블스페이스를 변경하고 싶은 경우가 생길 수 있음
이때 방법이 2가지가 존재하는데 개별 파티션 인덱스를 rebuild 하면서 테이블스페이스를 변경 해주거나
파티션 인덱스의 기본 속성 자체를 바꾸는 방법이 존재함
rebuild 의 경우 파티션 인덱스 전체를 동시에 변경할수는 없음
하지만 인덱스 기본 속성을 바꾸는 방법은 전역적으로 적용됨, 하지만 기존 인덱스의 파티션은 적용되지 않고 변경 이후 새로 추가되는 파티션만 변경된 테이블스페이스를 사용하게됨
참고
Typically, you can change the physical storage attributes of a partition in a single step using an ALTER TABLE/INDEX MODIFY PARTITION statement.
However, there are some physical attributes, such as TABLESPACE, that you cannot modify using MODIFY PARTITION. In these cases, use the MOVE PARTITION clause.
Modifying some other attributes, such as table compression, affects only future storage, but not existing data.
일반적으로 파티션의 물리적 스토리지 속성은 ALTER TABLE/INDEX MODIFY PARTITION 문을 사용하여 한 번에 변경할 수 있습니다.
그러나 테이블 공간과 같은 일부 물리적 특성은 MODIFY PARTITION을 사용하여 수정할 수 없습니다. 이러한 경우 MOVE PARTITION 절을 사용하십시오.
테이블 압축과 같은 일부 다른 속성을 수정하면 향후 저장소에만 영향을 미치며 기존 데이터에는 영향을 미치지 않습니다.
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html#GUID-3DB0B6A6-FD33-4355-8BAC-7C5B4BE4C4E0
본문에서는 두가지 방법을 사용하여 파티션 인덱스의 테이블스페이스를 변경하는 방법을 설명함
테스트
사전 환경 구성
방법1. rebuild로 변경
방법2. 인덱스 기본 속성 변경
사전 환경 구성
샘플 유저 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1017 )
샘플 파티션 테이블 생성
|
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
|
SQL>
conn imsi/imsi
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace imsits
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 ('20220100')
);
Table created.
|
로컬 인덱스 생성
|
1
2
3
|
SQL> create index pt_ix02 on ptable(col1, col2) local;
Index created.
|
샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF PTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for j in 1..10 loop
FOR i IN 1..100000 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 := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
COMMIT;
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
21
22
23
24
25
26
27
28
29
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
col tablespace_name for a15
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment, dip.tablespace_name
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT TABLESPACE_NAME
---------- -------------------- -------------------- -------- ------ ------------ ---------------
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED IMSITS
12 rows selected.
|
현재 PT_IX02 인덱스의 파티션들이 모두 IMSITS에 존재함
방법1. rebuild로 변경
인덱스 rebuild 하면서 테이블스페이스 변경
|
1
2
3
4
5
|
SQL> alter index pt_ix02 rebuild tablespace users online;
alter index pt_ix02 rebuild tablespace users online
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
|
모두 변경하려 하면 에러가 발생함
P202101 파티션의 테이블스페이스만 변경
|
1
2
3
|
SQL> alter index pt_ix02 rebuild partition P202101 tablespace users online;
Index altered.
|
인덱스 재확인
|
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 index_owner for a10
col index_name for a20
col partition_name for a20
col tablespace_name for a15
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment, dip.tablespace_name
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT TABLESPACE_NAME
---------- -------------------- -------------------- -------- ------ ------------ ---------------
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS <<---
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED IMSITS
12 rows selected.
|
P202101 파티션의 테이블스페이스가 정상적으로 USERS로 변경됨
방법2. 인덱스 기본 속성 변경
alter index modify default attributes 옵션으로 변경
|
1
2
3
|
SQL> alter index pt_ix02 modify default attributes tablespace users;
Index altered.
|
인덱스 재확인
|
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 index_owner for a10
col index_name for a20
col partition_name for a20
col tablespace_name for a15
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment, dip.tablespace_name
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT TABLESPACE_NAME
---------- -------------------- -------------------- -------- ------ ------------ ---------------
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED IMSITS
12 rows selected.
|
변경전과 동일함
추가 샘플 데이터 삽입
|
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 i IN 1..100000 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 := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
COMMIT;
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
21
22
23
24
25
26
27
28
29
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
col tablespace_name for a15
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment, dip.tablespace_name
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT TABLESPACE_NAME
---------- -------------------- -------------------- -------- ------ ------------ ---------------
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED IMSITS
12 rows selected.
|
추가 데이터를 삽입했음에도 변경되지 않음
신규 파티션 추가
|
1
2
3
|
SQL> alter table PTABLE add partition p202201 values less than ('202202');
Table altered.
|
추가 샘플 데이터 삽입
|
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 i IN 1..100000 LOOP
w_ins(i).col1 := '202201'||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).col2 := ceil(dbms_random.value(1, 10000000));
END LOOP;
FORALL i in 1..100000 INSERT INTO PTABLE VALUES w_ins(i);
COMMIT;
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
21
22
23
24
25
26
27
28
29
30
|
SQL>
set lines 200 pages 1000
col index_owner for a10
col index_name for a20
col partition_name for a20
col tablespace_name for a15
select dip.index_owner, dip.index_name, dip.partition_name, dip.status, dpi.locality, dpi.alignment, dip.tablespace_name
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
INDEX_OWNE INDEX_NAME PARTITION_NAME STATUS LOCALI ALIGNMENT TABLESPACE_NAME
---------- -------------------- -------------------- -------- ------ ------------ ---------------
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED IMSITS
IMSI PT_IX02 P202201 USABLE LOCAL PREFIXED USERS <<---
13 rows selected.
|
신규 파티션이 정상적으로 USERS 테이블스페이스에 들어감
결론 :
파티션 인덱스의 테이블스페이스를 변경하고 싶은 경우 먼저 default attributes tablespace 로 인덱스의 테이블스페이스 기본 속성을 변경해주고
이후 개별 파티션 인덱스를 rebuild 해주어야함
파티션 인덱스 전체에 대해 rebuild 하면서 테이블스페이스를 옮기려는 경우 ORA-14086 에러가 발생하는데
아마 전체 인덱스를 옮기는데에 따른 위험, 부하 때문에 이 기능을 막아놓은게 아닌가 싶음
참고로 전체 리빌드를 하고 싶으면 아래와 같이 구문을 SQL로 뽑아내서 수행하면 편하게 rebuild 가능함
전체 인덱스를 한번에 rebuild 하기보다 모니터링 하면서 하나씩 수행하는걸 권장함
|
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>
select 'alter index '||index_owner||'.'||dip.index_name||' rebuild partition '||dip.partition_name||' tablespace users online;' "SQL"
from dba_ind_partitions dip, dba_part_indexes dpi
where 1=1
and dpi.index_name = dip.index_name
and dpi.table_name ='PTABLE'
order by dip.index_name, dip.partition_name;
SQL
----------------------------------------------------------------------------
alter index IMSI.PT_IX02 rebuild partition P202101 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202102 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202103 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202104 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202105 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202106 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202107 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202108 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202109 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202110 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202111 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202112 tablespace users online;
alter index IMSI.PT_IX02 rebuild partition P202201 tablespace users online;
13 rows selected.
|
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/maintenance-partition-tables-indexes.html#GUID-3DB0B6A6-FD33-4355-8BAC-7C5B4BE4C4E0
https://positivemh.tistory.com/839
https://positivemh.tistory.com/1003
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c alert log DDE flood control 기능(에러 홍수 제어) (0) | 2025.03.24 |
|---|---|
| 오라클 19c dbca 기본 템플릿과 컴포넌트(옵션) (1) | 2025.03.17 |
| 오라클 19c 언두 테이블스페이스에 일반 테이블 생성 (0) | 2025.03.10 |
| 오라클 19c bigfile과 smallfile 테이블스페이스의 extent 할당 단위 확인 (0) | 2025.03.03 |
| 오라클 19c rac 환경에서 undo 사용률 확인시 주의사항 (0) | 2025.02.18 |