title: Auto partition the existing tables that we used to manually add the partitions by splitting the max partition in Oracle
body: <p>I need suggestions on adding partitions on to the existing manually partitioned tables.
  My case:
  version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  Existing table data type: total rows: 13495522</p>
  <pre><code>   CREATE TABLE NJIISPROD.TEMP_TABLE
             (  ID_SEQ                 NUMBER(12),
                USER_CREATED           NUMBER(12) CONSTRAINT NN_TEMP_TABLE_USR_CRTD NOT NULL,
                DATE_CREATED           DATE CONSTRAINT NN_TEMP_TABLE_DATE_CRTD NOT NULL,
                USER_UPDATED           NUMBER(12) CONSTRAINT NN_TEMP_TABLE_USR_UPTD NOT NULL,
                DATE_UPDATED           DATE CONSTRAINT NN_TEMP_TABLE_DATE_UPTD NOT NULL,
                USER_DELETED           NUMBER(12),
                DATE_DELETED           DATE
              )
              PARTITION BY RANGE (ID_SEQ)
              (  
                PARTITION P1 VALUES LESS THAN (100001),  
                PARTITION P2 VALUES LESS THAN (200001),  
                PARTITION P70 VALUES LESS THAN (MAXVALUE)
              )
              MONITORING
              ENABLE ROW MOVEMENT      
          ;
  </code></pre>
  <p>High Partition stats:</p>
  <pre><code>Name    Position    High Value  Tablespace  Compression Num Rows    Blocks  
  P70      70         MAXVALUE    TABSPACE    DISABLED    6,544,623   79,229  
  </code></pre>
  <p>We used to manually add the partitions by splitting the max partition but with the number of rows in max partition, doesn't look it is a feasible solution.
  This lead us to the use of dbms_redefinition package.</p>
  <p>Steps to my current solution to auto partition the tables using dbms_redefinition:</p>
  <pre><code>CREATE TABLE NJIISPROD.TEMP_TABLE_A
  (
    ID_SEQ                 NUMBER(12),
    USER_CREATED           NUMBER(12) ,
    DATE_CREATED           DATE ,
    USER_UPDATED           NUMBER(12),
    DATE_UPDATED           DATE ,
    USER_DELETED           NUMBER(12),
    DATE_DELETED           DATE
  )
  PARTITION BY RANGE (ID_SEQ) INTERVAL (100000)
  (  
  PARTITION P1 VALUES LESS THAN (100001),
  PARTITION P2 VALUES LESS THAN (200001)  
  )
  ENABLE ROW MOVEMENT;
  
  EXEC DBMS_REDEFINITION.can_redef_table(USER, 'TEMP_TABLE');
  
  ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
  ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
  
  BEGIN
    DBMS_REDEFINITION.start_redef_table(
      uname      =&gt; USER,        
      orig_table =&gt; 'TEMP_TABLE',
      int_table  =&gt; 'TEMP_TABLE2');
  END;
  /
  
  BEGIN
    dbms_redefinition.sync_interim_table(
      uname      =&gt; USER,        
      orig_table =&gt; 'TEMP_TABLE',
      int_table  =&gt; 'TEMP_TABLE2');
  END;
  /
  
  SET SERVEROUTPUT ON
  DECLARE
    l_errors  NUMBER;
  BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
      uname            =&gt; USER,
      orig_table       =&gt; 'TEMP_TABLE',
      int_table        =&gt; 'TEMP_TABLE2',
      copy_indexes     =&gt; 0,--DBMS_REDEFINITION.cons_orig_params,
      copy_triggers    =&gt; TRUE,
      copy_constraints =&gt; TRUE,
      copy_privileges  =&gt; TRUE,
      ignore_errors    =&gt; FALSE,
      num_errors       =&gt; l_errors,
      copy_statistics  =&gt; FALSE,
      copy_mvlog       =&gt; FALSE);
      
    DBMS_OUTPUT.put_line('Errors=' || l_errors);
  END;
  /
   select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
  --create indexes manually 
  
  EXEC DBMS_STATS.gather_table_stats(USER, 'TEMP_TABLE2', cascade =&gt; TRUE);
  
  BEGIN
    dbms_redefinition.finish_redef_table(
      uname      =&gt; USER,        
      orig_table =&gt; 'TEMP_TABLE',
      int_table  =&gt; 'TEMP_TABLE2');
  END;
  /
  DROP TABLE TEMP_TABLE2;
  </code></pre>
  <p>In lower lanes it took me more than 7 hours for DBMS_REDEFINITION.start_redef_table process(13495522 records).</p>
  <p>I would like to know if there is any other way to automate the partitions.</p>