Re: Export/Import question

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/11/13
Message-ID: <64e4ot$ql3@bgtnsc02.worldnet.att.net>#1/1

I suspect that the following things are happening:

• you do not use PRIMARY KEY or UNIQUE constraints to enforce uniqueness of columns in your tables. You enforce uniqueness directly through unique indexes.
• during export, ORACLE (for reasons I cannot fathom, possibly default behavior) re-creates these indexes by generating ALTER TABLE table_name ADD CONSTRAINTconstraint_name PRIMARY KEY/UNIQUE (column_name,...), which automatically generate unique indexes. However, since there are no such constraints defined in the data dictionary (you haven't created them - see assumption above), ORACLE assigns them default names in the format SYS_Cnnn.... These ALTER TABLE ... statements are then used by Import to create the constraints and the indexes. It is possible that Export generates both ALTER TABLE and CREATE UNIQUE INDEX statements.

To verify this hypothesis, run a "fake" import with that export file and use either the SHOW=Y LOG=file_name parameters or the INDEXFILE=file_name parameter (my personal preference in this situation is for the SHOW... option), and look at the file generated by the import for those statements. If you need more information about the SHOW and INDEXFILE Import parameters, check the Oracle7 Server Utilities or e-mail me.

Alternatively, after you do again a regular import and BEFORE DROPPING again the unique indexes, query the DBA/USER/ALL_CONSTRAINTS tables to see if there are any UNIQUE or PRIMARY constraints with the same SYS_Cnnn.. names as those indexes (constraint_type IN ('P','U')).

Hope this helps.

Michael Serbanescu

---

On 12 Nov 1997 12:34:17 GMT, "Steve Sochacki" <steve.sochacki_at_exmi01.exch.eds.com> wrote:

>This has happened to me twice now. We're Windows NT (4.0) and Oracle
>7.3.2. I'm doing a full database export from one server to another server
>that is identical (switching physical boxes). Both have the same users and
>schemas. Everything imports correctly except the primary index names. On
>the export box they're named like XP_PART_MASTER and on the import box
>they're all changed to the defaults like SYS_C0045. This happened before
>when I tested and I ended up droppping and re-creating the primary indexes
>with the correct names. I'm relatively new at this so any suggestions
>would be appreciated.
>
Received on Thu Nov 13 1997 - 00:00:00 CST