8 DBMS_XSTREAM_ADM

This DBMS_XSTREAM_ADM package provides interfaces for streaming database changes between an Oracle database and other systems. XStream enables applications to stream out or stream in database changes.

This chapter contains the following topic:

Using DBMS_XSTREAM_ADM
- Overview
- Security Model
- Operational Notes
Summary of DBMS_XSTREAM_ADM Subprograms

See Also:

Using DBMS_XSTREAM_ADM

This section contains topics which relate to using the DBMS_XSTREAM_ADM package.

Overview
Security Model
Operational Notes

Overview

The package provides interfaces for configuring outbound servers that stream database changes from an Oracle database to other systems. The package also provides interfaces for configuring inbound servers that stream database changes from other systems to an Oracle database. In both cases, the database changes are encapsulated in logical change records (LCRs). Also, the other systems can be Oracle systems or a non-Oracle systems, such as non-Oracle databases or file systems.

XStream outbound servers can stream out LCRs from an Oracle database programmatically using C or Java. After receiving the LCRs, the other system can process them in any customized way. For example, the other system can save the contents of the LCRs to a file, send the LCRs to an Oracle database through an XStream inbound server, or generate SQL statements and execute them on any Oracle or non-Oracle databases.

XStream inbound servers accept LCRs from another system and either apply them to an Oracle database or process them in a customized way using apply handlers.

See Also:

Chapter 2, "XStream Concepts"

Security Model

To ensure that the user who runs the subprograms in this package has the necessary privileges, configure an XStream administrator and connect as the XStream administrator when using this package.

An administrator must be granted the DBA role when the administrator is performing any of the following actions:

Running the ADD_OUTBOUND procedure while connected as a user that is different from the configured connect user for an outbound server
Running the ALTER_OUTBOUND procedure to change the capture user for a capture process or the connect user for an outbound server
Running the CREATE_OUTBOUND procedure, because this procedure creates a capture process
Running the ALTER_INBOUND procedure to change the apply user for an inbound server
Running the ADD_INBOUND procedure while connected as a user that is different from the configured apply user for an inbound server

When the administrator does not need to perform the preceding tasks, the DBA role is not required.

See Also:

"Granting Privileges for the XStream Administrator"
"XStream and Security" for more information about XStream and security

Operational Notes

Some subprograms in the DBMS_APPLY_ADM package can manage XStream outbound servers, and some subprograms in the DBMS_APPLY_ADM package can manage XStream inbound servers.

See Also:

Oracle Database PL/SQL Packages and Types Reference for details about which subprograms can manage outbound servers and inbound servers

Summary of DBMS_XSTREAM_ADM Subprograms

Table 8-1 DBMS_XSTREAM_ADM Package Subprograms

Subprogram	Description
ADD_OUTBOUND Procedure	Creates an XStream outbound server that dequeues LCRs from the specified queue
ADD_SUBSET_OUTBOUND_RULES Procedure	Adds subset rules to an outbound server configuration
ALTER_INBOUND Procedure	Modifies an XStream inbound server
ALTER_OUTBOUND Procedure	Modifies an XStream outbound server
CREATE_INBOUND Procedure	Creates an XStream inbound server and its queue
CREATE_OUTBOUND Procedure	Creates an XStream outbound server, queue, and capture process to enable XStream client applications to stream out Oracle database changes encapsulated in LCRs
DROP_INBOUND Procedure	Removes an inbound server configuration
DROP_OUTBOUND Procedure	Removes an outbound server configuration
ENABLE_GG_XSTREAM_FOR_STREAMS Procedure	Enables XStream performance optimizations for Oracle Streams components
IS_GG_XSTREAM_FOR_STREAMS Function	Returns `TRUE` if XStream performance optimizations are enabled for Oracle Streams components, or returns `FALSE` if XStream performance optimizations are disabled for Oracle Streams components
REMOVE_SUBSET_OUTBOUND_RULES Procedure	Removes subset rules from an outbound server configuration

Note:

All subprograms commit unless specified otherwise.

ADD_OUTBOUND Procedure

This procedure creates an XStream outbound server that dequeues LCRs from the specified queue. The outbound server streams out the LCRs to an XStream client application.

This procedure creates neither a capture process nor a queue. To create an outbound server, a capture process, and a queue with one procedure call, use the CREATE_OUTBOUND Procedure.

To create the capture process individually, use one of the following packages:

DBMS_STREAMS_ADM
DBMS_CAPTURE_ADM

To create a queue individually, use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package.

