Re: Oracle XE: Import error

In message <1164817463.995404.29050_at_j44g2000cwa.googlegroups.com>, Jens Lenge <spampot_at_gmx.net> writes
>sybrandb wrote:
>
>> 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.
>
>I just tried using "alter table TestTable drop primary key" instead.
>
>The behavior is exactly the same:
>The error occurs if and only if the schema has been imported.
>
>> Anyway in 9i and higher a PK constraint can be implemented by a
>> non-unique index.
>
>Interesting. I thought a PK must necessarily be unique.
>
>However, the behavior should be the same before and after the import,
>shouldn't it?
>If the index was supposed to persist, it should persist in both cases.
>So I still believe in an import bug.
>
>> Also, you would need to imp show=y file=<any suitable filename>
>> to dig up the exact sequence of events.
>
>When I use" show=y" in my example, I get:
>
><---Log Start--->
>
>. Import TEST1's Objekte in TEST2
> "BEGIN "
>
>"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
> "CURRENT_SCHEMA'), export_db_name=>'XE', inst_scn=>'1231834');"
> "COMMIT; END;"
> "ALTER SESSION SET CURRENT_SCHEMA= "TEST2""
> "CREATE TABLE "TESTTABLE" ("TESTID" NUMBER(*,0)) PCTFREE 10 PCTUSED
>40 INIT"
> "RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
>1 BUF"
> "FER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
>. . Überspringen von Tabelle "TESTTABLE"
>
> "CREATE UNIQUE INDEX "PK_TEST" ON "TESTTABLE" ("TESTID" ) PCTFREE 10
>INITRA"
> "NS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
>BUFFE"
> "R_POOL DEFAULT) TABLESPACE "USERS" LOGGING"
> "ALTER SESSION SET CURRENT_SCHEMA= "TEST2""
> "ALTER TABLE "TESTTABLE" ADD CONSTRAINT "PK_TEST" PRIMARY KEY
>("TESTID") US"
> "ING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
>FREELIST"
> "S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
>ENABL"
> "E "
>Import erfolgreich ohne Warnungen beendet.
>
><---Log End--->
>
>I am not familiar with such a log, but it seems that the index is
>created before the PK contstraint. Maybe that is the cause why the PK
>and the index are no longer linked to each other after the import.
>
>As I have not found a parameter for imp.exe that controls the creation
>of indices, I would say that the imp tool of XE is defective in this
>respect. Am I wrong?
>

imp only executes the statements that exp puts into the dump file, so if there is a defect it is in the exp tool. If you specify imp indexfile=<filename> then indexfile will contain all the ddl. Try comparing the 9i and 10g dump files.

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.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>