Re: import questions

EdStevens wrote:
> Platform: Oracle 9.2 on Solaris 8
>
> Database has a schema, FLEETTEAMDEV. Task is to duplicate it to a new
> schema, CARGODEV.
>
> As SYSTEM, I took an export with OWNER=FLEETTEAMDEV
>
> Created new tablespace CARGODEV. Created new user CARGODEV with default
> tablespace of CARGODEV. (all users have their own default ts, named
> the same as the user).
>
> Ran imp with FROMUSER=FLEETTEAMDEV and TOUSER=CARGODEV.
>
> On the first try, all of the tables created under CARGODEV, but in
> FLEETTEAMDEV's tablespace. I found MetaLink Note:1012307.6, which
> supposedly took care of the problem by revoking UNLIMITED TABLESPACE
> from CARGODEV and making sure he had zero quota on the FLEETTEAMDEV ts.
>
> Now, there is one single table trying to be created in the wrong ts.
> Here's the relevant part of the import log: (ran import as CARGODEV)
>
> ========
> Warning: the objects were exported by SYSTEM, not by you
>
> import done in US7ASCII character set and AL16UTF16 NCHAR character set
> import server uses WE8ISO8859P1 character set (possible charset
> conversion)
> . . importing table "ACTIONS" 6282 rows
> imported
> . . importing table "ACTION_CATEGORIES" 31 rows
> imported
> . . importing table "ACTION_TYPES" 480 rows
> imported
> . . importing table "ACTIVE_PART_INTERFACE" 19055 rows
> imported
> . . importing table "ACTIVE_SERVICE_INTERFACE" 15 rows
> imported
> . . importing table "ADDRESSES" 3398 rows
> imported
> . . importing table "ADDRESS_TYPES" 6 rows
> imported
> . . importing table "ADMINISTRATION" 1 rows
> imported
> . . importing table "ALERTS" 0 rows
> imported
> . . importing table "ALERT_CLIENT_TYPES" 0 rows
> imported
> IMP-00017: following statement failed with ORACLE error 1950:
> "CREATE TABLE "ALERT_QUEUE" ("ALERT_QUEUE_ID" NUMBER NOT NULL ENABLE,
> "FROM_"
> "EMAIL" VARCHAR2(100) NOT NULL ENABLE, "TO_EMAIL" VARCHAR2(1000) NOT
> NULL EN"
> "ABLE, "SUBJECT" VARCHAR2(100), "MESSAGE" CLOB NOT NULL ENABLE,
> "STATUS_FLAG"
> "" VARCHAR2(1) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1
> MAXTRANS 2"
> "55 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
> "FLEETTE"
> "AMDEV" LOGGING NOCOMPRESS LOB ("MESSAGE") STORE AS (TABLESPACE
> "FLEETTEAMD"
> "EV" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE
> STORAGE(INITIAL"
> " 65536 FREELISTS 1 FREELIST GROUPS 1))"
> IMP-00003: ORACLE error 1950 encountered
> ORA-01950: no privileges on tablespace 'FLEETTEAMDEV'
> . . importing table "ATTRIBUTE_TYPES" 10 rows
> imported
> . . importing table "AUD$ACTIONS" 0 rows
> imported
> ========
>
> All of the tables were successfully created and loaded except this one,
> which insists on trying to create into the tablespace from which it was
> exported. All of the other tables were also exported from this
> tablespace, but went quite nicely into the new tablspace under the new
> user schema. Very strange.
>
> Later, there were also a bunch of "CREATE FORCE VIEW" statements than
> failed with compile errors; create FK constraints that failed with
> "no matching unique or primary key"; and a bunch of triggers
> 'created with compile errors'.
>
> Does anyone have an explanation and/or fix for this one table trying to
> go to the wrong ts?
>
> Are there some well-known (except by me) gotcha's to address the
> other errors in a case like this - of importing FROM usera TO userb?
>
> Thanks.
>

If the triggers and views were originally created referencing the original schema name, this does not get changed in the fromuser/touser import. You'll need to dump the source for these things and edit the name out.

--Peter Received on Thu Jul 13 2006 - 16:17:26 CDT