This procedure is overloaded. One table_names parameter is type VARCHAR2 and the other table_names parameter is type DBMS_UTILITY.UNCL_ARRAY. Also, one schema_names parameter is type VARCHAR2 and the other schema_names parameter is type DBMS_UTILITY.UNCL_ARRAY. These parameters enable you to enter the lists of tables and schemas in different ways and are mutually exclusive.

Note:

A client application can create multiple sessions. Each session can attach to only one outbound server, and each outbound server can serve only one session at a time. However, different client application sessions can connect to different outbound servers. See Part IV, "XStream OCI API Reference" and Oracle Database XStream Java API Reference for information about attaching to an outbound server.
This procedure enables the outbound server that it creates.
Starting with Oracle Database 11g Release 2 (11.2.0.2), the capture_name, start_scn, and start_time parameters are included in this procedure.

Syntax

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

DBMS_XSTREAM_ADM.ADD_OUTBOUND(
   server_name     IN  VARCHAR2,
   queue_name      IN  VARCHAR2   DEFAULT NULL,
   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);

Parameters

Table 8-2 ADD_OUTBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server being created. A `NULL` specification is not allowed. Do not specify an owner. The specified name must not match the name of an existing outbound server, inbound server, apply process, or messaging client. Note: The `server_name` setting cannot be altered after the outbound server is created.
`queue_name`	The name of the local queue from which the outbound server dequeues LCRs, specified as `[schema_name.]queue_name`. The current database must contain the queue, and the queue must be `ANYDATA` type. For example, to specify a queue named `xstream_queue` in the `xstrmadmin` schema, enter `xstrmadmin.xstream_queue` for this parameter. If the schema is not specified, then the current user is the default. If `NULL`, the procedure raises an error.
`source_database`	The global name of the source database. The source database is where the changes being captured originated. If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify `DBS1` and the domain is `EXAMPLE.COM`, then the procedure specifies `DBS1.EXAMPLE.COM` automatically. If `NULL`, then this procedure does not add a condition regarding the source database to the generated rules. Otherwise, a condition regarding the source database is added.
`table_names`	The tables for which data manipulation language (DML) and data definition language (DDL) changes are streamed out to the XStream client application. The tables can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a table. Specify the first table in position 1. The last position must be `NULL`. Each table should be specified as `[schema_name.]table_name`. For example, you can specify `hr.employees`. If the schema is not specified, then the current user is the default. See Also: "Usage Notes" for more information about this parameter
`schema_names`	The schemas for which DML and DDL changes are streamed out to the XStream client application. The schemas can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a schema. Specify the first schema in position 1. The last position must be `NULL`. Note: This procedure does not concatenate the `schema_names` parameter with the `table_names` parameter. To specify tables, enter fully qualified table names in the `table_names` parameter (`schema_name.table_name`). See Also: "Usage Notes" for more information about this parameter
`connect_user`	The user who can attach to the specified outbound server to retrieve the LCR stream. The client application must attach to the outbound server as the specified connect user. See "CREATE_OUTBOUND Procedure" for information about the privileges required by a connect user. If `NULL`, then the current user is the default.
`comment`	An optional comment associated with the outbound server.
`capture_name`	The name of the capture process configured to capture changes for the outbound server. Do not specify an owner. If the specified name matches the name of an existing capture process for another outbound server, then the procedure uses the existing capture process and adds the rules for capturing changes to the database to the positive capture process rule set. If the specified name matches the name of an existing capture process for an apply process, then an error is raised. If the specified name does not match the name of an existing capture process, then an error is raised. If `NULL`, then the outbound server is created without a capture process.
`start_scn`	A valid system change number (SCN) for the database from which the capture process starts capturing changes. If the `capture_name` parameter is `NULL`, then this parameter is ignored. If `NULL` and the `capture_name` parameter is non-`NULL`, then the start SCN of the capture process is not changed. An error is returned if an invalid SCN is specified. The `start_scn` and `start_time` parameters are mutually exclusive.
`start_time`	A valid time from which the capture process starts capturing changes. If the `capture_name` parameter is `NULL`, then this parameter is ignored. If `NULL` and the `capture_name` parameter is non-`NULL`, then the start SCN of the capture process is not changed. The `start_scn` and `start_time` parameters are mutually exclusive.

Usage Notes

The following list describes the behavior of the outbound server for various combinations of the table_names and schema_names parameters:

If both the table_names and schema_names parameters are NULL or empty, then the outbound server streams all DML and DDL changes to the client application.

