Re: Oracle XE: Import error

Jens Lenge wrote:
> 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?

indexes=n will skip index creation. Primary key constraints are created regardless.
It looks like you didn't originally use
alter table blah add constraint ... primary key *using index*, because the index you complain about is just a standalone index. Which explains, as constraint creation and index creation is now a 2 step process, why it wasn't dropped.
I would call it a 'feature', not a bug.
I usually peruse the release notes of a new release to spot such peculiarities, so I am prepared for disaster.

-- 
Sybrand Bakker
Senior Oracle DBA