Re: import questions

From: EdStevens <quetico_man_at_yahoo.com>
Date: 14 Jul 2006 06:57:07 -0700
Message-ID: <1152885427.847320.240420@75g2000cwc.googlegroups.com>

Peter Sylvester wrote:
> 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

Looking at just the views for now ... none of the references are qualified by schema name, so they should all default to the current logon. I pulled out the first CREATE VIEW and ran it in sqlplus as SYSTEM, and got 'table not found'. Ran it as CARGODEV and got 'insufficient privileges'. Granted CREATE VIEW to CARGODEV but still get 'insufficient privileges'. Pulled out just the SELECT portion of the definition and it runs fine when executed as CARGODEF. I know I'm overlooking something simple but it is escaping me. Received on Fri Jul 14 2006 - 08:57:07 CDT