This procedure is overloaded. The table_names and schema_names parameters are defaulted to NULL. Do not specify NULL for both table_names and schema_names in the same call; otherwise, error PLS-00307 is returned.
If both the table_names and schema_names parameters are specified, then the outbound server streams DML and DDL changes for the specified tables and schemas.
If the table_names parameter is specified and the schema_names parameter is NULL or empty, then the outbound server streams DML and DDL changes for the specified tables.
If the table_names parameter is NULL or empty and the schema_names parameter is specified, then the outbound server streams DML and DDL changes for the specified schemas.

For the procedure that uses the DBMS_UTILITY.UNCL_ARRAY type for the table_names and schema_names parameters, both parameters must be specified. To specify only tables, the schema_names parameter must be specified and empty. To specify only schemas, the table_names parameter must be specified and empty.

Note:

An empty array includes one NULL entry.

ADD_SUBSET_OUTBOUND_RULES Procedure

This procedure adds subset rules to an outbound server configuration. Subset rules instruct the outbound server to stream out a subset of the changes to the specified tables. Outbound servers can stream out a subset of both rows and columns.

This procedure is overloaded. One column_list parameter is type VARCHAR2 and the other column_list parameter is type DBMS_UTILITY.LNAME_ARRAY. These parameters enable you to enter the list of columns in different ways and are mutually exclusive.

Note:

This procedure does not add rules to the outbound server's capture process.

Syntax

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

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

Parameters

