This content has been marked as final. Show 1 reply
The implicit triggers on the tables will help you to capture the transactions and enqueue it to the deferred queue. The push job (dbms_job) will actually push all these deferred transactions to the target or destination database.
As far as I have seen (I am pretty much experienced with Oracle Advanced Replication and Streams) there is no need for you to schedule the purge job for every 1 minute interval. You can schedule it to run in every 30 mins or 1 hour and that should be sufficient. The push needs to be scheduled based on your business requirements for the data on the destination database.
Answering your questions:
1. The deferred transactions are in the system queue called SYSTEM.DEF$_AQCALL. You can check:
SQL> select name,queue_table from dba_queues where owner='SYSTEM';
The deferred transactions are basically stored in queue table SYSTEM.DEF$_AQCALL and accessed via the queue interface DEF$_AQCALL (same name for both). The errored transactions are kept in SYSTEM.DEF$_AQERROR. These are stored as individual calls in a transaction. Oracle has some procedures which allows you print these transactions and you can view the statements in them.
2. Generally you dont need to do a manual push or purge, provided if your job executes fine. What generally happens is, if there is a network issue or any other kinds of issues, your job keeps failing. After the job has failed 16 times, it gets broken and then does not auto-execute further. In that case you will have to manually execute the job with following (this generally happens only to push, purge normally works fine):
select log_user,job,what from dba_jobs where what like '%push%';
select log_user,job,what from dba_jobs where what like '%purge%';
This will enable the job and will auto-execute next time.
Hope this is helpful.