106 DBMS_STREAMS_ADM

Using DBMS_STREAMS_ADM

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

• Overview

• Deprecated Subprograms

• Operational Notes

• Security Model

Overview

The DBMS_STREAMS_ADM package, one of a set of Streams packages, provides subprograms for adding and removing simple rules for capture, propagation, apply, and dequeue at the table, schema, and database level. These rules support logical change records (LCRs), which include row LCRs and data definition language (DDL) LCRs. This package also contains subprograms for creating message rules for specific message types. This package also contains subprograms for configuring a Streams replication environment, creating queues, and for managing Streams metadata, such as data dictionary information.

If you require more sophisticated rules, refer to Chapter 91, "DBMS_RULE" package.

Deprecated Subprograms

The following subprograms are deprecated with Oracle Database 10g Release 2:

• MAINTAIN_SIMPLE_TABLESPACE

  This procedure is replaced by the MAINTAIN_SIMPLE_TTS procedure.

  
  

  See Also: MAINTAIN_SIMPLE_TTS Procedure

  
  

• MAINTAIN_TABLESPACES

  This procedure is replaced by the MAINTAIN_TTS procedure.

  
  

  See Also: MAINTAIN_TTS Procedure

  
  

Security Model

A user is associated with each Streams client. The following sections describe these users.

Capture User

The following procedures can create a capture process:

• ADD_GLOBAL_RULES Procedure

• ADD_SCHEMA_RULES Procedure

• ADD_SUBSET_RULES Procedure

• ADD_TABLE_RULES Procedure

If one of these procedures creates a capture process, then it configures the current user as the capture_user. The capture user is 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. This user must have the necessary privileges to capture changes. The procedure grants the capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue.

Propagation User

The following procedures can create a propagation:

• ADD_GLOBAL_PROPAGATION_RULES Procedure

• ADD_MESSAGE_PROPAGATION_RULE Procedure

• ADD_SCHEMA_PROPAGATION_RULES Procedure

• ADD_SUBSET_PROPAGATION_RULES Procedure

• ADD_TABLE_PROPAGATION_RULES Procedure

When a propagation is created, a propagation job also might be created. If a propagation job is created when one of these procedures is run, then the user who runs the procedure owns the propagation job.

Apply User

The following procedures can create an apply process:

• ADD_GLOBAL_RULES Procedure

• ADD_MESSAGE_RULE Procedure

• ADD_SCHEMA_RULES Procedure

• ADD_SUBSET_RULES Procedure

• ADD_TABLE_RULES Procedure

If one of these procedures creates an apply process, then it configures the current user as the apply_user. The apply user is the user in whose security domain an apply process dequeues messages that satisfy its rule sets, applies messages directly to database objects, runs custom rule-based transformations configured for apply process rules, and runs apply handlers configured for the apply process. This user must have the necessary privileges to apply changes. The procedure grants the apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue.

Messaging Client User

The following procedures can create a messaging client:

• ADD_GLOBAL_RULES Procedure

• ADD_MESSAGE_RULE Procedure

• ADD_SCHEMA_RULES Procedure

• ADD_SUBSET_RULES Procedure

• ADD_TABLE_RULES Procedure

If one of these procedures creates a messaging client, then the user who runs this procedure is granted the privileges to dequeue from the queue using the messaging client. The procedure configures this user as a secure queue user of the queue, and only this user can use the messaging client.

Operational Notes

Several procedures in this package create rules for Streams clients, and several procedures configure and maintain a Streams replication environment. The following sections provide information about using these procedures:

• Procedures That Create Rules and Streams Clients

• Procedures That Configure a Streams Replication Environment

Procedures That Create Rules and Streams Clients

Streams clients include capture processes, propagations, apply processes, and messaging clients. Some of the procedures in the DBMS_STREAMS_ADM package add rules to the rule sets of Streams clients. The rules can pertain to changes in the redo log, to logical change records (LCRs), or to user messages.

An LCR represents either a row change that results from a data manipulation language (DML) change or a data definition language (DDL) change. An LCR that represents a row change is a row LCR, and an LCR that represents a DDL change is a DDL LCR. LCRs can either represent changes in the redo record that were captured by a capture process, or they can represent changes created by a user or application. A user message is a custom message that is based on a user-defined type and created by users or applications.

For all of the procedures except the ones that create subset rules, you use the inclusion_rule parameter to specify the type of rule set (either positive or negative) for the created rules. If the Streams client does not have a rule set of the specified type, then a rule set is created automatically, and the rules are added to the rule set. Other rules in an existing rule set for the Streams client are not affected. Additional rules can be added to a rule set using either the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package. If a Streams client has both a positive and a negative rule set, then the negative rule set is always evaluated first.

The following sections describe each type of rule in detail:

• Capture Process Rules for Changes in the Redo Log

• Propagation Rules for LCRs

• Propagation Rules for User Messages

• Apply Process Rules for LCRs

• Apply Process Rules for User Messages

• Messaging Client Rules for LCRs

• Messaging Client Rules for User Messages

Capture Process Rules for Changes in the Redo Log

The following procedures add rules to a rule set of a capture process when you specify capture for the streams_type parameter:

• The ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all changes made to a source database. See ADD_GLOBAL_RULES Procedure.

• The ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for changes made to a specified schema. See ADD_SCHEMA_RULES Procedure.

• The ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for DML changes made to a subset of rows in a specified table. See ADD_SUBSET_RULES Procedure.

• The ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for changes made to a specified table. See ADD_TABLE_RULES Procedure.

If one of these procedures adds rules to the positive rule set for a capture process, then the capture process captures row changes resulting from DML changes, or DDL changes, or both from a source database and enqueues these changes into the specified queue. If one of these procedures adds rules to the negative rule set for a capture process, then the capture process discards row changes, or DDL changes, or both from a source database.

A capture process can capture changes locally at a source database or remotely at a downstream database. Therefore, for capture process rules, you should execute the procedure either at the source database or at a downstream database.

If the capture process is a local capture process, or if the capture process is a downstream capture process that uses a database link to the source database, then these procedures automatically prepare the appropriate database objects for instantiation:

• ADD_GLOBAL_RULES invokes the PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.

• ADD_SCHEMA_RULES invokes the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.

• ADD_SUBSET_RULES and ADD_TABLE_RULES invoke the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.

These procedures also enable supplemental logging for the primary key, unique key, foreign key, and bitmap index columns in the tables prepared for instantiation. The primary key columns are unconditionally logged. The unique key, foreign key, and bitmap index columns are conditionally logged.

If the capture process is a downstream capture process that does not use a database link to the source database, then you must prepare the appropriate objects for instantiation and specify the necessary supplemental logging manually at the source database.

If one of these procedures is executed at a downstream database, then you specify the source database using the source_database parameter, and the specified capture process must exist. The procedure cannot create a capture process if it is run at a downstream database. You can create a capture process at a downstream database using the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

Propagation Rules for LCRs

The following procedures add propagation rules for LCRs to a rule set of a propagation:

• The ADD_GLOBAL_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in a source queue. See ADD_GLOBAL_PROPAGATION_RULES Procedure.

• The ADD_SCHEMA_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in a source queue containing changes made to a specified schema. See ADD_SCHEMA_PROPAGATION_RULES Procedure.

• The ADD_SUBSET_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in a source queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_PROPAGATION_RULES Procedure".

• The ADD_TABLE_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in a source queue containing changes made to a specified table. See "ADD_TABLE_PROPAGATION_RULES Procedure".

If one of these procedures adds rules to the positive rule set for the propagation, then the rules specify that the propagation propagates LCRs in a source queue to a destination queue. If one of these procedures adds rules to the negative rule set for the propagation, then the rules specify that the propagation discards LCRs in a source queue. When you create rules with one of these procedures, and you specify a value for the source_databse parameter, then the rules include conditions for the specified source database.

Propagation Rules for User Messages

The ADD_MESSAGE_PROPAGATION_RULE procedure adds a message rule to a rule set of a propagation. If this procedure adds a rule to the positive rule set for the propagation, then the rule specifies that the propagation propagates the user-enqueued messages of a specific message type that evaluate to TRUE for the rule condition in a source queue to a destination queue. If this procedure adds a rule to the negative rule set for the propagation, then the rule specifies that the propagation discards the user-enqueued messages of a specific message type that evaluate to TRUE for the rule condition in a source queue. This procedure generates a rule name for the rule.

Apply Process Rules for LCRs

The following procedures add rules to a rule set of an apply process when you specify apply for the streams_type parameter:

• The ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in the apply process queue. See "ADD_GLOBAL_RULES Procedure".

• The ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the apply process queue containing changes made to a specified schema. See "ADD_SCHEMA_RULES Procedure".

• The ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in the apply process queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_RULES Procedure".

• The ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the apply process queue containing changes made to a specified table. See "ADD_TABLE_RULES Procedure".

If one of these procedures adds rules to the positive rule set for the apply process, then the rules specify that the apply process applies LCRs in its queue. If one of these procedures adds rules to the negative rule set for the apply process, then the rules specify that the apply process discards LCRs in its queue. For apply process rules, you should execute these procedures at the destination database.

An apply process can apply captured LCRs from only one source database. If one of these procedures creates an apply process, then specify the source database for the apply process using the source_database parameter. If the source_database parameter is NULL, and one of these procedures creates an apply process, then the source database name of the first LCR received by the apply process is used for the source database.

The rules in the apply process rule sets determine which messages are dequeued by the apply process. When you create rules with one of these procedures, and you specify a value for the source_databse parameter, then the rules include conditions for the specified source database. If the apply process dequeues an LCR with a source database that is different than the source database for the apply process, then an error is raised. In addition, when adding rules to an existing apply process, the database specified in the source_database parameter cannot be different than the source database for the apply process. You can determine the source database for an apply process by querying the DBA_APPLY_PROGRESS data dictionary view.

Changes applied by an apply process created by one of these procedures generate tags in the redo log at the destination database with a value of '00' (double zero). You can use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to alter the tag value after the apply process is created, if necessary.

An apply process created by one of these procedures can apply messages only at the local database and can apply only captured messages. To create an apply process that applies messages at a remote database or an apply process that applies user-enqueued messages, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

