Oracle 12c DBMS_AQADM Demos

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	DROP_QUEUE	QUEUE_SUBSCRIBERS
ADD_CONNECTION_TO_LDAP	DROP_QUEUE_TABLE	RECOVER_PROPAGATION
ADD_SUBSCRIBER	DROP_SHARDED_QUEUE	REMOVE_SUBSCRIBER
ALTER_AQ_AGENT	ENABLE_DB_ACCESS	RESET_REPLAY_INFO
ALTER_PROPAGATION_SCHEDULE	ENABLE_JMS_TYPES	REVOKE_QUEUE_PRIVILEGE
ALTER_QUEUE	ENABLE_PROPAGATION_SCHEDULE	REVOKE_SYSTEM_PRIVILEGE
ALTER_QUEUE_TABLE	GET_MAX_STREAMS_POOL	SCHEDULE_PROPAGATION
ALTER_SUBSCRIBER	GET_MIN_STREAMS_POOL	SET_MAX_STREAMS_POOL
AQ$_PROPAQ	GET_PROP_SEQNO	SET_MIN_STREAMS_POOL
CREATE_AQ_AGENT	GET_REPLAY_INFO	SET_WATERMARK
CREATE_NP_QUEUE	GET_TYPE_INFO	START_QUEUE
CREATE_QUEUE	GET_WATERMARK	START_TIME_MANAGER
CREATE_QUEUE_TABLE	GRANT_QUEUE_PRIVILEGE	STOP_QUEUE
CREATE_SHARDED_QUEUE	GRANT_SYSTEM_PRIVILEGE	STOP_TIME_MANAGER
DEL_ALIAS_FROM_LDAP	GRANT_TYPE_ACCESS	UNSCHEDULE_PROPAGATION
DEL_CONNECTION_FROM_LDAP	MIGRATE_QUEUE_TABLE	VERIFY_QUEUE_TYPES
DISABLE_DB_ACCESS	NONREPUDIATE_RECEIVER	VERIFY_QUEUE_TYPES_GET_NRP
DISABLE_PROPAGATION_SCHEDULE	NONREPUDIATE_SENDER	VERIFY_QUEUE_TYPES_NO_QUEUE
DROP_AQ_AGENT	PURGE_QUEUE_TABLE

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