Oracle DBMS_AQADM
Version 19.3

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(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;
/

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

Related Topics
Advanced Queuing Demo 1
Advanced Queuing RAC Demo
Built-in Functions
Built-in Packages
DBMS_ALERT
DBMS_AQ
DBMS_AQADM_INV
DBMS_AQADM_VAR
DBMS_AQELM
DBMS_AQIN
DBMS_AQ_BQVIEW
DBMS_AQ_EXP_CMT_TIME_TABLES
DBMS_AQ_EXP_DEQUEUELOG_TABLES
DBMS_AQ_EXP_HISTORY_TABLES
DBMS_AQ_EXP_INDEX_TABLES
DBMS_AQ_EXP_TIMEMGR_TABLES
DBMS_AQ_EXP_ZECURITY
DBMS_AQ_IMP_ZECURITY
DBMS_JOB
DBMS_SERVER_ALERT
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx