EdStevens schrieb:
> 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.
>
If you want go the way of ddl extraction/editing - then you can also
have a look on
http://www.databee.com/dt_home.htm. This tool provides
the capability of script generation taking in account dependencies for
execution order - i.e. first run pk scripts , then fk scripts etc. Also,
it can replace all the tablespace names by new ( or selectively, based
on your template ). Last time i've used it , it had some problems with
dbms_jobs, but it was over a year and since then collegue from me told
it has been fixed. I personally would prefer however import in the same
tablespace. Then you can easily generate scripts from data dictionary (
like select 'alter table '||table_name||' move new_tablespace;' from
dba_tables where owner='CARGODEV', resp. rebuild for indexes). Thus you
can move your objects within seconds into new tablespace hence you don't
have any data. By this approach you should exclude only tables with
long/long raw, which can't be moved.
Best regards
Maxim
Received on Sat Jul 15 2006 - 03:36:20 CDT
