Exp/Imp Errors: Tablespace Specific?

[johanna . doran]

Hi,

 

    I have One db instance containing three schemas to represent development, stage, and demo environments.  I occassionally need to *roll* the schemas as a release occurs.

 

    We have been using import/export to accomplish this.  In the past, another dba has done this task (so this is why I ask the question now).

 

 

    Schema1 (dev)

    Schema2 (stage)

    Schema3 (demo)

 

    In order to roll out I did the following (after backing up all three of course:>):

 

    1) Create export file of schema1   

    2) Create export file of schema2

 

    3) Drop all schema objects in Schema2

    4) Drop all schema objects in Schema3

 

    5) Import Schema1 dmp file into Schema2

    6) Import Schema2 dmp file into Schema3

 

 

Everything went fine except for step 5.  I received errors on ALL of the PK constraints :

 

. . importing table                      "ACCOUNT"      17972 rows imported
IMP-00015: following statement failed because the object already exists:
 "ALTER TABLE "ACCOUNT" ADD  CONSTRAINT "PK_ACCOUNT" PRIMARY KEY ("ACCOUNT_ID"
 "","GENERATION") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INIT"
 "IAL 8388608) TABLESPACE "AURDEV_TS" ENABLE "

 

Also, had it added one trigger twice (may NOT be a related issue) - I deleted the extra trigger and recompiled it and it was fine (but it was weird to see two with same name).

 

Also, received errors on ALL of the FKs (as the referencing PKs did not exist).

 

IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
 "ALTER TABLE "COMMODITY" ADD CONSTRAINT "FK_COMMODITY_SECURITY" FOREIGN KEY "
 "("SECURITY_ID","GENERATION") REFERENCES "SECURITY" ("SECURITY_ID","GENERATI"
 "ON") ENABLE NOVALIDATE"

 

Then, err'd out on enabling all constraints (Of course.)

 

My main question is why does it think that the object already exists?  I imported using FromUser Touser.  The only thing I can see is that the error also is containing the tablespace that is assigned to Schema1.  Schema2 and Schema3 use the same tablespace which differs from the tablespace assigned to Schema1. ie:

 

 

                        Schema            Default Tablespace

                        **********            *********************

                        Schema1            tablespace1

                        Schema2            tablespace2

                        Schema3            tablespace2

 

I am wondering if the error is inaccurate and what the error should read is that the current user is not assigned as a resource to the named tablespace.   So, I am wondering how to strip the tablespace specification out of the import. 

 

Actually I am not understanding why it is there at all as I dont believe that the storage info should be there>?

 

Any clues?

 

Thanks,

 

    Hannah