Re: Oracle XE: Import error

Jim Smith wrote:

> imp only executes the statements that exp puts into the dump file, so if
> there is a defect it is in the exp tool.

I have tried these combinations:

Export from 9i, then import into 9i => no error
Export from 9i, then import into XE => error
Export from XE, then import into XE => error

The error is independent of whether the export was done using "exp.exe" from 9i or XE. So, if the exp tool was defective, it must be defective in both 9i and XE. But then, the import into 9i would also have failed.

> If you specify imp indexfile=<filename> then indexfile will contain all the ddl. Try
> comparing the 9i and 10g dump files.

Good hint. I will have a look.

> I can't remember where I read this, and I can't remember the details,
> but I think there has been a change of behaviour between 9i and 10g with
> regard to the dropping of indexes used by constraints. I think if the
> index existed before the constraint was created then be default it is
> not dropped. That is the behaviour you are seeing here.

Exactly. So it might well be that the error is that imp.exe creates the index first and then the corresponding constraint.

In this respect, you also may be right the exp.exe is defective rather than imp.exe, if it exports the index creation command before the constraint command. And maybe this was also the case with 9i, but does not show up because 9i has a different behavior on dropping an index.

However, I still consider this a defect, because exporting and then re-importing a schema should yield the exact same result as without exporting at all, and the re-imported schema should behave exactly as it did before it was exported. Received on Wed Nov 29 2006 - 12:54:00 CST