26 DBMS_AQADM
The DBMS_AQADM
package provides procedures to manage Oracle Database Advanced Queuing (AQ) configuration and administration information.
See Also:
-
Oracle Database Advanced Queuing (AQ) Types for information about the
TYPE
s to use withDBMS_AQADM
This chapter contains the following topics:
26.1 DBMS_AQADM Security Model
Initially, only SYS
and SYSTEM
have execution privilege for the procedures in DBMS_AQADM
and DBMS_AQ
. Users who have been granted EXECUTE
rights to DBMS_AQ
and DBMS_AQADM
are able to create, manage, and use queues in their own schemas. The MANAGE_ANY
AQ system privilege is used to create and manage queues in other schemas and can be granted and revoked through DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
and DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE
respectively. Starting from Oracle Database 12c Release 2, MANAGE_ANY
privilege will not allow access to SYS
owned queues by users other than SYS
.
User Roles
The database administrator has the option of granting the system privileges ENQUEUE_ANY
and DEQUEUE_ANY
, exercising DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
and DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE
directly to a database user, if you want the user to have this level of control.
The application developer gives rights to a queue by granting and revoking privileges at the object level by exercising DBMS_AQADM.GRANT_QUEUE_PRIVILEGE
and DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE
. Starting from Oracle Database 12c Release 2, ENQUEUE_ANY
and DEQUEUE_ANY
privileges will not allow access to SYS
owned queues by users other than SYS
.
See Also:
-
Oracle Database Advanced Queuing User's Guide for more information on queue privileges and access control.
Security Required for Propagation
Propagation jobs are owned by SYS
, but the propagation occurs in the security context of the queue table owner. Previously propagation jobs were owned by the user scheduling propagation, and propagation occurred in the security context of the user setting up the propagation schedule. The queue table owner must be granted EXECUTE
privileges on the DBMS_AQADM
package. Otherwise, the Oracle Database snapshot processes do not propagate and generate trace files with the error identifier SYS.DBMS_AQADM
not defined. Private database links owned by the queue table owner can be used for propagation. The username specified in the connection string must have EXECUTE
access on the DBMS_AQ
and DBMS_AQADM
packages on the remote database.
See Also:
Oracle Database Advanced Queuing User's Guide for more information on security required for propagation.
Queue Table Migration
The MIGRATE_QUEUE_TABLE
procedure requires that the EXECUTE
privilege on DBMS_AQADM
be granted to the queue table owner, who is probably an ordinary queue user. If you do not want ordinary queue users to be able to create and drop queues and queue tables, add and delete subscribers, and so forth, then you must revoke the EXECUTE
privilege as soon as the migration is done.
See Also:
-
Oracle Database Advanced Queuing User's Guide for more information on granting Oracle Database Advanced Queuing system privileges.
26.2 DBMS_AQADM Constants
When using enumerated constants, such as INFINITE
, TRANSACTIONAL
, or NORMAL_QUEUE
, the symbol must be specified with the scope of the packages defining it. All types associated with the administrative interfaces must be prepended with DBMS_AQADM
. For example: DBMS_AQADM.NORMAL_QUEUE
.
Table 26-1 Enumerated Types in the Administrative Interface
Parameter | Options |
---|---|
|
|
|
|
|
|
See Also:
For more information on the Java classes and data structures used in both DBMS_AQ
and DBMS_AQADM
, see the DBMS_AQ package.
26.3 DBMS_AQADM Subprogram Groups
This section lists and describes the DBMS_AQADM subprogram groups.
This DBMS_AQADM package is made up of the following subprogram groups:
26.3.1 DBMS_AQADM Queue Table Subprograms
This section lists and describes the DBMS_AQADM Queue Table subprograms.
Table 26-2 Queue Table Subprograms
Subprograms | Description |
---|---|
Alters the existing properties of a queue table |
|
Creates a queue table for messages of a predefined type |
|
Drops an existing queue table |
|
A precondition for the enqueue of JMS types and XML types |
|
Upgrades an 8.0-compatible queue table to an 8.1-compatible or higher queue table, or downgrades an 8.1-compatible or higher queue table to an 8.0-compatible queue table |
|
Purges messages from queue tables |
26.3.2 DBMS_AQADM Privilege Subprograms
This sections lists and describes the DBMS_AQADM Privilege subprograms.
Table 26-3 Privilege Subprograms
Subprograms | Description |
---|---|
Grants privileges on a queue to users and roles |
|
Grants Oracle Database Advanced Queuing system privileges to users and roles |
|
Revokes privileges on a queue from users and roles |
|
Revokes Oracle Database Advanced Queuing system privileges from users and roles |
26.3.3 DBMS_AQADM Queue Subprograms
This sections lists and describes the DBMS_AQADM Queue subprograms.
Table 26-4 Queue Subprograms
Subprograms | Description |
---|---|
Alters existing properties of a queue |
|
Creates a nonpersistent |
|
Creates a queue in the specified queue table |
|
Creates a queue and its queue table for a sharded queue all together. |
|
Drops an existing sharded queue from the database queuing system |
|
Alters an sharded queue in the database queuing system |
|
Creates an exception queue for a sharded queue |
|
Drops an existing queue |
|
Returns the subscribers to an 8.0-compatible multiconsumer queue in the PL/SQL index by table collection type |
|
Enables the specified queue for enqueuing or dequeuing |
|
Disables enqueuing or dequeuing on the specified queue |
26.3.4 DBMS_AQADM Subscriber Subprograms
This sections lists and describes the DBMS_AQADM Subscriber subprograms.
Table 26-5 Subscriber Subprograms
Subprograms | Description |
---|---|
Adds a default subscriber to a queue |
|
Alters existing properties of a subscriber to a specified queue |
|
Removes a default subscriber from a queue |
26.3.5 DBMS_AQADM Propagation Subprograms
This section lists and describes the DBMS_AQADM propagation subprograms.
Table 26-6 Propagation Subprograms
Subprograms | Description |
---|---|
Alters parameters for a propagation schedule |
|
Disables a propagation schedule |
|
Enables a previously disabled propagation schedule |
|
Schedules propagation of messages from a queue to a destination identified by a specific database link |
|
Unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific database link |
|
Verifies that the source and destination queues have identical types |
26.3.6 DBMS_AQADM Miscellaneous Subprograms
This section lists and describes the DBMS_AQADM miscellaneous subprograms.
Table 26-7 Miscellaneous Subprograms
Subprograms | Description |
---|---|
Used to get different parameters for sharded queues at queue or database level. |
|
Retrieves the value of Oracle Database Advanced Queuing maximum streams pool memory limit |
|
Retrieves the value of Oracle Database Advanced Queuing minimum streams pool memory limit |
|
Retrieves the value of watermark set by the |
|
Used to set different parameters for sharded queues at queue or database level. |
|
Used for Oracle Database Advanced Queuing to specify and limit maximum streams pool memory use |
|
Used for Oracle Database Advanced Queuing to specify and limit minimum streams pool memory use |
|
Used for Oracle Database Advanced Queuing notification to specify and limit memory use |
|
Used to unset different parameters for sharded queues at queue or database level. |
26.3.7 DBMS_AQADM Agent Subprograms
This section lists and describes the DBMS_AQADM agent subprograms.
Table 26-8 Oracle Streams AQ Agent Subprograms
Subprograms | Description |
---|---|
Alters an agent registered for Oracle Database Advanced Queuing Internet access, and an Oracle Database Advanced Queuing agent that accesses secure queues |
|
Registers an agent for Oracle Database Advanced Queuing Internet access using HTTP/SMTP protocols, and creates an Oracle Database Advanced Queuing agent to access secure queues |
|
Revokes the privileges of a specific database user from an Oracle Database Advanced Queuing Internet agent |
|
Drops an agent that was previously registered for Oracle Database Advanced Queuing Internet access |
|
Grants an Oracle Database Advanced Queuing Internet agent the privileges of a specific database user |
26.4 Summary of DBMS_AQADM Subprograms
This section lists and describes the DBMS_AQADM package subprograms.
Table 26-10 DBMS_AQADM Package Subprograms
Subprograms | Description |
---|---|
Creates an alias for a queue, agent, or a JMS ConnectionFactory in LDAP |
|
Adds a default subscriber to a queue |
|
Alters an agent registered for Oracle Database Advanced Queuing Internet access, and an Oracle Database Advanced Queuing agent that accesses secure queues |
|
Alters parameters for a propagation schedule |
|
Alters existing properties of a queue |
|
Alters the existing properties of a queue table |
|
Provides user the ability to alter the Starting with Oracle Database 20c, sharded queues are deprecated and will be desupported in a futur release. Use Transactional Event Queues(TEQ) instead. |
|
Alters existing properties of a subscriber to a specified queue |
|
Provides user the ability to alter the |
|
Registers an agent for Oracle Database Advanced Queuing Internet access using HTTP/SMTP protocols, and creates an Oracle Database Advanced Queuing agent to access secure queues |
|
Creates a nonpersistent |
|
Creates a queue in the specified queue table |
|
Creates a queue and its queue table for a sharded queue all together. Starting with Oracle Database 20c, sharded queues are deprecated and will be desupported in a futur release. Use Transactional Event Queues(TEQ) instead. |
|
Creates an exception queue. Starting with Oracle Database 20c, sharded queues are deprecated and will be desupported in a futur release. Use Transactional Event Queues(TEQ) instead. |
|
Creates an exception queue. |
|
Creates a queue and its queue table for a Transactional Event Queue (TEQ). |
|
Creates a queue table for messages of a predefined type |
|
Drops an alias for a queue, agent, or JMS ConnectionFactory in LDAP |
|
Revokes the privileges of a specific database user from an Oracle Database Advanced Queuing Internet agent |
|
Disables a propagation schedule |
|
Drops an agent that was previously registered for Oracle Database Advanced Queuing Internet access |
|
Drops an existing queue |
|
Drops an existing sharded queue from the database queuing system Starting with Oracle Database 20c, sharded queues are deprecated and will be desupported in a futur release. Use Transactional Event Queues(TEQ) instead. |
|
Drops an existing TEQ queue from the database queuing system |
|
Drops an existing queue table |
|
Grants an Oracle Database Advanced Queuing Internet agent the privileges of a specific database user |
|
A precondition for the enqueue of JMS types and XML types |
|
Enables a previously disabled propagation schedule |
|
Retrieves the value of Oracle Database Advanced Queuing maximum streams pool memory limit |
|
Retrieves the value of Oracle Database Advanced Queuing minimum streams pool memory limit |
|
Used to get different parameters for sharded queues at queue or database level. |
|
Retrieves the value of watermark set by the |
|
Grants privileges on a queue to users and roles |
|
Grants Oracle Database Advanced Queuing system privileges to users and roles |
|
Upgrades an 8.0-compatible queue table to an 8.1-compatible or higher queue table, or downgrades an 8.1-compatible or higher queue table to an 8.0-compatible queue table |
|
Purges messages from queue tables |
|
Returns the subscribers to an 8.0-compatible multiconsumer queue in the PL/SQL index by table collection type |
|
Removes a default subscriber from a queue |
|
Revokes privileges on a queue from users and roles |
|
Revokes Oracle Database Advanced Queuing system privileges from users and roles |
|
Schedules propagation of messages from a queue to a destination identified by a specific database link |
|
Used to set different parameters for sharded queues at queue or database level. |
|
Used for Oracle Database Advanced Queuing to specify and limit maximum streams pool memory use |
|
Used for Oracle Database Advanced Queuing to specify and limit minimum streams pool memory use |
|
Used for Oracle Database Advanced Queuing notification to specify and limit memory use |
|
Enables the specified queue for enqueuing or dequeuing |
|
Disables enqueuing or dequeuing on the specified queue |
|
Unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific database link |
|
Used to unset different parameters for sharded queues at queue or database level. |
|
Verifies that the source and destination queues have identical types |
26.4.1 ADD_ALIAS_TO_LDAP Procedure
This procedure creates an alias for a queue, agent, or a JMS ConnectionFactory in LDAP. The alias will be placed directly under the database server's distinguished name in LDAP hierarchy.
Syntax
DBMS_AQADM.ADD_ALIAS_TO_LDAP( alias IN VARCHAR2, obj_location IN VARCHAR2);
Parameters
Table 26-11 ADD_ALIAS_TO_LDAP Procedure Parameters
Parameter | Description |
---|---|
|
Name of the alias. Example: |
|
The distinguished name of the object (queue, agent or connection factory) to which |
Usage Notes
This method can be used to create aliases for queues, agents, and JMS ConnectionFactory objects. These object must exist before the alias is created. These aliases can be used for JNDI lookup in JMS and Oracle Database Advanced Queuing Internet access.
26.4.2 ADD_SUBSCRIBER Procedure
This procedure adds a default subscriber to a queue.
Syntax
DBMS_AQADM.ADD_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2 DEFAULT NULL, transformation IN VARCHAR2 DEFAULT NULL queue_to_queue IN BOOLEAN DEFAULT FALSE, delivery_mode IN PLS_INTEGER DEFAULT DBMS_AQADM.PERSISTENT);
Parameters
Table 26-12 ADD_SUBSCRIBER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue. |
|
Agent on whose behalf the subscription is being defined. |
|
A conditional expression based on the message properties, the message data properties and PL/SQL functions. A rule is specified as a Boolean expression using syntax similar to the To specify rules on a message payload (object payload), use attributes of the object type in clauses. You must prefix each attribute with |
|
Specifies a transformation that will be applied when this subscriber dequeues the message. The source type of the transformation must match the type of the queue. If the subscriber is remote, then the transformation is applied before propagation to the remote queue. |
|
If |
|
The administrator may specify one of |
Usage Notes
A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation only succeeds on queues that allow multiple consumers. This operation takes effect immediately, and the containing transaction is committed. Enqueue requests that are executed after the completion of this call will reflect the new behavior.
Any string within the rule must be quoted:
rule => 'PRIORITY <= 3 AND CORRID = ''FROM JAPAN'''
Note that these are all single quotation marks.
26.4.3 ALTER_AQ_AGENT Procedure
This procedure alters an agent registered for Oracle Database Advanced Queuing Internet access. It is also used to alter an Oracle Database Advanced Queuing agent that accesses secure queues.
Syntax
DBMS_AQADM.ALTER_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http IN BOOLEAN DEFAULT FALSE, enable_smtp IN BOOLEAN DEFAULT FALSE, enable_anyp IN BOOLEAN DEFAULT FALSE )
Parameters
Table 26-13 ALTER_AQ_AGENT Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Database Advanced Queuing Internet agent. |
|
Agent's certificate location in LDAP (default is |
|
|
|
|
|
|
26.4.4 ALTER_PROPAGATION_SCHEDULE Procedure
This procedure alters parameters for a propagation schedule.
Syntax
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT 60, destination_queue IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-14 ALTER_PROPAGATION_SCHEDULE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Duration of the propagation window in seconds. A |
|
Date function to compute the start of the next propagation window from the end of the current window. If this value is |
|
Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued. The default value is 60. Caution: if latency is not specified for this call, then latency will over-write any existing value with the default value. For example, if the latency is 60 seconds and there are no messages to be propagated during the propagation window, then messages from that queue for the destination are not propagated for at least 60 more seconds. It will be at least 60 seconds before the queue will be checked again for messages to be propagated for the specified destination. If the latency is 600, then the queue will not be checked for 10 minutes and if the latency is 0, then a job queue process will be waiting for messages to be enqueued for the destination and as soon as a message is enqueued it will be propagated. |
|
Name of the target queue to which messages are to be propagated in the form of a |
26.4.5 ALTER_QUEUE Procedure
This procedure alters existing properties of a queue. The parameters max_retries
, retention_time
, and retry_delay
are not supported for nonpersistent queues.
Syntax
DBMS_AQADM.ALTER_QUEUE ( queue_name IN VARCHAR2, max_retries IN NUMBER DEFAULT NULL, retry_delay IN NUMBER DEFAULT NULL, retention_time IN NUMBER DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE, comment IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-15 ALTER_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue that is to be altered |
|
Limits the number of times a dequeue with A message is moved to an exception queue if Note that |
|
Delay time in seconds before this message is scheduled for processing again after an application rollback. The default is Note that |
|
Retention time in seconds for which a message is retained in the queue table after being dequeued. The default is |
|
Caution: This parameter has been deprecated. |
|
User-specified description of the queue. This user comment is added to the queue catalog. The default value is |
26.4.6 ALTER_QUEUE_TABLE Procedure
This procedure alters the existing properties of a queue table.
Syntax
DBMS_AQADM.ALTER_QUEUE_TABLE ( queue_table IN VARCHAR2, comment IN VARCHAR2 DEFAULT NULL, primary_instance IN BINARY_INTEGER DEFAULT NULL, secondary_instance IN BINARY_INTEGER DEFAULT NULL, replication_mode IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 26-16 ALTER_QUEUE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of a queue table to be created. |
|
Modifies the user-specified description of the queue table. This user comment is added to the queue catalog. The default value is |
|
This is the primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table will be done in this instance. The default value is |
|
The queue table fails over to the secondary instance if the primary instance is not available. The default value is |
|
|
26.4.7 ALTER_SHARDED_QUEUE Procedure
This procedure provides user the ability to alter a sharded queue.
See Also:
Oracle® Database Advanced Queuing User's Guide for information about sharded queues
Syntax
PROCEDURE ALTER_SHARDED_QUEUE( queue_name IN VARCHAR2, max_retries IN NUMBER DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL, queue_properties IN QUEUE_PROPS_T DEFAULT NULL, replication_mode IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 26-17 ALTER_SHARDED_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This parameter specifies the name of the sharded queue. A maximum of 128 characters are allowed. |
|
The maximum number of retries allowed. |
|
The comment of the queue. |
|
Properties such as Normal or Exception Queue, Retry delay, retention time, sort list and cache hint. Refer to QUEUE_PROPS_T Typefor more information about |
|
Reserved for future use. |
26.4.8 ALTER_SUBSCRIBER Procedure
This procedure alters existing properties of a subscriber to a specified queue. Only the rule can be altered.
Syntax
DBMS_AQADM.ALTER_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2 transformation IN VARCHAR2);
Parameters
Table 26-18 ALTER_SUBSCRIBER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue. |
|
Agent on whose behalf the subscription is being altered. See "AQ__AGENT Type". |
|
A conditional expression based on the message properties, the message data properties and PL/SQL functions. The rule parameter cannot exceed 4000 characters. To eliminate the rule, set the rule parameter to |
|
Specifies a transformation that will be applied when this subscriber dequeues the message. The source type of the transformation must match the type of the queue. If the subscriber is remote, then the transformation is applied before propagation to the remote queue. |
Usage Notes
This procedure alters both the rule and the transformation for the subscriber. If you want to retain the existing value for either of them, you must specify its old value. The current values for rule and transformation for a subscriber can be obtained from the schema
.AQ$
queue_table
_R
and schema
.AQ$
queue_table
_S
views.
26.4.9 ALTER_TRANSACTIONAL_EVENT_QUEUE Procedure
This procedure provides user the ability to alter a TEQ queue.
See Also:
Oracle® Database Advanced Queuing User's Guide for information about TEQ queues
Syntax
PROCEDURE ALTER_TRANSACTIONAL_EVENT_QUEUE( queue_name IN VARCHAR2, max_retries IN NUMBER DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL, queue_properties IN QUEUE_PROPS_T DEFAULT NULL, replication_mode IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 26-19 ALTER_TRANSACTIONAL_EVENT_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This parameter specifies the name of the TEQ queue. A maximum of 128 characters are allowed. |
|
The maximum number of retries allowed. |
|
The comment of the queue. |
|
Properties such as Normal or Exception Queue, Retry delay, retention time, sort list and cache hint. Refer to QUEUE_PROPS_T Typefor more information about |
|
Reserved for future use. |
26.4.10 CREATE_AQ_AGENT Procedure
This procedure registers an agent for Oracle Database Advanced Queuing Internet access using HTTP/SMTP protocols. It is also used to create an Oracle Database Advanced Queuing agent to access secure queues.
Syntax
DBMS_AQADM.CREATE_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http IN BOOLEAN DEFAULT FALSE, enable_smtp IN BOOLEAN DEFAULT FALSE, enable_anyp IN BOOLEAN DEFAULT FALSE )
Parameters
Table 26-20 CREATE_AQ_AGENT Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Database Advanced Queuing Internet agent. |
|
Agent's certificate location in LDAP (default is |
|
|
|
|
|
|
Usage Notes
The SYS.AQ$INTERNET_USERS
view has a list of all Oracle Database Advanced Queuing Internet agents.
26.4.11 CREATE_NP_QUEUE Procedure
This procedure creates a nonpersistent RAW
queue.
Note:
Nonpersistent queues are deprecated as of Release 10gR2. Oracle recommends using buffered messaging.
Syntax
DBMS_AQADM.CREATE_NP_QUEUE ( queue_name IN VARCHAR2, multiple_consumers IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-21 CREATE_NP_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the nonpersistent queue that is to be created. The name must be unique within a schema and must follow object name guidelines in Oracle Database SQL Language Reference. |
|
Note that this parameter is distinguished at the queue level, because a nonpersistent queue does not inherit this characteristic from any user-created queue table. |
|
User-specified description of the queue. This user comment is added to the queue catalog. |
Usage Notes
The queue may be either single-consumer or multiconsumer queue. All queue names must be unique within a schema. The queues are created in a 8.1-compatible or higher system-created queue table (AQ$_MEM_SC
or AQ$_MEM_MC
) in the same schema as that specified by the queue name.
If the queue name does not specify a schema name, the queue is created in the login user's schema. After a queue is created with CREATE_NP_QUEUE
, it can be enabled by calling START_QUEUE
. By default, the queue is created with both enqueue and dequeue disabled.
You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the OCI notification mechanism. You cannot invoke the LISTEN
call on a nonpersistent queue.
26.4.12 CREATE_QUEUE Procedure
This procedure creates a queue in the specified queue table.
Syntax
DBMS_AQADM.CREATE_QUEUE ( queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE, max_retries IN NUMBER DEFAULT NULL, retry_delay IN NUMBER DEFAULT 0, retention_time IN NUMBER DEFAULT 0, dependency_tracking IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE);
Parameters
Table 26-22 CREATE_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue that is to be created. The name must be unique within a schema and must follow object name guidelines in Oracle Database SQL Language Reference with regard to reserved characters. |
|
Name of the queue table that will contain the queue. |
|
Specifies whether the queue being created is an exception queue or a normal queue. |
|
Limits the number of times a dequeue with the A message is moved to an exception queue if Note that |
|
Delay time, in seconds, before this message is scheduled for processing again after an application rollback. The default is 0, which means the message can be retried as soon as possible. This parameter has no effect if |
|
Number of seconds for which a message is retained in the queue table after being dequeued from the queue. |
|
Reserved for future use. |
|
User-specified description of the queue. This user comment is added to the queue catalog. |
|
Caution: This parameter has been deprecated. |
Usage Notes
All queue names must be unique within a schema. After a queue is created with CREATE_QUEUE
, it can be enabled by calling START_QUEUE
. By default, the queue is created with both enqueue and dequeue disabled.
26.4.13 CREATE_QUEUE_TABLE Procedure
This procedure creates a queue table for messages of a predefined type.
Syntax
DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table IN VARCHAR2, queue_payload_type IN VARCHAR2, storage_clause IN VARCHAR2 DEFAULT NULL, sort_list IN VARCHAR2 DEFAULT NULL, multiple_consumers IN BOOLEAN DEFAULT FALSE, message_grouping IN BINARY_INTEGER DEFAULT NONE, comment IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE, primary_instance IN BINARY_INTEGER DEFAULT 0, secondary_instance IN BINARY_INTEGER DEFAULT 0, compatible IN VARCHAR2 DEFAULT NULL, secure IN BOOLEAN DEFAULT FALSE replication_mode IN BINARY_INTEGER DEFAULT NONE);
Parameters
Table 26-23 CREATE_QUEUE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of a queue table to be created |
|
Type of the user data stored. See Type Name inDBMS_AQ Data Types for valid values for this parameter. |
|
Storage parameter. The storage parameter is included in the If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. See Oracle Database SQL Language Reference for the usage of these parameters. |
|
The columns to be used as the sort key in ascending order. This parameter has the following format: ' The allowed column names are After a queue table is created with a specific ordering mechanism, all queues in the queue table inherit the same defaults. The order of a queue table cannot be altered after the queue table has been created. If no sort list is specified, then all the queues in this queue table are sorted by the enqueue time in ascending order. This order is equivalent to FIFO order. Even with the default ordering defined, a dequeuer is allowed to choose a message to dequeue by specifying its When See also "Priority and Ordering of Messages" in Oracle Database Advanced Queuing User's Guide for information about message ordering in Oracle Database Advanced Queuing. |
|
|
|
Message grouping behavior for queues created in the table. |
|
User-specified description of the queue table. This user comment is added to the queue catalog. |
|
Note: This parameter has been deprecated. |
|
The primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table are done in this instance. The default value for primary instance is 0, which means queue monitor scheduling and propagation will be done in any available instance. |
|
The queue table fails over to the secondary instance if the primary instance is not available. The default value is 0, which means that the queue table will fail over to any available instance. |
|
The lowest database version with which the queue is compatible. Currently the possible values are either |
|
This parameter must be set to |
|
|
Usage Notes
The sort keys for dequeue ordering, if any, must be defined at table creation time. The following objects are created at this time:
-
aq$_
queue_table_name
_e
, a default exception queue associated with the queue table -
aq$
queue_table_name
, a read-only view, which is used by Oracle Database Advanced Queuing applications for querying queue data -
aq$_
queue_table_name
_t
, an index (or an index organized table (IOT) in the case of multiple consumer queues) for the queue monitor operations -
aq$_
queue_table_name
_i
, an index (or an index organized table in the case of multiple consumer queues) for dequeue operations
For 8.1-compatible or higher queue tables, the following index-organized tables are created:
-
aq$_
queue_table_name
_s
, a table for storing information about the subscribers -
aq$_
queue_table_name
_r
, a table for storing information about rules on subscriptions
aq$_
queue_table_name
_h
, an index-organized table for storing the dequeue history data
CLOB
, BLOB
, and BFILE
are valid attributes for Oracle Database Advanced Queuing object type payloads. However, only CLOB
and BLOB
can be propagated using Oracle Database Advanced Queuing propagation in Oracle8i release 8.1.5 or later. See the Oracle Database Advanced Queuing User's Guide for more information.
The default value of the compatible parameter depends on the database compatibility mode in the init.ora
. If the database is in 10.1-compatible mode, the default value is 10.0. If the database is in 8.1-compatible or 9.2-compatible mode, the default value is 8.1. If the database is in 8.0 compatible mode, the default value is 8.0
You can specify and modify the primary_instance
and secondary_instance
only in 8.1-compatible or higher mode. You cannot specify a secondary instance unless there is a primary instance.
26.4.14 CREATE_SHARDED_QUEUE Procedure
The CREATE_SHARDED_QUEUE
API creates a queue and its queue table as appropriate for a sharded queue. This API cannot be used to create unsharded queues. Sharded queues must be created using this single integrated API that will automatically set AQ properties as needed
Starting with Oracle Database 20c, the CREATE_SHARDED_QUEUE
procedure is deprecated and will be desupported in a future release. Use the CREATE_TRANSACTIONAL_EVENT_QUEUE Procedure procedure instead.
Sharded queues may be either a single consumer or a multi-consumer queue.
Syntax
PROCEDURE CREATE_SHARDED_QUEUE ( queue_name IN VARCHAR2, storage_clause IN VARCHAR2 DEFAULT NULL, multiple_consumers IN BOOLEAN DEFAULT FALSE, max_retries IN NUMBER DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL, queue_payload_type IN VARCHAR2 DEFAULT JMS_TYPE, queue_properties IN QUEUE_PROPS_T DEFAULT NULL, replication_mode IN BINARY_INTEGER DEFAULT NONE, queue_type IN BINARY_INTEGER DEFAULT SHARDED_QUEUE, squeue_ver IN BINARY_INTEGER DEFAULT SQ);
Parameters
Table 26-24 CREATE_SHARDED_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This required parameter specifies the name of the new queue. Maximum of 128 characters allowed. |
|
The storage parameter is included in the If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. See Oracle Database SQL Language Reference for the usage of these parameters. |
|
|
|
This optional parameter limits the number of times that a dequeue can reattempted on a message after a failure. The maximum value of |
|
This optional parameter is a user-specified description of the queue table. This user comment is added to the queue catalog. |
|
Payload can be See DBMS_AQ Data Types. |
|
Properties such as Normal or Exception Queue, Retry delay, retention time, sort list and cache hint. Refer to QUEUE_PROPS_T Type for more information about |
|
Reserved for future use. |
|
This parameter specifies the queue type. The following types are:
The default value is |
|
The sharded queue phase/version.
|
26.4.15 CREATE_EXCEPTION_QUEUE Procedure
The CREATE_EXCEPTION_QUEUE
API creates an exception queue for a sharded queue.
Syntax
PROCEDURE CREATE_EXCEPTION_QUEUE( sharded_queue_name IN VARCHAR2, exception_queue_name IN VARCHAR2 DEFAULT NULL );
Parameters
Table 26-25 CREATE_EXCEPTION_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the sharded queue. |
|
The name of the exception queue. |
26.4.16 CREATE_EQ EXCEPTION_QUEUE Procedure
The CREATE_EQ_EXCEPTION_QUEUE
API creates an exception queue for a TEQ queue.
Syntax
PROCEDURE CREATE_EQ_EXCEPTION_QUEUE( teq_queue_name IN VARCHAR2, exception_queue_name IN VARCHAR2 DEFAULT NULL );
Parameters
Table 26-26 CREATE_EQ_EXCEPTION_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the TEQ queue. |
|
The name of the exception queue. |
26.4.17 CREATE_TRANSACTIONAL_EVENT_QUEUE Procedure
The CREATE_TRANSACTIONAL_EVENT_QUEUE
API creates a queue and its queue table as appropriate for a Transactional Event Queue (TEQ). This API cannot be used to create AQ queues. TEQs must be created using this single integrated API that will automatically set AQ properties as needed.
TEQs may be either a single consumer or a multi-consumer queue.
Syntax
PROCEDURE CREATE_TRANSACTIONAL_EVENT_QUEUE ( queue_name IN VARCHAR2, storage_clause IN VARCHAR2 DEFAULT NULL, multiple_consumers IN BOOLEAN DEFAULT FALSE, max_retries IN NUMBER DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL, queue_payload_type IN VARCHAR2 DEFAULT JMS_TYPE, queue_properties IN QUEUE_PROPS_T DEFAULT NULL, replication_mode IN BINARY_INTEGER DEFAULT NONE);
Parameters
Table 26-27 CREATE_TRANSACTIONAL_EVENT_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This required parameter specifies the name of the new queue. Maximum of 128 characters allowed. |
|
The storage parameter is included in the If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. See Oracle Database SQL Language Reference for the usage of these parameters. |
|
|
|
This optional parameter limits the number of times that a dequeue can reattempted on a message after a failure. The maximum value of |
|
This optional parameter is a user-specified description of the queue table. This user comment is added to the queue catalog. |
|
Payload can be See DBMS_AQ Data Types. |
|
Properties such as Normal or Exception Queue, Retry delay, retention time, sort list and cache hint. Refer to QUEUE_PROPS_T Type for more information about |
|
Reserved for future use. |
26.4.18 DEL_ALIAS_FROM_LDAP Procedure
This procedure drops an alias for a queue, agent, or JMS ConnectionFactory in LDAP.
Syntax
DBMS_AQADM.DEL_ALIAS_FROM_LDAP( alias IN VARCHAR2);
Parameters
Table 26-28 DEL_ALIAS_FROM_LDAP Procedure Parameters
Parameter | Description |
---|---|
|
The alias to be removed. |
26.4.19 DISABLE_DB_ACCESS Procedure
This procedure revokes the privileges of a specific database user from an Oracle Database Advanced Queuing Internet agent.
Syntax
DBMS_AQADM.DISABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
Parameters
Table 26-29 DISABLE_DB_ACCESS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Database Advanced Queuing Internet agent. |
|
Specifies the database user whose privileges are to be revoked from the Oracle Database Advanced Queuing Internet agent. |
Usage Notes
The Oracle Database Advanced Queuing Internet agent should have been previously granted those privileges using the ENABLE_DB_ACCESS Procedure.
26.4.20 DISABLE_PROPAGATION_SCHEDULE Procedure
This procedure disables a propagation schedule.
Syntax
DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, destination_queue IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-30 DISABLE_PROPAGATION_SCHEDULE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Name of the target queue to which messages are to be propagated in the form of a |
26.4.21 DROP_AQ_AGENT Procedure
This procedure drops an agent that was previously registered for Oracle Database Advanced Queuing Internet access.
Syntax
DBMS_AQADM.DROP_AQ_AGENT ( agent_name IN VARCHAR2)
Parameters
Table 26-31 DROP_AQ_AGENT Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Database Advanced Queuing Internet agent |
26.4.22 DROP_QUEUE Procedure
This procedure drops an existing queue.
Syntax
DBMS_AQADM.DROP_QUEUE ( queue_name IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
Parameters
Table 26-32 DROP_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue that is to be dropped. |
|
Caution: This parameter has been deprecated. |
Usage Notes
DROP_QUEUE
is not allowed unless STOP_QUEUE
has been called to disable the queue for both enqueuing and dequeuing. All the queue data is deleted as part of the drop operation.
26.4.23 DROP_QUEUE_TABLE Procedure
This procedure drops an existing queue table.
Syntax
DBMS_AQADM.DROP_QUEUE_TABLE ( queue_table IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE, auto_commit IN BOOLEAN DEFAULT TRUE);
Parameters
Table 26-33 DROP_QUEUE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of a queue table to be dropped. |
|
|
|
Caution: This parameter has been deprecated. |
Usage Notes
All the queues in a queue table must be stopped and dropped before the queue table can be dropped. You must do this explicitly unless the force
option is used, in which case this is done automatically.
26.4.24 DROP_SHARDED_QUEUE Procedure
This procedure drops an existing sharded queue from the database queuing system.
You must stop the queue before calling DROP_SHARDED_QUEUE
. User must stop the queue explicitly if force is set to FALSE
before calling DROP_SHARDED_QUEUE
. If force is set to TRUE
then queue will be stopped internally and then dropped.
Syntax
DBMS_AQADM.DROP_SHARDED_QUEUE( queue_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE )
Parameters
Table 26-34 DROP_SHARDED_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This required parameter specifies the name of the sharded queue. |
|
The sharded queue is dropped even if the queue is not stopped. |
26.4.25 DROP_TRANSACTIONAL_EVENT_QUEUE Procedure
This procedure drops an existing TEQ queue from the database queuing system.
You must stop the queue before calling DROP_TRANSACTIONAL_EVENT_QUEUE
. User must stop the queue explicitly if force is set to FALSE
before calling DROP_TRANSACTIONAL_EVENT_QUEUE
. If force is set to TRUE
then queue will be stopped internally and then dropped.
Syntax
DBMS_AQADM.DROP_TRANSACTIONAL_EVENT_QUEUE( queue_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE )
Parameters
Table 26-35 DROP_TRANSACTIONAL_EVENT_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
This required parameter specifies the name of the TEQ queue. |
|
The TEQ queue is dropped even if the queue is not stopped. |
26.4.26 ENABLE_DB_ACCESS Procedure
This procedure grants an Oracle Database Advanced Queuing Internet agent the privileges of a specific database user.
Syntax
DBMS_AQADM.ENABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
Parameters
Table 26-36 ENABLE_DB_ACCESS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Database Advanced Queuing Internet agent. |
|
Specified the database user whose privileges are to be granted to the Oracle Database Advanced Queuing Internet agent. |
Usage Notes
The Oracle Database Advanced Queuing Internet agent should have been previously created using the CREATE_AQ_AGENT Procedure.
For secure queues, the sender and receiver agent of the message must be mapped to the database user performing the enqueue or dequeue operation.
The SYS.AQ$INTERNET_USERS
view has a list of all Oracle Database Advanced Queuing Internet agents and the names of the database users whose privileges are granted to them.
26.4.27 ENABLE_JMS_TYPES Procedure
Enqueue JMS types and XML types.
Syntax
DBMS_AQADM.ENABLE_JMS_TYPES ( queue_table IN VARCHAR2);
Parameters
Table 26-37 ENABLE_JMS_TYPES Procedure Parameters
Parameter | Description |
---|---|
|
Specifies name of the queue table to be enabled for JMS and XML types. |
26.4.28 ENABLE_PROPAGATION_SCHEDULE Procedure
This procedure enables a previously disabled propagation schedule.
Syntax
DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, destination_queue IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-38 ENABLE_PROPAGATION_SCHEDULE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Name of the target queue to which messages are to be propagated in the form of a |
26.4.29 GET_MAX_STREAMS_POOL Procedure
This procedure retrieves the value of Oracle Database Advanced Queuing maximum streams pool memory limit.
Syntax
DBMS_AQADM.GET_MAX_STREAMS_POOL ( value OUT NUMBER);
Parameters
Table 26-39 GET_MAX_STREAMS_POOL Procedure Parameter
Parameter | Description |
---|---|
|
Value in megabytes. |
26.4.30 GET_MIN_STREAMS_POOL Procedure
This procedure retrieves the value of Oracle Database Advanced Queuing minimum streams pool memory limit.
Syntax
DBMS_AQADM.GET_MIN_STREAMS_POOL ( value OUT NUMBER);
Parameters
Table 26-40 GET_MIN_STREAMS_POOL Procedure Parameter
Parameter | Description |
---|---|
|
Value in megabytes. |
26.4.31 GET_QUEUE_PARAMETER Procedure
This procedure allows user to get different parameters for sharded queues at queue or database level.
For database level the queue_name
should be NULL
. Note that queue overrides database level parameter values.
See Also:
Oracle® Database Advanced Queuing User's Guide for information about sharded queues
Syntax
PROCEDURE GET_QUEUE_PARAMETER( queue_name IN VARCHAR2, param_name IN VARCHAR2, param_value OUT NUMBER);
Parameters
Table 26-41 GET_QUEUE_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the sharded queue. |
|
The name of the parameter. Table 26-42 and Table 26-43 describe the valid parameter names. |
|
The value of the parameter. |
Table 26-42 Sharded queue parameters
Parameter Name | Scope | Allowed Values | Description |
---|---|---|---|
|
Queue level |
|
Number of shards created per instance for a queue, when The default value is |
|
Queue level |
|
When set, the shard to which a message gets enqueued is determined by the key value specified in the message. Refer to key-based sharding (link) for more details. This parameter cannot be unset once set. When this parameter is not set (default), a session is bound to a shard at the time of first enqueue to the queue. All messages enqueued by the session will go to the same shard to which the session is bound. |
|
Queue level |
|
When set, dequeue session sticks to a shard in the queue. A session is bound to a shard on first dequeue from the queue. All messages dequeued by the session come from the same shard to which it is bound. This parameter cannot be unset once set. When this parameter is not set, messages dequeued by a session can spread across multiple shards of the queue. |
Table 26-43 Key-based Parameters
Parameter Name | Scope | Description |
---|---|---|
|
Queue level |
Shard number to which a given key is mapped. When key-based sharding is enabled, this parameter is used to establish mapping between a key and a shard number or retrieve the shard number to which given key is mapped. |
|
Queue level |
Instance number that owns the shard to which a given key is mapped. Applicable only when key-based sharding is enabled. It is a read-only parameter. |
Example 26-1 Key to shard mapping
Users can explicitly map a new key to an existing shard or to a new shard.
To map a key value 'RED
' to a shard with id 0
for queue named MY_SHQ1
, submit the following:
execute
dbms_aqadm.set_queue_paramter('MY_SHQ1', 'AQ$KEY_TO_SHARD_MAP=RED', 0);
If a key is being enqueued which has not been explicitly mapped to a shard, then one of the shards is chosen at random and mapped to that key permanently. Once a key is mapped to shard, the mapping cannot be changed.
To get the shard identifier for a key which is mapped already, submit the following:
declare pval number; begin dbms_aqadm.get_queue_parameter('MY_SHQ1', 'AQ$GET_KEY_SHARD=RED', pval); dbms_output.put_line('The key RED is mapped to shard id ' || pval); END; /
Example 26-2 Key to instance mapping
User sharding performs best when cross instance enqueues are not involved. To know the instance where a key can be enqueued without any cross instance enqueue, submit the following:
declare pval number; begin dbms_aqadm.get_queue_parameter('MY_SHQ1', 'AQ$GET_KEY_SHARD_INST=RED', pval); dbms_output.put_line('The key RED is owned by instance id ' || pval); END; /
26.4.32 GET_WATERMARK Procedure
This procedure retrieves the value of watermark set by SET_WATERMARK
.
Syntax
DBMS_AQADM.GET_WATERMARK ( wmvalue IN NUMBER);
Parameters
Table 26-44 GET_WATERMARK Procedure Parameter
Parameter | Description |
---|---|
|
Watermark value in megabytes. |
26.4.33 GRANT_QUEUE_PRIVILEGE Procedure
This procedure grants privileges on a queue to users and roles. The privileges are ENQUEUE
or DEQUEUE
. Initially, only the queue table owner can use this procedure to grant privileges on the queues.
Syntax
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE ( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2, grant_option IN BOOLEAN := FALSE);
Parameters
Table 26-45 GRANT_QUEUE_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The Oracle Database Advanced Queuing queue privilege to grant. The options are |
|
Name of the queue. |
|
Grantee(s). The grantee(s) can be a user, a role, or the |
|
Specifies if the access privilege is granted with the |
26.4.34 GRANT_SYSTEM_PRIVILEGE Procedure
This procedure grants Oracle Database Advanced Queuing system privileges to users and roles.
The privileges are ENQUEUE_ANY
, DEQUEUE_ANY
, and MANAGE_ANY
. Initially, only SYS
and SYSTEM
can use this procedure successfully.
Note:
Starting from Oracle Database 12c Release 2, MANAGE_ANY
, ENQUEUE_ANY
, and DEQUEUE_ANY
privileges will not allow access to SYS
owned queues by users other than SYS
.
Syntax
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2, admin_option IN BOOLEAN := FALSE);
Parameters
Table 26-46 GRANT_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The Oracle Database Advanced Queuing system privilege to grant. The options are Note: Starting from Oracle Database 12c Release 2, |
|
Grantee(s). The grantee(s) can be a user, a role, or the |
|
Specifies if the system privilege is granted with the If the privilege is granted with the |
26.4.35 MIGRATE_QUEUE_TABLE Procedure
This procedure upgrades an 8.0-compatible queue table to an 8.1-compatible or higher queue table, or downgrades an 8.1-compatible or higher queue table to an 8.0-compatible queue table.
Syntax
DBMS_AQADM.MIGRATE_QUEUE_TABLE ( queue_table IN VARCHAR2, compatible IN VARCHAR2);
Parameters
Table 26-47 MIGRATE_QUEUE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies name of the queue table to be migrated. |
|
Set this to |
26.4.36 PURGE_QUEUE_TABLE Procedure
This procedure purges messages from queue tables. You can perform various purge operations on both single-consumer and multiconsumer queue tables for persistent and buffered messages.
Syntax
DBMS_AQADM.PURGE_QUEUE_TABLE( queue_table IN VARCHAR2, purge_condition IN VARCHAR2, purge_options IN aq$_purge_options_t);
where type aq$_purge_options_t
is described in Oracle Database Advanced Queuing (AQ) Types.
Parameters
Table 26-48 PURGE_QUEUE_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the queue table to be purged. |
|
Specifies the purge condition to use when purging the queue table. The purge condition must be in the format of a SQL When specifying the To purge all queues in a queue table, set |
|
Type
|
Usage Notes
-
You an purge selected messages from the queue table by specifying a purge_condition. Table 26-48 describes these parameters. Messages can be enqueued to and dequeued from the queue table while the queue table is being purged.
-
A trace file is generated in the
udump
destination when you run this procedure. It details what the procedure is doing. -
This procedure commits batches of messages in autonomous transactions. Several such autonomous transactions may get executed as a part of one
purge_queue_table
call depending on the number of messages in the queue table.
26.4.37 QUEUE_SUBSCRIBERS Function
This function returns the subscribers to an 8.0-compatible multiconsumer queue in the PL/SQL index by table collection type DBMS_AQADM.AQ$_subscriber_list_t
.
Each element of the collection is of type sys.aq$_agent
. This functionality is provided for 8.1-compatible queues by the AQ$
queue_table_name
_S
view.
Syntax
DBMS_AQADM.QUEUE_SUBSCRIBERS ( queue_name IN VARCHAR2); RETURN aq$_subscriber_list_t IS
Parameters
Table 26-49 QUEUE_SUBSCRIBERS Function Parameters
Parameter | Description |
---|---|
|
Specifies the queue whose subscribers are to be printed. |
26.4.38 REMOVE_SUBSCRIBER Procedure
This procedure removes a default subscriber from a queue. This operation takes effect immediately, and the containing transaction is committed. All references to the subscriber in existing messages are removed as part of the operation.
Syntax
DBMS_AQADM.REMOVE_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent);
Parameters
Table 26-50 REMOVE_SUBSCRIBER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue. |
|
Agent who is being removed. See AQ$_AGENT Type. |
26.4.39 REVOKE_QUEUE_PRIVILEGE Procedure
This procedure revokes privileges on a queue from users and roles. The privileges are ENQUEUE
or DEQUEUE
.
Syntax
DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE ( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2);
Parameters
Table 26-51 REVOKE_QUEUE_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The Oracle Database Advanced Queuing queue privilege to revoke. The options are |
|
Name of the queue. |
|
Grantee(s). The grantee(s) can be a user, a role, or the |
Usage Notes
To revoke a privilege, the revoker must be the original grantor of the privilege. The privileges propagated through the GRANT
option are revoked if the grantor's privileges are revoked.
26.4.40 REVOKE_SYSTEM_PRIVILEGE Procedure
This procedure revokes Oracle Database Advanced Queuing system privileges from users and roles. The privileges are ENQUEUE_ANY
, DEQUEUE_ANY
and MANAGE_ANY
. The ADMIN
option for a system privilege cannot be selectively revoked. Starting from Oracle Database 12c Release 2, MANAGE_ANY
, ENQUEUE_ANY
, and DEQUEUE_ANY
privileges will not allow access to SYS
owned queues by users other than SYS
.
Syntax
DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2);
Parameters
Table 26-52 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The Oracle Database Advanced Queuing system privilege to revoke. The options are Note: Starting from Oracle Database 12c Release 2, |
|
Grantee(s). The grantee(s) can be a user, a role, or the |
26.4.41 SCHEDULE_PROPAGATION Procedure
This procedure schedules propagation of messages from a queue to a destination identified by a specific database link.
Syntax
DBMS_AQADM.SCHEDULE_PROPAGATION ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, start_time IN DATE DEFAULT SYSDATE, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT 60, destination_queue IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-53 SCHEDULE_PROPAGATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the administrative user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Initial start time for the propagation window for messages from the source queue to the destination. |
|
Duration of the propagation window in seconds. A |
|
Date function to compute the start of the next propagation window from the end of the current window. If this value is |
|
Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued. For example, if the latency is 60 seconds and there are no messages to be propagated during the propagation window, then messages from that queue for the destination are not propagated for at least 60 more seconds. It is at least 60 seconds before the queue is checked again for messages to be propagated for the specified destination. If the latency is 600, then the queue is not checked for 10 minutes, and if the latency is 0, then a job queue process will be waiting for messages to be enqueued for the destination. As soon as a message is enqueued, it is propagated. |
|
Name of the target queue to which messages are to be propagated in the form of a |
Usage Notes
Messages may also be propagated to other queues in the same database by specifying a NULL
destination. If a message has multiple recipients at the same destination in either the same or different queues, the message is propagated to all of them at the same time.
Oracle extensions for JMS such as JMS propagation and remote subscribers are not currently supported for sharded queues. Propagation between sharded and non-sharded queues is not supported
Related Topics
26.4.42 SET_QUEUE_PARAMETER Procedure
This procedure allows user to set different parameters for sharded queues at queue or database level. For database level the queue_name
should be NULL
. Note that queue overrides database level parameter values.
See Also:
Oracle® Database Advanced Queuing User's Guide for information about sharded queues
Syntax
PROCEDURE SET_QUEUE_PARAMETER( queue_name IN VARCHAR2, param_name IN VARCHAR2, param_value IN NUMBER);
Parameters
Table 26-54 SET_QUEUE_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the sharded queue. |
|
The name of the parameter. Table 26-55 and Table 26-56 describe the valid parameter names. |
|
The value of the parameter. |
Table 26-55 Sharded queue parameters
Parameter Name | Scope | Allowed Values | Description |
---|---|---|---|
|
Queue level |
|
Maximum number of shards allowed for the queue. |
|
Queue level |
|
Default value is 20,000. This is the size of each partition for allocation of memory in a shard. Each shard consists of multiple subshards, which are allocated, used, and purged automatically based on message enqueue and dequeue rate for the shard. |
|
Queue level |
|
When set, the shard to which a message gets enqueued is determined by the key value specified in the message. Refer to key-based sharding (link) for more details. This parameter cannot be unset once set. When this parameter is not set (default), a session is bound to a shard at the time of first enqueue to the queue. All messages enqueued by the session will go to the same shard to which the session is bound. |
|
Queue level |
|
When set, dequeue session sticks to a shard in the queue. A session is bound to a shard on first dequeue from the queue. All messages dequeued by the session come from the same shard to which it is bound. This parameter cannot be unset once set. When this parameter is not set, messages dequeued by a session can spread across multiple shards of the queue. |
Table 26-56 Key-based Parameters
Parameter Name | Scope | Description |
---|---|---|
|
Queue level |
Shard number to which a given key is mapped. When key-based sharding is enabled, this parameter is used to establish mapping between a key and a shard number or retrieve the shard number to which given key is mapped. |
|
Queue level |
Instance number that owns the shard to which a given key is mapped. Applicable only when key-based sharding is enabled. It is a read-only parameter. |
Example 26-3 Create a sharded queue with key based enqueues
To create a sharded queue with key based enqueues (user sharding), the PL/SQL procedure DBMS_AQADM.SET_QUEUE_PARAMETER
is called after CREATE_SHARDED_QUEUE
.
execute
sys.dbms_aqadm.create_sharded_queue(queue_name => 'MY_SHQ1');
execute
dbms_aqadm.set_queue_parameter('MY_SHQ1', 'KEY_BASED_ENQUEUE', 1);
Example 26-4 Create a sharded queue with sticky dequeues
To create a sharded queue with key based enqueues (user sharding), the PL/SQL procedure DBMS_AQADM.SET_QUEUE_PARAMETER
is called after CREATE_SHARDED_QUEUE
.
execute
sys.dbms_aqadm.create_sharded_queue(queue_name => 'MY_SHQ1');
execute
dbms_aqadm.set_queue_parameter('MY_SHQ1', 'STICKY_DEQUEUE', 1);
Example 26-5 Setting or changing the number of shards
Once a queue is created, it can have a maximum of 5 shards by default in non-Oracle RAC databases. In Oracle RAC databases, each queue can have a maximum of 5 shards per database instance by default.
execute
dbms_aqadm.set_queue_parameter('MY_SHQ1', 'SHARD_NUM', 200);
Note:
Odd numbered shard identifiers are reserved for internal use. In the above example, the 200 shard identifiers used will be 0, 2, 4, …, 398.
26.4.43 SET_MAX_STREAMS_POOL Procedure
This procedure is used for Oracle Database Advanced Queuing to specify and limit maximum streams pool memory use.
Syntax
DBMS_AQADM.SET_MAX_STREAMS_POOL ( value IN NUMBER);
Parameters
Table 26-57 SET_MAX_STREAMS_POOL Procedure Parameter
Parameter | Description |
---|---|
|
Value in megabytes. |
26.4.44 SET_MIN_STREAMS_POOL Procedure
This procedure is used for Oracle Database AQ to specify and limit minimum streams pool memory use.
Syntax
DBMS_AQADM.SET_MIN_STREAMS_POOL ( value IN NUMBER);
Parameters
Table 26-58 SET_MIN_STREAMS_POOL Procedure Parameter
Parameter | Description |
---|---|
|
Value in megabytes. |
26.4.45 SET_WATERMARK Procedure
This procedure is used for Oracle Database Advanced Queuing notification to specify and limit memory use.
Syntax
DBMS_AQADM.SET_WATERMARK ( wmvalue IN NUMBER);
Parameters
Table 26-59 SET_WATERMARK Procedure Parameter
Parameter | Description |
---|---|
|
Watermark value in megabytes. |
26.4.46 START_QUEUE Procedure
This procedure enables the specified queue for enqueuing or dequeuing.
Syntax
DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE);
Parameters
Table 26-60 START_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue to be enabled |
|
Specifies whether |
|
Specifies whether |
Usage Notes
After creating a queue, the administrator must use START_QUEUE
to enable the queue. The default is to enable it for both ENQUEUE
and DEQUEUE
. Only dequeue operations are allowed on an exception queue. This operation takes effect when the call completes and does not have any transactional characteristics.
26.4.47 STOP_QUEUE Procedure
This procedure disables enqueuing or dequeuing on the specified queue.
Syntax
DBMS_AQADM.STOP_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE, free_memory IN BOOLEAN DEFAULT FALSE);
Parameters
Table 26-61 STOP_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the queue to be disabled |
|
Specifies whether |
|
Specifies whether |
|
Specifies whether to wait for the completion of outstanding transactions. |
|
Specifies whether the queue should be stopped. |
Usage Notes
By default, this call disables both ENQUEUE
and DEQUEUE
. A queue cannot be stopped if there are outstanding transactions against the queue. This operation takes effect when the call completes and does not have any transactional characteristics.
26.4.48 UNSCHEDULE_PROPAGATION Procedure
This procedure unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific database link.
Syntax
DBMS_AQADM.UNSCHEDULE_PROPAGATION ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL destination_queue IN VARCHAR2 DEFAULT NULL);
Parameters
Table 26-62 UNSCHEDULE_PROPAGATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the administrative user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Name of the target queue to which messages are to be propagated in the form of a |
26.4.49 UNSET_QUEUE_PARAMETER Procedure
This procedure allows user to unset different parameters for sharded queues at queue or database level.
For database level the queue_name
should be NULL
. Note that queue overrides database level parameter values.
See Also:
Oracle® Database Advanced Queuing User's Guide for information about sharded queues
Syntax
PROCEDURE UNSET_QUEUE_PARAMETER( queue_name IN VARCHAR2, param_name IN VARCHAR2);
Parameters
Table 26-63 UNSET_QUEUE_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the sharded queue. |
|
The name of the parameter. Table 26-64 and Table 26-65 describe the valid parameter names. |
Table 26-64 Sharded queue parameters
Parameter Name | Scope | Allowed Values | Description |
---|---|---|---|
|
Queue level |
|
Maximum number of shards allowed for the queue. |
|
Queue level |
|
Default value is 20,000. This is the size of each partition for allocation of memory in a shard. Each shard consists of multiple subshards, which are allocated, used, and purged automatically based on message enqueue and dequeue rate for the shard. |
|
Queue level |
|
When set, the shard to which a message gets enqueued is determined by the key value specified in the message. Refer to key-based sharding (link) for more details. This parameter cannot be unset once set. When this parameter is not set (default), a session is bound to a shard at the time of first enqueue to the queue. All messages enqueued by the session will go to the same shard to which the session is bound. |
|
Queue level |
|
When set, dequeue session sticks to a shard in the queue. A session is bound to a shard on first dequeue from the queue. All messages dequeued by the session come from the same shard to which it is bound. This parameter cannot be unset once set. When this parameter is not set, messages dequeued by a session can spread across multiple shards of the queue. |
Table 26-65 Key-based Parameters
Parameter Name | Scope | Description |
---|---|---|
|
Queue level |
Shard number to which a given key is mapped. When key-based sharding is enabled, this parameter is used to establish mapping between a key and a shard number or retrieve the shard number to which given key is mapped. |
|
Queue level |
Instance number that owns the shard to which a given key is mapped. Applicable only when key-based sharding is enabled. It is a read-only parameter. |
26.4.50 VERIFY_QUEUE_TYPES Procedure
This procedure verifies that the source and destination queues have identical types.
The result of the verification is stored in the table sys
.aq$_message_types
, overwriting all previous output of this command.
Syntax
DBMS_AQADM.VERIFY_QUEUE_TYPES ( src_queue_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, rc OUT BINARY_INTEGER);
Parameters
Table 26-66 VERIFY_QUEUE_TYPES Procedure Parameters
Parameter | Description |
---|---|
|
Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user. |
|
Name of the destination queue where messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user. |
|
Destination database link. Messages in the source queue for recipients at this destination are propagated. If it is |
|
Return code for the result of the procedure. If there is no error, and if the source and destination queue types match, then the result is 1. If they do not match, then the result is 0. If an Oracle error is encountered, then it is returned in |
Note:
-
SYS.AQ$_MESSAGE_TYPES
can have multiple entries for the same source queue, destination queue, and database link, but with different transformations. -
VERIFY_QUEUE_TYPES
check happens once per AQ propagation schedule and not for every propagated message send. -
In case the payload of the queue is modified then the existing propagation schedule between source and destination queue needs to be dropped and recreated.