Oracle | DBMS

Oracle DBMS_AQADM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Administration of Advanced Queue queues

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
12c Sharded Queue
AUTO	BINARY_INTEGER	1
CACHED	BINARY_INTEGER	2
UNCACHED	BINARY_INTEGER	3
Delivery Mode
PERSISTENT	BINARY_INTEGER	1
BUFFERED	BINARY_INTEGER	2
PERSISTENT_OR_BUFFERED	BINARY_INTEGER	3
Get/Set_Replay_Info
LAST_ENQUEUED	BINARY_INTEGER	0
LAST_ACKNOWLEDGED	BINARY_INTEGER	1
GoldenGate (OGG) Replicated Queue
REPLICATION_MODE	BINARY_INTEGER	1
PROPAGATION_MODE	BINARY_INTEGER	2
SWITCHOVER_FORCE	BINARY_INTEGER	4
LDAP
AQ_QUEUE_CONNECTION	BINARY_INTEGER	1
AQ_TOPIC_CONNECTION	BINARY_INTEGER	2
Message Grouping
TRANSACTIONAL	BINARY_INTEGER	1
NONE	BINARY_INTEGER	0
Non-Repudiation Properties
NON_REPUDIATE_SENDER	BINARY_INTEGER	1
NON_REPUDIATE_SNDRCV	BINARY_INTEGER	2
Payload
JMS_TYPE	VARCHAR2(10)	'JMS'
Protocols
TTC	BINARY_INTEGER	0
HTTP	BINARY_INTEGER	1
SMTP	BINARY_INTEGER	2
FTP	BINARY_INTEGER	4
ANYP	BINARY_INTEGER	HTTP + SMTP
LOGMINER_PROTOCOL	BINARY_INTEGER	1
LOGAPPLY_PROTOCOL	BINARY_INTEGER	2
TEST_PROTOCOL	BINARY_INTEGER	3
Queue Type
NORMAL_QUEUE	BINARY_INTEGER	0
EXCEPTION_QUEUE	BINARY_INTEGER	1
NON_PERSISTENT_QUEUE	BINARY_INTEGER	2
Retention
INFINITE	BINARY_INTEGER	-1
Retention Types
DEQUEUE_TIME	BINARY_INTEGER	0
ENQUEUE_TIME	BINARY_INTEGER	1
ENQUEUE_TIME_AND_ALL_DEQUEUED	BINARY_INTEGER	2
NUM_RETENTION_TYPES	BINARY_INTEGER	3
Sort List
PRIORITY	BINARY_INTEGER	1
ENQ_TIME	BINARY_INTEGER	2
PRIORITY_ENQ_TIME	BINARY_INTEGER	3
COMMIT_TIME	BINARY_INTEGER	4
PRIORITY_COMMIT_TIME	BINARY_INTEGER	5
ENQ_TIME_PRIORITY	BINARY_INTEGER	7
Subscriber
QUEUE_TO_QUEUE_SUBSCRIBER	BINARY_INTEGER	8

Data Types TYPE sys.aq$_agent AS OBJECT( name VARCHAR2(30), -- name of message producer or consumer address VARCHAR2(1024), -- address where message to be sent protocol NUMBER DEFAULT 0); -- must be 0 / TYPE aq$_purge_options_t IS RECORD(block BOOLEAN DEFAULT FALSE, delivery_mode PLS_INTEGER DEFAULT dbms_aqadm.persistent); / TYPE aq$_subscriber_list_t IS TABLE OF sys.aq$_agent INDEX BY BINARY_INTEGER; / TYPE queue_props_t IS RECORD ( queue_type BINARY_INTEGER DEFAULT NORMAL_QUEUE, retry_delay NUMBER DEFAULT 0, retention_time NUMBER DEFAULT 0, sort_list VARCHAR2(30) DEFAULT NULL, cache_hint BINARY_INTEGER DEFAULT AUTO);

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_AQADM' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_AQADM' ORDER BY 1;

Returns 43 objects

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-00904	Not a Sharded Queue: invalid identifier
ORA-24079	Invalid name <string>, names with AQ$_ prefix are not valid for QUEUE_TABLE

First Available Not known

Parameters

