Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

Guide to Changing Subpartition Lob Tablespace

Document Type | Technical Information

Category | Administration

Applicable Product Version | Tibero 7.2.3

Document Number | TADTI145

 

Overview

In an operational environment, there may be cases where it is necessary to change only the LOB Tablespace of a specific subpartition, rather than moving the entire table, due to the increase in LOB data size or the need to separate storage.

This document is a guide on how to change only the tablespace of the LOB segment included in a subpartition of a partitioned table.

 

Test Environment

  • DB: Tibero7 7.2.3
  • OS: Rocky Linux 8.10

 

 

Method

  1. Create TABLESPACE / TABLE for Testing
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. Check PARTITION / SUBPARTITION Details

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. Perform MOVE SUBPARTITION

SQL> alter table T1 move subpartition _IMSI_SUBP369000 lob (DAT) store as (tablespace NEW_LOB);

 

4. Verify the Change

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

 

Note

  • The guide for changing Subpartition LOB Tablespace described in this document has been confirmed to work properly on Tibero 7.2.2 or higher, and Tibero 6.7.2 or higher versions.
  • This feature may not work correctly in environments with Tibero 7 / Tibero 6 versions lower than the above-mentioned versions.
  • In lower version environments, prior testing and verification of applicability are required before performing the operation.

 

 

 

 

Related to