Search code examples
oracle-databaseupdatesoracle12cdatabase-linkadvanced-queuing

Oracle Advanced Queues versus a Small Oracle Database Table

I'm looking for a simple way to communicate between two databases, there currently exists a database link between both database.

I want to process a job on database 1 for a batch of records (batch code for each batch of records), once the process has finished on database 1 and all the batches of records have been processed. I want database 2 to see that database 1 has processed a number of batches (batch codes) either by querying a oracle table or an Oracle advanced queue which sits on either database 1 or database 2.

Database 2 will process the batches of records that are on database 1 through a database linked view using each batch code and update the status of that batch to complete.

I want to be able to update the Oracle Advanced Queue or database table of its batch no, progress status ('S' started, 'C' completed), status date

Table name.

batch_records

Table columns

Batch No,
Status,
status date

Questions:

  1. Can this be done by a simple database table rather than a complex Oracle Advanced Queue?

  2. Can a table be updated over a database link?

  3. Are there any examples of this?

Solution

  • To answer your question first:

    1. yes, I believe so
    2. yes, it can. But, if there are many rows involved, it can be pretty slow
    3. probably

    Database link is the way to communicate between two databases. If those jobs run on the database 1 (DB1), I'd suggest you to keep it there - in the DB1. Doing stuff over a database link calls for problems of different kinds. Might be slow, you can't do everything over the database link (LOBs, for example). One option is to schedule a job (using DBMS_SCHEDULER or DBMS_JOB (which is quite OK for simple things)). Let the procedure maintain job status in some table (that would be a "simple table" from your 1st question) in DB1 which will be read by the DB2.

    How? Do it directly, or create a materialized view which will be refreshed in a scheduled manner (e.g. every morning at 07:00) or on demand (not that good idea) or on commit (once the DB1 procedure does the job and commits changes, materialized view will be refreshed).

    If there aren't that many rows involved, I'd probably read the DB1 status table directly, and think of other options later (if necessary).