Oracle | DBMS

Oracle DBMS_AQADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 (new 21c)
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

AQ$_AGENT	DBMS_AQ_SYS_IMP_INTERNAL	DBMS_STREAMS_ADM_IVK
AQ$_SIG_PROP	DBMS_ASSERT	DBMS_SYSTEM
DBMS_AQADM	DBMS_GSM_CLOUDADMIN	DBMS_SYS_ERROR
DBMS_AQADM_INV	DBMS_ISCHED	DBMS_TEQK
DBMS_AQADM_SYS	DBMS_LOGREP_IMP	DBMS_TRANSFORM_INTERNAL
DBMS_AQADM_SYSCALLS	DBMS_PROPAGATION_INTERNAL	DBMS_UTILITY
DBMS_AQADM_VAR	DBMS_PRVTAQIM	DBMS_XSTREAM_ADM_UTL
DBMS_AQIN	DBMS_PRVTAQIP	KUPC$QUE_INT
DBMS_AQJMS_INTERNAL	DBMS_PRVTAQIS	KUPV$FT_INT
DBMS_AQ_IND_MON	DBMS_SCHEDULER	KUPW$WORKER
DBMS_AQ_LIB	DBMS_STATS	LTADM
DBMS_AQ_SYS_EXP_ACTIONS	DBMS_STATS_INTERNAL	SDO_TRKR

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 SYSTEM, MDSYS and WMSYS and the roles AQ_ADMINISTRATOR_ROLE, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, IMP_FULL_DATABASE, OEM_MONITOR

Source $ORACLE_HOME/rdbms/admin/dbmsaqad.sql

also see: $ORACLE_HOME/rdbms/admin/catqueue.sql

Subprograms

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(add_alias_to_ldap, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(add_connection_to_ldap, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(add_connection_to_ldap, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(add_subscriber, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_aq_agent, AUTO);

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_propagation_schedule, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_queue, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_queue_table, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(ALTER_SHARDED_QUEUE, 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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_subscriber, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(alter_subscriber, NONE);

TBD

ALTER_TRANSACTIONAL_EVENT_QUEUE (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(ALTER_TRANSACTIONAL_EVENT_QUEUE, 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); PRAGMA SUPPLEMENTAL_LOG_DATA(create_aq_agent, AUTO);

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

CREATE_EQ_EXCEPTION_QUEUE (new 21c)

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_EXCEPTION_QUEUE, NONE);

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

CREATE_NP_QUEUE

Create a nonpersistent RAW queue

Deprecated in 10gR2 but present for backward compatibility dbms_aqadm.create_np_queue( queue_name IN VARCHAR2, multiple_consumers IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(create_np_queue, NONE);

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 PRAGMA SUPPLEMENTAL_LOG_DATA(create_queue, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(create_queue_table, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_SHARDED_QUEUE, NONE);

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 (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(CREATE_TRANSACTIONAL_EVENT_QUEUE, 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); PRAGMA SUPPLEMENTAL_LOG_DATA(del_alias_from_ldap, NONE);

TBD

DEL_CONNECTION_FROM_LDAP

Drops a connection string from an LDAP directory dbms_aqadm.add_connection_to_ldap(connection IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(del_connection_from_ldap, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(disable_db_access, AUTO);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(disable_propagation_schedule, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(drop_aq_agent, AUTO);

exec dbms_aqadm.drop_aq_agent('UWCLASS');

DROP_QUEUE

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(drop_queue_table, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(drop_sharded_queue, NONE);

See CREATE_SHARDED_QUEUE Demo Above

DROP_TRANSACTIONAL_EVENT_QUEUE (new 21c)

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(enable_db_access, AUTO);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(enable_jms_types, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(enable_propagation_schedule, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_max_streams_pool, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_min_streams_pool, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_prop_seqno, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_queue_parameter, NONE);

DECLARE

       pValue NUMBER;

      BEGIN

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

        dbms_output.put_line(pValue);

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-00904: Not a Sharded Queue: invalid identifier

      ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5259

      ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 182

      ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5254

      ORA-06512: at "SYS.DBMS_AQADM", line 315

      ORA-06512: at line 4

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_replay_info, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_type_info, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_type_info, NONE);

TBD

GET_WATERMARK

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

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_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); PRAGMA SUPPLEMENTAL_LOG_DATA(grant_queue_privilege, NONE); Choices: ENQUEUE, DEQUEUE, ALL

See AQ Demo 1: Linked at page bottom

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); PRAGMA SUPPLEMENTAL_LOG_DATA(grant_system_privilege, AUTO); PRAGMA SUPPLEMENTAL_LOG_DATA(grant_system_privilege, AUTO); Choices: ENQUEUE_ANY, DEQUEUE_ANY, MANAGE_ANY

See AQ Demo 1: Linked at page bottom

GRANT_TYPE_ACCESS

Undocumented dbms_aqadm.grant_type_access(user_name IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(grant_type_access, AUTO);

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')

      FROM dual;

      

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

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

                                                0

IS_TRANSACTIONAL_EVENT_QUEUE (new 21c)

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); PRAGMA SUPPLEMENTAL_LOG_DATA(migrate_queue_table, NONE);

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

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>); PRAGMA SUPPLEMENTAL_LOG_DATA(nonrepudiate_receiver, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(nonrepudiate_receiver, NONE);

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>); PRAGMA SUPPLEMENTAL_LOG_DATA(nonrepudiate_sender, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(nonrepudiate_sender, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(purge_queue_table, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(recover_propagation, NONE);

TBD

REMOVE_SUBSCRIBER

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(reset_replay_info, NONE);

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_SYSTEM_PRIVILEGE

Revokes Oracle Streams AQ system privileges from users and roles dbms_aqadm.revoke_system_privilege( privilege IN VARCHAR2, grantee IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(revoke_queue_privilege, NONE ); 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); PRAGMA SUPPLEMENTAL_LOG_DATA(schedule_propagation, NONE); -- 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); PRAGMA SUPPLEMENTAL_LOG_DATA(set_max_streams_pool, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(set_min_streams_pool, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(set_queue_parameter, NONE);

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

      BEGIN dbms_aqadm.set_queue_parameter('SRVQUEUE', 'RETENTION', 1); END;

      *

      ERROR at line 1:

      ORA-00904: Not a Sharded Queue: invalid identifier

      ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5207

      ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 153

      ORA-06512: at "SYS.DBMS_AQADM_SYS", line 5175

      ORA-06512: at "SYS.DBMS_AQADM", line 248

      ORA-06512: at line 1

SET_WATERMARK

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(start_queue, NONE);

See AQ Demo 1: Linked at page bottom

START_TIME_MANAGER

Undocumented dbms_aqadm.start_time_manager; PRAGMA SUPPLEMENTAL_LOG_DATA(start_time_manager, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(stop_queue, NONE);

See AQ Demo 1: Linked at page bottom

STOP_TIME_MANAGER

Undocumented dbms_aqadm.stop_time_manager; PRAGMA SUPPLEMENTAL_LOG_DATA(stop_time_manager, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(unschedule_propagation, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(unset_queue_parameter, NONE);

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

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); PRAGMA SUPPLEMENTAL_LOG_DATA(verify_queue_types, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(verify_queue_types_get_nrp, NONE);

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); PRAGMA SUPPLEMENTAL_LOG_DATA(verify_queue_types_no_queue, NONE);

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 (new 21c)

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 Dan Morgan. Last Updated:

DBSecWorx