Convert Non Partitioned table into Partitioned

It happens to be DBAs sometime end up converting non partitioned table into partitioned table based on new use cases or requirements whether to split the table based on Range for future maintenance operations or to Hash it for better performance, and what not.

However it is DBAs responsibility to seamlessly convert a normal table into a partitioned one (or vice versa) without affecting the application or requesting for a downtime to perform the same.

Let's see how to easily get this done..

--Create a Sample table:

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500)
 );

SQL> insert into INVOICES values ('12345', systimestamp,'This is a test');

1 row created.

SQL> commit;

Commit complete.

--Verify if the table can be redefined

begin
dbms_redefinition.can_redef_table
 (uname=>'SCHEMA_NAME',
 tname=>'INVOICES',
 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

PL/SQL procedure successfully completed

--(This means the table is eligible for Redefinition)

Follow below steps or format and put this into a script as you want..

--Step 1: 
SET SERVEROUTPUT ON
SET LINESIZE 100
SET VERIFY OFF
SET FEEDBACK ON
SET TERMOUT OFF
SET TIMING ON
spool invoices_refer.log

-- Step2: Create interim table 

CREATE TABLE invoices_int
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500)
 )
PARTITION BY RANGE ("INVOICE_DATE") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
(PARTITION "P0"  VALUES LESS THAN (TIMESTAMP' 1900-01-01 00:00:00')) ;

--Step3: Start Redefinition

-- Use this only if your table is big in size and wanted to increase the parallelism

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
 (uname=>'SCHEMA_NAME',
 orig_table=>'INVOICES',
 int_table=>'INVOICES_INT',
 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

Sync table is required to load the delta after the redefinition was started

--Step4: SYNC Interim table

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE
 (uname=>'SCHEMA_NAME',
 orig_table=>'INVOICES',
 int_table=>'INVOICES_INT');
end;
/

Copying Dependents. Kindly read through Oracle documentation as which dependants to copy.

I am intentionally including copy_constraints to generate an error.

--Step5: Copy Dependents

SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'SCHEMA_NAME',
    orig_table        => 'INVOICES',
    int_table         => 'INVOICES_INT',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
    copy_triggers     => TRUE,  -- Default
    copy_constraints  => TRUE,  -- Default
    copy_privileges   => TRUE,  -- Default
    ignore_errors     => TRUE, 
    num_errors        => l_num_errors); 
  DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

-- Use below script to find errors. You should see one which is intentional.

set lines 200
set long 99999999
col object_type for a10
col object_owner for a20
col base_table_name for a20
select object_type, object_owner, base_table_name, ddl_txt from dba_redefinition_errors;

Just in case if you have to change your mind and cancel the Redefinition process (Only if needed).

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'SCHEMA_NAME', orig_table=>'INVOICES', int_table=>'INVOICES_INT'); -- reference

--Step6: Finish Redefinition

begin
dbms_redefinition.finish_redef_table
 (uname=>'SCHEMA_NAME',
 orig_table=>'INVOICES',
 int_table=>'INVOICES_INT');
end;
/  

Now see if you have successfully converted the table using below query.

select owner, table_name, PARTITIONING_TYPE from dba_part_tables where table_name='INVOICES';

Hope this helps you. Cheers!!

Regards,

Balaaji Dhananjayan