You can also use the DBMS_APPLY_ADM.CREATE_APPLY procedure to specify nondefault values for the apply_captured, apply_user, apply_database_link, and apply_tag parameters when you run that procedure. You can use one of the procedures in the DBMS_STREAMS_ADM package to add rules to a rule set used by the apply process after you create it.

Apply Process Rules for User Messages

The ADD_MESSAGE_RULE procedure adds a message rule to a rule set of an apply process when you specify apply for the streams_type parameter. For an apply process rule, you should execute this procedure at the destination database.

If this procedure adds a rule to the positive rule set for an apply process, then the apply process dequeues user-enqueued messages of a specific message type that satisfy the apply process rule and sends these messages to its message handler. If no message handler is specified for the apply process, then use the ALTER_APPLY procedure in the DBMS_APPLY_ADM package to set the message handler. If this procedure adds a rule to the negative rule set for an apply process, then the apply process discards user-enqueued messages of a specific message type that satisfy the apply process rule.

Messaging Client Rules for LCRs

The following procedures add rules to a rule set of a messaging client when you specify dequeue for the streams_type parameter:

• The ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in the messaging client queue. See "ADD_GLOBAL_RULES Procedure".

• The ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the messaging client queue containing changes made to a specified schema. See "ADD_SCHEMA_RULES Procedure".

• The ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in the messaging client queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_RULES Procedure".

• The ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the messaging client queue containing changes made to a specified table. See "ADD_TABLE_RULES Procedure".

If one of these procedures adds rules to the positive rule set for a messaging client, then the messaging client can dequeue user-enqueued row LCRs, or DDL LCRs, or both that originated at the source database matching the source_database parameter. If one of these procedures adds rules to the negative rule set for a messaging client, then the messaging client discards user-enqueued row LCRs, or DDL LCRs, or both that originated at the source database matching the source_database parameter. You should execute these procedures at the database where you want to dequeue the messages with the messaging client.

Messaging Client Rules for User Messages

The ADD_MESSAGE_RULE procedure adds a message rule to a rule set of a messaging client when you specify dequeue for the streams_type parameter. You should execute this procedure at the database that will dequeue messages.

If this procedure adds a rule to the positive rule set for a messaging client, then the messaging client dequeues user-enqueued messages of a specific message type that satisfy the message rule. If this procedure adds a rule to the negative rule set for a messaging client, then the messaging client discards user-enqueued messages of a specific message type that satisfy the message rule.

Procedures That Configure a Streams Replication Environment

The following procedures in this package configure a replication environment that is maintained by Streams:

• MAINTAIN_GLOBAL Procedure

• MAINTAIN_SCHEMAS Procedure

• MAINTAIN_SIMPLE_TTS Procedure

• MAINTAIN_TABLES Procedure

• MAINTAIN_TTS Procedure

• PRE_INSTANTIATION_SETUP Procedure and POST_INSTANTIATION_SETUP Procedure

The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures must be used together to complete the Streams replication configuration.

The following sections contain information about using these procedures:

• Local Capture or Downstream Capture for the Source Database

• Single Source and Bi-Directional Configurations

• Streams Clients and Queues Configured By These Procedures

• Change Cycling

• Automatic Platform Conversion

• Actions Performed by These Procedures

• Configuration Progress and Recoverability

• Requirements for Running These Procedures

• Common Parameters for the Configuration Procedures

Local Capture or Downstream Capture for the Source Database

Local capture means that a capture process runs on the source database. Downstream capture means that a capture process runs on a database other than the source database. These procedures can either configure local capture or downstream capture for the database specified in the source_database parameter.

The database that captures changes made to the source database is called the capture database. These procedures can configure one of the following databases as the capture database:

• Source database (local capture)

• Destination database (downstream capture)

• A third database (downstream capture)

The database on which the procedure is run is configured as the capture database for the source database. Therefore, to configure local capture at the source database, run the procedure at the source database. To configure downstream capture at the destination database or a third database, run the procedure at the destination database or third database.

Single Source and Bi-Directional Configurations

These procedures either set up a single source Streams configuration with the database specified in the source_database parameter acting as the only source database, or these procedures set up a bi-directional Streams configuration with both databases acting as source and destination databases. The bi_directional parameter controls whether the Streams configuration is single source or bi-directional:

• If bi_directional is FALSE, then a capture process captures changes made to the source database and an apply process at the destination database applies these changes. If the destination database is not the capture database, then a propagation propagates the captured changes to the destination database. The default value for this parameter is FALSE.

• If bi_directional is TRUE, then a separate capture process captures changes made to each database, propagations propagate these changes to the other database, and each database applies changes from the other database.

If bi_directional is set to FALSE, then these procedures do not configure bi-directional replication. Therefore, changes made to the shared database objects at the destination database are not replicated to the source database, and the shared database objects are not kept synchronized at the two databases, unless no changes are made to the shared database objects at the destination database. However, if bi_directional is set to TRUE, then Streams is configured to keep the shared database objects synchronized at the two databases, even if both databases allow changes to the database objects.

Streams Clients and Queues Configured By These Procedures

These procedures configure the following Streams clients:

• These procedures configure a capture process that captures changes to the source database. If bi-directional replication is configured, then these procedures also configure a capture process that captures changes to the destination database.

• If the capture database and the destination database are different databases, then these procedures configure a propagation that propagates changes from the capture database to the destination database.

• If the capture database and the destination database are the same database, then the queue names determine whether a propagation is created:

  • If the capture_queue_name and apply_queue_name parameters specify different queue names, then a propagation is created between the two queues within the destination database.

  • If the capture_queue_name and apply_queue_name parameters specify the same queue name, then a propagation is not created, and the downstream capture process and the apply process use the same queue. This configuration is possible only if the bi_directional parameter is set to FALSE to configure a single source replication environment.

• If bi-directional replication is configured, then these procedures configure a propagation that propagates changes from the destination database to the source database.

• These procedures configure an apply process that applies changes at the destination database. These changes originated at the source database. If bi-directional replication is configured, then these procedures also configure an apply process that applies changes to the source database. These changes originated at the destination database.

By default, the capture_queue_name and apply_queue_name parameters are set to NULL. When these parameters are set to NULL, these procedures configure a separate queue for each capture process and apply process. The Streams replication environment might operate more efficiently if each Streams client has its own separate queue.

However, two Streams clients share a queue in the following configurations:

• The configuration described previously in this section in which the downstream capture process and the apply process at the destination database share a queue.

• A configuration in which all of the following conditions are met:

  • The capture database is the source database or a third database.

  • The bi_directional parameter is set to TRUE.

  • The same queue name is specified for the capture_queue_name and apply_queue_name parameters.

  In this case, the local capture process and the apply process at the destination database share the same queue. If the source database is the capture database, then the local capture process and the apply process at the source database also share the same queue.

The capture_name and capture_queue_name parameters must be set to NULL when both of the following conditions are met:

• The destination database is the capture database.

• The bi_directional parameter is set to TRUE.

When both of these conditions are met, these procedure configure two capture processes at the destination database, and these capture processes must have different names. When the capture_name and capture_queue_name parameters are set to NULL, the system generates a different name for the capture processes and queues. These procedures raise an error if both conditions are met and either the capture_name parameter or the capture_queue_name parameter is set to a non-NULL value.

Change Cycling

Change cycling happens when a change is sent back to the database where it originated. Typically, change cycling should be avoided because it can result in each change going through endless loops back to the database where it originated. Such loops can result in unintended data in the database and tax the networking and computer resources of an environment.

If the bi_directional parameter is set to TRUE, then these procedures configure bi-directional replication. To prevent change cycling in a bi-directional Streams replication environment, these procedures configure the environment in the following way:

• The apply process at each database applies each change with an apply tag that is unique to the environment. An apply tag is a Streams tag that is part of each redo record created by the apply process. For example, if a procedure configures databases sfdb.net and nydb.net for bi-directional replication, then the apply tag for the apply process at sfdb.net can be the hexidecimal equivalent of '1', and the apply tag for the apply process at nydb.net can be the hexidecimal equivalent of '2'.

• The capture process at each database captures all changes to the shared database objects, regardless of tags in the redo records for the changes to these database objects.

• Each propagation propagates all changes made to the shared database objects to the other database in the bi-directional replication environment, except for changes that originated at the other database. Continuing the example, the propagation at sfdb.net propagates all changes to nydb.net, except for changes with a tag value that is the hexidecimal equivalent of '1', because these changes originated at nydb.net. Similarly, the propagation at nydb.net propagates all changes to sfdb.net, except for changes with a tag value that is the hexidecimal equivalent of '2'. A change that is not propagated because of its tag value is discarded.

These procedures cannot be used to configure multi-directional replication where changes can be cycled back to a source database by a third database in the environment. For example, these procedures cannot be used to configure a Streams replication environment with three databases where each database shares changes with the other two databases in the environment. If these procedures were used to configure a three way replication environment such as this, then changes made at a source database would be cycled back to the same source database. In a valid three way replication environment, a particular change is made only once at each database.

These procedures can be used to configure a Streams replication environment that includes more than two databases, as long as changes made at a source database cannot cycle back to the same source database. For example, a procedure can be run multiple times to configure an environment in which a primary database shares changes with multiple secondary databases. Such an environment is sometimes called a "hub and spoke" environment.

You can configure the Streams environment manually to replicate changes in a multiple source environment where each source database shares changes with the other source databases, or you can modify generated scripts to achieve this.

Automatic Platform Conversion

If the source and destination databases are running on different platforms, then these procedures, or the scripts generated by these procedures, convert transferred datafiles to the appropriate platform automatically.

Actions Performed by These Procedures

To view all of the actions performed by one of these procedures in detail, use the procedure to generate a script, and view the script in a text editor.

Configuration Progress and Recoverability

When one of these procedures is run with the perform_actions parameter set to TRUE, metadata about its configuration actions is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then you can use the RECOVER_OPERATION procedure to complete the configuration after you correct the conditions that caused the error.

Requirements for Running These Procedures

Meet the following requirements when you use one of these procedures:

• Run the procedure at the capture database.

• If the bi_directional parameter is set to TRUE, or if the source database is not the capture database, then the source_database parameter must specify a database that contains the database objects to be shared. The database specified in the destination_database parameter might or might not contain these database objects. If the destination database does not contain the shared database objects, then these procedures will instantiate the database objects at the destination database (excluding the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures).

