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-3: 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:
SchemaDefault Tablespace
***
Schema1tablespace1
Schema2tablespace2
Schema3tablespace2
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
