Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> import questions
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)
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