Parameter	Options
message_grouping	TRANSACTIONAL, NONE
queue_type	NORMAL_QUEUE, EXCEPTION_QUEUE, NON_PERSISTENT_QUEUE
retention	0, 1, 2 ... INFINITE

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to the users CTX_SYS, MDSYS, SYSTEM, WMSYS and the AQ_ADMINISTRATOR_ROLE, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, OEM_MINITOR, OGG_APPLY, OGG_APPLY_PROCREP, OGG_CAPTURE, and OGG_SHARED_CAPTURE roles

Source {ORACLE_HOME}/rdbms/admin/dbmsaqad.sql
also see: {ORACLE_HOME}/rdbms/admin/catqueue.sql

Subprograms

ADD_ALIAS_TO_LDAP	GRANT_PRIV_FOR_RM_PLAN
ADD_CONNECTION_TO_LDAP	GRANT_QUEUE_PRIVILEGE
ADD_SUBSCRIBER	GRANT_SCHEMA_PRIVILEGE
ALTER_AQ_AGENT	GRANT_SYSTEM_PRIVILEGE
ALTER_DATABASE_KAFKA_TOPIC	GRANT_TYPE_ACCESS
ALTER_PROPAGATION_SCHEDULE	INVALIDATE_QUEUE
ALTER_QUEUE	ISSHARDEDQUEUE
ALTER_QUEUE_TABLE	IS_AQ_TABLE
ALTER_SHARDED_QUEUE	IS_TRANSACTIONAL_EVENT_QUEUE
ALTER_SUBSCRIBER	MIGRATE_QUEUE_TABLE
ALTER_TRANSACTIONAL_EVENT_QUEUE	MOVE_QUEUE_TABLE
AQ$_PROPAQ	NEXT_SEQNUM
CREATE_AQ_AGENT	NONREPUDIATE_RECEIVER
CREATE_DATABASE_KAFKA_TOPIC	NONREPUDIATE_SENDER
CREATE_EQ_EXCEPTION_QUEUE	PURGE_QUEUE_TABLE
CREATE_EXCEPTION_QUEUE	QUEUE_SUBSCRIBERS
CREATE_NP_QUEUE	RECOVER_PROPAGATION
CREATE_QUEUE	RECOVER_QUEUE_TABLE
CREATE_QUEUE_TABLE	REMOVE_SUBSCRIBER
CREATE_SHARDED_QUEUE	RESET_REPLAY_INFO
CREATE_TRANSACTIONAL_EVENT_QUEUE	REVOKE_QUEUE_PRIVILEGE
DEL_ALIAS_FROM_LDAP	REVOKE_SCHEMA_PRIVILEGE
DEL_CONNECTION_FROM_LDAP	REVOKE_SYSTEM_PRIVILEGE
DISABLE_DB_ACCESS	SCHEDULE_PROPAGATION
DISABLE_PROPAGATION_SCHEDULE	SET_MAX_STREAMS_POOL
DROP_AQ_AGENT	SET_MIN_STREAMS_POOL
DROP_DATABASE_KAFKA_TOPIC	SET_QUEUE_PARAMETER
DROP_QUEUE	SET_WATERMARK
DROP_QUEUE_TABLE	START_QUEUE
DROP_SHARDED_QUEUE	START_TIME_MANAGER
DROP_TRANSACTIONAL_EVENT_QUEUE	STOP_QUEUE
ENABLE_DB_ACCESS	STOP_TIME_MANAGER
ENABLE_JMS_TYPES	UNSCHEDULE_PROPAGATION
ENABLE_PROPAGATION_SCHEDULE	UNSET_QUEUE_PARAMETER
GET_MAX_STREAMS_POOL	VALIDATE_QUEUE
GET_MIN_STREAMS_POOL	VERIFY_QUEUE_TYPES
GET_PROP_SEQNO	VERIFY_QUEUE_TYPES_GET_NRP
GET_QUEUE_PARAMETER	VERIFY_QUEUE_TYPES_NO_QUEUE
GET_REPLAY_INFO	VERIFY_SHARDED_QUEUE
GET_TYPE_INFO	VERIFY_TRANSACTIONAL_EVENT_QUEUE
GET_WATERMARK

ADD_ALIAS_TO_LDAP

Creates an alias for a queue, agent, or a JMS ConnectionFactory in LDAP dbms_aqadm.add_alias_to_ldap( alias IN VARCHAR2, obj_location IN VARCHAR2);

TBD

ADD_CONNECTION_TO_LDAP

Add a connection string to LDAP directory

