oracle partition name 변경하기

SQL> alter table p_emp rename partition emp_p1 to emp_p01;
SQL> alter table p_emp rename partition emp_p2 to emp_p02;
SQL> alter table p_emp rename partition emp_p3 to emp_p03;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P01                        2000
P_EMP                          EMP_P02                        3000
P_EMP                          EMP_P03                        4000

partition table의 partition 이름 변경시 index의 partition 이름은 같이 변경되지 않는다. 따라서 index partition이름도 같이 변경해 주어야 한다.

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p1 TO emp_p01;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p2 TO emp_p02;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p3 TO emp_p03;

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P01                        2000       USABLE
P_EMP_I              EMP_P02                        3000       USABLE
P_EMP_I              EMP_P03                        4000       USABLE

참고 : Oracle Technical iSeminar - Partition Table과 Index 관리