OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 로컬 파티션 인덱스의 기본 테이블스페이스
오라클에서 파티션 테이블을 생성할수 있음, 이때 파티션 인덱스도 만들어줄수 있는데
각각 로컬, 글로벌, 논파티션 인덱스 생성이 가능함
본문에서는 글로벌 파티션 인덱스와 로컬 파티션 인덱스를 생성할때 이 인덱스들이 각각 어떤 테이블스페이스에 생성되는지 확인해봄
테스트
샘플 유저 및 테이블스페이스 생성
|
1
2
3
4
5
6
7
8
|
SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect to imsi;
SQL>
drop tablespace imsits including contents and datafiles;
create tablespace imsits datafile '/ORA19/app/oracle/oradata/ORACLE19/imsits01.dbf' size 2g autoextend off;
alter user imsi default tablespace imsits quota unlimited on imsits;
|
샘플 유저의 default 테이블스페이스 확인
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 200 pages 1000
col username for a10
select username, default_tablespace from dba_users where username = 'IMSI';
USERNAME DEFAULT_TABLESPACE
---------- ------------------------------
IMSI IMSITS
|
현재 imsi 유저의 default_tablespace는 imsits임
db의 default 테이블스페이스 확인
|
1
2
3
4
5
6
7
|
SQL>
col property_value for a20
select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------
USERS
|
현재 db 자체의 default_tablespace는 users임
imsi 유저로 접속 후 샘플 파티션 테이블 생성
|
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 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 ('20220100')
);
Table created.
|
샘플 데이터 삽입
|
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
|
SQL>
create index pt_ix01 on ptable(col1) global
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'),
partition p_max values less than (maxvalue)
);
Index 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
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>
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_IX01 P202101 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202102 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202103 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202104 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202105 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202106 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202107 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202108 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202109 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202110 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202111 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202112 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P_MAX USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED USERS
25 rows selected.
|
글로벌 인덱스는 정상적으로 imsi 유저의 default tablespace인 imsits에 저장되었지만
로컬 인덱스는 users에 저장됨
db의 default_tablespace가 users라서 여기에 생겼을수도 있으니 변경 후 테이블 및 인덱스 재생성
db의 default_tablespace 변경
*아래 명령 사용시 모든 일반유저의 default tablespace가 변경되니 모든 유저의 default tablespace를 변경하려는게 아니라면 사용을 하지않는게 좋음
참고 : 오라클 19c alter database default tablespace 명령어 ( https://positivemh.tistory.com/1222 )
|
1
2
3
|
SQL> alter database default tablespace imsits;
Database altered.
|
db의 default 테이블스페이스 재확인
|
1
2
3
4
5
6
7
|
SQL>
col property_value for a20
select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------
IMSITS
|
정상적으로 imsits로 변경됨
테이블 재생성 및 글로벌, 로컬 인덱스 재생성
(쿼리 생략)
인덱스 파티션 테이블스페이스 확인
|
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>
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_IX01 P202101 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202102 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202103 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202104 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202105 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202106 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202107 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202108 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202109 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202110 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202111 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202112 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P_MAX USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX02 P202101 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202102 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202103 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202104 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202105 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202106 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202107 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202108 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202109 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202110 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202111 USABLE LOCAL PREFIXED USERS
IMSI PT_IX02 P202112 USABLE LOCAL PREFIXED USERS
25 rows selected.
|
재생성했지만 여전히 로컬 인덱스는 users에 저장됨
원인은 테이블 생성 구문에 있음
샘플 파티션 테이블 생성구문을 보면 ptable의 tablespace가 users로 설정되어 있음
|
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 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 ('20220100')
);
Table created.
|
그래서 테이블 파티션들도 users 테이블스페이스에 저장되어 있음
파티션 별 용량 및 테이블스페이스 확인
|
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 segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a20
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
from dba_segments
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
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 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
12 rows selected.
|
글로벌 인덱스는 테이블 파티션과 관계없이 독립적으로 만드는 오브젝트이기 때문에 테이블스페이스가 imsi 유저의 기본 테이블스페이스에 저장되었고
로컬 인덱스는 테이블 파티션과 동일한 구조로 생성되기 때문에 테이블스페이스까지 테이블 파티션의 테이블스페이스를 따라간것임
테이블을 재생성 할때 이 구문을 빼게 되면 테이블 파티션과, 로컬 인덱스 모두 users 테이블스페이스가 아닌 imsits 테이블스페이스에 저장되게됨
db의 default_tablespace 원상복구
|
1
2
3
|
SQL> alter database default tablespace users;
Database altered.
|
imsi 유저의 default tablespace도 imsits로 재설정
|
1
2
3
|
SQL> alter user imsi default tablespace imsits;
User 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
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
|
SQL>
conn imsi/imsi
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
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')
);
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;
/
create index pt_ix01 on ptable(col1) global
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'),
partition p_max values less than (maxvalue)
);
create index pt_ix02 on ptable(col1, col2) local;
|
테이블 파티션 테이블스페이스 확인
|
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 segment_name for a20
col tablespace_name for a20
col partition_name for a20
col segment_type for a20
select owner, tablespace_name, segment_name, partition_name, segment_type, round(bytes/1024/1024) mb
from dba_segments
where segment_name = 'PTABLE'
and segment_type in ('TABLE', 'TABLE PARTITION','TABLE SUBPARTITION')
order by 3,4;
OWNER TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MB
---------- -------------------- -------------------- -------------------- -------------------- ----------
IMSI IMSITS PTABLE P202101 TABLE PARTITION 8
IMSI IMSITS PTABLE P202102 TABLE PARTITION 8
IMSI IMSITS PTABLE P202103 TABLE PARTITION 8
IMSI IMSITS PTABLE P202104 TABLE PARTITION 8
IMSI IMSITS PTABLE P202105 TABLE PARTITION 8
IMSI IMSITS PTABLE P202106 TABLE PARTITION 8
IMSI IMSITS PTABLE P202107 TABLE PARTITION 8
IMSI IMSITS PTABLE P202108 TABLE PARTITION 8
IMSI IMSITS PTABLE P202109 TABLE PARTITION 8
IMSI IMSITS PTABLE P202110 TABLE PARTITION 8
IMSI IMSITS PTABLE P202111 TABLE PARTITION 8
IMSI IMSITS PTABLE P202112 TABLE PARTITION 8
12 rows selected.
|
정상적으로 모두 imsits에 저장됨
인덱스 파티션 테이블스페이스 확인
|
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>
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_IX01 P202101 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202102 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202103 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202104 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202105 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202106 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202107 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202108 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202109 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202110 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202111 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P202112 USABLE GLOBAL PREFIXED IMSITS
IMSI PT_IX01 P_MAX USABLE GLOBAL PREFIXED IMSITS
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
25 rows selected.
|
정상적으로 모두 imsits에 저장됨
결론 :
로컬 파티션 인덱스는 파티션 테이블의 구조를 따라가는것 뿐만 아니라 테이블스페이스까지도 파티션 테이블의 속성을 따라감
그렇기 때문에 파티션 테이블을 생성할때는 유저의 default tablespace를 잘 보고 알맞게 tablespace절을 지정해주어야함(또는 미지정)
또는 로컬 인덱스를 생성할때 테이블의 테이블스페이스와 다르게 저장하고싶은 경우 create index 구문에 tablespace {ts명}절을 명시해줘야함
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 smallfile 테이블스페이스 shrink (0) | 2025.06.21 |
|---|---|
| 오라클 19c tde 설정 방법 및 데이터파일 암호화 테스트 (0) | 2025.06.15 |
| 오라클 19c alter database default tablespace 명령어 (0) | 2025.06.07 |
| 오라클 19c 데이터 1천만건 delete 시간 측정 (0) | 2025.06.01 |
| 오라클 19c temp 사용시 tempfile 내용 확인 (0) | 2025.05.25 |