Table 8-3 ADD_SUBSET_OUTBOUND_RULES Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server to which rules are being added. Specify an existing outbound server. Do not specify an owner.
`table_name`	The name of the table specified as `[schema_name.]object_name`. For example, you can specify `hr.employees`. If the schema is not specified, then the current user is the default. If the outbound server configuration uses a local capture process, then the table must exist at the local source database. If the outbound server configuration uses a downstream capture process, then the table must exist at both the source database and at the downstream capture database. The specified table cannot have any LOB, `LONG`, or `LONG` `RAW` columns currently or in the future.
`condition`	The subset condition. Specify this condition similar to the way you specify conditions in a `WHERE` clause in SQL. For example, to specify rows in the `hr.employees` table where the `salary` is greater than `4000` and the `job_id` is `SA_MAN`, enter the following as the condition: `' salary > 4000 and job_id = ''SA_MAN'' '` If `NULL`, then the procedure raises an error. Note: The quotation marks in the preceding example are all single quotation marks.
`column_list`	The list of columns either to include in the outbound server configuration or to exclude from the outbound server configuration. Whether the columns are included or excluded depends on the setting for the `keep` parameter. The columns can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.LNAME_ARRAY`, where each element is the name of a column. Specify the first column in position 1. The last position must be `NULL`. To include or exclude all of the columns in a table, specify each column in the table in the list or array. If `NULL`, then the procedure raises an error.
`keep`	If `TRUE`, then the columns specified in the `column_list` parameter are kept as part of the outbound server configuration. Therefore, changes to these columns that satisfy the condition in the `condition` parameter are streamed to the outbound server's client application. If `FALSE`, then the columns specified in the `column_list` parameter are excluded from the outbound server configuration. Therefore, changes to these columns are not streamed to the outbound server's client application. See Also: "Usage Notes"

Usage Notes

When the keep parameter is set to TRUE, this procedure creates a keep columns declarative rule-based transformation for the columns listed in column_list.

When the keep parameter is set to FALSE, this procedure creates a delete column declarative rule-based transformation for each column listed in column_list.

See Also:

Oracle Streams Concepts and Administration for information about declarative rule-based transformations

ALTER_INBOUND Procedure

This procedure modifies an XStream inbound server.

Syntax

DBMS_XSTREAM_ADM.ALTER_INBOUND(
   server_name IN VARCHAR2,
   apply_user  IN VARCHAR2  DEFAULT NULL,  
   comment     IN VARCHAR2  DEFAULT NULL);

Parameters

Table 8-4 ALTER_INBOUND Procedure Parameters

Parameter Description

Parameter	Description
`server_name`	The name of the inbound server being altered. Specify an existing inbound server. Do not specify an owner.
`apply_user`	The user who applies all DML and DDL changes that satisfy the inbound server rule sets, who runs user-defined apply handlers, and who runs custom rule-based transformations configured for inbound server rules. The client application must attach to the inbound server as the apply user. Specify a user to change the apply user. In this case, the user who invokes the `ALTER_INBOUND` procedure must be granted the `DBA` role. Only the `SYS` user can set the `apply_user` to `SYS`. If `NULL`, then the apply user is not changed. See "CREATE_INBOUND Procedure" for information about the required privileges for an apply user.
`comment`	An optional comment associated with the inbound server. If non-`NULL`, then the specified comment replaces the existing comment. If `NULL`, then the existing comment is not changed.

server_name

The name of the inbound server being altered. Specify an existing inbound server. Do not specify an owner.

apply_user

The user who applies all DML and DDL changes that satisfy the inbound server rule sets, who runs user-defined apply handlers, and who runs custom rule-based transformations configured for inbound server rules.

The client application must attach to the inbound server as the apply user.

Specify a user to change the apply user. In this case, the user who invokes the ALTER_INBOUND procedure must be granted the DBA role. Only the SYS user can set the apply_user to SYS.

If NULL, then the apply user is not changed.

See "CREATE_INBOUND Procedure" for information about the required privileges for an apply user.

comment

An optional comment associated with the inbound server.

If non-NULL, then the specified comment replaces the existing comment.

If NULL, then the existing comment is not changed.

ALTER_OUTBOUND Procedure

This procedure modifies an XStream outbound server configuration.

This procedure always alters the specified outbound server. This procedure can also alter the outbound server's capture process when either of the following conditions is met:

The capture process was created by the CREATE_OUTBOUND procedure in this package.
The queue used by the capture process was created by the CREATE_OUTBOUND procedure.

To check whether this procedure can alter the outbound server's capture process, query the CAPTURE_NAME column in the DBA_XSTREAM_OUTBOUND view. When the name of the capture process appears in the CAPTURE_NAME column of this view, the ALTER_OUTBOUND procedure can manage the capture process's rules or change the capture user for the capture process. When the CAPTURE_NAME column of this view is NULL, the ALTER_OUTBOUND procedure cannot manage the capture process.

This procedure is overloaded. One table_names parameter is type VARCHAR2 and the other table_names parameter is type DBMS_UTILITY.UNCL_ARRAY. Also, one schema_names parameter is type VARCHAR2 and the other schema_names parameter is type DBMS_UTILITY.UNCL_ARRAY. These parameters enable you to enter the list of tables and schemas in different ways and are mutually exclusive.

Note:

Starting with Oracle Database 11g Release 2 (11.2.0.2), the start_scn and start_time parameters are included in this procedure.

Syntax

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 VARCHR2    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);

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

Parameters

Table 8-5 ALTER_OUTBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server being altered. Specify an existing outbound server. Do not specify an owner.
`table_names`	The tables that are either added to or removed from the XStream Out configuration. Whether the tables are added or removed depends on the setting for the `add` parameter. The tables can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a table. Specify the first table in position 1. The last position must be `NULL`. Each table should be specified as `[schema_name.]table_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default. See Also: "Usage Notes" for more information about this parameter
`schema_names`	The schemas that are either added to or removed from the XStream Out configuration. Whether the schemas are added or removed depends on the setting for the `add` parameter. The schemas can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a schema. Specify the first schema in position 1. The last position must be `NULL`. Note: This procedure does not concatenate the `schema_names` parameter with the `table_names` parameter. To specify tables, enter fully qualified table names in the `table_names` parameter (`schema_name.table_name`). See Also: "Usage Notes" for more information about this parameter
`add`	If `TRUE`, then the procedure adds to the XStream Out configuration the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter. If `FALSE`, then the procedure removes from the XStream Out configuration the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter.
`capture_user`	The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. Specify a user to change the capture user. In this case, the user who invokes the `ALTER_OUTBOUND` procedure must be granted the `DBA` role. Only the `SYS` user can set the `capture_user` to `SYS`. If `NULL`, then the capture user is not changed. If you change the capture user, then this procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user. Ensure that the capture user is granted the other required privileges. See "CREATE_OUTBOUND Procedure" for information about the privileges required by a capture user. The capture process is stopped and restarted automatically when you change the value of this parameter. Note: If the capture user for a capture process is dropped using `DROP` `USER . . .` `CASCADE`, then the capture process is also dropped automatically.
`connect_user`	The user who can attach to the specified outbound server to retrieve the change stream. The XStream client application must attach to the outbound server as the specified connect user. Specify a user to change the connect user. In this case, the user who invokes the `ALTER_OUTBOUND` procedure must be granted the `DBA` role. Only the `SYS` user can set the `connect_user` to `SYS`. If `NULL`, then the connect user is not changed. If you change the connect user, then this procedure grants the new connect user dequeue privileges on the queue used by the outbound server and configures the user as a secure queue user. Ensure that the connect user is granted the other required privileges. See "CREATE_OUTBOUND Procedure" for information about the privileges required by a connect user.
`comment`	An optional comment associated with the outbound server. If non-`NULL`, then the specified comment replaces the existing comment. If `NULL`, then the existing comment is not changed.
`inclusion_rule`	If `TRUE` and the `add` parameter is set to `TRUE`, then the procedure adds rules for the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter to the positive rule sets in the XStream Out configuration. When rules for tables and schemas are in positive rule sets, the XStream Out configuration streams DML and DDL changes to the tables and schemas out to the client application. If `TRUE` and the `add` parameter is set to `FALSE`, then the procedure removes rules for the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter from the positive rule sets in the XStream Out configuration. If `FALSE` and the `add` parameter is set to `TRUE`, then the procedure adds rules for the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter to the negative rule sets in the XStream Out configuration. When rules for tables and schemas are in negative rule sets, the XStream Out configuration does not stream changes to the tables and schemas out to the client application. If `FALSE` and the `add` parameter is set to `FALSE`, then the procedure removes rules for the tables specified in the `table_names` parameter and the schemas specified in the `schema_names` parameter from the negative rule sets in the XStream Out configuration.
`start_scn`	A valid SCN for the database from which the capture process starts capturing changes. To be valid, the SCN value must be greater than or equal to the first SCN for the capture process. If a valid SCN is specified, then the capture process captures changes from the specified SCN when it is restarted. An error is returned if an invalid SCN is specified. If `NULL` and the `start_time` parameter is `NULL`, then the start SCN is not changed. If `NULL` and the `start_time` parameter is non-`NULL`, then the start SCN is changed to match the specified start time. The `start_scn` and `start_time` parameters are mutually exclusive. Note: If the capture process is enabled, then the `ALTER_OUTBOUND` procedure automatically stops and restarts the capture process when the `start_scn` parameter is non-`NULL`. If the capture process is disabled, then the `ALTER_OUTBOUND` procedure automatically starts the capture process when the `start_scn` parameter is non-`NULL`.
`start_time`	A valid time from which the capture process starts capturing changes. To be valid, the time must correspond to an SCN value that is greater than or equal to the first SCN for the capture process. If a valid time is specified, then the capture process captures changes from the specified time when it is restarted. An error is returned if an invalid time is specified. If `NULL` and the `start_scn` parameter is `NULL`, then the start time is not changed. If `NULL` and the `start_scn` parameter is non-`NULL`, then the start time is changed to match the specified start SCN. The `start_scn` and `start_time` parameters are mutually exclusive. Note: If the capture process is enabled, then the `ALTER_OUTBOUND` procedure automatically stops and restarts the capture process when the `start_time` parameter is non-`NULL`. If the capture process is disabled, then the `ALTER_OUTBOUND` procedure automatically starts the capture process when the `start_time` parameter is non-`NULL`.

