Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 08, 2017 - 9:07 am UTC

Last updated: June 18, 2024 - 4:28 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Chirs/Connor,

Couple of question related to Table data movement from Primary DB to Archival DB.

We are having Two DB's - Primary (holding current 2 years of data)
Archival (Primary data + historical)

We have separate Archival DB using IMP/EXP from Primary DB and delete records from few tables (keeping only 2 years of data).

Now we need to move data from Primary table to Archival table via DB link by writing PL/SQL scripts

Question - 1. When we create DB link - which database we need to create if Primary Or Archival DB?
2. Since we need to move data older than 2 years (monthly basis), in which DB we can schedule a job (Primary/Archival)?
3. We are going for MERGE statement to merge records from primary TO Archival, which is the bets practice to write MERGE statement in which DB Primary or Archival?

and Connor said...

Assuming both environments are by and large the same, then you can choose either direction, eg

1) database link on archive node, scheduled job on archive, which does

- read data across db link
- write data to local database
- delete the source data across db link


2) database link on primary node, scheduled job on primary, which does

- read data locally
- write data to archive database across db link
- delete the source data locally

and of course variations of that as well, eg, archives runs a scheduled job which calls a procedure on the primary etc.

In my experience, the fewer usage of a database link is normally better, because often the network throughput is a limiting factor, but that may or may not be the case for you.

And similarly, generally choose a method that invokes the least amount of load on the production node, because thats the most important one.


  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment


SELECT over DB link

A reader, April 06, 2017 - 6:01 am UTC

Consider scenario of Primary DB & Archival DB

Below SQL is going to be executed on Archival DB
1. Table - tb_card_request is created on Archival DB
2. Synonyms created on Archival DB which will point to Primary DB table e.g.
CREATE SYNONYM tb_card_request_details on tb_card_request_details@db_link;
CREATE SYNONYM tb_form_details on tb_form_details@db_link;

select cad.field_value
from tb_card_request_details cad, -- this will fetch records over dblink using synonym
tb_card_request ca, -- this will fetch records from Archival DB
tb_form_details fdt -- this will fetch records over dblink using synonym
where ca.request_id = 123456789
and ca.created_on = ’01-Jan-2009’
and cad.fk_tb_card_request(+) = ca.pk_tb_card_request
and ca.rec_status = 'AC'
and cad.fk_tb_form_details = fdt.pk_tb_form_details
and fdt.rec_status = 'AC';

Is it a good practice to fetch data over DB link?
Above is just a sample SQL, there are queries which includes many tables in joins which will fetch data from other DB using DB link.
Connor McDonald
April 07, 2017 - 2:16 am UTC

There is nothing "wrong" with joins across a database link, but you do need to be very aware of the implications of doing so.

Joins are basically done "locally" - by that I mean you are likely to see one of three possible scenarios:

a) all data pushed from local site to the remote site, join done remotely
b) all data pulled from remote site to the local site, join done locally
c) nested loop style, for each row in local, do a remote lookup

Its fairly self-evident that all of those approaches have potential risks depending on the size of the data being pushed back and forth.

This is why sometimes people will have materialized views etc to pay that cross-database penalty "in the background" so that subsequent queries can run locally.


A reader, April 11, 2017 - 7:29 am UTC

I have a table created on Primary DB as well as On Arcihval DB:
e.g. tb_card_request_details

Now on Primary DB, I have create synonym with the table name like below whic is pointing to Archival DB via DB link.

CREATE PUBLIC SYNONYM tb_card_request_details on tb_card_request_details@db_link;

When I run SELECT * FROM tb_card_request_details, it gives result from Primary itself though I have created it for Archival DB using DB link in CREATE SYNONYM command.

Can you please help to understand?

Connor McDonald
April 12, 2017 - 5:42 am UTC

Names are resolved from "inside out", ie

- does the object exist in my schema ? Yes, use that, otherwise:
- do I have a private synonym to somewhere else ? Yes, use that, otherwise:
- do I have a public synonym ? Yes, use that.

pull data remotely and exec local joins -- what about index and statistics

Simon, June 17, 2024 - 6:08 pm UTC

Hello community,

reading the thread my understanding is one possible scenario is
- pull data remotely
- exec the DML statement locally

In that case what about the index and statistics of the remove table?
I guess when data are fetch and DML process them it will be longer as we don't fetch index nor statistics?

Or I'm wrong :-)


Connor McDonald
June 18, 2024 - 4:28 am UTC

Even though during execution of the statement we might be bouncing back and forth across the database link, we optimize at the *commencement* of the statement. In that process, we'll use statistics from both local and remote databases to make a call on how we are going to proceed.

But my original statement still stands - try to minimise the data going across the link and you'll typically get the best result

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library