Home » SQL & PL/SQL » SQL & PL/SQL » Sync two tables through dblink (10g Xe)
icon9.gif  Sync two tables through dblink [message #308867] Tue, 25 March 2008 14:04 Go to next message
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
Re: Sync two tables through dblink [message #308868 is a reply to message #308867] Tue, 25 March 2008 14:16 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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.
Re: Sync two tables through dblink [message #308885 is a reply to message #308868] Tue, 25 March 2008 16:49 Go to previous messageGo to next message
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.


Re: Sync two tables through dblink [message #308991 is a reply to message #308885] Wed, 26 March 2008 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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!).
Re: Sync two tables through dblink [message #309017 is a reply to message #308991] Wed, 26 March 2008 04:09 Go to previous messageGo to next message
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.
Re: Sync two tables through dblink [message #309020 is a reply to message #308867] Wed, 26 March 2008 04:15 Go to previous messageGo to next message
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
Re: Sync two tables through dblink [message #309026 is a reply to message #309020] Wed, 26 March 2008 04:41 Go to previous messageGo to next message
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.

Re: Sync two tables through dblink [message #309034 is a reply to message #308867] Wed, 26 March 2008 04:54 Go to previous messageGo to next message
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
Re: Sync two tables through dblink [message #309039 is a reply to message #308867] Wed, 26 March 2008 05:02 Go to previous messageGo to next message
angelred
Messages: 11
Registered: March 2008
Location: Rome
Junior Member
PS:
The synchronization procedure resides on the clients.
Each client decides independently when synchronize.
Re: Sync two tables through dblink [message #309055 is a reply to message #309034] Wed, 26 March 2008 05:33 Go to previous messageGo to next message
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.



Re: Sync two tables through dblink [message #309066 is a reply to message #308867] Wed, 26 March 2008 06:27 Go to previous messageGo to next message
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 )
Please, how should I change the query?
Re: Sync two tables through dblink [message #309079 is a reply to message #309066] Wed, 26 March 2008 06:48 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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.
Re: Sync two tables through dblink [message #309085 is a reply to message #309066] Wed, 26 March 2008 06:52 Go to previous messageGo to next message
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.


Re: Sync two tables through dblink [message #309086 is a reply to message #308867] Wed, 26 March 2008 06:54 Go to previous messageGo to next message
angelred
Messages: 11
Registered: March 2008
Location: Rome
Junior Member

Thanks
I try and refer
Re: Sync two tables through dblink [message #309089 is a reply to message #309085] Wed, 26 March 2008 07:05 Go to previous messageGo to next message
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
Re: Sync two tables through dblink [message #309094 is a reply to message #309089] Wed, 26 March 2008 07:24 Go to previous messageGo to next message
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.

Re: Sync two tables through dblink [message #309096 is a reply to message #309094] Wed, 26 March 2008 07:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Check host column value in
SELECT * FROM all_db_links


And Post It Here
Re: Sync two tables through dblink [message #309761 is a reply to message #309096] Fri, 28 March 2008 13:02 Go to previous messageGo to next message
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!
Re: Sync two tables through dblink [message #309974 is a reply to message #309761] Sun, 30 March 2008 17:44 Go to previous messageGo to next message
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.
Re: Sync two tables through dblink [message #310086 is a reply to message #309974] Mon, 31 March 2008 04:00 Go to previous message
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
Previous Topic: email validation
Next Topic: Exceptions
Goto Forum:
  


Current Time: Wed Dec 07 07:13:54 CST 2016

Total time taken to generate the page: 0.16571 seconds