Re: Procedure to migrate a procedure

richard.a.elliott_at_williams.com (Richard Elliott) wrote in message news:<8c132b3c.0410120523.3c1ee665_at_posting.google.com>...
> I need to automate migrations of objects from one instance to another.
> I have the db links, but could use an example of a procedure or pl/sql
> to do the migrations. If possible I would like to just set up a single
> line for each object being migrated, vs. a cut and paste. Is there a
> standard way to do this ?
> Thanks in advance for the help.
>
> I have 8.1.7 and also have Toad 8 so I can set up an Oracle Job pretty
> easily.

Note I tried to post earier using another service but after 12 hours my post has not shown up so I am trying again via google.

Richard, exactly what do you mean by migrate. Migrate would normally mean move from A to B so that it would no longer exist on A. Do you really want to replicate the object/data?

You mention using a link
If you need to create the object and copy the data you could use statements of the form: create table local_copy as select * from owner.table_name_at_remote

or if the objects will exist insert into local_table select * from ....

You can use SQL to generate the SQL to run by querying the remote dictionary for all tables belonging to a specific owner, following a specific naming pattern, or via a hard coded list as the need dictates.

Depending on security rules, network capacity, and data quantities you may want to consider exp/imp or select into delimited files and sqlldr usage. Sometimes the old ways are still best.

HTH -- Mark D Powell -- Received on Tue Oct 12 2004 - 20:16:40 CDT