• Both databases must be open during configuration. If the procedure is generating a script only, then the database specified in the destination_database parameter does not need to be open when you run the procedure, but both databases must be open when you run the generated script.

• The user who runs one of these procedures should be granted DBA role. This user must have the necessary privileges to complete the following actions:

  • Create ANYDATA queues, capture processes, propagations, and apply processes.

  • Specify supplemental logging

  • Run subprograms in the DBMS_STREAMS_ADM and DBMS_AQADM packages.

  • Access the database specified in the destination_database parameter through a database link. This database link should have the same name as the global name of the destination database.

• If the bi_directional parameter is set to TRUE or if a network instantiation will be performed, then the corresponding user at the destination database must be able to use a database link to access the source database. This database link should have the same name as the global name of the source database.

• If these procedures configure downstream capture, then the corresponding user at the capture database must be able to use a database link to access the source database. This database link should have the same name as the global name of the source database.

• If these procedures configure downstream capture, then the corresponding user at the capture database must be able to use a database link to access the destination database. This database link should have the same name as the global name of the destination database.

• Each specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes one of these procedures must have READ and WRITE privilege on each one.

• These procedures, or the scripts generated by these procedures, must be run at an Oracle Database 10g Release 2 database.

• If the perform_actions parameter is set to TRUE in one of these procedures to configure the Streams replication environment directly, then all of the databases configured by the procedure must be Oracle Database 10g Release 2 databases.

• If the perform_actions parameter is set to FALSE in one of these procedures, and the replication environment is configured with a generated script, then the databases configured by the script must be Oracle Database 10g Release 1 or later databases. If the script configures an Oracle Database 10g Release 1 database, then the script must be modified so that it does not configure features that are available only in Oracle Database 10g Release 2, such as queue-to-queue propagation.

To ensure that the user who runs these procedures has the necessary privileges, you should configure a Streams administrator at each database, and each database link should be should be created in the Streams administrator's schema.

Common Parameters for the Configuration Procedures

Table 106-1 describes the common parameters for the procedures in this package that configure a Streams replication environment.

Summary of DBMS_STREAMS_ADM Subprograms

ADD_COLUMN Procedure

This procedure either adds or removes a declarative rule-based transformation which adds a column to a row logical change record (row LCR) that satisfies the specified rule.

For the transformation to be performed when the specified rule evaluates to TRUE, the rule must be in the positive rule set of a Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients.

This procedure is overloaded. The column_value and column_function parameters are mutually exclusive.

Syntax

DBMS_STREAMS_ADM.ADD_COLUMN(
   rule_name     IN  VARCHAR2,
   table_name    IN  VARCHAR2,
   column_name   IN  VARCHAR2,
   column_value  IN  ANYDATA,
   value_type    IN  VARCHAR2     DEFAULT 'NEW',
   step_number   IN  NUMBER       DEFAULT 0,
   operation     IN  VARCHAR2     DEFAULT 'ADD');

DBMS_STREAMS_ADM.ADD_COLUMN(
   rule_name        IN  VARCHAR2,
   table_name       IN  VARCHAR2,
   column_name      IN  VARCHAR2,
   column_function  IN  VARCHAR2,
   value_type       IN  VARCHAR2     DEFAULT 'NEW',
   step_number      IN  NUMBER       DEFAULT 0,
   operation        IN  VARCHAR2     DEFAULT 'ADD');

Parameters

Usage Notes

When 'REMOVE' is specified for the operation parameter, all of the add column declarative rule-based transformations for the specified rule are removed that match the specified table_name, column_name, and step_number parameters. Nulls specified for these parameters act as wildcards. The following table lists the behavior of the ADD_COLUMN procedures when one or more of these parameters is NULL:

ADD_GLOBAL_PROPAGATION_RULES Procedure

This procedure either adds global rules to the positive rule set for a propagation, or adds global rules to the negative rule set for a propagation, and creates the specified propagation if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   dml_rule_name           OUT  VARCHAR2,
   ddl_rule_name           OUT  VARCHAR2,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES(
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure configures propagation using the current user. Only one propagation is allowed between a particular source queue and destination queue.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the database name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the database name plus the sequence number is too long, then the database name is truncated. A propagation uses the rules for filtering.

Examples

The following is an example of a global rule condition created for DML changes:

(:dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )

ADD_GLOBAL_RULES Procedure

This procedure adds rules to a rule set of one of the following types of Streams clients:

• Capture process rules for capturing changes to an entire database when the streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.

• Apply process rules for applying all logical change records (LCRs) in a queue when the streams_type parameter is set to apply. The rules can specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.

• Messaging client rules for dequeuing all user-enqueued LCRs from a queue when the streams_type parameter is set to dequeue. The rules can specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.

This procedure creates the specified capture process, apply process, or messaging client if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2  DEFAULT NULL,
   queue_name          IN   VARCHAR2  DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN   DEFAULT TRUE,
   include_ddl         IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN   DEFAULT FALSE,
   source_database     IN   VARCHAR2  DEFAULT NULL,
   dml_rule_name       OUT  VARCHAR2,
   ddl_rule_name       OUT  VARCHAR2,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2  DEFAULT NULL,
   queue_name          IN   VARCHAR2  DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN   DEFAULT TRUE,
   include_ddl         IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN   DEFAULT FALSE,
   source_database     IN   VARCHAR2  DEFAULT NULL,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the database name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the database name plus the sequence number is too long, then the database name is truncated. A capture process, apply process, or messaging client uses the rules created for filtering.

Examples

The following is an example of a global rule condition created for DML changes:

(:dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )

ADD_MESSAGE_PROPAGATION_RULE Procedure

This procedure adds a message rule to the positive rule set for a propagation, or adds a message rule to the negative rule set for a propagation, and creates the specified propagation if it does not exist.

This procedure is overloaded. One version of this procedure contains the OUT parameter rule_name, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_MESSAGE_PROPAGATION_RULE(
   message_type            IN   VARCHAR2,    
   rule_condition          IN   VARCHAR2,    
   streams_name            IN   VARCHAR2  DEFAULT NULL,    
   source_queue_name       IN   VARCHAR2,    
   destination_queue_name  IN   VARCHAR2,    
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   rule_name               OUT  VARCHAR2,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_MESSAGE_PROPAGATION_RULE(
   message_type            IN   VARCHAR2,    
   rule_condition          IN   VARCHAR2,    
   streams_name            IN   VARCHAR2  DEFAULT NULL,    
   source_queue_name       IN   VARCHAR2,    
   destination_queue_name  IN   VARCHAR2,    
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

Parameters

:msg

Usage Notes

This procedure configures propagation using the current user. Only one propagation is allowed between a particular source queue and destination queue.

When you use this procedure to create a rule set for a message rule, the new rule set does not have an evaluation context. If no evaluation context exists for the specified message type, then this procedure creates a new evaluation context and associates it with the new rule. The evaluation context also has a system-generated name. If you create new rules that use an existing message type, then the new rules use the existing evaluation context for the message type.

Examples

Suppose the message type is VARCHAR2(128). Given this type, the following rule condition can be specified:

':msg = ''HQ'''

This rule condition evaluates to TRUE if a user-enqueued message of type VARCHAR2(128) has HQ for its value.

Suppose the message type is usr_msg, and that this type has the following attributes: source_dbname, owner, name, and message. Given this type, the following rule condition can be specified:

':msg.source_dbname = ''DBS1.NET'' AND ' || ':msg.owner = ''HR'' AND  ' ||
':msg.name = ''EMPLOYEES'''

This rule condition evaluates to TRUE if a user-enqueued message of type usr_msg has DBS1.NET for its source_dbname attribute, HR for its owner attribute, and EMPLOYEES for its name attribute.

ADD_MESSAGE_RULE Procedure

This procedure adds a message rule to a rule set of one of the following types of Streams clients:

• Apply process rule for dequeuing user-enqueued messages of a specific message type from a queue when the streams_type parameter is set to apply. See "Apply Process Rules for User Messages" for more information about such rules.

• Messaging client rule dequeuing user-enqueued messages of a specific message type from a queue when the streams_type parameter is set to dequeue. See "Messaging Client Rules for User Messages" for more information about such rules.

This procedure also creates the specified Streams client if it does not exist.

This procedure is overloaded. One version of this procedure contains the OUT parameter rule_name, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_MESSAGE_RULE(
   message_type    IN   VARCHAR2,    
   rule_condition  IN   VARCHAR2,    
   streams_type    IN   VARCHAR2,
   streams_name    IN   VARCHAR2  DEFAULT NULL,    
   queue_name      IN   VARCHAR2  DEFAULT 'streams_queue',    
   inclusion_rule  IN   BOOLEAN   DEFAULT TRUE,
   rule_name       OUT  VARCHAR2);

DBMS_STREAMS_ADM.ADD_MESSAGE_RULE(
   message_type    IN   VARCHAR2,    
   rule_condition  IN   VARCHAR2,    
   streams_type    IN   VARCHAR2,
   streams_name    IN   VARCHAR2  DEFAULT NULL,    
   queue_name      IN   VARCHAR2  DEFAULT 'streams_queue',    
   inclusion_rule  IN   BOOLEAN   DEFAULT TRUE);

Parameters

:msg

Usage Notes

If an apply process rule is added, then this procedure creates the apply process if it does not exist. An apply process created by this procedure can apply only user-enqueued messages, and dequeued messages are sent to the message handler for the apply process. If a messaging client rule is added, then this procedure creates a messaging client if it does not exist.

When you use this procedure to create a rule set for a message rule, the new rule set does not have an evaluation context. If no evaluation context exists for the specified message type, then this procedure creates a new evaluation context and associates it with the new rule. The evaluation context also has a system-generated name. If you create new rules that use an existing message type, then the new rules use the existing evaluation context for the message type.

Examples

Suppose the message type is VARCHAR2(128). Given this type, the following rule condition can be specified:

':msg = ''HQ'''

This rule condition evaluates to TRUE if a user-enqueued message of type VARCHAR2(128) has HQ for its value.

Suppose the message type is usr_msg, and that this type has the following attributes: source_dbname, owner, name, and message. Given this type, the following rule condition can be specified:

':msg.source_dbname = ''DBS1.NET'' AND ' || ':msg.owner = ''HR'' AND  ' ||
':msg.name = ''EMPLOYEES'''

This rule condition evaluates to TRUE if a user-enqueued message of type usr_msg has DBS1.NET for its source_dbname attribute, HR for its owner attribute, and EMPLOYEES for its name attribute.

ADD_SCHEMA_PROPAGATION_RULES Procedure

This procedure either adds schema rules to the positive rule set for a propagation, or adds schema rules to the negative rule set for a propagation, and creates the specified propagation if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name             IN   VARCHAR2,
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   dml_rule_name           OUT  VARCHAR2,
   ddl_rule_name           OUT  VARCHAR2,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name             IN   VARCHAR2,
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure configures propagation using the current user. Only one propagation is allowed between a particular source queue and destination queue.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the schema name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the schema name plus the sequence number is too long, then the schema name is truncated. A propagation uses the rules created for filtering.

Examples

The following is an example of a schema rule condition created for DML changes:

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.NET' )

ADD_SCHEMA_RULES Procedure

This procedures adds rules to a rule set of one of the following types of Streams clients:

• Capture process rules for capturing changes to a specified schema when the streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.

• Apply process rules for applying logical change records (LCRs) in a queue that contain changes to a specified schema when the streams_type parameter is set to apply. The rules can specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.

• Messaging client rules for dequeuing user-enqueued LCRs from a queue that contain changes to a specified schema when the streams_type parameter is set to dequeue. The rules can specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.

This procedure creates the specified capture process, apply process, or messaging client if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name         IN   VARCHAR2,
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2  DEFAULT NULL,
   queue_name          IN   VARCHAR2  DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN   DEFAULT TRUE,
   include_ddl         IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN   DEFAULT FALSE,
   source_database     IN   VARCHAR2  DEFAULT NULL,
   dml_rule_name       OUT  VARCHAR2,
   ddl_rule_name       OUT  VARCHAR2,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name         IN   VARCHAR2,
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2  DEFAULT NULL,
   queue_name          IN   VARCHAR2  DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN   DEFAULT TRUE,
   include_ddl         IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN   DEFAULT FALSE,
   source_database     IN   VARCHAR2  DEFAULT NULL,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the schema name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the schema name plus the sequence number is too long, then the schema name is truncated. A capture process, apply process, or messaging client uses the rules created for filtering.

Examples

The following is an example of a schema rule condition created for DML changes:

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.NET' )

ADD_SUBSET_PROPAGATION_RULES Procedure

This procedures adds propagation rules that propagate the logical change records (LCRs) related to a subset of the rows in the specified table in a source queue to a destination queue, and creates the specified propagation if it does not exist.

This procedure is overloaded. One version of this procedure contains three OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES(
   table_name               IN   VARCHAR2,
   dml_condition            IN   VARCHAR2,
   streams_name             IN   VARCHAR2  DEFAULT NULL,
   source_queue_name        IN   VARCHAR2,
   destination_queue_name   IN   VARCHAR2,
   include_tagged_lcr       IN   BOOLEAN   DEFAULT FALSE,
   source_database          IN   VARCHAR2  DEFAULT NULL,
   insert_rule_name         OUT  VARCHAR2,
   update_rule_name         OUT  VARCHAR2,
   delete_rule_name         OUT  VARCHAR2,
   queue_to_queue           IN   BOOLEAN   DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES(
   table_name               IN   VARCHAR2,
   dml_condition            IN   VARCHAR2,
   streams_name             IN   VARCHAR2  DEFAULT NULL,
   source_queue_name        IN   VARCHAR2,
   destination_queue_name   IN   VARCHAR2,
   include_tagged_lcr       IN   BOOLEAN   DEFAULT FALSE,
   source_database          IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue           IN   BOOLEAN   DEFAULT NULL);

Parameters

Usage Notes

This procedure configures propagation using the current user. Only one propagation is allowed between a particular source queue and destination queue.

Running this procedure generates three rules for the specified propagation: one for INSERT statements, one for UPDATE statements, and one for DELETE statements. For INSERT and DELETE statements, only row LCRs that satisfy the condition specified for the dml_condition parameter are propagated. For UPDATE statements, the following variations are possible:

• If both the new and old values in a row LCR satisfy the specified dml_condition, then the row LCR is propagated without any changes.

• If neither the new or old values in a row LCR satisfy the specified dml_condition, then the row LCR is not propagated.

• If the old values for a row LCR satisfy the specified dml_condition, but the new values do not, then the update row LCR is converted into a delete row LCR.

• If the new values for a row LCR satisfy the specified dml_condition, but the old values do not, then the update row LCR is converted to an insert row LCR.

When an update is converted into an insert or a delete, it is called row migration.

A propagation uses the rules created for filtering. If the propagation does not have a positive rule set, then the procedure creates a positive rule set automatically, and the rules for propagating changes to the table are added to the positive rule set. A subset rule can be added to positive rule set only, not to a negative rule set. Other rules in an existing positive rule set for the propagation are not affected. Additional rules can be added using either the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

Rules for INSERT, UPDATE, and DELETE statements are created automatically when you run this procedure, and these rules are given a system-generated rule name. Each rule has a system-generated rule name that consists of the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. The ADD_SUBSET_RULES procedure is overloaded, and the system-generated rule names for INSERT, UPDATE, and DELETE statements are returned.

When you create propagation subset rules for a table, you should create an unconditional supplemental log group at the source database with all the columns in the table. Supplemental logging is required if an update must be converted to an insert. The propagation rule must have all the column values to be able to perform this conversion correctly.

Examples

The following is an example of a rule condition created for filtering a row LCR containing an update operation when the dml_condition is region_id = 2, the table_name is hr.regions, and the source_database is dbs1.net:

:dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS' 
AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET' 
AND :dml.get_command_type()='UPDATE' 
AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) 
AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) 
AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2) 
AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)

