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

Related Topics
Advanced Queuing Demo 1
Advanced Queuing RAC Demo
DBMS_ALERT
DBMS_AQ
DBMS_AQELM
DBMS_AQINV
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_IMP_ZECURITY
DBMS_JOB
DBMS_SERVER_ALERT
Packages
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved