Oracle DBMS_XSTREAM_ADM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose Provides interfaces for streaming database changes between an Oracle database and heterogeneous, external, systems enabling applications to stream data changes both in or out.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Miscellaneous
RESET_PARAMETERS BINARY_INTEGER 1
RESET_HANDLERS BINARY_INTEGER 2
RESET_PROGRESS BINARY_INTEGER 4
RESET_ALL BINARY_INTEGER 2147483647
Set Message Tracing
ACTION_TRACE BINARY_INTEGER 1
ACTION_MEMORY BINARY_INTEGER 2
Variable is_xstream BOOLEAN;
Dependencies
ANYDATA DBMS_LOGREP_UTIL DBMS_STREAMS_DECL
DBMS_APPLY_ADM DBMS_STREAMS_ADM DBMS_UTILITY
DBMS_APPLY_ADM_INTERNAL DBMS_STREAMS_ADM_IVK DBMS_XSTREAM_ADM_UTL
DBMS_CAPTURE_ADM_INTERNAL DBMS_STREAMS_ADM_UTL DBMS_XSTREAM_GG_ADM
DBMS_CAPTURE_ADM_IVK DBMS_STREAMS_ADM_UTL_INVOK DBMS_XSTREAM_UTL_IVK
Documented Yes
First Available 11.2.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsxstr.sql
Subprograms

New subprograms identified with the help of Hans Forbrich as I no longer have an 11gR2 instance.
ADD_COLUMN CREATE_OUTBOUND REMOVE_RULE
ADD_GLOBAL_PROPAGATION_RULES DELETE_COLUMN REMOVE_SUBSET_OUTBOUND_RULES
ADD_GLOBAL_RULES DROP_INBOUND REMOVE_XSTREAM_CONFIGURATION
ADD_OUTBOUND DROP_OUTBOUND RENAME_COLUMN
ADD_SCHEMA_PROPAGATION_RULES ENABLE_GG_XSTREAM_FOR_STREAMS RENAME_SCHEMA
ADD_SCHEMA_RULES GET_MESSAGE_TRACKING RENAME_TABLE
ADD_SUBSET_OUTBOUND_RULES GET_TAG SET_MESSAGE_TRACKING
ADD_SUBSET_PROPAGATION_RULES IS_GG_XSTREAM_FOR_STREAMS SET_PARAMETER
ADD_SUBSET_RULES KEEP_COLUMNS SET_TAG
ADD_TABLE_PROPAGATION_RULES MERGE_STREAMS SET_UP_QUEUE
ADD_TABLE_RULES MERGE_STREAMS_JOB SPLIT_STREAMS
ALTER_INBOUND PURGE_SOURCE_CATALOG START_OUTBOUND
ALTER_OUTBOUND RECOVER_OPERATION STOP_OUTBOUND
CREATE_INBOUND REMOVE_QUEUE  
 
ADD_OUTBOUND (new 12.1 parameters)
Creates an Oracle XStream outbound server that dequeues logical change records (LCRs) from the specified queue