ADD_SUBSET_RULES Procedure

This procedure adds rules to a rule set of one of the following types of Streams clients:

• Capture process rules for capturing changes to a subset of rows in a specified table when the streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.

• Apply process rules for applying logical change records (LCRs) in a queue that contain changes to a subset of rows in a specified table when the streams_type parameter is set to apply. The rules can specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.

• Messaging client rules for dequeuing user-enqueued LCRs from a queue that contain changes to a subset of rows in a specified table when the streams_type parameter is set to dequeue. The rules can specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.

This procedure creates the specified capture process, apply process, or messaging client if it does not exist.

This procedure is overloaded. One version of this procedure contains three OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
   table_name          IN   VARCHAR2,
   dml_condition       IN   VARCHAR2,
   streams_type        IN   VARCHAR2 DEFAULT 'apply',
   streams_name        IN   VARCHAR2 DEFAULT NULL,
   queue_name          IN   VARCHAR2 DEFAULT 'streams_queue',
   include_tagged_lcr  IN   BOOLEAN  DEFAULT FALSE,
   source_database     IN   VARCHAR2 DEFAULT NULL,
   insert_rule_name    OUT  VARCHAR2,
   update_rule_name    OUT  VARCHAR2,
   delete_rule_name    OUT  VARCHAR2);

DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
   table_name          IN   VARCHAR2,
   dml_condition       IN   VARCHAR2,
   streams_type        IN   VARCHAR2 DEFAULT 'apply',
   streams_name        IN   VARCHAR2 DEFAULT NULL,
   queue_name          IN   VARCHAR2 DEFAULT 'streams_queue',
   include_tagged_lcr  IN   BOOLEAN  DEFAULT FALSE,
   source_database     IN   VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Running this procedure generates three rules for the specified capture process, apply process, or messaging client: one for INSERT statements, one for UPDATE statements, and one for DELETE statements. For INSERT and DELETE statements, only DML changes that satisfy the condition specified for the dml_condition parameter are captured, applied, or dequeued. For UPDATE statements, the following variations are possible:

• If both the new and old values in a DML change satisfy the specified dml_condition, then the DML change is captured, applied, or dequeued without any changes.

• If neither the new or old values in a DML change satisfy the specified dml_condition, then the DML change is not captured, applied, or dequeued.

• If the old values for a DML change satisfy the specified dml_condition, but the new values do not, then the DML change is converted into a delete.

• If the new values for a DML change satisfy the specified dml_condition, but the old values do not, then the DML change is converted to an insert.

When an update is converted into an insert or a delete, it is called row migration.

A capture process, apply process, or messaging client uses the rules created for filtering. If the Streams client does not have a positive rule set, then this procedure creates a positive rule set automatically, and adds the rules for the table to the positive rule set. A subset rule can be added to positive rule set only, not to a negative rule set. Other rules in an existing rule set for the process are not affected. Additional rules can be added using either the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

Rules for INSERT, UPDATE, and DELETE statements are created automatically when you run this procedure, and these rules are given a system-generated rule name. Each rule has a system-generated rule name that consists of the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. The ADD_SUBSET_RULES procedure is overloaded, and the system-generated rule names for INSERT, UPDATE, and DELETE statements are returned.

Examples

The following is an example of a rule condition created for filtering DML changes containing an update operation when the dml_condition is region_id = 2, the table_name is hr.regions, and the source_database is dbs1.net:

:dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS' 
AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET' 
AND :dml.get_command_type()='UPDATE' 
AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) 
AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) 
AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2) 
AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)

ADD_TABLE_PROPAGATION_RULES Procedure

This procedures adds table rules to the positive rule set for a propagation, or adds table rules to the negative rule set for a propagation, and creates the specified propagation if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name              IN   VARCHAR2,
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   dml_rule_name           OUT  VARCHAR2,
   ddl_rule_name           OUT  VARCHAR2,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name              IN   VARCHAR2,
   streams_name            IN   VARCHAR2  DEFAULT NULL,
   source_queue_name       IN   VARCHAR2,
   destination_queue_name  IN   VARCHAR2,
   include_dml             IN   BOOLEAN   DEFAULT TRUE,
   include_ddl             IN   BOOLEAN   DEFAULT FALSE,
   include_tagged_lcr      IN   BOOLEAN   DEFAULT FALSE,
   source_database         IN   VARCHAR2  DEFAULT NULL,
   inclusion_rule          IN   BOOLEAN   DEFAULT TRUE,
   and_condition           IN   VARCHAR2  DEFAULT NULL,
   queue_to_queue          IN   BOOLEAN   DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure configures propagation using the current user. Only one propagation is allowed between a particular source queue and destination queue.

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. A propagation uses the rules created for filtering.

