How to update the values in a subpartition table in oracle which has a lot of data?

Question

This is my exisitng table:

PARTITION BY RANGE ("D0_CRD_RPT_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
  SUBPARTITION BY LIST ("TA_SRC_STM_CD","TA_OBJ_TP_CD") 
  SUBPARTITION TEMPLATE ( 
    SUBPARTITION "SPTN_PRFL_LN_NL_LOC_AR" VALUES ( ( 'PRFL_LN_NL', 'LOC_AR' ) ), 
    SUBPARTITION "SPTN_PRFL_LN_NL_LOAN_AR" VALUES ( ( 'PRFL_LN_NL', 'LOAN_AR' ) ), 

Now i want to update the subpartition to

PARTITION BY RANGE ("D0_CRD_RPT_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
  SUBPARTITION BY LIST ("TA_SRC_STM_CD","TA_OBJ_TP_CD") 
  SUBPARTITION TEMPLATE ( 
    SUBPARTITION "SPTN_PRFL_LN_NL_LOC_AR" VALUES ( ( 'PRFL_LN_NL', '**AR_LOC_AR**' ) ), 
    SUBPARTITION "SPTN_PRFL_LN_NL_LOAN_AR" VALUES ( ( 'PRFL_LN_NL', '**AR_LOAN_AR**' ) ), 

How can i do it with a lot of data in this table?

tried to work with creating temp tables which is a long process, any simpler methods?

Answer 1

You should be able to do it with a simple ALTER TABLE, which can also be done ONLINE, eg

SQL> create table t
  2  ( D0_CRD_RPT_DT date,
  3    TA_SRC_STM_CD varchar2(100),
  4    TA_OBJ_TP_CD varchar2(100 )
  5  )
  6  PARTITION BY RANGE ("D0_CRD_RPT_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  7    SUBPARTITION BY LIST ("TA_SRC_STM_CD","TA_OBJ_TP_CD")
  8    SUBPARTITION TEMPLATE (
  9      SUBPARTITION "SPTN_PRFL_LN_NL_LOC_AR" VALUES ( ( 'PRFL_LN_NL', 'LOC_AR' ) ),
 10      SUBPARTITION "SPTN_PRFL_LN_NL_LOAN_AR" VALUES ( ( 'PRFL_LN_NL', 'LOAN_AR' ) )
 11  )
 12  ( partition p1 values less than ( date '2025-01-01' )
 13  );

Table created.

SQL>
SQL> alter table t modify
  2  PARTITION BY RANGE ("D0_CRD_RPT_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  3    SUBPARTITION BY LIST ("TA_SRC_STM_CD","TA_OBJ_TP_CD")
  4    SUBPARTITION TEMPLATE (
  5      SUBPARTITION "SPTN_PRFL_LN_NL_LOC_AR" VALUES ( ( 'PRFL_LN_NL', '**AR_LOC_AR**' ) ),
  6      SUBPARTITION "SPTN_PRFL_LN_NL_LOAN_AR" VALUES ( ( 'PRFL_LN_NL', '**AR_LOAN_AR**' ) )
  7  )
  8  ( partition p1 values less than ( date '2024-01-01' )
  9  ) online;

Table altered.