Re: 10g ImpDP Table_Exists_Action

From: Leyi Zhang (Kamus) <"Leyi>
Date: Wed, 2 Jun 2010 17:01:16 +0800
Message-ID: <>

Hi, Jack


SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> select count(*) from t;



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 - 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 - Production
With the Partitioning, Oracle Label Security and Real Application Testing options

. . imported "KAMUS"."T"                                 904.1 KB   10000 rows
Processing 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;



SQL> select index_name,table_name from ind where table_name = 'T';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_T                          T

Kamus <>

Visit my blog for more :

On Wed, Jun 2, 2010 at 8:55 AM, rjamya <> wrote:

> 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, <> wrote:
>> 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
Received on Wed Jun 02 2010 - 04:01:16 CDT

Original text of this message