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:

See Also:

Using DBMS_XSTREAM_ADM

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

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:

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

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.

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.

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.

See Also:

Oracle Streams Concepts and Administration

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.

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.

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:

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

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.

See Also:

"ENABLE_GG_XSTREAM_FOR_STREAMS Procedure"

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.