Re: 10g ImpDP Table_Exists_Action
Date: Tue, 1 Jun 2010 20:55:59 -0400
Message-ID: <AANLkTikxiAVNYbpWlCFFlJSgEpqKzsmdRPAoZkQwItVh_at_mail.gmail.com>
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-lReceived on Tue Jun 01 2010 - 19:55:59 CDT