SQL Performance (MOSC)

MOSC Banner

None-Partition Table to Partition Table conversion - Oracle 19c EE/RAC/AIX

Hi ,

I have none-partitioned table , that I would like to convert to a partition table. This table has about 150M rows, also has a BLOB column. The table data segment is like 400 GB and LOB segment is 1.5 TB, so total is like almost 2 TB.

I tried the latest feature to convert this table to a partition table using the alter table partition, but it was not able to do it over 24 hrs.

alter table OPEB_A modify
     PARTITION BY RANGE (CREATED) INTERVAL (NUMTODSINTERVAL(7, 'DAY'))
  ( PARTITION OPEB_A_01 VALUES LESS THAN (TO_DATE('2021-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_02 VALUES LESS THAN (TO_DATE('2021-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_03 VALUES LESS THAN (TO_DATE('2021-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_04 VALUES LESS THAN (TO_DATE('2021-10-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_05 VALUES LESS THAN (TO_DATE('2021-10-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_06 VALUES LESS THAN (TO_DATE('2021-11-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_07 VALUES LESS THAN (TO_DATE('2021-11-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_08 VALUES LESS THAN (TO_DATE('2021-11-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_09 VALUES LESS THAN (TO_DATE('2021-11-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION OPEB_A_010 VALUES LESS THAN (TO_DATE('2021-12-04

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center