Re: Export/Import question

From: Ian Cary <cary_at_gatwick.geco-prakla.slb.com>
Date: 1997/11/14
Message-ID: <346C28AF.1ABB4A51@gatwick.geco-prakla.slb.com>#1/1

Steve Sochacki 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.

I'm fairly sure that the reason your getting this behaviour is because you are creating your indexes before applying the primary key constraint e.g.

create table test(flda number);

create unique index test_ind on test(flda);

alter table test add primary key(flda);

The problem you are experiencing with imp is beacuse the imp utility imports table definitions before creating any indexes. However the primary key constraint is actually part of the table definition and this causes the index supporting the primary key to be created along with the table. In the syntax I have shown above (which I guess is similar to what you are using) the primary constraint is not named therefore the index will take the default SYS_Cnnn name, as you are observing.
The test_ind index creation stage will actuallly FAIL in the import stage as it duplicates the one created by a constraint. Unless you have ignore=Y set in the import script you should see something like;

IMP-00015: following statement failed because the object already exists: in your import log.

You have two alternatives to solve the problem;

1. rather than issue separate index and constraint statements, combine them as follows;

     alter table test
      add constraint test_ind primary key(flda)
      using index <index storage parameters>

     This will have the effect of creating a primary key constraint AND
supporting index both with
      the name  test_ind.

2)   Don't create the primary constraint at all.
       This isn't actually as daft as it sounds as, as long as flda is defined
as not null and has a unique index
       against it, it satisfies all the requirements for a primary key.
       This can actually be an advantage on systems supporting parallelism as
indexes created by constraint
       clauses cannot use the PARALLEL option whereas explicitly created ones
can, although unless the
       speed at which indexes are created becomes an issue I personally would

recommend option 1)

Hope this helps,

Ian Received on Fri Nov 14 1997 - 00:00:00 CST