Avatar of carlino70
carlino70Flag for Argentina asked on

Matching data from tables in different Oracle databases

Hi Experts,
In a database 11g Standard Edition (no replication)
I need to create a stored procedure to perform the matching of data from tables with the same name but in different instances.
Both bases are updated with the same data by the application, but before courts in communication, drops the database, or server crash, data is NOT sent to the other end when service is restored.

Manually, and with just a table, I can do:
insert into tbl1
select * from tbl1 @ base2
between: 1 and: 2 - DATE fields, with the period to match
minus
select * from tbl1
between: 1 and: 2; - DATE fields, with the period to match

Open in new window

And then the other way around:
insert into tbl1
select * from tbl1 @ base1
between: 1 and: 2 - DATE fields, with the period to match
minus
select * from tbl1
between: 1 and: 2; - DATE fields, with the period to match

Open in new window

The issue is that I have 100 tables, and the process should sweep 100.
Only I do INSERT / UPDATE, depending on whether the records are empty, since the application may have created it, but no data.
You will have an example to follow, or some solution that can do what I need?
Thank you very much.
Regards
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

I've never tried using MERGE across a database link but it should be possible.

The MERGE statement will match rows based on some key value and either perform an update or insert if necessary.

If you can provide some sample data and expected results we can try to come up with a working model.

The docs on MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#sthref6542

There is also a lot of examples on MERGE out there if you look around.
ASKER
carlino70

Yes, here it is:
I send a couple of tables for base for simulation.
The table structure is the same in both instances.
I send you the scripts insertion into tables @ base2, only with the values ¿¿previously inserted by the application (only prepares the fields: utcTime, POINTNUMBER, SITEID), the other fields are empty waiting for data from the other base crossed.
I hope you understand.
thank you very much
script-a-5min-tst1.sql
script-a-5min-tst2.sql
inserts-a-5min-tst1-base1.sql
inserts-a-5min-tst2-base1.sql
inserts-a-5min-tst1-base2.sql
inserts-a-5min-tst2-base2.sql
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
carlino70

Final Results: Both tables, a_5min_tst1@base1 and  a_5min_tst1@base2, must to have the same data.

The data are loaded by this procedure, you should upgrade SITEID field with a value = 0, to differentiate it from what normally loaded

Also, what aren't you using replication?
For now, I understand that not using replication, in fact my tests are based on two test instances, so I do not have real stage Replication.

I see available is a form of replication, try to find out how it really works.

Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
carlino70

Hi, I confirm that I am NOT using any replication. The same data is sent to each instance individually by the application.
Then I need to create a tool that complete data in both directions, when there is a break in communication between servers.
Thank you!
slightwv (䄆 Netminder)

>>Hi, I confirm that I am NOT using any replication.

I understand this.  My question is: Why aren't you using replication?  Then the application only needs to insert/update one database and the other database is automatically kept in sync.


Back to the question asked:
I haven't gotten time to look at your code yet.  It will take some time to get everything set up and tested.
ASKER
carlino70

ok, no problem.

I have some doubts about the using of Replication Tools, because I need use him only when the data in some tables in both instances are distinct.

Can I apply the replication just for a group of tables, in a period limited of time?,
I can enable replication only when necessary?
Are you refering to Stream Replication?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>Can I apply the replication just for a group of tables, in a period limited of time?,

No.

>>I can enable replication only when necessary?

No.

>>Are you refering to Stream Replication?

I don't believe Streams Replication is included with Standard Edition.  I'm not sure what 'Basic Replication' allows.  You would need to contact Oracle directly or dig through the docs.

>> because I need use him only when the data in some tables in both instances are distinct.

I'm not following your requirement.  You only want tables synced up 'some of the time'?  Typically you want things in sync or you don't.  Never heard of 'some rows need synced, others do not'.
ASKER
carlino70

To perform this task, I will use native replication tool for Oracle.
ASKER
carlino70

I've requested that this question be closed as follows:

Accepted answer: 0 points for carlino70's comment #a39536792

for the following reason:

To perform this task, I will use native replication tool for Oracle.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

I suggested replication.  If you are going to use that, you should accept my post as the solution.