-- Heartbeat tables
--   Based on Oracle supplied Heartbeat table V11
-- To be implemented in all GoldenGate deployments
-- table HEARTBEAT is required for any 'source' database
-- tables GGS_HEARTBEAT & GGS_HEARTBEAT_HISTORY are useful on 'target' databases
-- There should be no issue with creating/replicating all tables
-- Update History
-- 05-APR-2013 - MKruse - FIL - Updated to convert all timestamps to UTC

drop table GGADMIN.heartbeat;
-- Create table statement
CREATE TABLE GGADMIN.HEARTBEAT
(  ID 			NUMBER ,
   SRC_DB		VARCHAR2(30),
   TGT_DB		VARCHAR2(30),
   EXTRACT_NAME 	VARCHAR2(8),
   SOURCE_COMMIT 	TIMESTAMP,
   TARGET_COMMIT 	TIMESTAMP,
   CAPTIME 		TIMESTAMP,
   CAPLAG 		NUMBER,
   PMPTIME 		TIMESTAMP,
   PMPGROUP 		VARCHAR2(8),
   PMPLAG 		NUMBER,
   DELTIME 		TIMESTAMP,
   DELGROUP 		VARCHAR2(8),
   DELLAG 		NUMBER,
   TOTALLAG 		NUMBER,
   thread 		number,
   update_timestamp     timestamp,
   LOCAL_COMMIT         timestamp,
CONSTRAINT HEARTBEAT_PK PRIMARY KEY (SRC_DB) ENABLE
)
/

-- this assumes that the table is empty
INSERT INTO GGADMIN.HEARTBEAT (SRC_DB) select name from V$database;
commit;


DROP SEQUENCE GGADMIN.SEQ_GGS_HEARTBEAT_ID ;
CREATE SEQUENCE GGADMIN.SEQ_GGS_HEARTBEAT_ID INCREMENT BY 1 START WITH 1 ORDER ;
DROP TABLE GGADMIN.GGS_HEARTBEAT;
CREATE TABLE GGADMIN.GGS_HEARTBEAT
(  ID 			NUMBER ,
   SRC_DB		VARCHAR2(30),
   TGT_DB		VARCHAR2(30),
   EXTRACT_NAME 	varchar2(8),
   SOURCE_COMMIT 	TIMESTAMP,
   TARGET_COMMIT 	TIMESTAMP,
   CAPTIME 		TIMESTAMP,
   CAPLAG 		NUMBER,
   PMPTIME 		TIMESTAMP,
   PMPGROUP 		VARCHAR2(8),
   PMPLAG 		NUMBER,
   DELTIME 		TIMESTAMP,
   DELGROUP 		VARCHAR2(8),
   DELLAG 		NUMBER,
   TOTALLAG 		NUMBER,
   thread 		number,
   update_timestamp 	timestamp,
   LOCAL_COMMIT         timestamp,
CONSTRAINT GGS_HEARTBEAT_PK PRIMARY KEY (SRC_DB,EXTRACT_NAME) ENABLE
);
grant SELECT on GGADMIN.GGS_HEARTBEAT to public;

CREATE OR REPLACE TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG
  BEFORE INSERT OR UPDATE ON GGADMIN.GGS_HEARTBEAT
  FOR EACH ROW WHEN (NEW.id = 0)
BEGIN
  select seq_ggs_HEARTBEAT_id.nextval		into :NEW.ID              from dual;
  select systimestamp at time zone 'UTC'	into :NEW.target_commit   from dual;
  select name                                   into :NEW.tgt_db          from v$database;
  select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400
             + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600
             + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60
             + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))
             + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000
    into :NEW.CAPLAG
    from dual;
  select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400
             + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600
             + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60
             + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))
             + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000
    into :NEW.PMPLAG
    from dual;
  select trunc(to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),1, instr(:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME),' ')))) * 86400
             + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+1,2)) * 3600
             + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+4,2) ) * 60
             + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+7,2))
             + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+10,6)) / 1000000
    into :NEW.DELLAG
    from dual;
  select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),1, instr(:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP,' ')))) * 86400
             + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+1,2)) * 3600
             + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+4,2) ) * 60
             + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+7,2))
             + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+10,6)) / 1000000
    into :NEW.TOTALLAG
    from dual;
