ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Advanced Queuing support used by GoldenGate,
LDAP, Logical Standby, and for kernel support.
AUTHID
DEFINER
Constants
The list at right is likely incomplete but were found during discovery.
dbms_aqadm_sys.add_connection_to_ldap(
connection IN VARCHAR2,
host IN VARCHAR2,
port IN BINARY_INTEGER,
sid IN VARCHAR2,
driver IN VARCHAR2,
type IN BINARY_INTEGER);
TBD
Overload 2
dbms_aqadm_sys.add_connection_to_ldap(
connection IN VARCHAR2,
jdbc_string IN VARCHAR2,
username IN VARCHAR2,
password IN VARCHAR2,
type IN BINARY_INTEGER);
dbms_aqadm_sys.add_queue_tab_to_ldap (
queue_tab_schema IN VARCHAR2,
queue_table IN VARCHAR2,
payload_type IN VARCHAR2,
multiple_consumers IN BOOLEAN,
message_grouping IN BINARY_INTEGER,
primary_instance IN BINARY_INTEGER,
secondary_instance IN BINARY_INTEGER,
compatibility IN VARCHAR2,
comment IN VARCHAR2);
dbms_aqadm_sys.add_queue_to_ldap(
queue_schema IN VARCHAR2,
queue_name IN VARCHAR2,
qt_schema IN VARCHAR2,
queue_table IN VARCHAR2,
queue_type IN BINARY_INTEGER,
max_retries IN BINARY_INTEGER,
retry_delay IN BINARY_INTEGER,
retention_time IN BINARY_INTEGER);
dbms_aqadm_sys.add_subscriber(
queue_name IN VARCHAR2,
subscriber IN sys.aq$_agent,
rule IN VARCHAR2,
security_check IN BOOLEAN,
in_recursive_txn IN BOOLEAN,
add_jms_entry IN BOOLEAN,
transformation IN VARCHAR2,
ruleset IN VARCHAR2,
negative_ruleset IN VARCHAR2,
properties IN NUMBER,
name_canonicalized IN BOOLEAN,
queue_to_queue IN BOOLEAN,
client_session_guid IN VARCHAR2,
instno IN NUMBER,
current_user IN VARCHAR2);
-- Rem Define the HAE_SUB subscriber for the alert_que
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('HAE_SUB', NULL, NULL);
dbms_aqadm_sys.add_subscriber(queue_name => 'SYS.ALERT_QUE',
subscriber => subscriber,
rule => 'tab.user_data.MESSAGE_LEVEL <> '
|| sys.dbms_server_alert.level_clear ||
' AND tab.user_data.MESSAGE_GROUP = ' ||
'''High Availability''',
transformation => 'SYS.haen_txfm_obj',
properties => dbms_aqadm_sys.NOTIFICATION_SUBSCRIBER+dbms_aqadm_sys.PUBLIC_SUBSCRIBER);
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -24034 THEN
NULL
ELSE
RAISE;
END IF;
END;
/
dbms_aqadm_sys.add_queue_subscriber_to_ldap(
queue_schema IN VARCHAR2,
queue_name IN VARCHAR2,
subscriber IN sys.sq$agent,
add_jms_entry IN BOOLEAN,
rule IN VARCHAR2,
transformation IN VARCHAR2);
dbms_aqadm_sys.create_aq_agentr(
agent_name IN VARCHAR2,
certificate_location IN VARCHAR2 DEFAULT NULL,
enable_http IN BOOLEAN DEFAULT NULL,
enable_smtp IN BOOLEAN DEFAULT NULL,
enable_anyp IN BOOLEAN DEFAULT NULL,
name_canonicalized IN BOOLEAN);
BEGIN
dbms_aqadm_sys.create_aq_agent(agent_name => 'SCHEDULER$_EVENT_AGENT',
name_canonicalized => FALSE);
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -24089 THEN
NULL;
ELSE
RAISE;
END IF;
END;
/
Note that this code was will compile in 12.1 but will no longer compile as
ksdwrt is no longer in the dbms_system package.
dbms_aqadm_sys.create_base_view(
qt_schema IN VARCHAR2,
qt_name IN VARCHAR2,
qt_flags IN NUMBER);
DECLARE
altvstmt VARCHAR2(1000);
BEGIN
FOR cur_rec IN (SELECT distinct t.schema, t.name, t.flags
FROM system.aq$_queue_tables t, system.aq$_queues q
WHERE t.objno = q.table_objno and NVL(q.sharded,0) =0)
LOOP
BEGIN
BEGIN
altvstmt := 'alter view
'||dbms_assert.enquote_name(cur_rec.schema,FALSE) ||'.'||
dbms_assert.enquote_name('AQ$_'||cur_rec.name ||'_F',FALSE) ||
' compile';
EXECUTE IMMEDIATE altvstmt;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
IF cur_rec.name != 'AQ$DEF$_AQCALL' AND cur_rec.name != 'DEF$_AQERROR' THEN
IF bitand(cur_rec.flags, 1) = 1 THEN -- multi-consumer queue
sys.dbms_prvtaqim.create_base_view(cur_rec.schema,cur_rec.name,cur_rec.flags);
ELSE -- singleconsumer queue
sys.dbms_aqadm_sys.create_base_view(cur_rec.schema,cur_rec.name,cur_rec.flags);
END IF;
END IF;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_system.ksdwrt(dbms_system.trace_file,'error in unsharded view creation'||sqlcode);
RAISE;
END;
/
dbms_aqadm_sys.mcq_12gJMS(qt_flags IN NUMBER) RETURN BOOLEAN;
BEGIN
FOR cur_rec IN (SELECT t.schema, t.name, t.flags, q.eventid
FROM system.aq$_queue_tables t, system.aq$_queues q
WHERE t.objno = q.table_objno
AND q.sharded =1) LOOP
BEGIN
sys.dbms_prvtaqim.create_base_view_12C_12201(cur_rec.schema, cur_rec.name,
cur_rec.eventid, sys.dbms_aqadm_sys.mcq_12gJms(cur_rec.flags), cur_rec.flags);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_system..ksdwrt(dbms_system.trace_file, 'error in 12C view creation' || sqlcode);
RAISE;
END;
/
dbms_aqadm_sys.mcq_8_1(qt_flags IN NUMBER) RETURN BOOLEAN;
DECLARE
CURSOR qt_cur IS
SELECT qt.schema, qt.name, qt.flags
FROM system.aq$_queue_tables qt;
BEGIN
FOR qt_rec IN qt_cur LOOP
BEGIN
IF dbms_aqadm_sys.mcq_8_1(qt_rec.flags) THEN
sys.dbms_prvtaqim.create_deq_view(qt_rec.schema, qt_rec.name, qt_rec.flags);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_system.ksdwrt(dbms_system.alert_file, 'a1102000.sql: recreate
deq view ' || 'failed for ' || qt_rec.schema || '.' || qt_rec.name);
END;
END LOOP;
END;
/
dbms_aqadm_sys.mod_queue_in_ldap(
queue_schema IN VARCHAR2,
queue_name IN VARCHAR2,
max_retries IN BINARY_INTEGER,
retry_delay IN BINARY_INTEGER,
retention_time IN BINARY_INTEGER);
dbms_aqadm_sys.mod_queue_tab_in_ldap(
queue_tab_schema IN VARCHAR2,
queue_table IN VARCHAR2,
primary_instance IN BINARY_INTEGER,
secondary_instance IN BINARY_INTEGER,
comment IN VARCHAR2);
dbms_aqadm_sys.mod_subscriber_in_ldap(
queue_schema IN VARCHAR2,
queue_name IN VARCHAR2,
subscriber IN sys.aq$agent,
rule IN VARCHAR2,
transformation IN VARCHAR2);
DECLARE
cursor qt_cur is select schema, name, flags from system.aq$_queue_tables;
BEGIN
FOR qt IN qt_cur LOOP
IF (sys.dbms_aqadm_sys.mcq_8_1(qt.flags) AND
sys.dbms_aqadm_sys.newq_10_1(qt.flags)) THEN
BEGIN
--sys.dbms_prvtaqim.drop_dqlog_view(qt.schema, qt.name);
/* bug15964874/16105750: Recreate buffer view for WM$EVENT_QUEUE_TABLE */
IF (qt.name='WM$EVENT_QUEUE_TABLE') THEN
sys.dbms_aqadm_sys.drop_buffer_view(qt.schema, qt.name);
sys.dbms_aqadm_sys.create_buffer_view(qt.schema, qt.name, TRUE);
END IF;
sys.dbms_prvtaqim.create_base_view11_2_0(qt.schema, qt.name, qt.flags);
EXCEPTION
WHEN OTHERS THEN
dbms_system.ksdwrt(dbms_system.alert_file, 'f1102000.sql: create _L view or
recreate base' || ' view failed for ' || qt.schema || '.' || qt.name);
END;
END IF;
END LOOP;
END;
/