Examples

The following is an example of a table rule condition created for filtering DML statements:

(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'LOCATIONS')) 
and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )

ADD_TABLE_RULES Procedure

This procedure adds rules to a rule set of one of the following types of Streams clients:

• Capture process rules for capturing changes to a specified table when the streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.

• Apply process rules for applying logical change records (LCRs) in a queue that contain changes to a specified table when the streams_type parameter is set to apply. The rules can specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.

• Messaging client rules for dequeuing user-enqueued LCRs from a queue that contain changes to a specified table when the streams_type parameter is set to dequeue. The rules can specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.

This procedure creates the specified capture process, apply process, or messaging client if it does not exist.

This procedure is overloaded. One version of this procedure contains two OUT parameters, and the other does not.

Syntax

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name          IN   VARCHAR2,
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2 DEFAULT NULL,
   queue_name          IN   VARCHAR2 DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN  DEFAULT TRUE,
   include_ddl         IN   BOOLEAN  DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN  DEFAULT FALSE,
   source_database     IN   VARCHAR2 DEFAULT NULL,
   dml_rule_name       OUT  VARCHAR2,
   ddl_rule_name       OUT  VARCHAR2,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name          IN   VARCHAR2,
   streams_type        IN   VARCHAR2,
   streams_name        IN   VARCHAR2 DEFAULT NULL,
   queue_name          IN   VARCHAR2 DEFAULT 'streams_queue',
   include_dml         IN   BOOLEAN  DEFAULT TRUE,
   include_ddl         IN   BOOLEAN  DEFAULT FALSE,
   include_tagged_lcr  IN   BOOLEAN  DEFAULT FALSE,
   source_database     IN   VARCHAR2 DEFAULT NULL,
   inclusion_rule      IN   BOOLEAN   DEFAULT TRUE,
   and_condition       IN   VARCHAR2  DEFAULT NULL);

Parameters

(system_condition) AND (and_condition)

:lcr.get_tag() = HEXTORAW(''02'')

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. Each rule has a system-generated rule name that consists of the table name with a sequence number appended to it. The sequence number is used to avoid naming conflicts. If the table name plus the sequence number is too long, then the table name is truncated. A capture process, apply process, or messaging client uses the rules created for filtering.

Examples

The following is an example of a table rule condition created for DML changes:

(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'LOCATIONS')) 
and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )

CLEANUP_INSTANTIATION_SETUP Procedure

This procedure removes a Streams replication configuration that was set up by the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in this package. This procedure either remove the configuration directly, or it can generate a script that removes the configuration.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

Syntax

DBMS_STREAMS_ADM.CLEANUP_INSTANTIATION_SETUP(
   maintain_mode           IN VARCHAR2,
   tablespace_names        IN DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET,
   source_database         IN VARCHAR2,
   destination_database    IN VARCHAR2,
   perform_actions         IN BOOLEAN   DEFAULT TRUE,
   script_name             IN VARCHAR2  DEFAULT NULL,
   script_directory_object IN VARCHAR2  DEFAULT NULL,
   capture_name            IN VARCHAR2  DEFAULT NULL,
   capture_queue_table     IN VARCHAR2  DEFAULT NULL,
   capture_queue_name      IN VARCHAR2  DEFAULT NULL,
   capture_queue_user      IN VARCHAR2  DEFAULT NULL,
   propagation_name        IN VARCHAR2  DEFAULT NULL,
   apply_name              IN VARCHAR2  DEFAULT NULL,
   apply_queue_table       IN VARCHAR2  DEFAULT NULL,
   apply_queue_name        IN VARCHAR2  DEFAULT NULL,
   apply_queue_user        IN VARCHAR2  DEFAULT NULL,
   bi_directional          IN BOOLEAN   DEFAULT FALSE,
   change_global_name      IN BOOLEAN   DEFAULT FALSE);

Parameters

DELETE_COLUMN Procedure

This procedure either adds or removes a declarative rule-based transformation which deletes a column from a row logical change record (LCR) that satisfies the specified rule.

For the transformation to be performed when the specified rule evaluates to TRUE, the rule must be in the positive rule set of a Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients.

Syntax

DBMS_STREAMS_ADM.DELETE_COLUMN(
   rule_name     IN  VARCHAR2,
   table_name    IN  VARCHAR2,
   column_name   IN  VARCHAR2,
   value_type    IN  VARCHAR2   DEFAULT '*',
   step_number   IN  NUMBER     DEFAULT 0,
   operation     IN  VARCHAR2   DEFAULT 'ADD');

Parameters

Usage Notes

When 'REMOVE' is specified for the operation parameter, all of the delete column declarative rule-based transformations for the specified rule are removed that match the specified table_name, column_name, and step_number parameters. Nulls specified for these parameters act as wildcards. The following table lists the behavior of the DELETE_COLUMN procedure when one or more of these parameters is NULL:

GET_SCN_MAPPING Procedure

This procedure gets information about the system change number (SCN) values to use for Streams capture and apply processes in a Streams replication environment. This information can be used for the following purposes:

• To recover transactions after point-in-time recovery is performed on a source database in a multiple source Streams environment

• To run flashback queries for the corresponding SCN at a source database and destination database in a Streams single source replication environment

  
  

  See Also: Oracle Streams Replication Administrator's Guide for information about point-in-time recovery and flashback queries in a Streams replication environment

  
  

Syntax

DBMS_STREAMS_ADM.GET_SCN_MAPPING(
   apply_name             IN  VARCHAR2,
   src_pit_scn            IN  NUMBER,
   dest_instantiation_scn OUT NUMBER,
   dest_start_scn         OUT NUMBER,
   dest_skip_txn_ids      OUT DBMS_UTILITY.NAME_ARRAY);

Parameters

MAINTAIN_GLOBAL Procedure

This procedure configures a Streams environment that replicates changes at the database level between two databases. This procedure can either configure the environment directly, or it can generate a script that configures the environment.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

Syntax

DBMS_STREAMS_ADM.MAINTAIN_GLOBAL(
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                             DBMS_STREAMS_ADM.INSTANTIATION_FULL);

Parameters

MAINTAIN_SCHEMAS Procedure

This procedure configures a Streams environment that replicates changes to specified schemas between two databases. This procedure can either configure the environment directly, or it can generate a script that configures the environment.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

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

Syntax

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
   schema_names                 IN VARCHAR2,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                           DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);

DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
   schema_names                 IN DBMS_UTILITY.UNCL_ARRAY,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                           DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);

Parameters

MAINTAIN_SIMPLE_TABLESPACE Procedure

This procedure clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases. This procedure can either perform these actions directly, or it can generate a script that performs these actions. Run this procedure at the source database.

Syntax

DBMS_STREAMS_ADM.MAINTAIN_SIMPLE_TABLESPACE(
   tablespace_name              IN VARCHAR2,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   destination_database         IN VARCHAR2,
   setup_streams                IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE);

Parameters

Usage Notes

The specified tablespace must be a simple tablespace. A simple tablespace is a single, self-contained tablespace that uses only one datafile. A self-contained tablespace has no references from the tablespace pointing outside of the tablespace. For example, if an index in the tablespace is for a table in a different tablespace, then the tablespace is not self-contained. This procedure cannot be used for a non simple tablespace or a set of tablespaces.

DDL Changes Not Maintained

This procedure does not configure the Streams environment to maintain DDL changes to the tablespace nor to the database objects in the tablespace. For example, the Streams environment is not configured to replicate ALTER TABLESPACE statements on the tablespace, nor is it configured to replicate ALTER TABLE statements on tables in the tablespace. You can configure the Streams environment to maintain DDL changes manually or modify generated scripts to achieve this.

Additional Documentation for this Procedure

The documentation in the following sections applies to the MAINTAIN_SIMPLE_TABLESPACE procedure:

• Single Source and Bi-Directional Configurations

• Change Cycling

• Automatic Platform Conversion

Requirements for Running this Procedure

Meet the following requirements when run the MAINTAIN_SIMPLE_TABLESPACE procedure:

• Run the procedure at the source database.

• Both databases must be open during configuration. If the procedure is generating a script only, then the database specified in the destination_database parameter does not need to be open when you run the procedure, but both databases must be open when you run the generated script.

• The user who runs this procedure should be granted DBA role. This user must have the necessary privileges to complete the following actions:

  • Create ANYDATA queues, capture processes, propagations, and apply processes.

  • Specify supplemental logging

  • Run subprograms in the DBMS_STREAMS_ADM and DBMS_AQADM packages.

  • Access the database specified in the destination_database parameter through a database link. This database link should have the same name as the global name of the destination database.

  • Run subprograms in the DBMS_STREAMS_TABLESPACES_ADM package

  • The necessary privileges to run the CLONE_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_SIMPLE_TABLESPACE Procedure for the list of required privileges.

  • The necessary privileges to run the ATTACH_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_SIMPLE_TABLESPACE Procedure for the list of required privileges.

  To ensure that the user who runs this procedure has the necessary privileges, you should configure a Streams administrator at each database, and each database link should be should be created in the Streams administrator's schema.

• If the bi_directional parameter is set to TRUE, then the corresponding user at the destination database must be able to use a database link to access the source database. This database link should have the same name as the global name of the source database.

• Each specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes this procedure must have READ and WRITE privilege on each one.

• The databases configured by this procedure must be Oracle Database 10g Release 2 databases when this procedure is run under the following conditions:

  • The procedure is run at an Oracle Database 10g Release 2 database.

  • The setup_streams parameter is set to TRUE to configure the Streams replication environment directly.

• The databases configured by this procedure must be Oracle Database 10g Release 1 or later databases when this procedure is run under the following conditions:

  • The procedure is run at an Oracle Database 10g Release 2 database.

  • The setup_streams parameter is set to FALSE in this procedure, and the replication environment is configured with a generated script.

  If the script configures an Oracle Database 10g Release 1 database, then the script must be modified so that it does not configure features that are available only in Oracle Database 10g Release 2, such as queue-to-queue propagation.

