On the remote database, the subscriber to the remote queue will be the subscriber on the local database.
The dequeue script that is created is then registered to listen for messages on the remote queue for dequeueing.
Below are the detailed set of scripts for an advanced queue setup. This has been tested on a 11g environment. 184.108.40.206.
Create the queue admin users on both the local and remote databases.
CONNECT sys/change_on_install as sysdba
DROP USER aq_admin CASCADE;
CREATE USER aq_admin IDENTIFIED BY aq_admin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
ALTER USER aq_admin QUOTA UNLIMITED ON users;
GRANT aq_administrator_role TO aq_admin;
GRANT connect TO aq_admin;
GRANT create type TO aq_admin;
GRANT create sequence TO aq_admin;
GRANT CREATE JOB TO aq_admin;
GRANT CREATE PROCEDURE TO aq_admin;
GRANT CREATE TABLE TO aq_admin;
Steps on On the local database
Connect as aq_admin user and create the database link between local and remote databases and make sure the link is working. Test it by accessing a table from the remote database.
CREATE DATABASE LINK ln_uat_dev CONNECT TO aq_admin identified by aq_admin using ’11gdev’;
–Test the kink
Select * from T1@ln_uat_dev; — This should give an output.