Re: 10g ImpDP Table_Exists_Action
Date: Wed, 2 Jun 2010 17:01:16 +0800
Message-ID: <AANLkTiktdspn1WperXuyrEz2qk7ajKj8K-q_TOegzWbL_at_mail.gmail.com>
Hi, Jack
Why not try "INCLUDE=INDEX INCLUDE=TABLE_DATA" with "TABLE_EXISTS_ACTION"?
SQL> create table t as select * from dba_objects where 1=0;
Table created.
SQL> select count(*) from t;
COUNT(*)
0
SQL> select index_name,table_name from ind where table_name = 'T';
no rows selected
[oracle_at_dbserver ~]$ impdp kamus/passwd DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDAT.DMP TABLES=kamus.t TABLE_EXISTS_ACTION=TRUNCATE INCLUDE=INDEX INCLUDE=TABLE_DATA Import: Release 11.2.0.1.0 - Production on Wed Jun 2 16:54:12 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security and Real Application
Testing options
Master table "KAMUS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "KAMUS"."SYS_IMPORT_TABLE_01": kamus/********
DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXPDAT.DMP TABLES=kamus.t
TABLE_EXISTS_ACTION=TRUNCATE INCLUDE=INDEX INCLUDE=TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "KAMUS"."T" 904.1 KB 10000 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Job "KAMUS"."SYS_IMPORT_TABLE_01" successfully completed at 16:54:36
SQL> select count(*) from t;
COUNT(*)
10000
SQL> select index_name,table_name from ind where table_name = 'T';
INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ IDX_T T
-- Kamus <kamusis_at_gmail.com> Visit my blog for more : http://www.dbform.com Join ACOUG: http://www.acoug.org On Wed, Jun 2, 2010 at 8:55 AM, rjamya <rjamya_at_gmail.com> wrote:Received on Wed Jun 02 2010 - 04:01:16 CDT
> Jack, it will be 2 step process either way. You might be better off writing
> a script using dbms_datapump which will give you most flexibility. first to
> load data on pre-created table and then again using same package create
> dependent objects.
> or
> Use two impdp sessions ...
> 1. precreate tables
> 2. load data only (first impdp session)
> 3. create indexes/constraints etc (second impdp session)
> I use the second method of back to back impdp thusly,
> 1. truncate existing tables, drop off all objects except tables alone,
> remember t_e_a doesn't override synonyms and sequences.
> 2. impdp tables,sequences,synonyms with transform=storage:n
> 3. impdp multiple times to extract plsql code in one file, index,
> constraints in another file.
> 4. use sed to adjust schema owner for plsql code (to workaround the bug,
> when remap_schema is used oracle has trouble importing plsql code/triggers
> in correct destination indexes.
> 5. use sed against create indexes to create them parallel in parallel, and
> make constraints enable novalidate.
> Yes Yes I understand the risks but the point at which i take export dump,
> application guarantees that all data is consistent so i can afford that
> luxury.
> Works fine, maybe you can do something similar.
> Raj
>
> On Fri, May 28, 2010 at 2:27 PM, <japplewhite_at_austinisd.org> wrote:
>>
>> 10.2.0.4 DBs on Linux.
>>
>> I've looked at the Utilities docs as well as the API docs and can't find a
>> way to tell impdp to ignore an already-existing table but do all the other
>> stuff like create indexes, enable constraints, etc. With the old imp
>> utility you could specify Ignore=Y and imp would not barf if the table
>> existed, but it would happily import the data, create the indexes, etc.,
>> etc.
>>
>> I'm moving a large schema that makes very extensive use of LOB columns
>> from one 10g DB to another. I want to pre-create the tables in the target
>> schema with all their LOB columns as Enable Storage in Row instead of the
>> Disable Storage in Row specification of the source schema tables.
>>
>> Using impdp with Table_Exists_Action = Skip or Truncate doesn't get the
>> other table-related objects created. The Append and Replace options are
>> definitely not what I want. Also, I don't want to have to manually
>> re-create all the indexes, etc after the impdp operation.
>>
>> Using imp with Ignore=Y does exactly what I want, but imp is slower than
>> impdp.
>>
>> Any suggestions?
>>
>> Jack C. Applewhite - Database Administrator
>> Austin I.S.D. - MIS Department
>> 512.414.9250 (wk) / 512.935.5929 (pager)
>
>
>
> --
> -----
> Best regards
> Rjamya
>
-- http://www.freelists.org/webpage/oracle-l