Usage Notes

The following list describes the behavior of the outbound server for various combinations of the table_names and schema_names parameters:

If both the table_names and schema_names parameters are NULL or empty, then no rules are changed for the XStream Out configuration.

This procedure is overloaded. The table_names and schema_names parameters are defaulted to NULL. Do not specify NULL for both table_names and schema_names in the same call; otherwise, error PLS-00307 is returned.
If both the table_names and schema_names parameters are specified, then the rules for the tables and schemas are added to or removed from the XStream Out configuration, depending on the setting of the add parameter.
If the table_names parameter is specified and the schema_names parameter is NULL or empty, then the rules for the tables are added to or removed from the XStream Out configuration, depending on the setting of the add parameter. The existing rules for schemas are not changed for the XStream Out configuration.
If the table_names parameter is NULL or empty and the schema_names parameter is specified, then the rules for the schemas are added to or removed from the XStream Out configuration, depending on the setting of the add parameter. The existing rules for tables are not changed for the XStream Out configuration.

Note:

An empty array includes one NULL entry.

CREATE_INBOUND Procedure

This procedure creates an XStream inbound server and its queue.

Note:

A client application can create multiple sessions. Each session can attach to only one inbound server, and each inbound server can serve only one session at a time. However, different client application sessions can connect to different inbound servers. See Part IV, "XStream OCI API Reference" and Oracle Database XStream Java API Reference for information about attaching to an inbound server.

Syntax

DBMS_XSTREAM_ADM.CREATE_INBOUND(
   server_name IN VARCHAR2,
   queue_name  IN VARCHAR2,
   apply_user  IN VARCHAR2  DEFAULT NULL,  
   comment     IN VARCHAR2  DEFAULT NULL);

