Re: Method to replicate certain tables

"Mark Bole" <makbo_at_pacbell.net> wrote in message news:9saPe.643$sV7.469_at_newssvr21.news.prodigy.com...
> MSUDad_at_gmail.com wrote:
>
>> I have a database (Oracle 9i) with approximately 100 tables in our
>> production facility and need to replicate most but not all of the
>> tables to a database at our preproduction facility. We can't overlay
>> all of the tables because certain preprod tables contain important info
>> different from Prod. I need to perform this replication on a monthly
>> basis and it must be done as quickly as possible.
>>
>> Doing a simple export/import of each table has proven to be very
>> tedious and takes much too long.
>>
>> Any suggestions on methods to perform this quickly?
>>
>
> Since, according to what you have stated, you can segregate the tables to
> be updated from those not to be updated (no referential integrity
> constraints), then simply move the tables to be updated to separate
> tablespace(s) and transport the tablespace(s).
>
> You didn't mention platform or specific version information, so we'll
> assume this is possible in your environment.
>
> BTW, what part of exp/imp is very tedious or takes much too long? The data
> load? The index creation? The constraint enabling? The statistics
> gathering? Maybe exp/imp isn't as bad as you think... provide more
> details to support your claim or discover if there isn't a better way...
>
> -Mark Bole
>
>

The disadvantage of transportable tablespaces is that it puts your source tablespace in read-only mode during the transport, while exp can be done online (with consistent=Y and big enough undo tablespace). Also, exp/imp can be automised with shell scripts (that run at night for example). Another possibility would be to create a dblink from pre-prod to prod and do something like :

INSERT INTO <preprod table>
SELECT * FROM table_at_prod

Indexes can be disabled during this operation and rebuild in parallel with nologging afterwards. Statistics can also be generated in parallel.

Matthias Received on Thu Aug 25 2005 - 07:49:55 CDT