Skip to Main Content
  • Questions
  • Moving Constraints from one table to another

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sumit.

Asked: June 28, 2016 - 12:34 pm UTC

Answered by: Connor McDonald - Last updated: June 29, 2016 - 4:12 am UTC

Category: Developer - Version: 11gR2

Viewed 1000+ times

You Asked

Hi asktom team,

I want to convert a NON - PARTITIONED table to LIST - partitioned table. So to do that I am doing the following:

FOR EXAMPLE take ORDERS table:

-Renaming the TABLE ORDERS to ORDERS_OLD.
-Creating a table ORDERS same as ORDERS_OLD in structure but it is LIST partitioned by say STORE_ID.
- Alter table orders exchange partition p1 with table orders_old;
-Dropping ORDERS_OLD.

But my requirement is also to migrate all the constraints (PK and FK) which were there on original ORDERS table to new - LIST partitioned ORDERS table.

Can you please help me here and tell me how can I migrate all the constraints from old ORDERS table to new ORDERS table. I want to retain the same name (for named constraints) if possible for all the constraints.

NOTE: This I have to do for more than 70+ tables. hence doing it manually will not be a good option. I want to write a procedure which takes tablename as parameter and does the job for me. currently inside this procedure I am creating LIST partitioned ORDERS table dynamically using data dictionary.

Can you suggest me some way to migrate all named constraints to from ORDERS_OLD to ORDERS (LIST partitioned) table before dropping ORDERS_OLD? Is there any simple way of doing it.

Thanks and Regards
Sumit Sharma

and we said...

I'd use DBMS_REDEFINTION

Examples here

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484

and lots more on AskTom and various internet posts.

A lot easier than writing this yourself.

Is this answer out of date? If it is, please let us know via a Review