Oracle XE: Import error

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"! Received on Wed Nov 29 2006 - 03:43:17 CST