Overload 1 dbms_aqadm.add_connection_to_ldap( connection IN VARCHAR2, host IN VARCHAR2, port IN BINARY_INTEGER, sid IN VARCHAR2, driver IN VARCHAR2 DEFAULT NULL, type IN BINARY_INTEGER DEFAULT AQ_QUEUE_CONNECTION);

TBD

Overload 2 dbms_aqadm.add_connection_to_ldap( connection IN VARCHAR2, jdbc_string IN VARCHAR2, username IN VARCHAR2 DEFAULT NULL, password IN VARCHAR2 DEFAULT NULL, type IN BINARY_INTEGER DEFAULT AQ_QUEUE_CONNECTION);

TBD

ADD_SUBSCRIBER

Adds a default subscriber to a queue 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);

See AQ Demo 1: Linked at page bottom

      

      -- a rule based on a VARCHAR2 must be in the format: 'priority < 11 AND SOURCE = ''EF''');

ALTER_AQ_AGENT

Alters an agent registered for Oracle Streams AQ Internet access 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);

exec dbms_aqadm.alter_aq_agent(agent_name=>'UWAGENT', enable_http=>TRUE, enable_smtp=>TRUE);

ALTER_DATABASE_KAFKA_TOPIC (new 23ai)

Undocumented dbms_aqadm.alter_database_kafka_topic(topicname IN VARCHAR2);

TBD

ALTER_PROPAGATION_SCHEDULE

Alters parameters for a propagation schedule 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);

exec dbms_aqadm.alter_propagation_schedule('rx_queue', 'finance_link');

ALTER_QUEUE

Alters an existing queue 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);

exec dbms_aqadm.alter_queue(queue_name=>'rx_queue', retry_delay=>2, comment=> '2 sec delay');

ALTER_QUEUE_TABLE

Alters the existing properties of a queue table for use with RAC 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);

exec dbms_aqadm.alter_queue_table('rx_queue_table', 'Prescription Queue Table', 1, 2);

ALTER_SHARDED_QUEUE

Alters the characteristics of a sharded queue dbms_aqadm.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 NONE);

TBD

ALTER_SUBSCRIBER

Alters existing properties of a subscriber to a specified queue. Only the rule can be altered

Overload 1 dbms_aqadm.alter_subscriber( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2);

DECLARE

       q_subsc sys.aq$_agent;

      BEGIN

        q_subsc := sys.aq$_agent('outpatient_rx', NULL, NULL);

        dbms_aqadm.alter_subscriber('rx_queue', q_subsc, 'priority < 10');

      END;

      /

Overload 2 dbms_aqadm.alter_subscriber( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2, transformation IN VARCHAR2);

TBD

ALTER_TRANSACTIONAL_EVENT_QUEUE

Changes the properties of a transactional event queue dbms_aqadm.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 NONE);

TBD

AQ$_PROPAQ

Undocumented
Overload 1 aq$_propaq(job IN NUMBER) RETURN DATE;

TBD

Overload 2 aq$_propaq( job IN NUMBER, next_date IN DATE, qname IN VARCHAR2, schema IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, toid_char IN VARCHAR2 DEFAULT NULL, version_char IN VARCHAR2 DEFAULT NULL, start_time IN VARCHAR2, duration IN VARCHAR2 DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN VARCHAR2 DEFAULT '60') RETURN DATE;

TBD

CREATE_AQ_AGENT

Creates an Internet access agent 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);

exec dbms_aqadm.create_aq_agent(agent_name=>'UWAGENT', 
      certificate_location=>'cn=uwclass,cn=mlib,cn=org', enable_http=>TRUE;)

CREATE_DATABASE_KAFKA_TOPIC (new 23ai)

Undocumented dbms_aqadm.create_database_kafka_topic( topicname IN VARCHAR2, partition_num IN NUMBER, retentiontime IN NUMBER, parittion_assignment_mode IN NUMBER, replication_mode IN BINARY_INTEGER);

TBD

CREATE_EQ_EXCEPTION_QUEUE

Creates an EQ exception queue dbms_aqadm.create_eq_exeption_queue( queue_name IN VARCHAR2, exception_queue_name IN VARCHAR2 DEFAULT NULL);

TBD

CREATE_EXCEPTION_QUEUE

