문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero 7.2.3
문서번호ㅣTADTI145
개요
운영 환경에서의 LOB 데이터의 용량 증가 또는 스토리지 분리 필요로 인해 테이블 전체 이동이 아닌 특정 Subpartition의 LOB Tablespace만 변경이 필요한 경우가 발생할 수 있습니다.
본 문서는 파티션 테이블에서 Subpartition에 포함된 LOB 세그먼트의 Tablespace만 변경하는 방법에 대한 가이드 입니다.
테스트 환경
- DB : Tibero7 7.2.3
- OS : Rocky Linux 8.10
방법
- 테스트를 위한 TABLESPACE / TABLE 생성
SQL> create tablespace DATA1 datafile 'data_01.dtf' size 1G;
Tablespace 'DATA1' created.
SQL> create tablespace NEW_LOB datafile 'lob_01.dtf' size 1G;
Tablespace 'DATA1' created.
SQL> CREATE TABLE imsi.T1
(
TRN_DAY CHAR(2),
TRN_DT CHAR(8),
TRN_TM VARCHAR(6),
TRN_ITR_GID VARCHAR(16),
TRN_TL_SEQ NUMBER,
TRN_GID VARCHAR(64),
APPLICATION VARCHAR(256),
HST_PBLS_STS VARCHAR(24),
PROC_STS VARCHAR(32),
DAT CLOB,
DAT_LENGTH NUMBER
)
TABLESPACE DATA1
LOB (DAT) STORE AS IMSIUSR_LOB1408500
(
TABLESPACE DATA1
)
PARTITION BY RANGE (TRN_DAY)
SUBPARTITION BY HASH (TRN_ITR_GID)
(
PARTITION PAR_GTW_DTL_LOG_1 VALUES LESS THAN ('02')
TABLESPACE DATA1
LOB (DAT) STORE AS
(
TABLESPACE DATA1
),
PARTITION PAR_GTW_DTL_LOG_2 VALUES LESS THAN ('03')
TABLESPACE DATA1
LOB (DAT) STORE AS
(
TABLESPACE DATA1
),
PARTITION PAR_GTW_ 2 DTL_LOG_3 VALUES LESS THAN ('04')
TABLESPACE DATA1
LOB (DAT) STORE AS
(
TABLESPACE DATA1
3 ),
PARTITION PAR_GTW_DTL_LOG_4 VALUES LESS THAN ('05')
TABLESPA 4 CE DATA1
LOB (DAT) STORE AS
(
TABLESP 5 ACE DATA1
)
);
2. PARTITION / SUBPARTITION 내용 조회
SQL> select owner,table_name,partition_name,tablespace_name from dba_tbl_partitions where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 DATA1 SQL> select owner,table_name,partition_name,lob_partition_name,lob_indpart_name,tablespace_name from dba_lob_partitions where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME LOB_PARTITION_NAME LOB_INDPART_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 _IMSI_LOB_P369300 _IMSI_LIDX_P369400 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 _IMSI_LOB_P369700 _IMSI_LIDX_P369800 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 _IMSI_LOB_P370100 _IMSI_LIDX_P370200 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 _IMSI_LOB_P370500 _IMSI_LIDX_P370600 DATA1 SQL> select owner,table_name,partition_name,subpartition_name,lob_partition_name,lob_subpartition_name,tablespace_name from dba_LOB_SUBPARTITIONS where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LOB_PARTITION_NAME LOB_SUBPARTITION_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 _IMSI_SUBP368400 _IMSI_LOB_P369300 _IMSI_LOB_SUBP369500 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 _IMSI_SUBP368600 _IMSI_LOB_P369700 _IMSI_LOB_SUBP369900 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 _IMSI_SUBP368800 _IMSI_LOB_P370100 _IMSI_LOB_SUBP370300 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 _IMSI_SUBP369000 _IMSI_LOB_P370500 _IMSI_LOB_SUBP370700 DATA1 SQL> select owner,segment_name,partition_name,segment_type,tablespace_name from dba_segments where owner='IMSI' and segment_name='IMSIUSR_LOB1408500'; OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------ ------------------------------ IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP369500 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP369900 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP370300 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP370700 LOB SUBPARTITION DATA1
3. MOVE SUBPARTITION 수행
SQL> alter table T1 move subpartition _IMSI_SUBP369000 lob (DAT) store as (tablespace NEW_LOB);
4. 변경 확인
select owner,table_name,partition_name,tablespace_name from dba_tbl_partitions where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 DATA1 select owner,table_name,partition_name,lob_partition_name,lob_indpart_name,tablespace_name from dba_lob_partitions where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME LOB_PARTITION_NAME LOB_INDPART_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 _IMSI_LOB_P369300 _IMSI_LIDX_P369400 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 _IMSI_LOB_P369700 _IMSI_LIDX_P369800 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 _IMSI_LOB_P370100 _IMSI_LIDX_P370200 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 _IMSI_LOB_P370500 _IMSI_LIDX_P370600 DATA1 select owner,table_name,partition_name,subpartition_name,lob_partition_name,lob_subpartition_name,tablespace_name from dba_LOB_SUBPARTITIONS where owner='IMSI'; OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LOB_PARTITION_NAME LOB_SUBPARTITION_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ IMSI T1 PAR_GTW_DTL_LOG_1 _IMSI_SUBP368400 _IMSI_LOB_P369300 _IMSI_LOB_SUBP369500 DATA1 IMSI T1 PAR_GTW_DTL_LOG_2 _IMSI_SUBP368600 _IMSI_LOB_P369700 _IMSI_LOB_SUBP369900 DATA1 IMSI T1 PAR_GTW_DTL_LOG_3 _IMSI_SUBP368800 _IMSI_LOB_P370100 _IMSI_LOB_SUBP370300 DATA1 IMSI T1 PAR_GTW_DTL_LOG_4 _IMSI_SUBP369000 _IMSI_LOB_P370500 _IMSI_LOB_SUBP370700 NEW_LOB select owner,segment_name,partition_name,segment_type,tablespace_name from dba_segments where owner='IMSI' and segment_name='IMSIUSR_LOB1408500'; OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME --------------- ------------------------------ ------------------------------ ------------------ ------------------------------ IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP369500 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP369900 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP370300 LOB SUBPARTITION DATA1 IMSI IMSIUSR_LOB1408500 _IMSI_LOB_SUBP370700 LOB SUBPARTITION NEW_LOB
참고사항
- 본 문서에서 안내한 Subpartition LOB Tablespace 변경 가이드는 Tibero 7.2.2 이상, Tibero 6.7.2 버전 이상 버전에서 정상 동작이 확인되었습니다.
- Tibero 7 / Tibero 6 버전 중 상기 버전 미만 환경에서는 해당 기능이 정상 동작하지 않을 수 있습니다.
- 하위 버전 환경에서는 작업 수행 전 사전 테스트 및 적용 가능 여부에 대한 검증이 필요합니다.