Re: Importing just one table to another schema using imp

Walt <walt_askier_at_yahoo.com> wrote in message news:<4076AE88.EEC9EC94_at_yahoo.com>...
> Anurag Varma wrote:
> > <peltier3_at_bellsouth.net> wrote
> > > "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> > --snip--
> > statement like this:
> > > > create or replace trigger trig_name on schema1.tablename
> > > >
> > > > instead of
> > > >
> > > > create or replace trigger trig_name on tablename ...
> > > >
> > > > In that case the import will create the trigger as you specified it
> (schema1.tablename). I believe,
> > > > this is what you are seeing .. and I do not think this could be called a
> > > bug.
>
> > > Fyi... a search in Metalink on 1502847 will show 3 entries where this number
> > > is referred to as a bug. All the entries deal with this problem.
> >
> > Well I did search the number you noted on Metalink .. and its been referred to as
> > an enhancement request. That too an unpublished one.
> > :) Not a bug!
>
> This definitely sheds some light on it, and my situation is exactly as
> described. If you specify the schema in the trigger create statement,
> imp will import the trigger exactly as specified with the effect that
> you have two triggers acting on the table in the old schema and none on
> the new.
>
> At a high level, I'm trying to create a copy of something without
> affecting the original. The way things are set up this is impossible to
> do with imp. Bug or not, it's undesirable behavior.

How about importing first with ROWS=N to create the metadata, disable or drop the trigger, then import the rows?

>
> I'd have much less of a problem with this if it were possible to prevent
> the importation of the trigger. (Yes, I know I can prevent it by not
> including triggers on export, but I'm working with a full export here so
> that's not really an option.)
>
> Thanks for your help.
>
> BTW, the (partial) deal with indexes is that unique constraint indexes
> are always imported whether you specify inedxes=n or not. I don't
> understand why it's that way, but at least it's documented.
>
> The curious part is that I'm picking up index creation statements for
> unrelated tables on a third schema. This seems to be due to the fact
> that these indexes are owned by schema1 (they shouldn't be, but they
> are). I haven't found any documentation that explains this behavior.

exp/imp has evolved over time to handle new features, so has some things that may or may not make sense. If you are using TABLES=() and getting indices for unrelated tables, that might very well be a bug. Otherwise, ownership changes of objects with fromuser and touser should cause all indices owned by the fromuser to be picked up.

You need to be very careful about stuff like this, especially setting a default tablespace for the user. Otherwise, you can wind up with things created in the system tablespace, a Very Bad Thing.

>
> Maybe I should stop messing around with show=y - I'd never figure out
> this stuff was going on if I just crossed my fingers and hit go. (c:

It's good to experiment to understand how things work. Otherwise you wind up believing all sorts of incredible tales. That's why I encourage looking at (but not touching!) dmp and data files with OS utilities. It can be quite enlightening as so much is plain old text data.

jg

--
@home.com is bogus.
At least he got the baseball stadium built. 
http://www.signonsandiego.com/uniontrib/20040408/news_1b8prgrn.html