Parameters

Table 8-6 CREATE_INBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the inbound server being created. A `NULL` specification is not allowed. Do not specify an owner. The specified name must not match the name of an existing outbound server, inbound server, apply process, or messaging client. Note: The `server_name` setting cannot be altered after the inbound server is created.
`queue_name`	The name of the local queue used by the inbound server, specified as `[schema_name.]queue_name`. If the specified queue exists, then it is used. If the specified queue does not exist, then the procedure creates it. For example, to specify a queue named `xstream_queue` in the `xstrmadmin` schema, enter `xstrmadmin.xstream_queue` for this parameter. If the schema is not specified, then the current user is the default. Note: An inbound server's queue is used only to store error transactions.
`apply_user`	The apply user. If `NULL`, then the current user is the default. The client application must attach to the inbound server as the apply user. The apply user is the user in whose security domain an inbound server evaluates whether LCRs satisfy its rule sets, applies DML and DDL changes directly to database objects, runs custom rule-based transformations configured for inbound server rules, and runs apply handlers configured for the inbound server. This user must have the necessary privileges to perform these actions. This procedure grants the apply user dequeue privileges on the queue used by the inbound server and configures the user as a secure queue user. In addition to the privileges granted by this procedure, you must grant the following privileges to the apply user: The necessary privileges to perform DML and DDL changes on the apply objects `EXECUTE` privilege on the rule sets used by the inbound server `EXECUTE` privilege on all rule-based transformation functions used in the rule set `EXECUTE` privilege on all apply handler procedures You can grant these privileges directly to the apply user, or you can grant them through roles. In addition, the apply user must be granted `EXECUTE` privilege on all packages, including Oracle supplied packages, that are invoked in subprograms run by the inbound server. These privileges must be granted directly to the apply user. They cannot be granted through roles. Note: If the apply user for an inbound server is dropped using `DROP` `USER . . .` `CASCADE`, then the inbound server is also dropped automatically.
`comment`	An optional comment associated with the inbound server.

Usage Notes

By default, an inbound server does not use rules or rule sets. Therefore, an inbound server applies all of the LCRs sent to it by an XStream client application. However, to filter the LCRs sent to an inbound server, you can add rules and rule sets to an inbound server using the DBMS_STREAMS_ADM and DBMS_RULE_ADM packages.

CREATE_OUTBOUND Procedure

This procedure creates an XStream outbound server, queue, and capture process to enable client applications to stream out Oracle database changes.

This procedure is overloaded. One table_names parameter is type VARCHAR2 and the other table_names parameter is type DBMS_UTILITY.UNCL_ARRAY. Also, one schema_names parameter is type VARCHAR2 and the other schema_names parameter is type DBMS_UTILITY.UNCL_ARRAY. These parameters enable you to enter the list of tables and schemas in different ways and are mutually exclusive.

Note:

A client application can create multiple sessions. Each session can attach to only one outbound server, and each outbound server can serve only one session at a time. However, different client application sessions can connect to different outbound servers. See "OCIXStreamOutAttach()" and Oracle Database XStream Java API Reference for information about attaching to an outbound server.
If the capture_name parameter is NULL, then this procedure automatically generates a name for the capture process that it creates.
This procedure automatically generates a name for the queue that it creates.
This procedure enables both the capture process and outbound server that it creates.
Starting with Oracle Database 11g Release 2 (11.2.0.2), the capture_name parameter is included in this procedure.

Syntax

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

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

Parameters

