This technique illustrates how to have N connections through a db-link that sees the same consistent image of the remote database, ie the (committed) state of the remote database at the same point in time.
It's very easy - in the remote database, create as-of-scn (or as-of-timestamp) views for the tables you're interested in, using the same SCN (or timestamp):
create or replace view t1_scn_view as select * from t1 as of scn 1234;
create or replace view t2_scn_view as select * from t2 as of scn 1234;
...
create or replace view tK_scn_view as select * from tK as of scn 1234;
And then have the N connections read from the tK_scn_view views instead of the tK tables.
Note: we need to create these remote views since AS OF SCN is not supported for sql statements referencing remote objects.
Setting the transaction isolation level to SERIALIZABLE will allow us to see a consistent image, but not to share it between different connections.
The following scripts illustrate the technique:
as_of_scn_views_setup.sql (here's its spool on 9.2.0.6) creates the remote tables, and a remote package to create as-of-scn views on the fly;
as_of_scn_views.sql (here's its spool on 9.2.0.6) uses the remote package to replicate the tables consistently.
I've used it for:
parallelizing consistent refreshes from remote database
I needed to replicate a set of N tables from a remote database, and the replica had to be consistent, ie the local tables had to be the copy of the remote ones as they existed at the same point in time (there were FKs between them).
For sure, I'd be better off using one of the tools Oracle provides us for replication:
a) Streams
b) Materialized Views (aka Snapshots) Refresh Groups
c) Change Data Capture (CDC)
But Streams and CDC were refused by the remote database DBA team (for good reasons, they wanted to investigate and test them deeply before introducing these new features in their mission-critical instance).
This left only Refresh Groups to consider.
"Fast" incremental refreshes were going to be slower than complete refreshes, given the rate of change between refreshes.
But complete refreshes of Refresh Groups make a delete of the local tables, followed by a (conventional) insert-as-select from remote; this of course produces a lot of undo and redo, doesn't allow for direct-path insert, and for nologging operations (and it was ok for me to loose recoverability of the local tables).
Furthermore, since no one was going to read the local tables during refresh, i didn't need local consistency; it was ok for the local tables to become empty, and then fill up, each one in a separate transaction.
End of the story - my refresh process became a creation of the as-of-scn views, followed by the spawning of N jobs, each one performing:
1) truncate table tK_local; (tK_local being NOLOGGING tables)
2) insert /*+ append */ into tK_local as select * from tK_scn_view@db_link;
3) commit.
That's illustrated in the scripts provided above (serially, without the jobs).
Possible variations: you could also have N jobs reading from the same table (eg one for each partition), or joining/aggregating the as-of-scn views - and then direct-path inserting into the nologging local tables.
Since it's a direct-path operation, you may enable table compression if useful.
Obviously, the standard trick to put indexes in UNUSABLE state, and then rebuild them at the end of the insert-append, may be used also.