Oracle-L: Exp/Imp Errors: Tablespace Specific?

From: <johanna.doran_at_sungard.com>
Date: Thu, 06 Jun 2002 13:33:41 -0800
Message-ID: <F001.00476CA5.20020606133341@fatcity.com>

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:>):

Create export file of schema1
Create export file of schema2
Drop all schema objects in Schema2
Drop all schema objects in Schema3
Import Schema1 dmp file into Schema2
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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: johanna.doran_at_sungard.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Jun 06 2002 - 16:33:41 CDT