Forum Stats

  • 3,853,654 Users
  • 2,264,249 Discussions
  • 7,905,426 Comments

Discussions

ddl script for modify existing sub-partition

N_Raj
N_Raj Member Posts: 606 Bronze Badge
edited Aug 10, 2022 2:42PM in General Database Discussions

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,

Answers

  • FrankGordon
    FrankGordon Member Posts: 47 Blue Ribbon

    Hello,


    Can you give us an example of the alter statement you would do to change a single sub-partition?

    You may be able to use sql to generate sql something like

    SELECT 'ALTER TABLE xxx MODIFY PARTITION ' ||yyy||';'

    FROM dba_tab_subpartitions

    WHERE table_name = 'ABC'

    AND zzz;


    Regards,

    Frank

  • N_Raj
    N_Raj Member Posts: 606 Bronze Badge

    Hi Frank,

    Thanks for your reply.......

    Attached sql of current alter table.

    In the 3rd line, we need to add the column ORA_ARCHIVE_STATE from  SUBPARTITION BY RANGE (DOJ)  to  SUBPARTITION BY RANGE (ORA_ARCHIVE_STATE, DOJ) 

    and

    Each sub partition need to create another one as below, 5th line

    from

    SUBPARTITION "M25_Y2011_Q4" VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 

    to

    SUBPARTITION "M25_Y2011_Q4_0" VALUES LESS THAN ('0', TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) , 

     SUBPARTITION "M25_Y2011_Q4_1" VALUES LESS THAN ('1', TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) ) 


    Thanks