Overload 1
dbms_xstream_adm.add_outbound(
server_name           IN VARCHAR2,
queue_name            IN VARCHAR2  DEFAULT NULL,
source_database       IN VARCHAR2  DEFAULT NULL,
table_names           IN DBMS_UTILITY.UNCL_ARRAY,
schema_names          IN DBMS_UTILITY.UNCL_ARRAY,
connect_user          IN VARCHAR2  DEFAULT NULL,
comment               IN VARCHAR2  DEFAULT NULL,
capture_name          IN VARCHAR2  DEFAULT NULL,
start_scn             IN NUMBER    DEFAULT NULL,
start_time            IN TIMESTAMP DEFAULT NULL,
include_dml           IN BOOLEAN DEFAULT TRUE,
include_ddl           IN BOOLEAN DEFAULT TRUE,
source_root_name      IN VARCHAR2 DEFAULT NULL,
source_container_name IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_xstream_adm.add_outbound(
server_name           IN VARCHAR2,
queue_name            IN VARCHAR2,
source_database       IN VARCHAR2  DEFAULT NULL,
table_names           IN VARCHAR2  DEFAULT NULL,
schema_names          IN VARCHAR2  DEFAULT NULL,
connect_user          IN VARCHAR2  DEFAULT NULL,
comment               IN VARCHAR2  DEFAULT NULL,
capture_name          IN VARCHAR2  DEFAULT NULL,
start_scn             IN NUMBER    DEFAULT NULL,
start_time            IN TIMESTAMP DEFAULT NULL,
include_dml           IN BOOLEAN DEFAULT TRUE,
include_ddl           IN BOOLEAN DEFAULT TRUE,
source_root_name      IN VARCHAR2 DEFAULT NULL,
source_container_name IN VARCHAR2 DEFAULT NULL);
TBD
 
ADD_SUBSET_OUTBOUND_RULES (new 12.1 parameters)
Adds subset rules to an outbound server configuration

Overload 1
dbms_xstream_adm.add_subset_outbound_rules(
server_name     IN VARCHAR2,
table_name      IN VARCHAR2,
condition       IN VARCHAR2 DEFAULT NULL,
column_list     IN DBMS_UTILITY.LNAME_ARRAY,
keep            IN BOOLEAN  DEFAULT TRUE,
source_database IN VARCHAR2 DEFAULT NULL);
SELECT rule_owner, subsetting_operation, rule_name
FROM dba_xstream_rules
WHERE subsetting_operation IS NOT NULL;
Overload 2 dbms_xstream_adm.add_subset_outbound_rules(
server_name     IN VARCHAR2,
table_name      IN VARCHAR2,
condition       IN VARCHAR2 DEFAULT NULL,
column_list     IN VARCHAR2 DEFAULT NULL,
keep            IN BOOLEAN  DEFAULT TRUE,
source_database IN VARCHAR2 DEFAULT NULL);
SELECT rule_owner, subsetting_operation, rule_name
FROM dba_xstream_rules
WHERE subsetting_operation IS NOT NULL;
 
ALTER_INBOUND
Modifies an Oracle XStream inbound server dbms_xstream_adm.alter_inbound(
server_name IN VARCHAR2,
apply_user  IN VARCHAR2 DEFAULT NULL,
comment     IN VARCHAR2 DEFAULT NULL);
TBD
 
ALTER_OUTBOUND (new 12.1 parameters)
Modifies an Oracle XStream outbound server

Overload 1
dbms_xstream_adm.alter_outbound(
server_name           IN VARCHAR2,
table_names           IN DBMS_UTILITY.UNCL_ARRAY,
schema_names          IN DBMS_UTILITY.UNCL_ARRAY,
add                   IN BOOLEAN   DEFAULT TRUE,
capture_user          IN VARCHAR2  DEFAULT NULL,
connect_user          IN VARCHAR2  DEFAULT NULL,
comment               IN VARCHAR2  DEFAULT NULL,
inclusion_rule        IN BOOLEAN   DEFAULT TRUE,
start_scn             IN NUMBER    DEFAULT NULL,
start_time            IN TIMESTAMP DEFAULT NULL,
include_dml           IN BOOLEAN   DEFAULT TRUE,
include_ddl           IN BOOLEAN   DEFAULT TRUE,
source_database       IN VARCHAR2  DEFAULT NULL,
source_container_name IN VARCHAR2  DEFAULT NULL);
TBD
Overload 2 dbms_xstream_adm.alter_outbound(
server_name           IN VARCHAR2,
table_names           IN VARCHAR2  DEFAULT NULL,
schema_names          IN VARCHAR2  DEFAULT NULL,
add                   IN BOOLEAN   DEFAULT TRUE,
capture_user          IN VARCHAR2  DEFAULT NULL,
connect_user          IN VARCHAR2  DEFAULT NULL,
comment               IN VARCHAR2  DEFAULT NULL,
inclusion_rule        IN BOOLEAN   DEFAULT TRUE,
start_scn             IN NUMBER    DEFAULT NULL,
start_time            IN TIMESTAMP DEFAULT NULL,
include_dml           IN BOOLEAN   DEFAULT TRUE,
include_ddl           IN BOOLEAN   DEFAULT TRUE,
source_database       IN VARCHAR2  DEFAULT NULL,
source_container_name IN VARCHAR2  DEFAULT NULL);
TBD
 
CREATE_INBOUND
Creates an Oracle XStream inbound server and its queue dbms_xstream_adm.create_inbound(
server_name IN VARCHAR2,
queue_name  IN VARCHAR2,
apply_user  IN VARCHAR2 DEFAULT NULL,
comment     IN VARCHAR2 DEFAULT NULL);
TBD
 
CREATE_OUTBOUND (new 12.1 parameters)
Creates an Oracle XStream outbound server, queue, and capture process to enable client applications to stream out Oracle database changes encapsulated in logical change records (LCRs)

Overload 1
dbms_xstream_adm.create_outbound(
server_name          IN VARCHAR2,
source_database      IN VARCHAR2 DEFAULT NULL,
table_names          IN VARCHAR2 DEFAULT NULL,
schema_names         IN VARCHAR2 DEFAULT NULL,
capture_user         IN VARCHAR2 DEFAULT NULL,
connect_user         IN VARCHAR2 DEFAULT NULL,
comment              IN VARCHAR2 DEFAULT NULL,
capture_name         IN VARCHAR2 DEFAULT NULL,
include_dml          IN BOOLEAN  DEFAULT TRUE,
include_ddl          IN BOOLEAN  DEFAULT TRUE,
source_root_name     IN VARCHAR2 DEFAULT NULL,
source_contaier_name IN VARCHAR2);
TBD
Overload 2 dbms_xstream_adm.create_outbound(
server_name          IN VARCHAR2,
source_database      IN VARCHAR2 DEFAULT NULL,
table_names          IN DBMS_UTILITY.UNCL_ARRAY,
schema_names         IN DBMS_UTILITY.UNCL_ARRAY,
capture_user         IN VARCHAR2 DEFAULT NULL,
connect_user         IN VARCHAR2 DEFAULT NULL,
comment              IN VARCHAR2 DEFAULT NULL,
capture_name         IN VARCHAR2 DEFAULT NULL,
include_dml          IN BOOLEAN  DEFAULT TRUE,
include_ddl          IN BOOLEAN  DEFAULT TRUE,
source_root_name     IN VARCHAR2 DEFAULT NULL,
source_contaier_name IN VARCHAR2);
TBD
 
DROP_INBOUND
Removes an inbound server configuration dbms_xstream_adm.drop_inbound(server_name IN VARCHAR2);
TBD
 
DROP_OUTBOUND
Removes an outbound server configuration dbms_xstream_adm.drop_outbound(server_name IN VARCHAR2);
TBD
 
ENABLE_GG_XSTREAM_FOR_STREAMS
Enables XStream performance optimizations for Oracle Streams components dbms_xstream_adm.enable_gg_xstream_for_streams(enable IN BOOLEAN DEFAULT TRUE);
exec dbms_xstream_adm.enable_gg_xstream_for_streams(FALSE);
 
GET_MESSAGE_TRACKING (new 12.1)
Returns the tracking label for the current session dbms_xstream_adm.get_message_tracking RETURN VARCHAR2;
SELECT dbms_xstream_adm.get_message_tracking
FROM dual;
 
GET_TAG (new 12.1)
Gets the binary tag for all redo entries generated by the current session dbms_xstream_adm.get_tag RETURN RAW;
See SET_TAG Demo Below
 
IS_GG_XSTREAM_FOR_STREAMS
Returns TRUE if the transaction is Golden Gate dbms_xstream_adm.is_gg_xstream_for_streams RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_xstream_adm.is_gg_xstream_for_streams THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
REMOVE_SUBSET_OUTBOUND_RULES
Removes subset rules from an outbound server configuration dbms_xstream_adm.remove_subset_outbound_rules(
server_name      IN VARCHAR2,
insert_rule_name IN VARCHAR2,
update_rule_name IN VARCHAR2,
delete_rule_name IN VARCHAR2);
SELECT rule_owner, subsetting_operation, rule_name
FROM dba_xstream_rules
WHERE subsetting_operation IS NOT NULL;
 
SET_MESSAGE_TRACKING (new 12.1)
Sets the current tracking label for logical change records (LCRs) produced by the current session dbms_xstream_adm.set_message_tracking(
tracking_label IN VARCHAR2,
actions        IN NUMBER DEFAULT dbms_xstream_adm.action_memory);
exec dbms_xstream_adm.set_message_tracking(NULL, dbms_xstream_adm.action_memory);
 
SET_TAG (new 12.1)
sets the binary tag for all redo entries subsequently generated by the current session. Each redo entry generated by DML or DDL statements in the current session will have this tag. dbms_xstream_adm.set_tag(tag IN RAW DEFAULT NULL);
DECLARE
 tagVal RAW(2000) := utl_raw.cast_to_raw('UWXSTag');
BEGIN
  dbms_xstream_adm.set_tag(tagVal);
  dbms_output.put_line(dbms_xstream_adm.get_tag);
END;
/
 
START_OUTBOUND (new 12.1)
Starts an XStream outbound server streaming out the LCRs to an XStream client application dbms_xstream_adm.start_outbound(server_name IN VARCHAR2);
TBD
 
STOP_OUTBOUND (new 12.1)
Stoops an XStream outbound server streaming out the LCRs to an XStream client application. dbms_xstream_adm.stop_outbound(server_name IN VARCHAR2);
TBD

Related Topics
DBMS_XSTREAM_ADM_UTL
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