Oracle | DBMS

Oracle DBMS_AQADM Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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