Delete Errors (Transaction Queue, Errors)

Posted By Sagar Patil

This call will delete all Errors from replication Queue i.e deferror & deftran

BEGIN
DBMS_DEFER_SYS.DELETE_ERROR( null, null);
END;
CPI_O1>>select count(*) from deferror;
COUNT(*)
----------
71000
CPI_O1>>BEGIN DBMS_DEFER_SYS.DELETE_ERROR( null, null); END;
PL/SQL procedure successfully completed.
CPI_O1VA>>commit;
Commit complete.
CPI_O1>>select count(*) from deferror;
COUNT(*)
----------
0

Also look at this slightly more complicated example. This example will first try to re-apply the error before deleting it:

spool apply_errors.sql
select 'exec dbms_defer_sys.execute_error(''' || deferred_tran_id ||''','''||
destination || ''')' from deferror;
spool off
@apply_errors commit;

spool delete_errors.sql
select 'exec dbms_defer_sys.delete_error(''' || deferred_tran_id ||''','''||
destination || ''')' from deferror;
spool off
@delete_errors commit;

Here’s how to delete all errors having XYZ.COM as a destination:

BEGIN
DBMS_DEFER_SYS.DELETE_ERROR(null, 'XYZ.COM' );
END;

Above API can only delete records. If you had built up a massive replication queue then Deftran, DefError, DefTranDest would grow to enormous size. Even after deleting all records a simple query “select count(*) from Deftrans, DefError£ can takes hours to return results. Under this case , please run following truncate to release space back to system.

truncate table SYSTEM.def$_aqerror

truncate table SYSTEM.def$_aqcall

select dblink,last_txn_count,last_error_number,total_txn_count,total_round_trips,
       total_bytes_received,total_bytes_sent,avg_throughput
       from DEFSCHEDULE where DBLINK like '&1'
DBLINK   LAST_TXN_COUNT    LAST_ERROR_NUMBER    TOTAL_TXN_COUNT    TOTAL_ROUND_TRIPS    TOTAL_BYTES_RECEIVED    TOTAL_BYTES_SENT    AVG_THROUGHPUT
TEST_O5VA    0    0    7779752    1032081    162998568    266832988    13.0574377737869
TEST_O5VC    0    0    7779798    292695    51866294    117581934    10.174205757076

BEGIN  DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (dblink => ‘TEST_O5VA’); END;

BEGIN  DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (dblink => ‘TEST_O5VB’); END;

DBLINK    LAST_TXN_COUNT    LAST_ERROR_NUMBER    TOTAL_TXN_COUNT    TOTAL_ROUND_TRIPS    TOTAL_BYTES_RECEIVED    TOTAL_BYTES_SENT    AVG_THROUGHPUT
TEST_O5VA    0    0    0    0    0    0    0
TEST_O5VC    0        0    0    0    0

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu