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;