I currently have a project to modify several tables from non-partitioned to partition. A fairly easy, although tedious process that I documented in this blog. However, one of the stumbling points was re-creating dropped referential constraints which is required when using dbms_redefinition since the tables with references to the table being modified will point to the original non-partitioned table with the interim table name. We can’t drop the interim table (which is now the original table) so that we can rename the constraints and indexes appropriately (back to their original names). The problem wasn’t recreating these constraints but finding the most efficient method of per-creating the script. I always perform all my maintenance tasks from scripts that have been tested through all the environments. Of course, the constraints would be lost if they are dropped prior to creating a script to create them. I found a few of different approaches. I didn’t need anything specific or too complex so I turned to the internet which is almost always loaded with scripts ready to be used. I settled on the following approach by Duncan Berriman.
I did modify the script slightly to include the owner of the table. This allows me to use the script as myself for multiple schemas.