Re: Copying constraints from one schema to another

The easiest way to duplicate a schema is to use oracle's "exp" and "imp" utility.

In article <8u7438$rsv$1_at_nnrp1.deja.com>,   dbottom_at_my-deja.com wrote:
> I need to duplicate a schema for development purposes (oracle
 7.3.3.4).
> I've manged to create all the tables in the new schema using "create
> table X.X as select * from Y.X" to create the tables.
>
> None of my primary keys were defined in this process, no constraints
> were copied over.
>
> I've been digging in the dba_constraints, dba_tables, and
> dba_tab_columns views to attempt to capture and recreate the
> constraints with no luck so far. I think if I can capture the primary
> keys and create those constraints the rest will fall into place fairly
> easily. I have all 4 types of constraints active in the original
> schema. Any ideas to help me out here?
>
> To create a script to generate the check constraints I tried this:
>
> select ' alter table X.' ||table_name|| ' add constraint '
> ||constraint_name|| ' check (' ||search_condition|| ');' from
> dba_constraints where user ='Y' and constraint_type='C';
>
> This errors out on the "search_condition" with an ORA-00932:
> inconsistant datatypes. As near as I can tell I cannot mix a long
> (search_condition is type long) with varchar datatypes. Any workaround
> for this?
>
> My next challenge will be to create synonyms for the new schema,
> currently most of the synonyms are public, am I correct in assuming
> that I will need to make these private for the first schema before
> attempting to create private synonyms for the new schema?
>
> While I feel like I've learned alot taking this approach, if there was
> a better way to do this, I'm all ears!!
>
> thanks in advance
>
> dave
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 06 2000 - 15:14:39 CST