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
