Oracle DBMS_AQADM
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Administration of Advanced Queue queuing
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_REPCAT_ADD_MASTER
ALL_QUEUE_TABLES DBA_QUEUES DBMS_STATS
ALL_RULE_SETS DBA_QUEUE_PUBLISHERS DBMS_STREAMS_ADM_IVK
ALL_RULESETS DBA_QUEUE_SCHEDULES DBMS_SYSTEM
ALL_SOURCE_AE DBA_QUEUE_TABLES DBMS_SYS_ERROR
ALL_TYPES DBA_RULE_SETS DBMS_XSTREAM_ADM_UTL
AQ$_AGENT DBA_RULESETS GV$AQ
AQ$_SIG_PROP DBA_SOURCE_AE KUPC$QUE_INT
AQ_EVENT_TABLE DBA_TYPES KUPW$WORKER
AQ$_INTERNET_USERS DBMS_AQADM_INV LTADM
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
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, IMP_FULL_DATABASE, OEM_MONITOR, and GSMADMIN_INTERNAL
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_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_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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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_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
 
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 (new 12.1)
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 (new 12.1)
Sets the minimum streams pool memory dbms_aqadm.set_min_streams_pool(value IN NUMBER);
See GET_MIN_STREAMS_POOL above
 
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');
 
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

Related Topics
Advanced Queuing Demo 1
DBMS_ALERT
DBMS_AQ
DBMS_AQELM
DBMS_AQ_EXP_CMT_TIME_TABLES
DBMS_AQ_EXP_DEQUEUELOG_TABLES
DBMS_AQ_EXP_HISTORY_TABLES
DBMS_AQ_EXP_INDEX_TABLES
DBMS_AQ_IMP_ZECURITY
DBMS_JOB
DBMS_SERVER_ALERT
Packages

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