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 -> Re: import questions

Re: import questions

From: EdStevens <quetico_man_at_yahoo.com>
Date: 14 Jul 2006 15:04:01 -0700
Message-ID: <1152914641.348534.44670@m79g2000cwm.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 me past the first problem, the table with the CLOB. Then I had a bunch of views, FKs, and procedures that weren't importing. Digging deeper, I found the FK's were failing because the PK's they referenced were failing. The PK defs were failing because they had storage clauses that referenced the TS used by the original schema, and am specifically preventing the new schema from having objects in the TS used by the old schema.

So I decided to take the same approach ... export rows=n, and message the resulting .dmp file. Lot's of problems. First, there are a couple of thousand tables, with their indexes and constraints, and a bunch of triggers, so manually fixing it up is not an option. Can't just globally add the semicolons to the end of every line because SOME of the DDL commands are spread across multiple lines. Again, too many to fix by hand. And there are some other things in there that appear to be markers used by IMP but are not valid SQL statements -- at least one for every table.

Next, I turned to dbms_metatdata. Have never used it so did a lot of reading, and it appears that a lot of people find it to be insufficient as well. Found some comments about this from Niall on his blog, and a LOT of discussion on AskTom. Appear to be a lot of problems again with long lines wrapping/breaking in bad places, no command terminators, etc. Also I don't know the password of the schema I'm exporting, so must do the export as system; can't seem to get that to work at all.

So I'm ready for a fresh look at this if anyone can throw me a bone. To restate the problem: replicate one schema to another in the same 9.2 database, with the new schema useing a new tablespace dedicated to it. Received on Fri Jul 14 2006 - 17:04:01 CDT

Original text of this message

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