Creates an exception queue dbms_aqadm.create_exception_queue( sharded_queue_name IN VARCHAR2, exception_queue_name IN VARCHAR2 DEFAULT NULL);

exec dbms_aqadm.create_exception_queue('UW_SQUEUE', 'UW_EQUEUE');

CREATE_NP_QUEUE

Create a non-persistent RAW queue dbms_aqadm.create_np_queue( queue_name IN VARCHAR2, multiple_consumers IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL);

Deprecated in 10gR2 but present for backward compatibility

      

      exec  dbms_aqadm.create_np_queue('rx_np_q', TRUE, 'NON_PERSISTENT_QUEUE');

CREATE_QUEUE

Creates a queue in the specified queue table 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); -- deprecated parameter

See AQ Demo 1: Linked at page bottom

CREATE_QUEUE_TABLE

Creates a queue table for messages of a predefined type 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, -- options are priority & enq_time multiple_consumers IN BOOLEAN DEFAULT FALSE, message_grouping IN BINARY_INTEGER DEFAULT NONE, comment IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE, -- deprecated parameter primary_instance IN BINARY_INTEGER DEFAULT 0, secondary_instance IN BINARY_INTEGER DEFAULT 0, compatible IN VARCHAR2 DEFAULT NULL, -- in 11g set to 10.0 non_repudiation IN BINARY_INTEGER DEFAULT 0, secure IN BOOLEAN DEFAULT FALSE); replication_mode IN BINARY_INTEGER DEFAULT NULL);

See AQ Demo 1: Linked at page bottom

CREATE_SHARDED_QUEUE

Creates a queue and its queue table for a sharded queue in one step dbms_aqadm.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);

exec  dbms_aqadm.create_sharded_queue(queue_name=>'SHARDQ',

                                           multiple_consumers=>TRUE,

                                           max_retries=>3,

                                           comment=>'AQ Sharding');

      

      set linesize 141

      col user_comment format a35

      

      SELECT owner, name, queue_table, max_retries, user_comment

      FROM dba_queues

      WHERE sharded = 'TRUE';

      

      exec  dbms_aqadm.drop_sharded_queue('SHARDQ', TRUE);

CREATE_TRANSACTIONAL_EVENT_QUEUE

Creates a transactional event queue for distribution across multiple RAC nodes dbms_aqadm.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);

TBD

DEL_ALIAS_FROM_LDAP

Drops an alias for a queue, agent, or JMS ConnectionFactory in LDAP dbms_aqadm.del_alias_from_ldap(alias IN VARCHAR2);

TBD

DEL_CONNECTION_FROM_LDAP

Drops a connection string from an LDAP directory dbms_aqadm.add_connection_to_ldap(connection IN VARCHAR2);

TBD

DISABLE_DB_ACCESS

Revokes the privileges of a specific database user from an Oracle Streams AQ Internet agent dbms_aqadm.disable_db_access( agent_name IN VARCHAR2, db_username IN VARCHAR2);

exec dbms_aqadm.disable_aq_agent('UWAGENT', 'UWCLASS');

DISABLE_PROPAGATION_SCHEDULE

Disable a propagation schedule dbms_aqadm.disable_propagation_schedule( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, destination_queue IN VARCHAR2 DEFAULT NULL);

