Repartitioning a table on a different column

Question

We need to change the structure of our DB by re-partitioning a parent table and adding partitions to the child tables. The partitions will be based on range of a date-time field (which is the time of insertion).

The goal is to implement the changes with as little downtime as possible. So we have though on doing it on 2 phases: do the main load while the application is running and load the delta after the application has been shutdown.

I would like to know if the following steps are correct for the plan I mentioned, focusing on the part of re-partitioning the parent table. I have based on this example from Oracle: https://docs.oracle.com/database/121/SUTIL/GUID-56D8448A-0106-4B8C-85E0-11CFED8C71B1.htm

1. create a directory for the export/import of dumps

-- user: SYS

CREATE OR REPLACE DIRECTORY dumps AS '/data/tmp';
GRANT READ, WRITE ON DIRECTORY dumps TO APP_USR;

2. export the table while the application is running

# nohup expdp APP_USR/pwd parfile=export.par &

directory=dumps
dumpfile=parent_table.dmp
parallel=8
logfile=export.log
tables=parent_table
query=parent_table:"WHERE VERSION_TSP <= sysdate"

3. create a duplicate of the table with the new partitioning

CREATE TABLE "PARENT_TABLE_REPARTITIONED"
(
    -- same fields as original
    CONSTRAINT PK_TABLE_ PRIMARY KEY (foo, boo) -- with different name than original
)
    PARALLEL 8
    PARTITION BY RANGE (VERSION_TSP)
    INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION PARENT_TABLE_PARTITION_ -- with different name than original (should I?)
    VALUES LESS THAN (TO_DATE('2007-07-01', 'YYYY-MM-DD')) COMPRESS FOR OLTP
)
    ENABLE ROW MOVEMENT
;

-- with new local indexes (named differently)
CREATE INDEX IDX_VERSION_TSP_ ON PARENT_TABLE_REPARTITIONED (VERSION_TSP) LOCAL;

4. populate the re-partitioned table while the application is running

# nohup impdp APP_USR/pwd parfile=import.par &

directory=dumps
dumpfile=parent_table.dmp
parallel=8
logfile=import.log
remap_table=PARENT_TABLE:PARENT_TABLE_REPARTITIONED
table_exists_action=APPEND

5. Application shutdown (and backup the DB)

6. populate the delta

-- execute with nohup
declare
    startDate DATE;
begin
    select max(version_tsp) into startDate from PARENT_TABLE_REPARTITIONED;
    insert into PARENT_TABLE_REPARTITIONED select /*+ parallel(8) */ s.* from PARENT_TABLE s where s.VERSION_TSP > startDate;
    commit;
end;

7. switch tables

drop table parent_table cascade constraints;
rename parent_table_repartitioned to parent_table;

8. deploy the new version of the application

Is ok to export ALL from the table or should I export DATA_ONLY?

But most importantly, are those steps a valid approach?

user_7367293 · Answer

Are you on 12.2 or higher? It could be as easy as:

SQL> create table t
  2  partition by range ( created )
  3  (
  4    partition p1 values less than ( date '2020-01-01' ),
  5    partition p2 values less than ( date '2021-01-01' ),
  6    partition p3 values less than ( date '2022-01-01' ),
  7    partition p4 values less than ( date '2023-01-01' )
  8  )
  9  as select * from dba_objects
 10  where created is not null
 11  and last_ddl_time is not null;

Table created.

SQL>
SQL> create index t_ix on t ( owner ) local;

Index created.

SQL>
SQL>
SQL> alter table t modify
  2  partition by range ( last_ddl_time )
  3  (
  4    partition p1 values less than ( date '2020-01-01' ),
  5    partition p2 values less than ( date '2021-01-01' ),
  6    partition p3 values less than ( date '2022-01-01' ),
  7    partition p4 values less than ( date '2023-01-01' )
  8  ) online ;

Table altered.

Repartitioning a table on a different column

1 Answers