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.