Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Conditional Job Runs Using Oracle
Advanced Queuing

Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

In this method, all tasks in the chain are scheduled as regular repeating jobs.  When a task completes successfully, it places a message on a queue for the next task to read.  With the exception of the first task, the first operation a task performs is read from its queue.  If there is a message on the queue, the task can proceed; otherwise, it waits indefinitely for the message to arrive.

 

Before any code can be written, a queuing infrastructure needs to be set up using the job_chain_aq_setup.sql script and background information must be introduced.  A full introduction to Oracle Advanced Queuing is beyond the scope of this book, so explanations will be limited to just those elements necessary to build a simple working system.

 

job_chain_aq_setup.sql

 

-- Grant necessary permissions

conn sys/password as sysdba

 

-- Create the queue payload

CREATE OR REPLACE TYPE job_user.job_chain_msg_type AS OBJECT (

  message  VARCHAR2(10)

)

/

-- Create the queue table and queues

BEGIN

  DBMS_AQADM.create_queue_table (

     queue_table            =>  'job_user.job_chain_queue_tab',

     queue_payload_type     =>  'job_user.job_chain_msg_type');

 

  DBMS_AQADM.create_queue (

     queue_name            =>  'job_user.task_2_queue',

     queue_table           =>  'job_user.job_chain_queue_tab');

 

  DBMS_AQADM.create_queue (

     queue_name            =>  'job_user.task_3_queue',

     queue_table           =>  'job_user.job_chain_queue_tab');

 

  DBMS_AQADM.start_queue (

     queue_name         => 'job_user.task_2_queue',

     enqueue            => TRUE);

 

  DBMS_AQADM.start_queue (

     queue_name         => 'job_user.task_3_queue',

     enqueue            => TRUE);

END;

/

grant execute on dbms_aq to job_user;

 

conn job_user/job_user

 

Advanced Queuing (AQ) is Oracle’s implementation of a messaging system which can be used as a replacement for the dbms_pipe package and other bespoke solutions.  The basic unit of any messaging system is a message with the most important element of the message being its contents, or payload. 

 

In order to define a queue table, the payload of the messages that will be stored within it must first be defined.  The job_chain_aq_setup.sql script contains a definition of an object type called job_chain_msg_type that will act as the payload.  The creation of object types requires the CREATE TYPE privilege.

 

The payload of the message can be as simple or complicated as desired.  In this case, the only concern is that the message has been sent.  The particular contents are not important at this time, so the message is extremely simple.

 

Administration of queues is done using the dbms_aqadm package and requires the aq_administrator_role to be granted to the administrator.  Alternatively, all administration can be performed by a privileged user such as SYS or SYSTEM.  With the payload object defined, the queue table is created using the create_queue_table  procedure.

 

Once the queue table has been created, the individual queues are created and started using the create_queue and start_queue procedures, respectively.  A single queue table can hold many queues as long as each queue uses the same type for its payload.

 

Messages are queued and dequeued using the dbms_aq  package.  Access to this package can be granted using the aq_user_role  role. However, access to it from a stored procedure is achieved by using the job_chain_aq_setup.sql script. This grants the privilege on this object directly to the test user.

 

The contents of the queue table can be monitored using the job_chain_aq_query.sql script.

 

job_chain_aq_query.sql

 

select

   queue,

   count(*) as messages

from

   aq$job_chain_queue_taB

group by

   queue

order by

   queue

;

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.