Automatically Enabling Oracle Advanced Queues

After the migration of several schemata with a lot of Oracle DB-Queues to another database, all the queues where disabled.

In order to re-enable these queues, I wrote the following script:

PROMPT ******************************************************

PROMPT ****               Enable Queues                  ****

PROMPT ******************************************************

SET FEEDBACK OFF

SET VERIFY OFF

SET SERVEROUTPUT ON

SET HEAD OFF

SET TIMING OFF

SET TRIMSPOOL ON

SET LINESIZE 200

SET PAGESIZE 999

COLUMN txt FORMAT A100;

SPOOL tmp_EnableQueues.sql

SELECT --owner, name, queue_table, queue_type, enqueue_enabled, dequeue_enabled,

       DECODE(TRIM(enqueue_enabled),'NO','EXEC SYS.DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'''||owner||'.'||name||''',ENQUEUE=>TRUE);', '')||chr(10)||

       DECODE(TRIM(dequeue_enabled),'NO','EXEC SYS.DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'''||owner||'.'||name||''',DEQUEUE=>TRUE);', '')

  FROM dba_queues

 WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN')

   AND queue_type <> 'EXCEPTION_QUEUE'

   AND (TRIM(enqueue_enabled) = 'NO' OR TRIM(dequeue_enabled) = 'NO');

SPOOL OFF

@tmp_EnableQueues.sql

PROMPT ... done

SET HEAD ON

SET FEEDBACK ON

EXIT;

For every queue one statement for each "direction" (enqueue / dequeue) is generated.

The generated statements are written (Spooled) into the file tmp_EnableQueues.sql

Then the file tmp_EnableQueues.sql is executed, enabling each queue.

I hope it helps.

Tags: Oracle, SQL*Plus, SQL, Publish, DB-Queue, Oracle Streams, Advanced Queues
July 27, 2016 at 02:22PM