Re: Oracle XE: Import error

On Nov 29, 10:43 am, "Jens Lenge" <spam..._at_gmx.net> wrote:
> Hello world,
>
> some time ago I came across an export/import issue when moving schemas
> from Oracle 9i to 10g XE: After the import, the link between a PK/UK
> constraint and the associated index with the same name is broken.
> Therefore, deleting the constraint does no longer also remove the
> index.
>
> I first believed in an 9i export error, but it rather seems an XE
> import issue, because it does not matter if the schema was exported
> from 9i or XE. An import error is also more likely because Sybrand
> Bakker has told me that PK related indices are not exported at all, but
> newly created after the import.
>
> I have set up a small example to reproduce the error below.
>
> Can you reproduce the error with Oracle 10g XE?
> Is this a known bug?
>
> >From my tests:* Oracle 10g XE 10.2.0.1 shows the error.
> * Oracle 9i does not show the error.
> * I am not sure about the full version of 10g (cannot test).
>
> Jens
>
> Example:
> --------------------
> 1. Create two schema owners "test1" and "test2" with the passwords
> "pw1" and "pw2".
>
> 2. Connect as "test1" and create an example table:
>
> create table TestTable (testid smallint constraint pk_test primary
> key);
>
> => Along with the constraint "pk_test", an associated index "pk_test"
> is automatically created.
>
> 3. Export the "test1" schema from the command line:
>
> exp.exe test1/pw1 file=TestExport.dmp
>
> 4. Import the schema to "test2" (where "pwsys" is the "system"
> password):
>
> imp.exe system/pwsys file=TestExport.dmp fromuser=test1 touser=test2
>
> => Now "test1" and "test2" contain the same schema, with the only
> difference that "test2" was imported.
>
> 5. Connect as "test1" and drop the constraint "pk_test":
>
> alter table TestTable drop constraint pk_test;
>
> => Along with the constraint "pk_test", the associated index "pk_test"
> is automatically deleted.
>
> 6. Connect as "test2" and drop the constraint "pk_test":
>
> alter table TestTable drop constraint pk_test;
>
> => ERROR: The associated index "pk_test" is NOT deleted along with the
> constraint "pk_test"!

I seem to recall somone (it might have been you) reported the same problem.
It struck me (and I didn't respond in that thread), that he was issuing alter table drop constraint <bla>
instead of
alter table drop primary key
There might be a difference, there might be not. Anyway in 9i and higher a PK constraint can be implemented by a non-unique index.
Also, you would need to
imp show=y file=<any suitable filename>
to dig up the exact sequence of events.

-- 
Sybrand Bakker
Senior Oracle DBA