Two databases and sync'ing them 1

[heydyrtt]

Hey guy's

Anyone know an easy was to do this with two databases.

This is a helpdesk program running oracle(not web based program), the tech's will enter their daily calls during the week, on their laptops. On Friday's they will need to upload data to master database which is identical to the one on their laptop. Also will need to retrieve new data from master database, is there something that oracle offers or script that can be ran to do this without too much trouble?

Thanks

Heydyrtt

 

[SantaMufasa]

Heydrytt:

1) Add an timestamp column to each "help_desk_incident" table:

ALTER TABLE help_desk_incident ADD upload_date_time date;

2) On each help-desk client database, build a database link to the master help_desk_incident schema:

CREATE DATABASE LINK help_desk_master
  CONNECT TO <name of help-desk master user>
  IDENTIFIED BY <password of help-desk master user>
  USING '<tnsnames.ora alias for help-desk master instance>;

3) On each help-desk client database, build an upload procedure:

CREATE OR REPLACE upload_proc IS
  hold_upload_date_time		DATE := SYSDATE;
BEGIN
  UPDATE help_desk_incident
     SET upload_date_time = hold_upload_date_time
   WHERE upload_date_time IS NULL;
  COMMIT;
  INSERT INTO help_desk_incident@help_desk_master
  SELECT * FROM help_desk_incident
   WHERE upload_date_time = hold_upload_date_time;
  COMMIT;
END;
/

4) Whenever a help-desk rep chooses to do an upload:

SQL> exec upload_proc

To query data from the master database:

SELECT <whatever expressions>
  FROM help_desk_incident@help_desk_master
 WHERE <whatever conditions apply;

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:28 (11Oct04) UTC (aka "GMT" and "Zulu"), 00:28 (11Oct04) Mountain Time)