Re: Running import datapump over a database link.

Even if his multistage process does not improve end-to-end elapsed time, doing the EXPORT portion to (fast) local disk is likely to decrease the duration of the export phase of the procedure, and therefore reduce the likelihood of hitting an ORA-1555. You won't get any guarantees, though, just "improved prospects". But the critical factor in avoiding an ORA-1555 is surely the elapsed time of the export, not the elapsed time of the export+import.

It took me a while to work out what Mladen was thinking of with MVIEWs -- maybe it was just a parsing problem on my part. If you create an MVIEW of the "problem" table *in the source database*, you can then export that. But you will probably want to convert it to a table, first. In fact, you might as well just create a copy of the data with CTAS. Either way, the idea is (I think) that you can then export without needing to worry about UNDO, because the cloned table is not being modified while the export runs.

If you want guarantees, then open the source database in READ-ONLY mode. Of course, I very much doubt that is an option. Cloning the table, and then exporting from the clone may be as close as you can get. Let's just hope you don't hit an ORA-1555 during the CTAS.

On Thu, May 7, 2015 at 1:14 PM, Jeremy Schneider < jeremy.schneider_at_ardentperf.com> wrote:

> On Wed, May 6, 2015 at 6:27 AM, Zabair Ahmed <roon987_at_yahoo.co.uk> wrote:
> > Am trying to import a large table (176gb) from our Production database
> to a
> > test database using a database link. This is test exercise and I want to
> > know the timings the import for when we do this for live later on in the
> > month.
>
> One additional quick thought -- when I've attempted to do dblink-based
> data pumps in the past, my biggest problem was network bandwidth - if
> I remember correctly, nothing is compressed when you data pump over a
> dblink.
>
> Counterintuitively, there are some cases where compressing and
> transferring disk-based export files will be faster than the db-link
> based import. Might be worth a try.
>
> If disk-based export/import isn't an option (e.g. you don't have file
> system access on the server) then disregard this email. :)
>
> -Jeremy
>
> --
> http://about.me/jeremy_schneider
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l