Oracle: Convert a partitioned table into an unpartitioned one

License fees can bee massive. So for some machines it’s just useless to buy Oracle Enterprise Edition, sometimes Standard Edition is simply enough. Migration to SE for a schema with partitioned tables leaves you one option: CTAS all partitioned tables and export them, reimport them on your new box and modify all DBMS_METATDATA-generated DDLs so that all constraints and indexes still fit.

For this case or for other cases it might be useful to simply “remove partitioning” from a table, preserving all dependent objects either valid (most) or running with a quick recompile (PL/SQL). The attached SQL script shows how to unpartition all tables of a given user, using DBMS_METADTA. You may want to change or remove the big loop wrapping the whole DBMS_REDEFINITION stuff to make some finer selection.

The unpartitioning works with a dummy table. It’s created “as select *” within the loop, used by the package, and dropped in the end of the iteration. The package duplicates all constraints, indexes and other metadata to the interim table’s values, and renames all that in the end. Since you created the table in the beginning “as select”, all data is already there. Finally, you have the option to synchronize original and dummy, so nothing will be lost in the end.

table-redefinition.sql

The script is rather simple, licensed under GPLv2, and considered as a starting point for future working with DBMS_REDEFINITION. This package is a very useful tool, once you discovered it’s possibilities.

If you find bugs, misses or have additions to make, feel free to contact me.

Regards
Usn




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Oracle: Convert a partitioned table into an unpartitioned one”

  1. Kjetil Nordstrand Says:

    Great stuff this one, I used it in a production enviroment and it solved my problem fast. One thing though, it misses default values on the columns.

  2. Sudhir Says:

    I need a simialr procedure to convert a non-partitioned table to partition table ( partition by list). Any sample will also work out.

Leave a Reply