Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Copy all list of constraints into New schema from Old Schema

Sekhar ReddyMar 22 2016 — edited Mar 22 2016

Hi All,

I have around X number of constraints in Schema: OLD and i have recently created a new schema : NEW similar to  OLD schema. Without any Data, Constraints, Indexes.

Now, I want to create all X number of constraints in NEW schema with a single script.

Is there any approach.

Thanks in Advance.

Comments

Manik Mar 22 2016 — edited on Mar 22 2016

Just try running this : (SCOTT being your source schema from which constraints have to be copied)

Untested !

SELECT    'select dbms_metadata.get_ddl(''CONSTRAINT'','''
       || CONSTRAINT_NAME
       || ''',''SCOTT'')||'';'' ddl_text FROM DUAL;'
  FROM DBA_CONSTRAINTS
WHERE  owner ='SCOTT'
and constraint_name not like '%=%'
and constraint_name not like 'SYS_C%'
/

Cheers,

Manik.

Nimish Garg Mar 22 2016

get_dll might give you a lot of extra information which you might not need.

Do have a look on DBMS_METADATA.SET_TRANSFORM_PARAM to strip down the extra details

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1000135

Manik Mar 22 2016

Thanks Nimish bhai, will check that

Cheers,

Manik.

unknown-7404 Mar 22 2016
I have around X number of constraints in Schema: OLD  and i have recently created a new schema : NEW similar to  OLD schema. Without any Data, Constraints, Indexes.

Now, I want to create all X number of constraints in NEW schema with a single script.

Yes

1. check out the DDL for the constraints from your version control system

2. put the DDL into a 'single script'

3. execute the script

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 19 2016
Added on Mar 22 2016
5 comments
600 views