Table 8-7 CREATE_OUTBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server being created. A `NULL` specification is not allowed. Do not specify an owner. The specified name must not match the name of an existing outbound server, inbound server, apply process, or messaging client. Note: The `server_name` setting cannot be altered after the outbound server is created.
`source_database`	The global name of the source database. The source database is where the changes to be captured originated. If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify `DBS1` and the domain is `EXAMPLE.COM`, then the procedure specifies `DBS1.EXAMPLE.COM` automatically. If `NULL`, or if the specified name is the same as the global name of the current database, then local capture is assumed. If non-`NULL` and the specified name is different from the global name of the current database, then downstream capture is assumed. In this case, configure the transmission of redo data from the source database to the downstream database before running the `CREATE_OUTBOUND` procedure. See Oracle Streams Replication Administrator's Guide for instructions.
`table_names`	The tables for which DML and DDL changes are streamed out to the XStream client application. The tables can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a table. Specify the first table in position 1. The last position must be `NULL`. Each table should be specified as `[schema_name.]table_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default. See Also: "Usage Notes" for more information about this parameter
`schema_names`	The schemas for which DML and DDL changes are streamed out to the XStream client application. The schemas can be specified in the following ways: Comma-delimited list of type `VARCHAR2`. A PL/SQL associative array of type `DBMS_UTILITY.UNCL_ARRAY`, where each element is the name of a schema. Specify the first schema in position 1. The last position must be `NULL`. Note: This procedure does not concatenate the `schema_names` parameter with the `table_names` parameter. To specify tables, enter fully qualified table names in the `table_names` parameter (`schema_name.table_name`). See Also: "Usage Notes" for more information about this parameter
`capture_user`	The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. If `NULL`, then the current user is the default. This procedure grants the capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user. In addition, ensure that the capture user has the following privileges: `EXECUTE` privilege on the rule sets used by the capture process `EXECUTE` privilege on all rule-based transformation functions used in the positive rule set You can grant these privileges directly to the apply user, or you can grant them through roles. In addition, the capture user must be granted `EXECUTE` privilege on all packages, including Oracle supplied packages, that are invoked in rule-based transformations run by the capture process. These privileges must be granted directly to the capture user. They cannot be granted through roles. Only a user who is granted the `DBA` role can set a capture user. Only the `SYS` user can set the `capture_user` to `SYS`. A capture user does not require privileges on a database object to capture changes made to it. The capture process can pass these changes to a custom rule-based transformation function. Therefore, ensure that you consider security implications when you configure a capture process.
`connect_user`	The user who can attach to the specified outbound server to retrieve the change stream. The client application must attach to the outbound server as the specified connect user. If `NULL`, then the current user is the default. The connect user is the user in whose security domain an outbound server dequeues LCRs that satisfy its rule sets and runs custom rule-based transformations configured for outbound server rules. This user must have the necessary privileges to perform these actions. This procedure grants the connect user dequeue privileges on the queue used by the outbound server and configures the user as a secure queue user. In addition to the privileges granted by this procedure, grant the following privileges to the connect user: `EXECUTE` privilege on the rule sets used by the outbound server `EXECUTE` privilege on all rule-based transformation functions used in the rule set You can grant these privileges directly to the connect user, or you can grant them through roles. In addition, the connect user must be granted `EXECUTE` privilege on all packages, including Oracle supplied packages, that are invoked in subprograms run by the outbound server. These privileges must be granted directly to the apply user. They cannot be granted through roles.
`comment`	An optional comment associated with the outbound server.
`capture_name`	The name of the capture process configured to capture changes for the outbound server. Do not specify an owner. The capture process must not exist. If the specified name matches the name of an existing capture process, then an error is raised. If the name does not match the name of an existing capture process, then the procedure creates a new capture process with the specified name. If `NULL`, then the system creates a new capture process with a system-generated name. Note: The capture process name cannot be altered after the capture process is created.

Usage Notes

The following list describes the behavior of the outbound server for various combinations of the table_names and schema_names parameters:

If both the table_names and schema_names parameters are NULL or empty, then the outbound server streams all DML and DDL changes to the client application.

This procedure is overloaded. The table_names and schema_names parameters are defaulted to NULL. Do not specify NULL for both table_names and schema_names in the same call; otherwise, error PLS-00307 is returned.
If both the table_names and schema_names parameters are specified, then the outbound server streams DML and DDL changes for the specified tables and schemas.
If the table_names parameter is specified and the schema_names parameter is NULL or empty, then the outbound server streams DML and DDL changes for the specified tables.
If the table_names parameter is NULL or empty and the schema_names parameter is specified, then the outbound server streams DML and DDL changes for the specified schema.

Note:

An empty array includes one NULL entry.

DROP_INBOUND Procedure

This procedure removes an inbound server configuration.

This procedure always removes the specified inbound server. This procedure also removes the queue for the inbound server if all of the following conditions are met:

One call to the CREATE_INBOUND procedure created the queue.
The inbound server is the only subscriber to the queue.

See Also:

"CREATE_INBOUND Procedure"

Syntax

DBMS_XSTREAM_ADM.DROP_INBOUND(
   server_name IN VARCHAR2);

Parameters

Table 8-8 DROP_INBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the inbound server being removed. Specify an existing inbound server. Do not specify an owner.

DROP_OUTBOUND Procedure

This procedure removes an outbound server configuration.

This procedure always drops the specified outbound server. This procedure also drops the queue used by the outbound server if both of the following conditions are met:

The queue was created by the CREATE_OUTBOUND procedure in this package.
The outbound server is the only subscriber to the queue.

If either one of the preceding conditions is not met, then the DROP_OUTBOUND procedure only drops the outbound server. It does not drop the queue.

This procedure also drops the capture process for the outbound server if both of the following conditions are met:

The procedure can drop the outbound server's queue.
The capture process was created by the CREATE_OUTBOUND procedure.

If the procedure can drop the queue but cannot manage the capture process, then it drops the queue without dropping the capture process.

See Also:

"ADD_OUTBOUND Procedure"
"CREATE_OUTBOUND Procedure"

Syntax

DBMS_XSTREAM_ADM.DROP_OUTBOUND(
   server_name IN VARCHAR2);

Parameters

Table 8-9 DROP_OUTBOUND Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server being removed. Specify an existing outbound server. Do not specify an owner.

ENABLE_GG_XSTREAM_FOR_STREAMS Procedure

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

This procedure enables XStream capabilities and performance optimizations for Oracle Streams components.

This procedure is intended for users of Oracle Streams who want to enable XStream capabilities and optimizations. For example, you can enable the optimizations for an Oracle Streams replication configuration that uses capture processes and apply processes to replicate changes between Oracle databases.

These capabilities and optimizations are enabled automatically for XStream components, such as outbound servers, inbound servers, and capture processes that send changes to outbound servers. It is not necessary to run this procedure for XStream components.

When XStream capabilities are enabled, Oracle Streams components can stream ID key LCRs and sequence LCRs. The XStream performance optimizations improve efficiency in various areas, including:

LCR processing
Handling large transactions
DML execution during apply
Dependency computation and scheduling
Capture process parallelism

Syntax

DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS(
   enable IN BOOLEAN  TRUE);

Parameters

Table 8-10 ENABLE_GG_XSTREAM_FOR_STREAMS Procedure Parameters

Parameter Description

Parameter	Description
`enable`	If `TRUE`, then enable XStream performance optimizations for Oracle Streams components. If `FALSE`, then disable XStream performance optimizations for Oracle Streams components.

enable

If TRUE, then enable XStream performance optimizations for Oracle Streams components.

If FALSE, then disable XStream performance optimizations for Oracle Streams components.

Usage Notes

The following usage notes apply to this procedure:

When you run this procedure, all capture processes and apply processes are restarted.
After you run this procedure, the PURPOSE column in the following views displays XStream Streams:
- ALL_APPLY
- DBA_APPLY
- ALL_CAPTURE
- DBA_CAPTURE
A license for the Oracle GoldenGate product is required to enable XStream performance optimizations for Oracle Streams components.

See Also:

IS_GG_XSTREAM_FOR_STREAMS Function

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

This function returns TRUE if XStream performance optimizations are enabled for Oracle Streams components, or this function returns FALSE if XStream performance optimizations are disabled for Oracle Streams components.

Syntax

DBMS_XSTREAM_ADM.IS_GG_XSTREAM_FOR_STREAMS
RETURN BOOLEAN;

REMOVE_SUBSET_OUTBOUND_RULES Procedure

This procedure removes subset rules from an outbound server configuration.

The names of the specified insert, update, and delete rules must match those generated by the ADD_SUBSET_OUTBOUND_RULES procedure. To view the rule names for subset rules, run the following query:

SELECT RULE_OWNER, SUBSETTING_OPERATION, RULE_NAME 
   FROM DBA_XSTREAM_RULES 
   WHERE SUBSETTING_OPERATION IS NOT NULL;

Note:

This procedure removes the declarative rule-based transformation associated with each rule it removes.
This procedure does not remove rules from the outbound server's capture process.

See Also:

"ADD_SUBSET_OUTBOUND_RULES Procedure"

Syntax

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

Parameters

Table 8-11 REMOVE_SUBSET_OUTBOUND_RULES Procedure Parameters

Parameter	Description
`server_name`	The name of the outbound server from which rules are being removed. Specify an existing outbound server. Do not specify an owner.
`insert_rule_name`	The name of the insert rule being removed, specified as `[schema_name.]rule_name`. For example, to specify a rule in the `hr` schema named `rule1`, enter `hr.rule1`. If the schema is not specified, then the current user is the default. If `NULL`, then the procedure raises an error.
`update_rule_name`	The name of the update rule being removed, specified as `[schema_name.]rule_name`. If `NULL`, then the procedure raises an error.
`delete_rule_name`	The name of the delete rule being removed, specified as `[schema_name.]rule_name`. If `NULL`, then the procedure raises an error.