Re: Oracle XE: Import error

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? Received on Wed Nov 29 2006 - 10:24:24 CST