2

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

   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      
        ;

High Partition stats:

Name    Position    High Value  Tablespace  Compression Num Rows    Blocks  
P70      70         MAXVALUE    TABSPACE    DISABLED    6,544,623   79,229  

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.

Steps to my current solution to auto partition the tables using dbms_redefinition:

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      => USER,        
    orig_table => 'TEMP_TABLE',
    int_table  => 'TEMP_TABLE2');
END;
/

BEGIN
  dbms_redefinition.sync_interim_table(
    uname      => USER,        
    orig_table => 'TEMP_TABLE',
    int_table  => 'TEMP_TABLE2');
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'TEMP_TABLE',
    int_table        => 'TEMP_TABLE2',
    copy_indexes     => 0,--DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => 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 => TRUE);

BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => USER,        
    orig_table => 'TEMP_TABLE',
    int_table  => 'TEMP_TABLE2');
END;
/
DROP TABLE TEMP_TABLE2;

In lower lanes it took me more than 7 hours for DBMS_REDEFINITION.start_redef_table process(13495522 records).

I would like to know if there is any other way to automate the partitions.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
B Red
  • 33
  • 4
  • Why do you think splitting the max partition isn't feasible? Did you try? – Boneist May 13 '21 at 17:20
  • Our main concern here is keep adding the partitions manually as the data is growing day by day. Just want to see if there is any other approach so that we don't have to do it manually and eventually run out of table space one day if we forget to partition. we encountered ORA01692 unable to extend lob segment in UAT and don't want to take chance on this in production – B Red May 13 '21 at 19:06
  • Did you look into interval partitioning? You don't say if the partition key is always increasing, ... – gsalem May 13 '21 at 20:05
  • @Boneist I did try splitting the max partitions using ALTER TABLE TEMP_TABLE_A SPLIT PARTITION P70 at (7000001) INTO (PARTITION P70 TABLESPACE TABSPACE, PARTITION PMAX) ONLINE; It is still taking time and I have a question on this, by doing this the indexes both local and global will be in USABLE state, but do we need to update partitions on global index? Thanks – B Red May 14 '21 at 13:33

0 Answers0