exec dbms_aqadm.disable_propagation_schedule('rx_queue', finance_link');

DROP_AQ_AGENT

Drops an agent that was previously registered for Oracle Streams AQ Internet access dbms_aqadm.drop_aq_agent(agent_name IN VARCHAR2);

exec dbms_aqadm.drop_aq_agent('UWCLASS');

DROP_DATABASE_KAFKA_TOPIC (new 23ai)

Undocumented dbms_aqadm.drop_database_kafka_topic(topicname IN VARCHAR2);

TBD

DROP_QUEUE

Drops an existing queue dbms_aqadm.drop_queue( queue_name IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);

See AQ Demo 1: Linked at page bottom

DROP_QUEUE_TABLE

Drops an existing queue table dbms_aqadm.drop_queue_table( queue_table IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE, auto_commit IN BOOLEAN DEFAULT TRUE);

See AQ Demo 1: Linked at page bottom

DROP_SHARDED_QUEUE

Drops a sharded queue and its queue table in one step dbms_aqadm.drop_sharded_queue( queue_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);

See CREATE_SHARDED_QUEUE Demo Above

DROP_TRANSACTIONAL_EVENT_QUEUE

Drops a transactional event queue dbms_aqadm.drop_transactional_event_queue( queue_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);

TBD

ENABLE_DB_ACCESS

Grants an AQ Internet agent the privileges of a specific database user dbms_aqadm.enable_db_access( agent_name IN VARCHAR2, db_username IN VARCHAR2);

exec dbms_aqadm.enable_db_access('UWAGENT', 'UWCLASS');

ENABLE_JMS_TYPES

Enqueue of JMS types and XML types does not work with Streams SYS.ANYDATA queues unless you call this procedure after DBMS_STREAMS_ADM.SET_UP_QUEUE dbms_aqadm.enable_jms_types(queue_table IN VARCHAR2);

exec dbms_aqadm.enable_jms_types(USER || '.' || 'QTABLE');

ENABLE_PROPAGATION_SCHEDULE

Enables a previously disabled propagation schedule dbms_aqadm.enable_propagation_schedule( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, destination_queue IN VARCHAR2 DEFAULT NULL);

exec dbms_aqadm.enable_propagation_schedule('rx_queue', 'finance_link');

GET_MAX_STREAMS_POOL

Returns the maximum streams pool memory dbms_aqadm.get_max_streams_pool(value OUT NUMBER);

DECLARE

       outVal NUMBER;

      BEGIN

        dbms_aqadm.get_max_streams_pool(outVal);

        dbms_output.put_line(TO_CHAR(outVal));

        dbms_aqadm.set_max_streams_pool(outVal);

      END;

      /

GET_MIN_STREAMS_POOL

Returns the minimum streams pool memory dbms_aqadm.get_min_streams_pool(value OUT NUMBER);

DECLARE

       outVal NUMBER;

      BEGIN

        dbms_aqadm.get_min_streams_pool(outVal);

        dbms_output.put_line(TO_CHAR(outVal));

        dbms_aqadm.set_min_streams_pool(outVal);

      END;

      /

GET_PROP_SEQNO

Undocumented dbms_aqadm.get_prop_seqno( qid IN BINARY_INTEGER, dqname IN VARCHAR2, dbname IN VARCHAR2, seq OUT BINARY_INTEGER);

TBD

GET_QUEUE_PARAMETER

Outputs the value of a queue parameter dbms_aqadm.get_queue_parameter( queue_name IN VARCHAR2, param_name IN VARCHAR2, param_value OUT NUMBER);

DECLARE

       pValue NUMBER;

      BEGIN

        dbms_aqadm.get_queue_parameter('SRVQUEUE', 'RETENTION', pValue);

        dbms_output.put_line(pValue);

      END;

      /

      *

      ORA-00904: Not a Sharded Queue: invalid identifier

GET_REPLAY_INFO

Get a sender's replay info dbms_aqadm.get_replay_info( queue_name IN VARCHAR2, sender_agent IN sys.aq$_agent, replay_attribute IN BINARY_INTEGER, correlation OUT VARCHAR2);

TBD

GET_TYPE_INFO

Undocumented

Overload 1 dbms_aqadm.get_type_info( schema IN VARCHAR2, qname IN VARCHAR2, gettds IN BOOLEAN, rc OUT BINARY_INTEGER, toid OUT RAW, version OUT NUMBER, tds OUT LONG RAW, queue_style OUT VARCHAR2, network_name OUT VARCHAR2);

TBD

Overload 2 dbms_aqadm.get_type_info( schema IN VARCHAR2, qname IN VARCHAR2, gettds IN BOOLEAN, rc OUT BINARY_INTEGER, toid OUT RAW, version OUT NUMBER, tds OUT LONG RAW);

TBD

GET_WATERMARK

Retrieves the value of watermark set by SET_WATERMARK dbms_aqadm.get_watermark(wmvalue OUT NUMBER); -- value in MB

set serveroutput on

      

      DECLARE

       x NUMBER;

      BEGIN

        dbms_aqadm.set_watermark(1);

        dbms_aqadm.get_watermark(x);

        dbms_output.put_line(x);

      

        dbms_aqadm.set_watermark(10);

        dbms_aqadm.get_watermark(x);

        dbms_output.put_line(x);

      END;

      /

GRANT_PRIV_FOR_RM_PLAN (new 23ai)

Undocumented dbms_aqadm.grant_priv_for_rm_plan( uname IN VARCHAR2, admin_option IN BOOLEAN);

TBD

GRANT_QUEUE_PRIVILEGE

Grants privileges on a queue to a users or role dbms_aqadm.grant_queue_privilege( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2, grant_option IN BOOLEAN := FALSE); Choices: ENQUEUE, DEQUEUE, ALL

See AQ Demo 1: Linked at page bottom

GRANT_SCHEMA_PRIVILEGE (new 23ai)

Undocumented dbms_aqadm.grant_schema_privilege( privilege IN VARCHAR2, schema IN VARCHAR2, grantee IN VARCHAR2, admin_option IN BOOLEAN);

TBD

GRANT_SYSTEM_PRIVILEGE

Grants Oracle Streams AQ system privileges to users and roles dbms_aqadm.grant_system_privilege( privilege IN VARCHAR2, grantee IN VARCHAR2, admin_option IN BOOLEAN := FALSE);

See AQ Demo 1: Linked at page bottom

GRANT_TYPE_ACCESS

Undocumented dbms_aqadm.grant_type_access(user_name IN VARCHAR2);

TBD

INVALIDATE_QUEUE (new 23ai)

Undocumented dbms_aqadm.invalidate_queue( q_schema IN VARCHAR2, q_name IN VARCHAR2);

TBD

ISSHARDEDQUEUE

Outputs 0 if a queue is not sharded, 1 if it is dbms_aqadm.isShardedQueue( schema IN VARCHAR2, qname IN VARCHAR2) RETURN NUMBER;

SELECT dbms_aqadm.isShardedQueue('SYS', 'SRVQUEUE');

      

      DBMS_AQADM.ISSHARDEDQUEUE('SYS','SRVQUEUE')

      -------------------------------------------

                                                0

IS_AQ_TABLE (new 23ai)

Undocumented dbms_aqadm.is_aq_table( table_owner IN VARCHAR2, table_name IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_aqadm.is_aq_table('RX_OWNER', 'RX_QUEUE_TABLE') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      T

      

      PL/SQL procedure successfully completed.

IS_TRANSACTIONAL_EVENT_QUEUE

Returns 1 is the queue is a transactional event queue, else 0 dbms_aqadm.is_transactional_event_queue( schema IN VARCHAR2, qname IN VARCHAR2) RETURN NUMBER;

TBD

MIGRATE_QUEUE_TABLE

Upgrade a queue table from ver 8.0 to ver 8.1 or higher compatibility dbms_aqadm.migrate_queue_table( queue_table IN VARCHAR2, compatible IN VARCHAR2);

exec dbms_aqadm.migrate_queue_table('AQ$_ORA$PREPLUGIN_BACKUP_QTB_T', '19.3');

MOVE_QUEUE_TABLE (new 23ai)

Undocumented dbms_aqadm.move_queue_table( queue_table IN VARCHAR2, to_tablespace IN VARCHAR2, flags IN BINARY_INTEGER);

TBD

NEXT_SEQNUM (new 23ai)

Undocumented dbms_aqadm.next_seqnum( q_schema IN VARCHAR2, q_name IN VARCHAR2, seq_name IN VARCHAR2, seqnum OUT NUMBER);

TBD

NONREPUDIATE_RECEIVER

Non-repudiate receiver of ADT payload

Overload 1 dbms_aqadm.nonrepudiate_receiver( queue_name IN VARCHAR2, msgid IN RAW, rcver_info IN sys.aq$_agent, signature OUT sys.aq$_sig_prop, payload OUT sys.standard.<ADT_1>);

TBD

Non-repudiate receiver of raw payload

Overload 2 dbms_aqadm.nonrepudiate_receiver( queue_name IN VARCHAR2, msgid IN RAW, rcver_info IN sys.aq$_agent, signature OUT sys.aq$_sig_prop, payload OUT RAW);

TBD

NONREPUDIATE_SENDER

Non-repudiate sender of ADT payload

Overload 1 dbms_aqadm.nonrepudiate_sender( queue_name IN VARCHAR2, msgid IN RAW, sender_info IN sys.aq$_agent, signature OUT sys.aq$_sig_prop, payload OUT sys.standard.<ADT_1>);

TBD

Non-repudiate sender of raw payload

Overload 2 dbms_aqadm.nonrepudiate_sender( queue_name IN VARCHAR2, msgid IN RAW, sender_info IN sys.aq$_agent, signature OUT sys.aq$_sig_prop, payload OUT RAW);

TBD

PURGE_QUEUE_TABLE

Purges messages from the named queue table dbms_aqadm.purge_queue_table( queue_table IN VARCHAR2, purge_condition IN VARCHAR2, purge_options IN aq$_purge_options_t);

CREATE OR REPLACE PROCEDURE purgeQtable(qtable IN VARCHAR2) AUTHID CURRENT_USER AS

       po_t dbms_aqadm.aq$_purge_options_t;

       qname VARCHAR2(30);

      

       CURSOR qcur IS

       SELECT name

       FROM user_queues

       WHERE queue_table = UPPER(qtable);

      BEGIN

        po_t.block := FALSE;

        dbms_aqadm.purge_queue_table(USER || '.' || qtable, NULL, po_t);

      

        execute immediate 'ALTER TABLE ' || qtable || ' ENABLE ROW MOVEMENT';

        execute immediate 'ALTER TABLE ' || qtable || ' SHRINK SPACE CASCADE';

        execute immediate 'ALTER TABLE ' || qtable || ' DISABLE ROW MOVEMENT';

      

        FOR qrec IN qcur LOOP

          qname := qrec.name;

          IF INSTR(qname, '$') > 0 THEN

            dbms_aqadm.start_queue(qname, enqueue=>FALSE);

          ELSE

            dbms_aqadm.start_queue(qname);

          END IF;

        END LOOP;

        dbms_utility.compile_schema(USER,compile_all=>FALSE);

      EXCEPTION

        WHEN OTHERS THEN

          dbms_output.put_line('PurgeQTable: Error Starting Queue: '||qname||': '||SQLERRM);

      END purgeQtable;

      /

QUEUE_SUBSCRIBERS

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 dbms_aqadm.queue_subscribers(queue_name IN VARCHAR2) RETURN aq$_subscriber_list_t;

TBD

RECOVER_PROPAGATION

Undocumented dbms_aqadm.recover_propagation( schema IN VARCHAR2, queue_name IN VARCHAR2, destination IN VARCHAR2, protocol IN BINARY_INTEGER DEFAULT TTC, url IN VARCHAR2 DEFAULT NULL, username IN VARCHAR2 DEFAULT NULL, passwd IN VARCHAR2 DEFAULT NULL, trace IN BINARY_INTEGER DEFAULT 0, destq IN BINARY_INTEGER DEFAULT 0);

TBD

RECOVER_QUEUE_TABLE (new 23ai)

Undocumented dbms_aqadm.recover_queue_table( queue_table IN VARCHAR2, options IN BINARY_INTEGER);

TBD

REMOVE_SUBSCRIBER

Removes a default subscriber from a queue dbms_aqadm.remove_subscriber( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent);

TBD

RESET_REPLAY_INFO

Reset sender's replay info dbms_aqadm.reset_replay_info( queue_name IN VARCHAR2, sender_agent IN sys.aq$_agent, replay_attribute IN BINARY_INTEGER);

TBD

REVOKE_QUEUE_PRIVILEGE

Revokes privileges on a queue from a user or role dbms_aqadm.revoke_queue_privilege( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2); Choices: ENQUEUE_ANY, DEQUEUE_ANY, MANAGE_ANY

exec dbms_aqadm.revoke_queue_privilege(ENQUEUE_ANY,'rx_queue', 'UWCLASS');

REVOKE_SCHEMA_PRIVILEGE (new 23ai)

Undocumented dbms_aqadm.revoke_schema_privilege( privilege IN VARCHAR2, schema IN VARCHAR2, grantee IN VARCHAR2);

TBD

REVOKE_SYSTEM_PRIVILEGE

Revokes Oracle Streams AQ system privileges from users and roles dbms_aqadm.revoke_system_privilege( privilege IN VARCHAR2, grantee IN VARCHAR2); Choices: ENQUEUE_ANY, DEQUEUE_ANY, MANAGE_ANY

exec dbms_aqadm.revoke_system_privilege(ENQUEUE_ANY, 'UWCLASS');

SCHEDULE_PROPAGATION

Schedules propagation of messages from a queue to a destination identified by a specific database link dbms_aqadm.schedule_propagation( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, start_time IN TIMESTAMP WITH TIMEZONE DEFAULT NULL, -- data type changed in 11g duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT 60, destination_queue IN VARCHAR2 DEFAULT NULL); -- Note: The file in /rdbms/admin shows start_time as data type TIMESTAMP WITH TIMEZONE but all_arguments does not.

See AQ Demo 1: Linked at page bottom

SET_MAX_STREAMS_POOL

Sets the maximum streams pool memory dbms_aqadm.set_max_streams_pool(value IN NUMBER);

See GET_MAX_STREAMS_POOL above

SET_MIN_STREAMS_POOL

Sets the minimum streams pool memory dbms_aqadm.set_min_streams_pool(value IN NUMBER);

See GET_MIN_STREAMS_POOL above

SET_QUEUE_PARAMETER

Sets the value of a queue parameter dbms_aqadm.set_queue_parameter( queue_name IN VARCHAR2, param_name IN VARCHAR2, param_value IN NUMBER);

exec dbms_aqadm.set_queue_parameter('SRVQUEUE', 'RETENTION', 1);

           *

      ORA-00904: Not a Sharded Queue: invalid identifier

SET_WATERMARK

Used for Oracle Streams AQ notification to specify and limit memory use dbms_aqadm.set_watermark(wmvalue IN NUMBER); -- value in MB

See GET_WATERMARK entry

START_QUEUE

Enables the specified queue for enqueuing or dequeuing dbms_aqadm.start_queue( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE);

See AQ Demo 1: Linked at page bottom

START_TIME_MANAGER

Undocumented dbms_aqadm.start_time_manager;

exec dbms_aqadm.start_time_manager;

STOP_QUEUE

Disables enqueuing or dequeuing on the specified queue dbms_aqadm.stop_queue( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE);

See AQ Demo 1: Linked at page bottom

STOP_TIME_MANAGER

Undocumented dbms_aqadm.stop_time_manager;

exec dbms_aqadm.stop_time_manager;

UNSCHEDULE_PROPAGATION

Unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific database link dbms_aqadm.unschedule_propagation( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, destination_queue IN VARCHAR2 DEFAULT NULL);

exec dbms_aqadm.unschedule_propagation('rx_queue', 'finance_link');

UNSET_QUEUE_PARAMETER

Unsets the value of a queue parameter dbms_aqadm.unset_queue_parameter( queue_name IN VARCHAR2, param_name IN VARCHAR2);

exec dbms_aqadm.unset_queue_parameter('SRVQUEUE', 'RETENTION');

VALIDATE_QUEUE (new 23ai)

Undocumented dbms_aqadm.validate_queue( q_schema IN VARCHAR2, q_name IN VARCHAR2);

TBD

VERIFY_QUEUE_TYPES

Verifies that the source and destination queues have identical types dbms_aqadm.verify_queue_types( src_queue_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, rc OUT BINARY_INTEGER transformation IN VARCHAR2 DEFAULT NULL);

set serveroutput on

      

      DECLARE

       x BINARY_INTEGER;

      BEGIN

        dbms_aqadm.verify_queue_types('rx_queue', 'finance_queue', 'finance_link', x);

        dbms_output.put_line(x);

      END;

      /

VERIFY_QUEUE_TYPES_GET_NRP

Undocumented dbms_aqadm.verify_queue_types_get_nrp( src_queue_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, rc OUT BINARY_INTEGER, transformation IN VARCHAR2 DEFAULT NULL);

TBD

VERIFY_QUEUE_TYPES_NO_QUEUE

Undocumented dbms_aqadm.verify_queue_types_no_queue( src_queue_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, rc OUT BINARY_INTEGER, transformation IN VARCHAR2 DEFAULT NULL);

TBD

VERIFY_SHARDED_QUEUE

Undocumented dbms_aqadm.verify_sharded_queue ( src_schema_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2, rc OUT NUMBER);

set serveroutput on

      

      DECLARE

       x BINARY_INTEGER;

      BEGIN

        dbms_aqadm.verify_sharded_queue('uw_squeue', 'uw_dqueue', 'finance_link', x);

        dbms_output.put_line(x);

      END;

      /

VERIFY_TRANSACTIONAL_EVENT_QUEUE

Validates a transactional event queue dbms_aqadm.verify_transactional_event_queue( src_schema_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2, rc OUT NUMBER);

TBD

Morgan's Library Page Footer

This site is maintained by Daniel Morgan. Last Updated: