Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> import questions

import questions

From: EdStevens <quetico_man_at_yahoo.com>
Date: 13 Jul 2006 13:10:46 -0700
Message-ID: <1152821446.320606.56060@i42g2000cwa.googlegroups.com>


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. Received on Thu Jul 13 2006 - 15:10:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US