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_METADATA-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_METADATA. 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.
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 have misspelled DBMS_METADATA twice: DBMS_METATDATA and DBMS_METADTA 🙂
Oh wow, after all the years.. 🙂 Thanks!
tried to download the table-redefinition.sql, but “Oops! That page can’t be found.” 🙁
“… after all the years” 😉
Fixed, thank you. New security policies prevented .sql files to being downloaded.