OraFAQ Forum: SQL & PL/SQL » Sync two tables through dblink

Home » SQL & PL/SQL » SQL & PL/SQL » Sync two tables through dblink (10g Xe)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sync two tables through dblink [message #308867]

Tue, 25 March 2008 14:04

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Hi,

I need to syncronize two table through a dblink.
The procedure currently contains:

(...)

-- insert all remote record not present in local

insert into local_table loc
select rem.*
from remote_table@dblink rem
where
rem.idA = vID and
(rem.idA, rem.idB, rem.idC, rem.idE, rem.idF) not in
(select s.idA, s.idB, s.idC, s.idE, s.idF from local_table s where s.idA = vID);

-- insert all local record not present in remote

insert into remote_table@dblink rem
select loc.*
from local_table loc
where
loc.idA = vID and
(loc.idA, loc.idB, loc.idC, loc.idE, loc.idF) not in
(select s.idA, s.idB, s.idC, s.idE, s.idF from remote_table@dblink s where s.idA = vID);

(...)

Note:

- at the moment remote_table have about 500,000 records and local_table have about 7,000 records
- remote_table and local_table definition are identical
- idA, ..., idF is primary key in both tables
- both tables have a blob field
- I have very very very long time for these queries...
- Have you idea???

Best regard
AngelRed

Report message to a moderator

Re: Sync two tables through dblink [message #308868 is a reply to message #308867]

Tue, 25 March 2008 14:16

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, you could create snapshots (materialized views, that is) (one on each side of a database link) which would be refreshed as often as you think they should (nightly scheduled job, perhaps? On commit? You choose!).

Updating tables would then be many times faster, as you'd join a table and a materialized view (so no data transfer via database link would be necessary).

Test this scenario and see how it behaves; perhaps you'll like it.

Report message to a moderator

Re: Sync two tables through dblink [message #308885 is a reply to message #308868]

Tue, 25 March 2008 16:49

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Explain the scenario:
I have > 30 clients that work off-line. Asynchronously, more clients even at same time, they initiate synchronization.

Report message to a moderator

Re: Sync two tables through dblink [message #308991 is a reply to message #308885]

Wed, 26 March 2008 03:23

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Where are these clients? Which database do they connect to? All of them the same database, or 30 different databases?

If it is the first case, do they connect to the same schema? I wouldn't say so, as code you provided in the first message suggests that each of them uses its own table. If so, is it a good idea? Why don't you keep all the records in the same table in the same schema?

If it is the second case, it really doesn't matter when any of them starts synchronization as noone affects the others. You, of course, understand that you can't synchronize something that doesn't exist (yet). In other words: if one user starts synchronization and inserts its own data into a "large" (500.000 records) table, but 25th user didn't start the process yet (and, consequentially, his records are not in the "large" table), the first user will not have copy of 25th user in his table.

It seems that - if you choose to use materialized views - you'll need to refresh them on commit. Doing so, it wouldn't matter when any of your clients synchronize data as it would be the same as its source table at the commit time (don't forget that refreshing might take a while if data volume is high!).

Report message to a moderator

Re: Sync two tables through dblink [message #309017 is a reply to message #308991]

Wed, 26 March 2008 04:09

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Thank you for your rapid response.

Currently clients and server have an oracle database 10g xe.

Actually clients produce data (off-line) that is sent to the server (via adsl and dblink).
The server collects them but every client "sees" only own data.

All clients connect to the same schema and the tables on the server is identical to the tables on the client.

Many other tables are synchronized with the same method, but this gives me problems. The differences are the size (intended to increase) and the presence of one blob field.

Report message to a moderator

Re: Sync two tables through dblink [message #309020 is a reply to message #308867]

Wed, 26 March 2008 04:15

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

In addition...
During synchronization, server often falls "asleep" for long time (several hours for a few mb) and always during these inserts, blocking other clients

Report message to a moderator

Re: Sync two tables through dblink [message #309026 is a reply to message #309020]

Wed, 26 March 2008 04:41

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

How many times in a day synchronization occurs.It seems that u are using a standalone procedure at client to put data into main server tables directly through dblink.It's a one time activity or it repeats in a day.

Report message to a moderator

Re: Sync two tables through dblink [message #309034 is a reply to message #308867]

Wed, 26 March 2008 04:54

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Not daily.
Usually each client synchronizes every 2-3 days.
Sometimes a client takes about 20 - 30 minutes, but sometimes the same client, with the same number of records, takes 3 - 4 hours

Report message to a moderator

Re: Sync two tables through dblink [message #309039 is a reply to message #308867]

Wed, 26 March 2008 05:02

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

PS:
The synchronization procedure resides on the clients.
Each client decides independently when synchronize.

Report message to a moderator

Re: Sync two tables through dblink [message #309055 is a reply to message #309034]

Wed, 26 March 2008 05:33

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Materailized view will solve the problem.

Quote:

But for this approach u have to make some modifications both at server and client side.Becuase it's a complete logic change.

Report message to a moderator

Re: Sync two tables through dblink [message #309066 is a reply to message #308867]

Wed, 26 March 2008 06:27

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Instead of nested select?
I have never used the materialized view... (I'm a true newbies! Embarassed

Embarassed

)
Please, how should I change the query?

Report message to a moderator

Re: Sync two tables through dblink [message #309079 is a reply to message #309066]

Wed, 26 March 2008 06:48

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Imagine that materialized view is just another table. You use it (in your query) just as any other table or view - no difference. For example:

SELECT a.id, b.name
FROM some_table a, materialized_view b
WHERE a.id = b.id;

Just in case you didn't notice, there's a documentation index; enter MATERIALIZED VIEW into the Quick Search field and read more about its capabilities.

Report message to a moderator

Re: Sync two tables through dblink [message #309085 is a reply to message #309066]

Wed, 26 March 2008 06:52

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Please clarify following points:-

Quote:

1)DBLink availability.
All The Time N/W Is available i.e Client and server can access
Each other at any time or not.

2)Where Are The Client database Located.
In Same Building Or Seperate Cities.Are they connected
through VPN.

3)Seperate tables To hold Client Data At server or you
have defined only one table at server to hold all client
data.

4)Do you wan't to change this whole logic (both at
client and server) or just looking for a workaround to
this problem.

Report message to a moderator

Re: Sync two tables through dblink [message #309086 is a reply to message #308867]

Wed, 26 March 2008 06:54

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Thanks
I try and refer

Report message to a moderator

Re: Sync two tables through dblink [message #309089 is a reply to message #309085]

Wed, 26 March 2008 07:05

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

>1)DBLink availability.
>All The Time N/W Is available i.e Client and server can access
>Each other at any time or not.

No. The clients aren't connected any time.

>2)Where Are The Client database Located.
>In Same Building Or Seperate Cities.Are they connected
>through VPN.

The clients are distribute in separate cities and is not connected through VPN.

>3)Seperate tables To hold Client Data At server or you
>have defined only one table at server to hold all client
>data.

The server have only one table for all client data.

>4)Do you wan't to change this whole logic (both at
>client and server) or just looking for a workaround to
>this problem.

I wan't change client and server logic, if necessary...

Thanks for all

Report message to a moderator

Re: Sync two tables through dblink [message #309094 is a reply to message #309089]

Wed, 26 March 2008 07:24

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

So what DBLINK is doing here.If u have defined DBLINKS than u must have TNS Entry for those. It means that The IP'S that are defined in TNS can be accessed And u are saying that you client and server can't access each other.

Strange...

Then how the hell data is inserted by client.

Report message to a moderator

Re: Sync two tables through dblink [message #309096 is a reply to message #309094]

Wed, 26 March 2008 07:27

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Check host column value in

SELECT * FROM all_db_links

And Post It Here

Report message to a moderator

Re: Sync two tables through dblink [message #309761 is a reply to message #309096]

Fri, 28 March 2008 13:02

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

On server:

OWNER = SSART_SRV
DB_LINK = SSART_MANAGER
USERNAME = SSART_SRV
HOST = localhost

On client:

OWNER = SSART_CLT
DB_LINK = SSART_MANAGER
USERNAME = SSART_SRV
HOST = xxxxx.xxxxx.it

OWNER = PUBLIC
DB_LINK = DBMS_CLRDBLINK
USERNAME =
HOST = ORACLR_CONNECTION_DATA

Please, save me!

Report message to a moderator

Re: Sync two tables through dblink [message #309974 is a reply to message #309761]

Sun, 30 March 2008 17:44

angelred
Messages: 11
Registered: March 2008
Location: Rome

Junior Member

Well...
I tried to use materialized views, but I don't understand how can force sync.
The client (all always offline) can sync at any time, theoretically more times in the same day.

Report message to a moderator

Re: Sync two tables through dblink [message #310086 is a reply to message #309974]

Mon, 31 March 2008 04:00

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Post your materialized view query that you have tried.

To refresh the snapshots you can use:-

DBMS_SNAPSHOT.REFRESH method

Report message to a moderator

Previous Topic:	using vaiable for IN query
Next Topic:	Exceptions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 23 21:11:04 CST 2026