Re: import questions

From: EdStevens <quetico_man_at_yahoo.com>
Date: 14 Jul 2006 05:35:04 -0700
Message-ID: <1152880503.939567.260450@h48g2000cwc.googlegroups.com>

Maxim Demenko wrote:
> EdStevens schrieb:
> > 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.
> >

>

> This is normal behaviour ( was several times discussed by asktom, on
> this list etc.) - all tables which consist of more than one segment (
> i.e. partitioned tables, iot, tables with lobs - as in your case , etc.)
> will not imported if in the target the not exists tablespaces from
> source for relevant segments ( or they exists, but target user don't
> have quota on it). The simpliest workaround for it - get ddl for such
> segments, edit it ,create them in new tablespace and import with ignore=y.
>
> Best regards
>
> Maxim

That got it. Thanks for th lead. Received on Fri Jul 14 2006 - 07:35:04 CDT