• If the procedure is run at an Oracle Database 10g Release 1 database, then the databases configured by the procedure must be Oracle Database 10g Release 1 or later databases.

Default Values for Parameters Excluded From the MAINTAIN_SIMPLE_TABLESPACE Procedure

This procedure uses the default values for the parameters in the MAINTAIN_TABLESPACES procedure that do not exist in the MAINTAIN_SIMPLE_TABLESPACE procedure. For example, this procedure creates a capture process at the source database named capture, because that is the default value for the capture_name parameter in the MAINTAIN_TABLESPACES procedure.

Configuration Progress and Recoverability

When this procedure is run with the setup_streams parameter set to TRUE, metadata about its configuration actions is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then you can use the RECOVER_OPERATION procedure to complete the configuration after you correct the conditions that caused the error.

MAINTAIN_SIMPLE_TTS Procedure

This procedure clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases. This procedure can either perform these actions directly, or it can generate a script that performs these actions.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

Syntax

DBMS_STREAMS_ADM.MAINTAIN_SIMPLE_TTS(
   tablespace_name              IN VARCHAR2,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE);

Parameters

Usage Notes

The specified tablespace must be a simple tablespace. A simple tablespace is a single, self-contained tablespace that uses only one datafile. A self-contained tablespace has no references from the tablespace pointing outside of the tablespace. For example, if an index in the tablespace is for a table in a different tablespace, then the tablespace is not self-contained. This procedure cannot be used for a non simple tablespace or a set of tablespaces.

DDL Changes Not Maintained

This procedure does not configure the Streams environment to maintain DDL changes to the tablespace nor to the database objects in the tablespace. For example, the Streams environment is not configured to replicate ALTER TABLESPACE statements on the tablespace, nor is it configured to replicate ALTER TABLE statements on tables in the tablespace. You can configure the Streams environment to maintain DDL changes manually or modify generated scripts to achieve this.

Additional Privileges Required by the MAINTAIN_SIMPLE_TTS Procedure

In addition to the required privileges described in "Requirements for Running These Procedures", the user who runs the MAINTAIN_SIMPLE_TTS procedure must have the necessary privileges to complete the following actions:

• Run subprograms in the DBMS_STREAMS_TABLESPACES_ADM package

• The necessary privileges to run the CLONE_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_SIMPLE_TABLESPACE Procedure for the list of required privileges.

• The necessary privileges to run the ATTACH_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_SIMPLE_TABLESPACE Procedure for the list of required privileges.

Default Values for Parameters Excluded From the MAINTAIN_SIMPLE_TTS Procedure

This procedure uses the default values for the parameters in the MAINTAIN_TTS procedure that do not exist in the MAINTAIN_SIMPLE_TTS procedure. For example, this procedure automatically generates the capture process name, because NULL is the default value for the capture_name parameter in the MAINTAIN_TTS procedure, and the procedure generates the capture process name when NULL is specified for capture_name.

MAINTAIN_TABLES Procedure

This procedure configures a Streams environment that replicates changes to specified tables between two databases. This procedure can either configure the environment directly, or it can generate a script that configures the environment.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

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

Syntax

DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  IN VARCHAR2,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                           DBMS_STREAMS_ADM.INSTANTIATION_TABLE);

DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  IN DBMS_UTILITY.UNCL_ARRAY,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                           DBMS_STREAMS_ADM.INSTANTIATION_TABLE);

Parameters

MAINTAIN_TABLESPACES Procedure

This procedure clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases. This procedure can either perform these actions directly, or it can generate a script that performs these actions. Run this procedure at the source database.

Syntax

DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES(
   tablespace_names             IN DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   destination_database         IN VARCHAR2,
   setup_streams                IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   source_queue_table           IN VARCHAR2  DEFAULT 'streams_queue_table',
   source_queue_name            IN VARCHAR2  DEFAULT 'streams_queue',
   source_queue_user            IN VARCHAR2  DEFAULT NULL,
   destination_queue_table      IN VARCHAR2  DEFAULT 'streams_queue_table',
   destination_queue_name       IN VARCHAR2  DEFAULT 'streams_queue',
   destination_queue_user       IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT 'capture',
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE);

Parameters

Usage Notes

The specified set of tablespaces must be self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained.

Additional Documentation for this Procedure

The documentation in the following sections applies to the MAINTAIN_TABLESPACES procedure:

• Single Source and Bi-Directional Configurations

• Change Cycling

• Automatic Platform Conversion

Requirements for Running this Procedure

Meet the following requirements when run the MAINTAIN_TABLESPACES procedure:

• Run the procedure at the source database.

• Both databases must be open during configuration. If the procedure is generating a script only, then the database specified in the destination_database parameter does not need to be open when you run the procedure, but both databases must be open when you run the generated script.

• The user who runs this procedure should be granted DBA role. This user must have the necessary privileges to complete the following actions:

  • Create ANYDATA queues, capture processes, propagations, and apply processes.

  • Specify supplemental logging

  • Run subprograms in the DBMS_STREAMS_ADM and DBMS_AQADM packages.

  • Access the database specified in the destination_database parameter through a database link. This database link should have the same name as the global name of the destination database.

  • Run subprograms in the DBMS_STREAMS_TABLESPACES_ADM package

  • The necessary privileges to run the CLONE_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_TABLESPACES Procedure for the list of required privileges.

  • The necessary privileges to run the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_TABLESPACES Procedure for the list of required privileges.

  To ensure that the user who runs this procedure has the necessary privileges, you should configure a Streams administrator at each database, and each database link should be should be created in the Streams administrator's schema.

• If the bi_directional parameter is set to TRUE, then the corresponding user at the destination database must be able to use a database link to access the source database. This database link should have the same name as the global name of the source database.

• Each specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes this procedure must have READ and WRITE privilege on each one.

• The databases configured by this procedure must be Oracle Database 10g Release 2 databases when this procedure is run under the following conditions:

  • The procedure is run at an Oracle Database 10g Release 2 database.

  • The setup_streams parameter is set to TRUE to configure the Streams replication environment directly.

• The databases configured by this procedure must be Oracle Database 10g Release 1 or later databases when this procedure is run under the following conditions:

  • The procedure is run at an Oracle Database 10g Release 2 database.

  • The setup_streams parameter is set to FALSE in this procedure, and the replication environment is configured with a generated script.

  If the script configures an Oracle Database 10g Release 1 database, then the script must be modified so that it does not configure features that are available only in Oracle Database 10g Release 2, such as queue-to-queue propagation.

• If the procedure is run at an Oracle Database 10g Release 1 database, then the databases configured by the procedure must be Oracle Database 10g Release 1 or later databases.

Configuration Progress and Recoverability

When this procedure is run with the setup_streams parameter set to TRUE, metadata about its configuration actions is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then you can use the RECOVER_OPERATION procedure to complete the configuration after you correct the conditions that caused the error.

MAINTAIN_TTS Procedure

This procedure clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases. This procedure can either perform these actions directly, or it can generate a script that performs these actions.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

Syntax

DBMS_STREAMS_ADM.MAINTAIN_TTS(
   tablespace_names             IN DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE);

Parameters

Usage Notes

The specified set of tablespaces must be self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained.

Additional Privileges Required by the MAINTAIN_TTS Procedure

In addition to the required privileges described in "Requirements for Running These Procedures", the user who runs the MAINTAIN_TTS procedure must have the necessary privileges to complete the following actions:

• Run subprograms in the DBMS_STREAMS_TABLESPACES_ADM package

• The necessary privileges to run the CLONE_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_TABLESPACES Procedure for the list of required privileges.

• The necessary privileges to run the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_TABLESPACES Procedure for the list of required privileges.

POST_INSTANTIATION_SETUP Procedure

This procedure performs the actions required after instantiation to configure a Streams replication environment.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

To complete the Streams replication configuration, follow these steps:

1. Run the PRE_INSTANTIATION_SETUP procedure at the source database.

2. Perform any necessary instantiation actions.

3. Run the POST_INSTANTIATION_SETUP procedure at the source database.

Typically, the Streams replication environment configured using these steps serves one of the following purposes:

• Replicates changes to shared database objects to keep the database objects synchronized at different databases.

• Replicates changes to database objects during a database maintenance operation, such migrating a database to a different platform. In this case, use the CLEANUP_INSTANTIATION_SETUP procedure to remove the replication environment after the maintenance operation is complete.

Syntax

DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
   maintain_mode           IN VARCHAR2,
   tablespace_names        IN DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET,
   source_database         IN VARCHAR2,
   destination_database    IN VARCHAR2,
   perform_actions         IN BOOLEAN         DEFAULT TRUE,
   script_name             IN VARCHAR2        DEFAULT NULL,
   script_directory_object IN VARCHAR2        DEFAULT NULL,
   capture_name            IN VARCHAR2        DEFAULT NULL,
   capture_queue_table     IN VARCHAR2        DEFAULT NULL,
   capture_queue_name      IN VARCHAR2        DEFAULT NULL,
   capture_queue_user      IN VARCHAR2        DEFAULT NULL,
   propagation_name        IN VARCHAR2        DEFAULT NULL,
   apply_name              IN VARCHAR2        DEFAULT NULL,
   apply_queue_table       IN VARCHAR2        DEFAULT NULL,
   apply_queue_name        IN VARCHAR2        DEFAULT NULL,
   apply_queue_user        IN VARCHAR2        DEFAULT NULL,
   bi_directional          IN BOOLEAN         DEFAULT FALSE,
   include_ddl             IN BOOLEAN         DEFAULT FALSE,
   start_processes         IN BOOLEAN         DEFAULT FALSE,
   instantiation_scn       IN NUMBER          DEFAULT NULL,
   exclude_schemas         IN VARCHAR2        DEFAULT NULL,
   exclude_flags           IN BINARY_INTEGER  DEFAULT NULL);

Parameters

Usage Notes

The following sections contain usage notes for this procedure.

Self-Contained Tablespace Sets

If the maintain_mode parameter is set to TRANSPORTABLE TABLESPACES, then the specified set of tablespaces must be self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained.

Destination Database Renamed During RMAN Database Instantiation

