Balaaji Dhananjayan
Jul 16, 20212 min
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