2 Replies Latest reply on Jul 2, 2020 6:36 AM by oraLaro

    Add subpartitions to existing partition table

    oraLaro

      12.2

       

      Is there an easy to add subpartitions to an existing partitioned table in 12.2

       

      This is existing table

       

      create table
      t_already_there (
         start_date        DATE,
         store_id          NUMBER,
         keep_flag         varchar2(1)
      )
      PARTITION BY RANGE (start_date)
      INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
      ( 
         PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
      ); 
      

       

       

      I want to add sub_partition on keep_flag where values would go a Y subpartition if Y or if anything else go to a default subpartition.   Could have several hundred partitions in tables Im doing this too.

       

       

      If I created from new heres how it would look and this works

      
      create table t_new
      (
         start_date        DATE,
         store_id          NUMBER,
         keep_flag         varchar2(1)
      )
      PARTITION BY RANGE (start_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
      SUBPARTITION BY LIST (keep_flag)
      SUBPARTITION TEMPLATE
         ( SUBPARTITION p_t VALUES ('Y')
         , SUBPARTITION to_others values (default)
         )
      (  
      PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
      );
      

       

       

      yes I could create new table and either redefine or insert and rename tables but interested to see can we do an alter table modify and the subpartitions.   I cant documentation that shows how.  thanks