Oracle 12cR2 DBMS

Oracle DBMS_AQADM Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"

~ Sen. Tammy Duckworth

Purpose Administration of Advanced Queue queues

AUTHID DEFINER

Constants

Name	Data Type	Value
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
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(1)	'0'
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
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; /

Dependencies

ALL_EVALUATION_CONTEXTS	CDB_RULESETS	DBMS_PROPAGATION_INTERNAL
ALL_EVALUATION_CONTEXT_TABLES	CDB_SOURCE_AE	DBMS_PRVTAQIM
ALL_QUEUES	CDB_TYPES	DBMS_PRVTAQIP
ALL_QUEUE_PUBLISHERS	DBA_EVALUATION_CONTEXTS	DBMS_PRVTAQIS
ALL_QUEUE_SCHEDULES	DBA_EVALUATION_CONTEXT_TABLES	DBMS_STATS
ALL_QUEUE_TABLES	DBA_QUEUES	DBMS_STREAMS_ADM_IVK
ALL_RULE_SETS	DBA_QUEUE_PUBLISHERS	DBMS_SYSTEM
ALL_RULESETS	DBA_QUEUE_SCHEDULES	DBMS_SYS_ERROR
ALL_SOURCE_AE	DBA_QUEUE_TABLES	DBMS_XSTREAM_ADM_UTL
ALL_TYPES	DBA_RULE_SETS	GV$AQ
AQ$_AGENT	DBA_RULESETS	KUPC$QUE_INT
AQ$_SIG_PROP	DBA_SOURCE_AE	KUPW$WORKER
AQ_EVENT_TABLE	DBA_TYPES	LTADM
AQ$_INTERNET_USERS	DBMS_AQADM_INV	SDO_TRKR
AQ$_QUEUE_STATISTICS	DBMS_AQADM_SYS	USER_EVALUATION_CONTEXTS
AQ$_PENDING_MESSAGES	DBMS_AQADM_SYSCALLS	USER_EVALUATION_CONTEXT_TABLES
AQ$_PROPAGATION_STATUS	DBMS_AQIN	USER_QUEUES
AQ$_MESSAGE_TYPES	DBMS_AQJMS_INTERNAL	USER_QUEUE_PUBLISHERS
AQ$_SCHEDULES	DBMS_AQ_LIB	USER_QUEUE_SCHEDULES
CDB_EVALUATION_CONTEXTS	DBMS_AQ_SYS_EXP_ACTIONS	USER_QUEUE_TABLES
CDB_EVALUATION_CONTEXT_TABLES	DBMS_AQ_SYS_IMP_INTERNAL	USER_RULE_SETS
CDB_QUEUES	DBMS_ASSERT	USER_RULESETS
CDB_QUEUE_PUBLISHERS	DBMS_GSM_CLOUDADMIN	USER_SOURCE_AE
CDB_QUEUE_SCHEDULES	DBMS_ISCHED	USER_TYPES
CDB_QUEUE_TABLES	DBMS_LOGREP_IMP	V$AQ
CDB_RULE_SETS

Documented Yes

Exceptions

Error Code	Reason
ORA-00904	Not a Sharded Queue: invalid identifier

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

Security Model Owned by SYS with EXECUTE granted to the users SYSTEM and WMSYS and the roles AQ_ADMINISTRATOR_ROLE, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, IMP_FULL_DATABASE, MDSYS, OEM_MONITOR, SYSTEM, and WMSYS

Source $ORACLE_HOME/rdbms/admin/dbmsaqad.sql

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

Subprograms

ADD_ALIAS_TO_LDAP	DROP_QUEUE	QUEUE_SUBSCRIBERS
ADD_CONNECTION_TO_LDAP	DROP_QUEUE_TABLE	RECOVER_PROPAGATION
ADD_SUBSCRIBER	DROP_SHARDED_QUEUE	REMOVE_SUBSCRIBER
ALTER_AQ_AGENT	ENABLE_DB_ACCESS	RESET_REPLAY_INFO
ALTER_PROPAGATION_SCHEDULE	ENABLE_JMS_TYPES	REVOKE_QUEUE_PRIVILEGE
ALTER_QUEUE	ENABLE_PROPAGATION_SCHEDULE	REVOKE_SYSTEM_PRIVILEGE
ALTER_QUEUE_TABLE	GET_MAX_STREAMS_POOL	SCHEDULE_PROPAGATION
ALTER_SHARDED_QUEUE	GET_MIN_STREAMS_POOL	SET_MAX_STREAMS_POOL
ALTER_SUBSCRIBER	GET_PROP_SEQNO	SET_MIN_STREAMS_POOL
AQ$_PROPAQ	GET_QUEUE_PARAMETER	SET_QUEUE_PARAMETER
CREATE_AQ_AGENT	GET_REPLAY_INFO	SET_WATERMARK
CREATE_EXCEPTION_QUEUE	GET_TYPE_INFO	START_QUEUE
CREATE_NP_QUEUE	GET_WATERMARK	START_TIME_MANAGER
CREATE_QUEUE	GRANT_QUEUE_PRIVILEGE	STOP_QUEUE
CREATE_QUEUE_TABLE	GRANT_SYSTEM_PRIVILEGE	STOP_TIME_MANAGER
CREATE_SHARDED_QUEUE	GRANT_TYPE_ACCESS	UNSCHEDULE_PROPAGATION
DEL_ALIAS_FROM_LDAP	ISSHARDEDQUEUE	UNSET_QUEUE_PARAMETER
DEL_CONNECTION_FROM_LDAP	MIGRATE_QUEUE_TABLE	VERIFY_QUEUE_TYPES
DISABLE_DB_ACCESS	NONREPUDIATE_RECEIVER	VERIFY_QUEUE_TYPES_GET_NRP
DISABLE_PROPAGATION_SCHEDULE	NONREPUDIATE_SENDER	VERIFY_QUEUE_TYPES_NO_QUEUE
DROP_AQ_AGENT	PURGE_QUEUE_TABLE	VERIFY_SHARDED_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);

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 DEFAULTAQ_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_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);

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

ALTER_SHARDED_QUEUE (new 12.2)

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

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

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_EXCEPTION_QUEUE (new 12.2)

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

exec dbms_aqadm.create_np_queue('rx_np_q', TRUE, 'Non-persistant 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);

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

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_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

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 Oracle 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 (new 12.2)

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

SQL> DECLARE

       
      2   pValue NUMBER;

       
      3  BEGIN

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

       
      5    dbms_output.put_line(pValue);

       
      6  END;

       
      7  /

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

TBF

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_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_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);

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

TBD

ISSHARDEDQUEUE (new 12.2)

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

SQL> SELECT dbms_aqadm.isShardedQueue('SYS', 'SRVQUEUE')

       
      2  FROM dual;

      

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

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

                                                0

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('legacy_tab', '8.1');

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 "<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 "<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

REMOVE_SUBSCRIBER

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

exec dbms_aqadm.remove_subscriber('rx_queue', 'uw_class');

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_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 (new 12.2)

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

      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

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 (new 12.2)

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

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 (new 12.2)

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;

      /