Move new data records automatically to another database in oracle xe

Question

What's the best way to move all the data from a table on my local oracle database to an identical table on another pc? I will have to transfer the data to the second database as soon as it's written to the local one. The local table should not contain any data, unless it's not possible to insert it to the remote database. If the connection is lost, all new datasets must be stored in the local table. I tried to solve this by using a trigger, but it didn't work as expected. It works just fine if the remote database connection is valid, but it performs an entire rollback (including the original insert) if the connection is lost. Because of that, the data isnt even inserted to the local database. Another huge problem is that it takes about 40 seconds every time to return ORA-12170 (Connection timeout). Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time?

create or replace TRIGGER DATA_TO_SERVER 
AFTER INSERT ON LOCAL_TABLE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SAVEPOINT sp;

  INSERT INTO SERVER.SERVER_TABLE@SERVER_LINK
  SELECT *
  FROM LOCAL_TABLE;

  DELETE
  FROM LOCAL_TABLE
  WHERE ID IS NOT NULL;

COMMIT;

EXCEPTION WHEN OTHERS THEN
    ROLLBACK to sp;
    RAISE;

END;

Answer 1

SQL> Create Directory EXPDIR as 'PATH';

The export command

expdp directory=expdir dumpfile=newExport.dmp full=y logfile=newExport.log

---

on the target database also create a directory

Create Directory EXPDIR as 'PATH';

The import command

IMPDP directory=ExpDir dumpfile=newExport.dmp Logfile=import_Database.LOG FULL=Y

---

By the way this is a full export and import, you can customize it according to your preference on what exactly to export or import

https://docs.oracle.com/cloud/latest/db112/SUTIL/dp_export.htm#SUTIL200

Answer 2

The only solution I see (I'm very myopic) is creating a view on that table, inserts happen into that view, and "instead-of" triggers handling those inserts. If inserting into the remote table fails, insert locally.

Answer 3

I would use a view with instead_of trigger as GerhardH.Pille proposed. In my environment I cannot use database links so I use a local table called remotetab. Replace it by remotetab@remotedb if you have a database remotedb. In my example the length of the second columns of the local and the remote table differ. But this is only to simulate an error when trying to insert in the remote table.

drop table localtab;
drop table remotetab;
drop view localview;

create table localtab(
id number primary key,
name varchar2(30) not null)
/

create table remotetab(
id number primary key,
name varchar2(3) not null) 
/

create view localview(id, name)
as
select id, name
from localtab
/

create trigger localtrig
instead of insert on localview
begin
insert into remotetab(id,name) values(:new.id, :new.name);
exception
    when others then
        insert into localtab(id,name) values(:new.id, :new.name);
end;
/

insert into localview(id, name) values(1,'a');
insert into localview(id, name) values(2,'hallo');
insert into localview(id, name) values('x','y');

select id, name remote from remotetab;
select id, name local from localtab;

The inserts return

1 row(s) inserted.

1 row(s) inserted.

ORA-01722: invalid number ORA-06512: at "SYS.DBMS_SQL", line 1721

and the selects return

id   local
 1     'a'

id  remote
 2  'hallo'

as expected.