List General Information About Master Groups & replication activity

You can retrieve following details :

• The number of unpropagated deferred transaction-destination pairs. Each deferred transaction can have multiple destinations to which it will be propagated, and each destination is a single deferred transaction-destination pair.
• The number of deferred transaction errors (error transactions) for each master group
• The number of administrative requests for each master group
• The number of administrative request errors for each master group

SELECT G.GNAME,
NVL(T.CNT1, 0) deftran,
NVL(IE.CNT2, 0) deftranerror,
NVL(A.CNT3, 0) adminreq,
NVL(B.CNT4, 0) adminreqerror
FROM
(SELECT DISTINCT GNAME FROM DBA_REPGROUP WHERE MASTER=’Y’) G,
(SELECT DISTINCT RO.GNAME, COUNT(DISTINCT D.DEFERRED_TRAN_ID) CNT1
FROM DBA_REPOBJECT RO, DEFCALL D, DEFTRANDEST TD
WHERE RO.SNAME = D.SCHEMANAME
AND RO.ONAME = D.PACKAGENAME
AND RO.TYPE IN (‘TABLE’, ‘PACKAGE’, ‘MATERIALIZED VIEW’)
AND TD.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
GROUP BY RO.GNAME ) T,
(SELECT DISTINCT RO.GNAME, COUNT(DISTINCT E.DEFERRED_TRAN_ID) CNT2
FROM DBA_REPOBJECT RO, DEFCALL D, DEFERROR E
WHERE RO.SNAME = D.SCHEMANAME
AND RO.ONAME = D.PACKAGENAME
AND RO.TYPE IN (‘TABLE’, ‘PACKAGE’, ‘MATERIALIZED VIEW’)
AND E.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
AND E.CALLNO = D.CALLNO
GROUP BY RO.GNAME ) IE,
(SELECT GNAME, COUNT(*) CNT3 FROM DBA_REPCATLOG GROUP BY GNAME) A,
(SELECT GNAME, COUNT(*) CNT4 FROM DBA_REPCATLOG
WHERE STATUS = ‘ERROR’
GROUP BY GNAME) B WHERE G.GNAME = IE.GNAME (+)
AND G.GNAME = T.GNAME (+)
AND G.GNAME = A.GNAME (+)
AND G.GNAME = B.GNAME (+) ORDER BY G.GNAME;