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

 

 


 

 

 

        
 

 Oracle DBMS_AQADM Package
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.

The dbms_aqadm package supports configuring and managing Oracle Stream Advanced Queuing such as Queue Tables, commonly referred to as AQ. This package and some OEM screens/utilities built on top of it are the only interfaces for these types of objects. There are no native DDL commands for them, so there is no such thing as CREATE TABLE for queue tables. One must use this package. Dbms_aqadm is a rather large and complex PL/SQL package. Thus many DBAs may simply prefer to use the OEM screens for managing queues.

 

There are a few enumerated constants that one must know to use this package:

 

-- retention

INFINITE                   CONSTANT BINARY_INTEGER := -1;

 

-- message grouping

TRANSACTIONAL              CONSTANT BINARY_INTEGER := 1;

NONE                       CONSTANT BINARY_INTEGER := 0;

 

-- queue type

NORMAL_QUEUE               CONSTANT BINARY_INTEGER := 0;

EXCEPTION_QUEUE            CONSTANT BINARY_INTEGER := 1;

NON_PERSISTENT_QUEUE       CONSTANT BINARY_INTEGER := 2;

 

-- non-repudiation properties

NON_REPUDIATE_SENDER       CONSTANT BINARY_INTEGER := 1;

NON_REPUDIATE_SNDRCV       CONSTANT BINARY_INTEGER := 2;

 

-- protocols (note that FTP is not supported yet so it is not part of anyp).

TTC                        CONSTANT BINARY_INTEGER := 0;

HTTP                       CONSTANT BINARY_INTEGER := 1;

SMTP                       CONSTANT BINARY_INTEGER := 2;

FTP                        CONSTANT BINARY_INTEGER := 4;

ANYP                       CONSTANT BINARY_INTEGER := HTTP + SMTP;

 

LOGMINER_PROTOCOL          CONSTANT BINARY_INTEGER := 1;

LOGAPPLY_PROTOCOL          CONSTANT BINARY_INTEGER := 2;

TEST_PROTOCOL              CONSTANT BINARY_INTEGER := 3;

 

-- Constants for LDAP connection factory type

AQ_QUEUE_CONNECTION        CONSTANT BINARY_INTEGER := 1;

AQ_TOPIC_CONNECTION        CONSTANT BINARY_INTEGER := 2;

 

-- Constants for delivery mode

PERSISTENT                 CONSTANT BINARY_INTEGER := 1 ;

BUFFERED                   CONSTANT BINARY_INTEGER := 2 ;

PERSISTENT_OR_BUFFERED     CONSTANT BINARY_INTEGER := 3 ;

 

-- subscriber properties.

QUEUE_TO_QUEUE_SUBSCRIBER  CONSTANT BINARY_INTEGER := 8;

 

-- Constants for get/set_replay_info

LAST_ENQUEUED              CONSTANT BINARY_INTEGER := 0;

LAST_ACKNOWLEDGED          CONSTANT BINARY_INTEGER := 1;

 

Add_alias_to_ldap is a procedure that creates an alias to an object like queue, agent or connection factory which is placed under the database in the LDAP hierarchy. Of course, the object must exist prior to creating an alias for it.

 

Argument

Type

In / Out

Default Value

ALIAS

VARCHAR2

IN

 

OBJ_LOCATION

VARCHAR2

IN

 

Table 6.59:  Add_alias_to_ldap Parameters

Add_subscriber is a procedure that adds a subscriber to queue. The rule parameter is a conditional expression string much like a WHERE clause and must be properly (double) quoted.

 

Argument

Type

In / Out

Default Value

QUEUE_NAME

VARCHAR2

IN

 

SUBSCRIBER

SYS.AQ$_AGENT

IN

 

RULE

VARCHAR2

IN

NULL

TRANSFORMATION

VARCHAR2

IN

NULL

QUEUE_TO_QUEUE

BOOELAN

IN

FALSE

DELIVERY_MODE

PLS_INTEGER

IN

DBMS_AQADM.PERSISTENT

Table 6.60:  Add_subscriber Parameters

Alter_aq_agent is a procedure that modifies an AQ agent’s registration for internet access; however, for access through HTTP, the certificate location is not required.

 

Argument

Type

In / Out

Default Value

AGENT_NAME

VARCHAR2

IN

 

CERTIFICATE_LOCATION

VARCHAR2

IN

NULL

ENABLE_HTTP

BOOLEAN

IN

FALSE

ENABLE_SMTP

BOOLEAN

IN

FALSE

ENABLE_ANYP

BOOELAN

IN

FALSE

 

 

 

 

 

 

Table 6.61:  Alter_aq_agent Parameters

Alter_propagation_schedule is a procedure used to alter the parameters of a propagation schedule. If the destination parameter for the destination database link is NULL, then the destination is the local database and all messages are propagated to other local database queues.

 

Argument

Type

In / Out

Default Value

QUEUE_NAME

VARCHAR2

IN

 

DESTINATION

VARCHAR2

IN

NULL

DURATION

NUMBER

IN

NULL

NEXT_TIME

VARCHAR2

IN

NULL

LATENCY

NUMBER

IN

60

DESTINATION_QUEUE

VARCHAR2

IN

NULL

Table 6:62:  Alter_propagation_schedule

Alter_queue is a procedure that modifies a queue’s properties. Note that a message is automatically transferred to an exception queue if retry_count > Max_retries. RETRY_COUNT. Also note that the auto_commit parameter has been deprecated.

 

Argument

Type

In / Out

Default Value

QUEUE_NAME

VARCHAR2

IN

 

MAX_RETRIES

NUMBER

IN

NULL

RETRY_DELAY

NUMBER

IN

NULL

RETENTION_TIME

NUMBER

IN

NULL

AUTO_COMMIT

BOOLEAN

IN

TRUE

COMMENT

VARCHAR2

IN

NULL

Table 6.63:  Alter_queue Parameters

Alter_queue_table is a procedure that modifies a queue table’s properties. The primary instance performs the monitor scheduling and propagation for the queues in the queue table. The secondary instance is strictly for failover, i.e. when the primary instance is unavailable.

 

Argument

Type

In / Out

Default Value

QUEUE_TABLE

VARCHAR2

IN

 

COMMENT

VARCHAR2

IN

NULL

PRIMARY_INSTACNE

BINARY_INTEGER

IN

NULL

SECONDARY_INSTANCE

BINARY_INTEGER

IN

NULL

Table 6.64:  Alter_queue_table Parameters

     

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.