Partitioning a non-partitioned table using dbms_redefinition

DBMS_REDEFINTION is a really useful package for partitioning a table that is currently not partitioned. It does all the things you would want to do if you partitioned the table manually but does it is a more reliable and bug free way. The only restriction is that the table to be partitioned must have a primary key constraint. The basis steps involved in partitioning are :-

• Create an empty copy of the table partitioned up as you need it. This table will eventually become your partitioned table so it is important the table and indexes etc are all in the correct tablespaces etc.
• Check the table can be partitioned using dbms_redefintion e,.g.

BEGIN

sys.dbms_redefinition.can_redef_table('rehfacts'

,reh_orig);

END;

• Start the redefinition procedure

BEGIN

dbms_redefinition.start_redef_table(uname => 'rehfacts'

,orig_table => 'reh_orig'

,int_table => 'REH_temp'

);

END;

Among other things this turns the new partitioned copy of the table into a materialized view.

• Given that the data population of the new partitioned table by the step above may take some time, the next step is to sync any data that may have been changed or added during this processs

BEGIN

dbms_redefinition.sync_interim_table(uname => 'dwfacts'

,orig_table => 'reh_orig'

,int_table => 'REH_temp'

);

END;

• Swap the tables and finish off

BEGIN

dbms_redefinition.finish_redef_table(uname => 'rehfacts'

,orig_table => 'reh_orig'

,int_table => reh_temp'

);

END;

• Rename all the indexes and move any the foreign key dependencies.

References

http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php

http://www.dbasupport.com/forums/archive/index.php/t-27379.html