If the maintain_mode parameter is set to GLOBAL, then database instantiation is required before running the POST_INSTANTIATION_SETUP procedure. If the RMAN DUPLICATE or RMAN CONVERT DATABASE command is used for database instantiation, then the global name of the destination database can be renamed to the global name of the source database during instantiation. In this case, before you run the POST_INSTANTIATION_SETUP procedure, complete the following steps:

1. Rename the global name of the destination database back to the name specified in the destination_database parameter.

2. At the destination database, drop and recreate any loopback database links that existed on the source and were cloned on the destination database. For example, suppose the source database dbs1.net has a database link that refers to itself. Suppose the destination database is dbs2.net. At the destination database, drop and recreate this database link as a loopback database link that refers to itself (dbs2.net).

3. At the destination database, drop any database links that were cloned from the source database and are from the source database to the destination database. For example, if the source database is dbs1.net and the destination database is dbs2.net, then drop any database links on the destination database that are from dbs1.net to dbs2.net.

4. Create a database link from the destination database to the source database with the same name as the global name of the source database. The database link must be accessible to the Streams administrator at the destination database.

   This database link is required because the POST_INSTANTIATION_SETUP procedure runs the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package at the destination database, and the SET_GLOBAL_INSTANTIATION_SCN procedure requires the database link. The instantiation SCN is set to the value specified in the instantiation_scn parameter of the POST_INSTANTIATION_SETUP procedure.

Streams Components Removed From the Destination Database

If the maintain_mode parameter is set to GLOBAL, then database instantiation is required before running the POST_INSTANTIATION_SETUP procedure. During database instantiation, Streams components created by the PRE_INSTANTIATION_SETUP procedure, such as Streams clients and queues, can be copied from the source database to the destination database. The POST_INSTANTIATION_SETUP procedure removes the Stream components created by the PRE_INSTANTIATION_SETUP procedure from the destination database.

In some cases, rule sets and rules created by the PRE_INSTANTIATION_SETUP procedure might not be removed from the destination database. The POST_INSTANTIATION_SETUP procedure does not associate these rule sets and rules with any Stream clients in the destination database. Optionally, you can remove these rule sets and rules from the destination database after the POST_INSTANTIATION_SETUP procedure, or the script generated by the procedure, completes.

PRE_INSTANTIATION_SETUP Procedure

This procedure performs the actions required before instantiation to configure a Streams replication environment.

Run this procedure at the capture database. The capture database is the database that captures changes made to the source database.

To complete the Streams replication configuration, follow these steps:

1. Run the PRE_INSTANTIATION_SETUP procedure at the database that will be the source database in the Stream replication environment.

2. Perform any necessary instantiation actions.

3. Run the POST_INSTANTIATION_SETUP procedure at the source database.

Typically, the Streams replication environment configured using these steps serves one of the following purposes:

• Replicates changes to shared database objects to keep the database objects synchronized at different databases.

• Replicates changes to database objects during a database maintenance operation, such migrating a database to a different platform. In this case, use the CLEANUP_INSTANTIATION_SETUP procedure to remove the replication environment after the maintenance operation is complete.

Syntax

DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
   maintain_mode           IN VARCHAR2,
   tablespace_names        IN DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET,
   source_database         IN VARCHAR2,
   destination_database    IN VARCHAR2,
   perform_actions         IN BOOLEAN         DEFAULT TRUE,
   script_name             IN VARCHAR2        DEFAULT NULL,
   script_directory_object IN VARCHAR2        DEFAULT NULL,
   capture_name            IN VARCHAR2        DEFAULT NULL,
   capture_queue_table     IN VARCHAR2        DEFAULT NULL,
   capture_queue_name      IN VARCHAR2        DEFAULT NULL,
   capture_queue_user      IN VARCHAR2        DEFAULT NULL,
   propagation_name        IN VARCHAR2        DEFAULT NULL,
   apply_name              IN VARCHAR2        DEFAULT NULL,
   apply_queue_table       IN VARCHAR2        DEFAULT NULL,
   apply_queue_name        IN VARCHAR2        DEFAULT NULL,
   apply_queue_user        IN VARCHAR2        DEFAULT NULL,
   bi_directional          IN BOOLEAN         DEFAULT FALSE,
   include_ddl             IN BOOLEAN         DEFAULT FALSE,
   start_processes         IN BOOLEAN         DEFAULT FALSE,
   exclude_schemas         IN VARCHAR2        DEFAULT NULL,
   exclude_flags           IN BINARY_INTEGER  DEFAULT NULL);

Parameters

Usage Notes

The following sections contain usage notes for this procedure.

Self-Contained Tablespace Sets

If the maintain_mode parameter is set to TRANSPORTABLE TABLESPACES, then the specified set of tablespaces must be self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained.

The exclude_flags Parameter

Specify one of the following values:

• DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL to exclude changes to the schemas and all of the database objects in the schemas

• DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED to exclude changes to the database objects that are not supported by Streams in the schemas

If both of these values are specified, then the procedure raises an error.

In addition to DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL or DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED, specify one or both of the following values:

• DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML to exclude data manipulation language (DML) changes made to the excluded database objects

• DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL to exclude data definition language (DDL) changes made to the excluded database objects

Use the plus sign (+) to specify more than one of these values. For example, to maintain DML changes to the tables in a schemas specified by the exclude_schemas parameter but exclude DDL changes to these schemas and the database objects in these schemas, specify the following for this parameter:

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL + 
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL

To exclude DML and DDL changes made to unsupported database objects in the schemas specified by the exclude_schemas parameter, specify the following for this parameter:

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL

Rules for the excluded database objects are added to the negative rule set of each capture process. Therefore, changes to the excluded database objects will not be captured and replicated.

This parameter is valid only if the maintain_mode parameter is set to GLOBAL and the exclude_schemas parameter is set to a non-NULL value. If the maintain_mode parameter is set to GLOBAL and the exclude_schemas parameter is set to a NULL, then the procedure ignores this parameter. If the maintain_mode parameter is set to TRANSPORTABLE TABLESPACES, then this the procedure ignores this parameter and excludes any database objects in the specified tablespace set that are not supported by Streams from the Streams configuration automatically.

Also, if schemas are specified in the exclude_schemas parameter, but the exclude_flags parameter is set to NULL, then the procedure does not add any rules to the negative rule set of any capture process, and the procedure includes the schemas specified in the exclude_schemas parameter in the replication environment.

PURGE_SOURCE_CATALOG Procedure

This procedure removes all Streams data dictionary information at the local database for the specified object. You can use this procedure to remove Streams metadata that is not needed currently and will not be needed in the future.

Syntax

DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG(
   source_database     IN  VARCHAR2,
   source_object_name  IN  VARCHAR2,
   source_object_type  IN  VARCHAR2);

Parameters

Usage Notes

The global name of the source database containing the object must be specified for the source_database parameter. If the current database is not the source database for the object, then the procedure removes data dictionary information about the object from the current database, not the source database.

For example, suppose changes to the hr.employees table at the dbs1.net source database are being applied to the hr.employees table at the dbs2.net destination database. Also, suppose hr.employees at dbs2.net is not a source at all. In this case, specifying dbs2.net as the source_database for this table results in an error. However, specifying dbs1.net as the source_database for this table while running the PURGE_SOURCE_CATALOG procedure at the dbs2.net database removes data dictionary information about the table at dbs2.net.

Do not run this procedure at a database if either of the following conditions are true:

• Logical change records (LCRs) captured by the capture process for the object are or might be applied locally without reinstantiating the object.

• LCRs captured by the capture process for the object are or might be forwarded by the database without reinstantiating the object.

  
  

  Note: These conditions do not apply to LCRs that were not created by the capture process. That is, these conditions do not apply to user-created LCRs.

  
  

RECOVER_OPERATION Procedure

This procedure provides options for a Streams replication configuration operation that stopped because it encountered an error. This procedure either rolls forward the operation, rolls back the operation, or purges all of the metadata about the operation.

This procedure only can perform these actions for replication configuration operations being done by one of the following procedures:

• MAINTAIN_GLOBAL Procedure

• MAINTAIN_SCHEMAS Procedure

• MAINTAIN_SIMPLE_TABLESPACE Procedure

• MAINTAIN_SIMPLE_TTS Procedure

• MAINTAIN_TABLES Procedure

• MAINTAIN_TABLESPACES Procedure

• MAINTAIN_TTS Procedure

• PRE_INSTANTIATION_SETUP Procedure

• POST_INSTANTIATION_SETUP Procedure

When these procedures configure the replication environment directly (not by generating a script), information about the configuration actions is stored in the following data dictionary views when the procedure is running:

• DBA_RECOVERABLE_SCRIPT

• DBA_RECOVERABLE_SCRIPT_PARAMS

• DBA_RECOVERABLE_SCRIPT_BLOCKS

• DBA_RECOVERABLE_SCRIPT_ERRORS

The data dictionary views are populated at the database where the replication configuration procedure is run. When one of these procedures completes successfully, metadata about the configuration operation is purged from these views. However, when one of these procedures encounters an error and stop, metadata about the configuration operation remains in these views. In this case, you can either roll forward, roll back, or purge the metadata about the operation using the RECOVER_OPERATION procedure. If you choose to roll forward the operation, then correct conditions that caused the errors reported in DBA_RECOVERABLE_SCRIPT_ERRORS before proceeding.

Run the RECOVER_OPERATION procedure at the database where the replication configuration procedure was run.

Syntax

DBMS_STREAMS_ADM.RECOVER_OPERATION(
   script_id       IN  RAW,
   operation_mode  IN  VARCHAR2  DEFAULT 'FORWARD');

Parameters

REMOVE_QUEUE Procedure

This procedure removes the specified ANYDATA queue.

Specifically, this procedure performs the following actions:

1. Waits until all current enqueue and dequeue transactions commit.

2. Stops the queue, which means that no further enqueues into the queue or dequeues from the queue are allowed.

3. Drops the queue.

4. If the drop_unused_queue_table parameter is set to TRUE, then drops the queue table if it is empty and no other queues are using it.

5. If the cascade parameter is set to TRUE, then drops all of the Streams clients that are using the queue.

   
   

   Note: The specified queue must be a ANYDATA queue.

   
   

Syntax