end;
/
ALTER TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG ENABLE;

CREATE OR REPLACE TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG2
  BEFORE INSERT OR UPDATE ON GGADMIN.GGS_HEARTBEAT
  FOR EACH ROW
BEGIN
  select systimestamp at time zone 'UTC'	into :NEW.local_commit   from dual;
END;
/
ALTER TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG2 ENABLE;

--
-- This is for the History heartbeat table
--
DROP SEQUENCE GGADMIN.SEQ_GGS_HEARTBEAT_HIST ;
CREATE SEQUENCE GGADMIN.SEQ_GGS_HEARTBEAT_HIST INCREMENT BY 1 START WITH 1 ORDER ;
DROP TABLE GGADMIN.GGS_HEARTBEAT_HISTORY;
CREATE TABLE GGADMIN.GGS_HEARTBEAT_HISTORY
(  ID 			NUMBER ,
   SRC_DB		VARCHAR2(30),
   TGT_DB		VARCHAR2(30),
   EXTRACT_NAME 	varchar2(8),
   SOURCE_COMMIT 	TIMESTAMP,
   TARGET_COMMIT 	TIMESTAMP,
   CAPTIME 		TIMESTAMP,
   CAPLAG 		NUMBER,
   PMPTIME 		TIMESTAMP,
   PMPGROUP 		VARCHAR2(8),
   PMPLAG 		NUMBER,
   DELTIME 		TIMESTAMP,
   DELGROUP 		VARCHAR2(8),
   DELLAG 		NUMBER,
   TOTALLAG 		NUMBER,
   thread 		number,
   update_timestamp	timestamp,
   LOCAL_COMMIT         timestamp
);

CREATE OR REPLACE TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG_HIST
  BEFORE INSERT OR UPDATE ON GGADMIN.GGS_HEARTBEAT_HISTORY
  FOR EACH ROW when (NEW.ID = 0)
BEGIN
  select seq_ggs_HEARTBEAT_HIST.nextval         into :NEW.ID              from dual;
  select systimestamp at time zone 'UTC'	into :NEW.target_commit   from dual;
  select name                                   into :NEW.tgt_db          from v$database;
select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400
           + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600
           + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60
           + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))
           + to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000
  into :NEW.CAPLAG
  from dual;
select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400
           + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600
           + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60
           + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))
           + to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000
  into :NEW.PMPLAG
  from dual;
select trunc(to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),1, instr(:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME),' ')))) * 86400
           + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+1,2)) * 3600
           + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+4,2) ) * 60
           + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+7,2))
           + to_number(substr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)), instr((:NEW.DELTIME - nvl(:NEW.PMPTIME,:NEW.CAPTIME)),' ')+10,6)) / 1000000
  into :NEW.DELLAG
  from dual;
select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),1, instr(:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP,' ')))) * 86400
           + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+1,2)) * 3600
           + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+4,2) ) * 60
           + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+7,2))
           + to_number(substr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP), instr((:NEW.TARGET_COMMIT - :NEW.UPDATE_TIMESTAMP),' ')+10,6)) / 1000000
  into :NEW.TOTALLAG
  from dual;
end ;
/
ALTER TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG_HIST ENABLE;

CREATE OR REPLACE TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG_HIST2
  BEFORE INSERT OR UPDATE ON GGADMIN.GGS_HEARTBEAT_HISTORY
  FOR EACH ROW when (NEW.ID = 0)
BEGIN
  select systimestamp at time zone 'UTC'	into :NEW.local_commit   from dual;
END;
/
ALTER TRIGGER GGADMIN.GGS_HEARTBEAT_TRIG_HIST2 ENABLE;
