Hi All,
We have an oracle 19c db.
We have partition tables and going to enable "in-db" archive on partition tables.
One table has 700 partition and sub-partition.
Need to alter/modify hidden column as a sub-partition like { SUBPARTITION BY RANGE (ORA_ARCHIVE_STATE,DOJ) }
Sample script about partition.
------------------
PARTITION BY LIST ("CU_ID")
SUBPARTITION BY RANGE ("DOJ")
(PARTITION "M17" VALUES (17)
(SUBPARTITION "M17_Y2021_Q4" VALUES LESS THAN (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
SUBPARTITION "M17_Y2022_Q1" VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
SUBPARTITION "M17_Y2022_Q2" VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
SUBPARTITION "M17_Y2022_Q3" VALUES LESS THAN (TO_DATE(' 2032-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
etc.............
)and so on...............
-------------------
Due to huge partition and sub-partitions, it is very difficult to modify one by one.
Is there any alternate method to prepare ddl.
Please help us.
Thanks,