DBMS_STREAMS_ADM.REMOVE_QUEUE(
   queue_name               IN  VARCHAR2,
   cascade                  IN  BOOLEAN  DEFAULT FALSE,
   drop_unused_queue_table  IN  BOOLEAN  DEFAULT TRUE);

Parameters

REMOVE_RULE Procedure

This procedure removes the specified rule or all rules from the rule set associated with the specified capture process, apply process, propagation, or messaging client.

If this procedure results in an empty positive rule set for a messaging client, then the procedure drops the messaging client automatically.

Syntax

DBMS_STREAMS_ADM.REMOVE_RULE(
   rule_name         IN  VARCHAR2,
   streams_type      IN  VARCHAR2,
   streams_name      IN  VARCHAR2,
   drop_unused_rule  IN  BOOLEAN  DEFAULT TRUE,
   inclusion_rule    IN  BOOLEAN  DEFAULT TRUE);

Parameters

REMOVE_STREAMS_CONFIGURATION Procedure

This procedure removes the Streams configuration at the local database.

Syntax

DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

Usage Notes

Specifically, this procedure performs the following actions at the local database:

• Drops all capture processes

• If any tables have been prepared for instantiation, then aborts preparation for instantiation for the table using the ABORT_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package

• If any schemas have been prepared for instantiation, then aborts preparation for instantiation for the schema using the ABORT_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package

• If the database has been prepared for instantiation, then aborts preparation for instantiation for the database using the ABORT_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package

• Drops propagations that were created using either the DBMS_STREAMS_ADM package or the DBMS_PROPAGATION_ADM package. Before a propagation is dropped, its propagation job is disabled. Does not drop propagations that were created using the DBMS_AQADM package.

• Disables all propagation jobs used by propagations

• Drops all apply processes. If there are apply errors in the error queue for an apply process, then this procedure deletes these apply errors before it drops the apply process.

• Removes specifications for DDL handlers used by apply processes, but does not delete the PL/SQL procedures used by these handlers

• Removes specifications for message handlers used by apply processes, but does not delete the PL/SQL procedures used by these handlers

• Removes specifications for precommit handlers used by apply processes, but does not delete the PL/SQL procedures used by these handlers

• Removes the instantiation SCN and ignore SCN for each apply object and schema and for the entire database

• Removes messaging clients

• Unsets message notification specifications that were set using the SET_MESSAGE_NOTIFICATION procedure in the DBMS_STREAMS_ADM package

• Removes specifications for DML handlers and error handlers, but does not delete the PL/SQL procedures used by these handlers

• Removes update conflict handlers

• Removes specifications for substitute key columns for apply tables

• Drops rules that were created using the DBMS_STREAMS_ADM package. Does not drop rules that were created using the DBMS_RULE_ADM package.

This procedure stops capture processes and apply processes before it drops them.

RENAME_COLUMN Procedure

This procedure either adds or removes a declarative rule-based transformation which renames a column in a row logical change record (LCR) that satisfies the specified rule.

For the transformation to be performed when the specified rule evaluates to TRUE, the rule must be in the positive rule set of a Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients.

Syntax

DBMS_STREAMS_ADM.RENAME_COLUMN(
   rule_name         IN  VARCHAR2,
   table_name        IN  VARCHAR2,
   from_column_name  IN  VARCHAR2,
   to_column_name    IN  VARCHAR2,
   value_type        IN  VARCHAR2  DEFAULT '*',
   step_number       IN  NUMBER    DEFAULT 0,
   operation         IN  VARCHAR2  DEFAULT 'ADD');

Parameters

Usage Notes

When 'REMOVE' is specified for the operation parameter, all of the rename column declarative rule-based transformations for the specified rule are removed that match the specified table_name, column_name, and step_number parameters. Nulls specified for these parameters act as wildcards. The following table lists the behavior of the RENAME_COLUMN procedure when one or more of these parameters is NULL:

RENAME_SCHEMA Procedure

This procedure either adds or removes a declarative rule-based transformation which renames a schema in a row logical change record (LCR) that satisfies the specified rule.

For the transformation to be performed when the specified rule evaluates to TRUE, the rule must be in the positive rule set of a Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients.

Syntax

DBMS_STREAMS_ADM.RENAME_SCHEMA(
   rule_name         IN  VARCHAR2,
   from_schema_name  IN  VARCHAR2,
   to_schema_name    IN  VARCHAR2,
   step_number       IN  NUMBER    DEFAULT 0,
   operation         IN  VARCHAR2  DEFAULT 'ADD');

Parameters

Usage Notes

When 'REMOVE' is specified for the operation parameter, all of the rename schema declarative rule-based transformations for the specified rule are removed that match the specified from_schema_name, to_schema_name, and step_number parameters. Nulls specified for these parameters act as wildcards. The following table lists the behavior of the RENAME_SCHEMA procedure when one or more of these parameters is NULL:

RENAME_TABLE Procedure

This procedure either adds or removes a declarative rule-based transformation which renames a table in a row logical change record (row LCR) that satisfies the specified rule.

For the transformation to be performed when the specified rule evaluates to TRUE, the rule must be in the positive rule set of a Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients.

Syntax

DBMS_STREAMS_ADM.RENAME_TABLE(
   rule_name        IN  VARCHAR2,
   from_table_name  IN  VARCHAR2,
   to_table_name    IN  VARCHAR2,
   step_number      IN  NUMBER    DEFAULT 0,
   operation        IN  VARCHAR2  DEFAULT 'ADD');

Parameters

Usage Notes

When 'REMOVE' is specified for the operation parameter, all of the rename table declarative rule-based transformations for the specified rule are removed that match the specified from_table_name, to_table_name, and step_number parameters. Nulls specified for these parameters act as wildcards. The following table lists the behavior of the RENAME_TABLE procedure when one or more of these parameters is NULL:

SET_MESSAGE_NOTIFICATION Procedure

This procedure sets a notification for messages that can be dequeued by a specified Streams messaging client from a specified queue. A notification is sent when a message is enqueued into the specified queue and the specified messaging client can dequeue the message because the message satisfies its rule sets.

Syntax

DBMS_STREAMS_ADM.SET_MESSAGE_NOTIFICATION(
   streams_name          IN  VARCHAR2,
   notification_action   IN  VARCHAR2,
   notification_type     IN  VARCHAR2     DEFAULT 'PROCEDURE',
   notification_context  IN  ANYDATA      DEFAULT NULL,
   include_notification  IN  BOOLEAN      DEFAULT TRUE,
   queue_name            IN  VARCHAR2     DEFAULT 'streams_queue');

Parameters

ANYDATA.ConvertRaw(HEXTORAW('FF'))

Usage Notes

You can specify one of the following types of notifications:

• An email address to which message notifications are sent. When a relevant message is enqueued into the queue, an email with the message properties is mailed to the specified email address.

• A PL/SQL procedure to be invoked on a notification. When a relevant message is enqueued into the queue, the specified PL/SQL procedure is invoked with the message properties. This PL/SQL procedure can dequeue the message.

• An HTTP URL to which the notification is posted. When a relevant message is enqueued into the queue, a notification with the message properties is posted to the specified URL specified.

A client does not need to be connected to the database to receive a notification.

If you register for email notifications, then you should use the DBMS_AQELM package to set the host name and port name for the SMTP server that will be used by the database to send email notifications. If required, then you should set the send-from email address, which is set by the database as the sent from field. You need a Java-enabled database to use this feature.

If you register for HTTP notifications, you might want to use the DBMS_AQELM package to set the host name and port number for the proxy server and a list of no-proxy domains that will be used by the database to post HTTP notifications.

Each notification is an AQXmlNotification, which includes of the following:

• notification_options, which includes the following:

  • destination - The destination queue from which the message was dequeued

  • consumer_name - The name of the messaging client that dequeued the message

• message_set - The set of message properties

  
  

  See Also: The documentation for the DBMS_AQELM package for more information on email notifications and HTTP notifications Oracle Streams Concepts and Administration for more information about setting message notifications Oracle Streams Advanced Queuing User's Guide and Reference and Oracle XML DB Developer's Guide for more information about message notifications and XML Oracle Streams Concepts and Administration for more information about how rules are used in Streams

  
  

Examples

If you use a message notification procedure, then this PL/SQL procedure must have the following signature:

PROCEDURE procedure_name(
  context  IN  ANYDATA,
  reginfo  IN  SYS.AQ$_REG_INFO,
  descr    IN  SYS.AQ$_DESCRIPTOR);

Here, procedure_name stands for the name of the procedure. The procedure is a PLSQLCALLBACK data structure that specifies the user-defined PL/SQL procedure to be invoked on message notification.

The following is a simple example of a notification procedure that dequeues a message of type oe.user_msg using the message identifier and consumer name sent by the notification. To complete the example, first create the type:

CREATE TYPE oe.user_msg AS OBJECT(
  object_name    VARCHAR2(30),
  object_owner   VARCHAR2(30),
  message        VARCHAR2(50));
/

Next, create the procedure:

CREATE OR REPLACE PROCEDURE oe.notification_dequeue(
  context  ANYDATA, 
  reginfo  SYS.AQ$_REG_INFO, 
  descr    SYS.AQ$_DESCRIPTOR)
AS 
  dequeue_options     DBMS_AQ.DEQUEUE_OPTIONS_T; 
  message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T; 
  message_handle      RAW(16); 
  message             ANYDATA; 
  oe_message          oe.user_msg; 
  rc                  PLS_INTEGER; 
BEGIN 
  -- Get the message identifier and consumer name from the descriptor 
  dequeue_options.msgid := descr.msg_id; 
  dequeue_options.consumer_name := descr.consumer_name; 
  -- Dequeue the message 
  DBMS_AQ.DEQUEUE( 
    queue_name         => descr.queue_name, 
    dequeue_options    => dequeue_options, 
    message_properties => message_properties, 
    payload            => message, 
    msgid              => message_handle);
  rc := message.getobject(oe_message); 
  COMMIT; 
END; 
/

SET_RULE_TRANSFORM_FUNCTION Procedure

This procedure sets or removes the transformation function name for a custom rule-based transformation.

Syntax

DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
   rule_name           IN  VARCHAR2,
   transform_function  IN  VARCHAR2);

Parameters