Oracle 18cR3 DBMS

Oracle DBMS_AQADM Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

Purpose Administration of Advanced Queue queues

AUTHID CURRENT_USER

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

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

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

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

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

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

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;

      /

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: