Monitoring Purges of Successfully Propagated Transactions

• Listing General Information About the Purge Job
• Checking the Definition of the Purge Job
• Determining the Amount of Time Since the Last Purge
• Determining the Total Number of Purged Transactions

1. Listing General Information About the Purge Job

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN PRIV_USER HEADING ‘Privilege|Schema’ FORMAT A10
COLUMN BROKEN HEADING ‘Broken?’ FORMAT A7
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A25

SELECT JOB,
PRIV_USER,
BROKEN,
TO_CHAR(NEXT_DATE, ‘DD.MM.YYYY:HH:MI:SS AM’) next_start,
INTERVAL
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’
ORDER BY 1;

2. Checking the Definition of the Purge Job

SELECT WHAT “Definition of the Purge Job”
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’ ORDER BY 1;

3. Determining the Amount of Time Since the Last Purge

SELECT ((SYSDATE – LAST_PURGE_TIME) / 60) “Minutes Since Last Purge”
FROM V$REPLQUEUE;

4. Determining the Total Number of Purged Transactions

SELECT TXNS_PURGED “Transactions Purged”
FROM V$REPLQUEUE;