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?
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.