Copying constraints from one schema to another

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. Received on Mon Nov 06 2000 - 14:25:21 CST