How to setup tables in Shareplex for Oracle to test for latency (4244393)

You would like to know what the delay is for updates to the target database. How far behind is my target database.

test to see if everything is operating as designed.

How to setup and test for latency in Shareplex. A table is created on the source and target to test for latency. The source table has a key value on rown and a date field with a default of sysdate to store the insert time on the source. The target table has a different date field with a default date of sysdate that stores the time the row was inserted on the target, by taking the time difference between the time the row was inserted on the source and target you can get the latency, or time it takes shareplex to insert the row on the target. The can help diagnose replication issues by telling you what is a normal latency for your system. This can also be used as a quick and easy check to see if replication is caught up and working normally.

--drop table SOURCE_LATENCY;
--drop table TARGET_LATENCY;

--drop sequence serial;
create sequence serial;

--source table needs key so so target time is not part of insert or update. The insert only contains values that are inserted e.g. rown, hostname, source_time.
create table SOURCE_LATENCY (
rown number primary key,
hostname varchar2(20),
source_time date default sysdate,
target_time date);

--target table
create table TARGET_LATENCY (
rown number primary key,
hostname varchar2(20),
source_time date,
target_time date default sysdate);

-- This insert statement will insert a row on source and give it a unique value and the nostname that it was inserted on.

insert into SOURCE_LATENCY values (serial.nextval, sys_context('USERENV', 'HOST'), sysdate,null);
commit;

select rown , hostname, (target_time-source_time)*24*60*60 "SECONDS" from target_latency ;