Can I partition an already existing table in oracle?

Question

I have a table in my oradb, whitch already has a lot of rows.

I want to partition this table by month by not recreating it. So I don't want to lose the data from the table.

I want this partition type:

PARTITION BY RANGE (date_column)
INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) 
         (partition transaction_old values less than (to_date('01-01-2015','DD-MM-YYYY')));

How can I set this to an already existing table? I wanted to use the alter table function, but it does not work:

alter table mytable
    PARTITION BY RANGE (date_column)
    INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) 
             ( partition transaction_old values less than (to_date('01-01-2015','DD-MM-YYYY') ));

Is there any solution for my problem?

Answer 1

Before 19c, you cannot directly partition an existing non-partitioned table. You will need to create an interim table/new table depending on the following methods to partition:

• DBMS_REDEFINITION

1. Create a Partitioned Interim Table
2. Start the Redefinition Process
3. Create Constraints and Indexes (Dependencies)
4. Complete the Redefinition Process

• EXCHANGE PARTITION

1. Create a Partitioned Destination Table
2. EXCHANGE PARTITION
3. SPLIT PARTITION (If required to split single large partition into smaller partitions)

Answer 2

In Oracle19C I can directly partition an existing non-partitioned table(with 200 Million rows). This video Can Help you.

Code for create partition :

ALTER TABLE TableNameExistData_for_partition MODIFY

PARTITION BY RANGE (Coluumns_You_Want_parttion)
INTERVAL (valueYouWant)
(  
  PARTITION P_INITIAL_Less VALUES LESS THAN (ValueYouWant),
   .... 
);