Rename operations in partitions and subpartitions are as easy as renaming tables  we just need to take care of syntax.

Rename Partitions :

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRANSACTIONS                   Y_2011
TRANSACTIONS                   Y_2013
TRANSACTIONS                   Y_2014
TRANSACTIONS                   Y_2015
TRANSACTIONS                   Y_2016
TRANSACTIONS                   Y_2017
TRANSACTIONS                   Y_2018
TRANSACTIONS                   Y_2019
TRANSACTIONS                   Y_2020

9 rows selected.

SQL> ALTER TABLE TRANSACTIONS RENAME PARTITION Y_2013 TO Y_1_2013;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRANSACTIONS                   Y_2011
TRANSACTIONS                   Y_1_2013
TRANSACTIONS                   Y_2014
TRANSACTIONS                   Y_2015
TRANSACTIONS                   Y_2016
TRANSACTIONS                   Y_2017
TRANSACTIONS                   Y_2018
TRANSACTIONS                   Y_2019
TRANSACTIONS                   Y_2020

9 rows selected.

SQL>

We can see in above steps I have renamed Y_2013  partition of transaction table to Y_1_2013.

Rename Subpartition :

Same way we can rename a subpartition too. Let us see how.

SQL>  SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='DEPT_LIST_RANGE';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
DEPT_LIST_RANGE                P2                             B1
DEPT_LIST_RANGE                P2                             B2
DEPT_LIST_RANGE                P2                             B3
DEPT_LIST_RANGE                P2                             B_MAX
DEPT_LIST_RANGE                P1                             A1
DEPT_LIST_RANGE                P1                             A2
DEPT_LIST_RANGE                P1                             A3
DEPT_LIST_RANGE                P1                             A_MAX

8 rows selected.

SQL> ALTER TABLE DEPT_LIST_RANGE RENAME SUBPARTITION A1 TO DEPT_25;

Table altered.

SQL>  SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='DEPT_LIST_RANGE';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
DEPT_LIST_RANGE                P2                             B1
DEPT_LIST_RANGE                P2                             B2
DEPT_LIST_RANGE                P2                             B3
DEPT_LIST_RANGE                P2                             B_MAX
DEPT_LIST_RANGE                P1                             DEPT_25
DEPT_LIST_RANGE                P1                             A2
DEPT_LIST_RANGE                P1                             A3
DEPT_LIST_RANGE                P1                             A_MAX

8 rows selected.

We can see in above output A1  partition of DEPT_LIST_RANGE table to dept_25.

Rename index partition :

Let us see how we can rename index partition :

SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME='TRANSACTION_IDX';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRANSACTION_IDX                Y_2011
TRANSACTION_IDX                Y_2013
TRANSACTION_IDX                Y_2014
TRANSACTION_IDX                Y_2015
TRANSACTION_IDX                Y_2016
TRANSACTION_IDX                Y_2017
TRANSACTION_IDX                Y_2018
TRANSACTION_IDX                Y_2019
TRANSACTION_IDX                Y_2020

9 rows selected.

SQL> ALTER INDEX TRANSACTION_IDX RENAME PARTITION  Y_2013 TO Y_Q_2013;

Index altered.

SQL>
SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME='TRANSACTION_IDX';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TRANSACTION_IDX                Y_2011
TRANSACTION_IDX                Y_2014
TRANSACTION_IDX                Y_2015
TRANSACTION_IDX                Y_2016
TRANSACTION_IDX                Y_2017
TRANSACTION_IDX                Y_2018
TRANSACTION_IDX                Y_2019
TRANSACTION_IDX                Y_2020
TRANSACTION_IDX                Y_Q_2013

9 rows selected.

We can see in above output Y_2013  index partition is renamed as Y_Q_2013.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.