On Apr 5, 3:22 pm, "Transalp" <nick_william..._at_mentor.com> wrote:
> I'm trying to fix a schema in a database (database #1) where some of
> the foreign key constraints have system-generated names (e.g.
> SYS_C00148724) instead of the developer-chosen names they're meant to
> have. Having loaded the schema into my own DB (database #2) from a DMP
> file and renamed the constraints, I export it into another DMP file.
> When this new DMP file is imported into a new & empty schema in
> database #1, the FK constraints are created with system-generated
> names and not the names I chose when I renamed them . Those that were
> wrong initially are still wrong, and those that were OK are still OK.
> This is happening on 9.2.0.8 and 9.0.1.4. I've done this process
> several times in the past, and not had this problem. I'm running all
> imports and exports as SYSTEM. It even happens if I use the same
> database for the whole process. What's going wrong?
>
> Exporting the data from database #2 after the FK constraints have been
> given proper names:
>
> exp owner=chs rows=y indexes=y grants=y constraints=y
>
> Importing the supposedly fixed data back into database #1 (user 'chs'
> has been dropped and recreated, and has no tables at this point):
>
> imp fromuser=chs touser=chs rows=y indexes=y grants=y constraints=y
>
> TIA
Never seen this.
In order to isolate this problem, could you (assuming 9i and higher)
do the following
connect to sqlplus
issue set long <any high number>
set pagesize newpage 0 heading off feedback off
select dbms_metadata.get_ddl('TABLE',<table_name>,<schema>) from dual;
this will dump the entire table definition including constraints.
Secondly could you run
imp full=y (assuming the only user in the export) show=y
log=<filename>
or
imp full=y indexfile=<any filename>
This will show the statements actually executed.
--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Apr 05 2007 - 08:50:28 CDT
