The DBMS_STREAMS_ADM
package, one of a set of Oracle Streams packages, provides subprograms for configuring Oracle Streams replication environments. This package also includes subprograms for adding and removing simple rules for capture, propagation, apply, and dequeue at the table, schema, and database level.
See Also:
Oracle Streams Concepts and Administration, Oracle Streams Replication Administrator's Guide, and Oracle Database 2 Day + Data Replication and Integration Guide for more information about this package and Oracle Streams
This chapter contains the following topics:
Overview
Deprecated Subprograms
Security Model
Operational Notes
This section contains topics which relate to using the DBMS_STREAMS_ADM
package.
The DBMS_STREAMS_ADM
package, one of a set of Oracle Streams packages, provides subprograms for configuring an Oracle Streams replication environment. This package also includes 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 creating queues, and for managing Oracle Streams metadata, such as data dictionary information.
If you require more sophisticated rules, then refer to Chapter 112, "DBMS_RULE" package.
Note:
Oracle recommends that you do not use deprecated subprograms. Support for deprecated features is for backward compatibility only.The following subprograms are deprecated with Oracle Database 10g Release 2 and later:
MAINTAIN_SIMPLE_TABLESPACE
This procedure is replaced by the MAINTAIN_SIMPLE_TTS
procedure.
See Also:
MAINTAIN_SIMPLE_TTS ProcedureMAINTAIN_TABLESPACES
This procedure is replaced by the MAINTAIN_TTS
procedure.
See Also:
MAINTAIN_TTS ProcedureA user is associated with each Oracle Streams client. The following sections describe these users:
The following procedures can create a capture process:
The following procedures can create a synchronous capture:
If one of these procedures creates a capture process or a synchronous capture, then it configures the current user as the capture user. The capture user is the user in whose security domain a capture process or synchronous capture captures changes that satisfy its rule set(s) and runs custom rule-based transformations configured for these 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 or synchronous capture and configures the user as a secure queue user of the queue.
See Also:
CREATE_CAPTURE Procedure and CREATE_SYNC_CAPTURE Procedure for information about the privileges required to capture changes (refer to thecapture_user
parameter)The following procedures can create a propagation:
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. Each propagation job is an Oracle Scheduler job. You can adjust the schedule of a propagation job using Oracle Scheduler.
Note:
The source queue owner performs the propagation, but the propagation job is owned by the user who creates it. These two users might or might not be the same.
For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate messages.
See Also:
CREATE_PROPAGATION Procedure for more information about the required privileges
"Propagation Rules for LCRs" for information about when a propagation job is created
The following procedures can create an apply process:
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.
See Also:
CREATE_APPLY Procedure for information about the privileges required to apply changes (refer to theapply_user
parameter)The following procedures can create a messaging client:
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.
Several procedures in this package create rules for Oracle Streams clients, and several procedures configure an Oracle Streams replication environment. The following sections provide information about using these procedures:
Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients. Some of the procedures in the DBMS_STREAMS_ADM
package add rules to the rule sets of Oracle Streams clients. The rules can pertain to changes in the redo log, to data manipulation language (DML) changes made to a table, to logical change records (LCRs), or to user messages.
An LCR represents either a row change that results from a 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 that were captured by a capture process or a synchronous capture, 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.
A capture process, propagation, apply process, or messaging client can have both positive and negative rule sets. A synchronous capture can have only a positive rule set.
For all of the procedures except the ones that create subset rules, and for all Oracle Streams clients except for synchronous captures, you use the inclusion_rule
parameter to specify the type of rule set (either positive or negative) for the created rules. If the Oracle 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 Oracle 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 an Oracle 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:
See Also:
Oracle Streams Concepts and Administration for more information about how rules are used in Oracle StreamsThe 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.
See Also:
Chapter 28, "Summary of DBMS_CAPTURE_ADM Subprograms" for more information about theCREATE_CAPTURE
procedure and the procedures that prepare database objects for instantiationThe following procedures add rules to the rule set of a synchronous capture when you specify sync_capture
for the streams_type
parameter:
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 a rule whose rule condition evaluates to TRUE
for DML 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 synchronous capture, then the synchronous capture captures row changes resulting from DML changes to the table at the source database and enqueues these changes into the specified queue. A synchronous capture cannot have a negative rule set.
A synchronous capture captures changes locally at the database where it is configured. This database is the source database for changes captured by the synchronous capture. Therefore, for synchronous capture rules, you should execute the procedure at the source database.
These procedures automatically prepare the appropriate tables for instantiation by invoking the PREPARE_SYNC_INSTANTIATION
function in the DBMS_CAPTURE_ADM
package at the source database.
Note:
A synchronous capture ignores rules in its rule set that were created by a procedure other than ADD_SUBSET_RULES
or ADD_TABLE_RULES
.
When the ADD_TABLE_RULES
or the ADD_SUBSET_RULES
procedure adds rules to a synchronous capture rule set, the procedure must obtain an exclusive lock on the specified table. If there are outstanding transactions on the specified table, then the procedure waits until it can obtain a lock.
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.
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 messages of a specific message type that evaluate to TRUE
for the rule condition from 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 messages in a source queue of a specific message type that evaluate to TRUE
for the rule condition. This procedure generates a rule name for the rule.
See Also:
"ADD_MESSAGE_PROPAGATION_RULE Procedure"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 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.
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 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 messages of a specific message type that satisfy the apply process rule.
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 persistent row LCRs, or DDL LCRs, or both that originated at the source database that matches 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 persistent row LCRs, or DDL LCRs, or both that originated at the source database that matches the source_database
parameter. You should execute these procedures at the database where you want to dequeue the messages with the messaging client.
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 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 messages of a specific message type that satisfy the message rule.
See Also:
"ADD_MESSAGE_RULE Procedure"The following procedures in this package configure a replication environment that is maintained by Oracle Streams:
The PRE_INSTANTIATION_SETUP
and POST_INSTANTIATION_SETUP
procedures must be used together to complete the Oracle Streams replication configuration.
The following sections contain information about using these 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.
Note:
When these procedures configure downstream capture, they always configure archived-log downstream capture. These procedures do not configure real-time downstream capture. However, you can configure redo transport services for real-time downstream capture before running a procedure, and then set the downstream_real_time_mine
capture process parameter to Y
after the procedure completes. You can also generate scrips with the procedures and modify the scripts to configure real-time downstream capture.
If these procedures configure bi-directional replication, then the capture process for the destination database always is a local capture process. That is, these procedures always configure the capture process for changes made to the destination database to run on the destination database.
When the RMAN DUPLICATE
or CONVERT
DATABASE
command is used for database instantiation with one of these procedures, the destination database cannot be the capture database.
See Also:
Oracle Streams Concepts and Administration for information about local capture and downstream capture
Oracle Database 2 Day + Data Replication and Integration Guide for an example that uses the MAINTAIN_SCHEMAS
procedure to configure a Streams replication environment that uses a real-time downstream capture process
Single Source and Bi-Directional Configurations
These procedures either set up a single source Oracle 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 Oracle Streams configuration with both databases acting as source and destination databases. The bi_directional
parameter controls whether the Oracle 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 Oracle Streams is configured to keep the shared database objects synchronized at the two databases, even if both databases allow changes to the database objects.
Note:
You might need to configure conflict resolution if bi-directional replication is configured.See Also:
"Local Capture or Downstream Capture for the Source Database"
Oracle Streams Replication Administrator's Guide for more information about conflict resolution
Oracle Streams Clients and Queues Configured By These Procedures
These procedures configure the following Oracle 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 Oracle Streams replication environment might operate more efficiently if each Oracle Streams client has its own separate queue.
However, two Oracle 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.
See Also:
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 Oracle 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 an Oracle 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
might be the hexidecimal equivalent of '1'
, and the apply tag for the apply process at nydb.net
might 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 an Oracle Streams replication environment with three databases where each database shares changes with the other two databases in the environment. Such an environment is sometimes called an "n-way" replication 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 an Oracle 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" replication environment.
You can configure the Oracle 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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about tags, for information about configuring a hub-and-spoke replication environment, and for information about configuring an n-way replication environment
Oracle Database 2 Day + Data Replication and Integration Guide or examples that configure hub-and-spoke replication environments
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.
Note:
When one of these procedures is run with theperform_actions
parameter set to FALSE
, these views are not populated. Also, the views are not populated when a script generated by one of these procedures is run.See Also:
"RECOVER_OPERATION Procedure"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 or later database.
If the perform_actions
parameter is set to TRUE
in one of these procedures to configure the Oracle Streams replication environment directly, then all of the databases configured by the procedure must be Oracle Database 10g Release 2 or later 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 or later, such as queue-to-queue propagation.
To ensure that the user who runs these procedures has the necessary privileges, you should configure an Oracle Streams administrator at each database, and each database link should be should be created in the Oracle Streams administrator's schema.
See Also:
Oracle Streams Concepts and Administration for information about configuring an Oracle Streams administratorCommon Parameters for the Configuration Procedures
Table 130-1 describes the common parameters for the procedures in this package that configure an Oracle Streams replication environment.
Table 130-1 Common Parameters for Configuration Procedures
Parameter | Description |
---|---|
|
The global name of the source database. If the specified global name is the same as the global name of the local database, then the procedure configures a local capture process for the source database. If the specified global name is different than the global name of the local database, then the procedure configures a downstream capture process at the local database. In this case, a database link from the local database to the source database with the same name as the global name of the source database must exist and must be accessible to the user who runs the procedure. If |
|
The global name of the destination database. If the local database is not the destination database, then a database link from the local database to the destination database with the same name as the global name of the destination database must exist and must be accessible to the user who runs the procedure. If |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
|
The name of each capture process configured to capture changes. Do not specify an owner. If the If the specified name matches the name of an existing capture process, then the procedure uses the existing capture process and adds the rules for capturing changes to the database to the positive capture process rule set. If Note: The capture process name cannot be altered after the capture process is created. |
|
The name of the queue table for each queue used by a capture process, specified as If |
|
The name of each queue used by a capture process, specified as If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. If |
|
The name of the user who requires If |
|
The name of each propagation configured to propagate changes. Do not specify an owner. If the specified name matches the name of an existing propagation, then the procedure uses the existing propagation and adds the rules for propagating changes to the positive propagation rule set. If Note: The propagation name cannot be altered after the propagation is created. |
|
The name of each apply process configured to apply changes. Do not specify an owner. If the specified name matches the name of an existing apply process, then the procedure uses the existing apply process and adds the rules for applying changes to the positive apply process rule set. The specified name must not match the name of an existing messaging client at the destination database. If Note: The apply process name cannot be altered after the apply process is created. |
|
The name of the queue table for each queue used by an apply process, specified as If |
|
The name of each queue used by an apply process, specified as If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. If |
|
The name of the user who requires If |
|
Specify Specify See Also: "Oracle Streams Clients and Queues Configured By These Procedures" for information about when propagations are configured |
|
Specify Specify |
Table 130-2 DBMS_STREAMS_ADM Package Subprograms
Subprogram | Description |
---|---|
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 |
|
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 |
|
Adds global rules to either the positive or negative rule set of a capture process, apply process, or messaging client, and creates the specified capture process, apply process, or messaging client if it does not exist |
|
Either 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 |
|
Adds a message rule to either the positive or negative rule set of an apply process or messaging client, and creates the specified apply process or messaging client if it does not exist |
|
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 |
|
Adds schema rules to either the positive or negative rule set of a capture process, apply process, or messaging client, and creates the specified capture process, apply process, or messaging client if it does not exist |
|
Adds subset rules to the positive rule set for a propagation, and creates the specified propagation if it does not exist |
|
Adds subset rules to the positive rule set of a capture process, synchronous capture, apply process, or messaging client, and creates the specified capture process, synchronous capture, apply process, or messaging client if it does not exist |
|
Either 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 |
|
Adds table rules to the rule set of a capture process, synchronous capture, apply process, or messaging client, and creates the specified capture process, synchronous capture, apply process, or messaging client if it does not exist |
|
Removes an Oracle Streams replication configuration that was set up by the |
|
Either adds or removes a declarative rule-based transformation which deletes a column from a row LCR that satisfies the specified rule |
|
Returns the tracking label for the current session |
|
Gets information about the system change number (SCN) values to use for Oracle Streams capture and apply processes in an Oracle Streams replication environment |
|
Configures an Oracle Streams environment that replicates changes at the database level between two databases |
|
Configures an Oracle Streams environment that replicates changes to specified schemas between two databases |
|
Clones a simple tablespace from a source database at a destination database and uses Oracle Streams to maintain this tablespace at both databases. This procedure is deprecated. |
|
Clones a simple tablespace from a source database at a destination database and uses Oracle Streams to maintain this tablespace at both databases |
|
Configures an Oracle Streams environment that replicates changes to specified tables between two databases |
|
Clones a set of tablespaces from a source database at a destination database and uses Oracle Streams to maintain these tablespaces at both databases. This procedure is deprecated. |
|
Clones a set of tablespaces from a source database at a destination database and uses Oracle Streams to maintain these tablespaces at both databases |
|
Merges a stream flowing from one capture process with a stream flowing from another capture process |
|
Determines whether the original capture process and the cloned capture are within the specified merge threshold and, if they are, runs the |
|
Performs the actions required after instantiation to configure an Oracle Streams replication environment |
|
Performs the actions required before instantiation to configure an Oracle Streams replication environment |
|
Removes all Oracle Streams data dictionary information at the local database for the specified object |
|
Provides options for an Oracle 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. |
|
Removes the specified |
|
Removes the specified rule or all rules from the rule set associated with the specified capture process, synchronous capture, propagation, apply process, or messaging client. |
|
Removes the Oracle Streams configuration at the local database |
|
Either adds or removes a declarative rule-based transformation which renames a column in a row LCR that satisfies the specified rule |
|
Either adds or removes a declarative rule-based transformation which renames a schema in a row LCR that satisfies the specified rule |
|
Either adds or removes a declarative rule-based transformation which renames a table in a row LCR that satisfies the specified rule |
|
Sets a notification for messages that can be dequeued by a specified Oracle Streams messaging client from a specified queue |
|
Sets the tracking label for logical change records (LCRs) produced by the current session |
|
Sets or removes the transformation function name for a rule-based transformation |
|
Creates a queue table and a queue for use with the capture, propagate, and apply functionality of Oracle Streams |
|
Splits one stream flowing from a capture process off from all of the other streams flowing from the capture process |
Note:
All subprograms commit unless specified otherwise.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 an Oracle Streams client. Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients.
This procedure is overloaded. The column_value
and column_function
parameters are mutually exclusive.
Note:
ADD_COLUMN
transformations cannot add columns of the following datatypes: BLOB
, CLOB
, NCLOB
, BFILE
, LONG
, LONG
RAW
, ROWID
, user-defined types (including object types, REF
s, varrays, nested tables), and Oracle-supplied types (including any types, XML types, spatial types, and media types).
Declarative transformations can transform row LCRs only. These row LCRs can be captured by a capture process, captured by a synchronous capture, or constructed and enqueued by an application. Therefore, a DML rule must be specified when you run this procedure. If a DDL is specified, then the procedure raises an error.
See Also:
Oracle Streams Concepts and Administration for more information about declarative rule-based transformationsDBMS_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');
Table 130-3 ADD_COLUMN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule, specified as For example, to specify a rule in the |
|
The name of the table to which the column is added in the row LCR, specified as |
|
The name of the column added to each row LCR that satisfies the rule. |
|
The value of the added column. Specify the appropriate This parameter cannot be specified if the |
|
Either the The The The function executes when the rule evaluates to This parameter cannot be specified if the |
|
Specify Specify |
|
The order of execution of the transformation. See Also: Oracle Streams Concepts and Administration for more information about transformation ordering |
|
Specify Specify |
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
:
table_name | column_name | step_number | Result |
---|---|---|---|
NULL |
NULL |
NULL |
Remove all add column transformations for the specified rule. |
NULL |
NULL |
non-NULL |
Remove all add column transformations with the specified step_number for the specified rule. |
NULL |
non-NULL |
non-NULL |
Remove all add column transformations with the specified column_name and step_number for the specified rule. |
non-NULL |
NULL |
non-NULL |
Remove all add column transformations with the specified table_name and step_number for the specified rule. |
NULL |
non-NULL |
NULL |
Remove all add column transformations with the specified column_name for the specified rule. |
non-NULL |
non-NULL |
NULL |
Remove all add column transformations with the specified table_name and column_name for the specified rule. |
non-NULL |
NULL |
NULL |
Remove all add column transformations with the specified table_name for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
Remove all add column transformations with the specified table_name , column_name , and step_number for the specified rule. |
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.
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);
Table 130-4 ADD_GLOBAL_PROPAGATION_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then the procedure creates it automatically. If If |
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the procedure uses the global name of the current database, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed. |
|
If |
|
If |
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. The source database is where the changes originated. If If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
|
If If The procedure cannot change the queue to queue property of an exiting propagation. If the specified propagation exists, then the procedure behaves in the following way for each setting:
See Also: Oracle Streams Concepts and Administration for more information about queue-to-queue propagations |
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.
See Also:
"Operational Notes" and "Propagation Rules for LCRs" for more information about the rules created by this procedure
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' )
This procedure adds rules to a rule set of one of the following types of Oracle 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 persistent 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.
Caution:
If you add global rules to the positive rule set for a capture process, then make sure you add rules to the negative capture process rule set to exclude database objects that are not support by Oracle Streams. Query theDBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.Note:
Currently, messaging clients cannot dequeue buffered messages.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);
Table 130-5 ADD_GLOBAL_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The type of Oracle Streams client:
|
|
The name of the capture process, apply process, or messaging client. Do not specify an owner. If the specified Oracle Streams client does not exist, then the procedure creates it automatically. If If
If An apply process and a messaging client cannot have the same name. |
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues LCRs. For apply process rules, this is the queue from which an apply process dequeues messages. For messaging client rules, this is the queue from which a messaging client dequeues messages. |
|
If |
|
If |
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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.
See Also:
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' )
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.
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);
Table 130-6 ADD_MESSAGE_PROPAGATION_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The type of the message. The type can be an Oracle built-in type, such as If the type is not an Oracle built-in type, then it is specified as For example, to specify The following datatypes require a size specification: See Also: Oracle Database SQL Language Reference for more information about data types |
|
The rule condition for this message type. The rule variable name specified in the rule condition must be the following: :msg See Also: "Examples" |
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then the procedure creates it automatically. If If |
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the procedure uses the global name of the current database, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed. |
|
If If In either case, the system creates the rule set if it does not exist. |
|
Contains the rule name |
|
If If This procedure cannot change the queue to queue property of an exiting propagation. If the specified propagation exists, then the procedure behaves in the following way for each setting:
See Also: Oracle Streams Concepts and Administration for more information about queue-to-queue propagations |
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.
See Also:
"Operational Notes" and "Propagation Rules for User Messages" for more information about the rules created by this procedure
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 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 message of type usr_msg
has DBS1.NET
for its source_dbname
attribute, HR
for its owner
attribute, and EMPLOYEES
for its name
attribute.
Note:
The quotation marks in the preceding examples are all single quotation marks.This procedure adds a message rule to a rule set of one of the following types of Oracle Streams clients:
Apply process rule for dequeuing user 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 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 Oracle 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.
Note:
Currently, messaging clients cannot dequeue buffered messages.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);
Table 130-7 ADD_MESSAGE_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The type of the message. The type can be an Oracle built-in type, such as If the type is not an Oracle built-in type, then it is specified as For example, to specify The following datatypes require a size specification: See Also: Oracle Database SQL Language Reference for more information about data types |
|
The rule condition for this message type. The rule variable name specified in the rule condition must be the following: :msg See Also: "Examples" |
|
The type of message consumer, either |
|
The name of the Oracle Streams apply process or messaging client. If the specified If the specified If If An apply process and a messaging client cannot have the same name. |
|
The name of the local queue from which messages will be dequeued, specified as For example, to specify a queue named |
|
If If In either case, the system creates the rule set if it does not exist. |
|
Contains the rule name |
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 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.
See Also:
ALTER_APPLY Procedure for more information about setting a message handler for an apply process
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 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 message of type usr_msg
has DBS1.NET
for its source_dbname
attribute, HR
for its owner
attribute, and EMPLOYEES
for its name
attribute.
Note:
The quotation marks in the preceding examples are all single quotation marks.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.
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);
Table 130-8 ADD_SCHEMA_PROPAGATION_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the schema. For example, |
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then the procedure creates it automatically. If If |
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the procedure uses the global name of the current database, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed. |
|
If |
|
If |
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
|
If If This procedure cannot change the queue to queue property of an exiting propagation. If the specified propagation exists, then the procedure behaves in the following way for each setting:
See Also: Oracle Streams Concepts and Administration for more information about queue-to-queue propagations |
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.
See Also:
"Operational Notes" and "Propagation Rules for LCRs" for more information about the rules created by this procedure
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' )
This procedures adds rules to a rule set of one of the following types of Oracle 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 persistent 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.
Caution:
If you add schema rules to the positive rule set for a capture process, then make sure you add rules to the negative capture process rule set to exclude database objects in the schema that are not support by Oracle Streams. Query theDBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.Note:
Currently, messaging clients cannot dequeue buffered messages.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);
Table 130-9 ADD_SCHEMA_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the schema. For example, You can specify a schema that does not yet exist, because Oracle Streams does not validate the existence of the schema. |
|
The type of Oracle Streams client:
|
|
The name of the capture process, apply process, or messaging client. Do not specify an owner. If the specified Oracle Streams client does not exist, then the procedure creates it automatically. If If
If An apply process and a messaging client cannot have the same name. |
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues LCRs. For apply process rules, this is the queue from which an apply process dequeues messages. For messaging client rules, this is the queue from which a messaging client dequeues messages. |
|
If |
|
If |
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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.
See Also:
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' )
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.
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);
Table 130-10 ADD_SUBSET_PROPAGATION_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table specified as The specified table must exist in the same database as the propagation. Also, the specified table cannot have any LOB, |
|
The subset condition. Specify this condition similar to the way you specify conditions in a For example, to specify rows in the
Note: The quotation marks in the preceding example are all single quotation marks. |
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then the procedure creates it automatically. If If |
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the procedure uses the global name of the current database, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed. |
|
If If See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
Contains the system-generated |
|
Contains the system-generated |
|
Contains the system-generated |
|
If If This procedure cannot change the queue to queue property of an exiting propagation. If the specified propagation exists, then the procedure behaves in the following way for each setting:
See Also: Oracle Streams Concepts and Administration for more information about queue-to-queue propagations |
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.
Attention:
Subset rules should only reside in positive rule sets. You should not add subset rules to negative rule sets. Doing so might have unpredictable results because row migration would not be performed on LCRs that are not discarded by the negative rule set.See Also:
"Operational Notes" and "Propagation Rules for LCRs" for more information about the rules created by this procedure
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)
This procedure adds rules to a rule set of one of the following types of Oracle 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.
Synchronous capture rules for capturing changes to a subset of rows in a specified table when the streams_type
parameter is set to sync_capture
. See "Synchronous Capture Rules for DML Changes to Tables" 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 persistent 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, synchronous capture, 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.
Note:
Currently, messaging clients cannot dequeue buffered messages.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);
Table 130-11 ADD_SUBSET_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table specified as The specified table must exist in the same database as the capture process, synchronous capture, apply process, or messaging client. Also, the specified table cannot have any LOB, |
|
The subset condition. Specify this condition similar to the way you specify conditions in a For example, to specify rows in the
Note: The quotation marks in the preceding example are all single quotation marks. |
|
The type of Oracle Streams client:
|
|
The name of the capture process, synchronous capture, apply process, or messaging client. Do not specify an owner. If the specified Oracle Streams client does not exist, then the procedure creates it automatically. If If
If An apply process and a messaging client cannot have the same name. |
|
The name of the local queue, specified as For example, to specify a queue named For capture process or synchronous capture rules, this is the queue into which a capture process or synchronous capture enqueues LCRs. For apply process rules, this is the queue from which an apply process dequeues messages. For messaging client rules, this is the queue from which a messaging client dequeues messages. |
|
If
If
A setting of See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. If For capture process rules, specify For synchronous capture rules, specify the name of the local database. For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify |
|
Contains the system-generated |
|
Contains the system-generated |
|
Contains the system-generated |
Running this procedure generates three rules for the specified capture process, synchronous capture, 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, synchronous capture, apply process, or messaging client uses the rules created for filtering. If the Oracle 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.
Attention:
Subset rules should only reside in positive rule sets. You should not add subset rules to negative rule sets. Doing so might have unpredictable results because row migration would not be performed on LCRs that are not discarded by the negative rule set.See Also:
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)
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.
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);
Table 130-12 ADD_TABLE_PROPAGATION_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table specified as |
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then the procedure creates it automatically. If If |
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the procedure uses the global name of the current database, and the source queue and destination queue must be in the same database. Note: Connection qualifiers are not allowed. |
|
If |
|
If The generated rule evaluates to |
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
|
If If This procedure cannot change the queue to queue property of an exiting propagation. If the specified propagation exists, then the procedure behaves in the following way for each setting:
See Also: Oracle Streams Concepts and Administration for more information about queue-to-queue propagations |
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.
See Also:
"Operational Notes" and "Propagation Rules for LCRs" for more information about the rules created by this procedure
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' )
This procedure adds rules to a rule set of one of the following types of Oracle 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.
Synchronous capture rules for capturing changes to a specified table when the streams_type
parameter is set to sync_capture
. See "Synchronous Capture Rules for DML Changes to Tables" 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 persistent 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, synchronous capture, 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.
Note:
Currently, messaging clients cannot dequeue buffered messages.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);
Table 130-13 ADD_TABLE_RULES Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table specified as You can specify a table that does not yet exist, because Oracle Streams does not validate the existence of the table. |
|
The type of Oracle Streams client:
|
|
The name of the capture process, synchronous capture, apply process, or messaging client. Do not specify an owner. If the specified Oracle Streams client does not exist, then the procedure creates it automatically. If If
If An apply process and a messaging client cannot have the same name. |
|
The name of the local queue, specified as For example, to specify a queue named For capture process or synchronous capture rules, this is the queue into which a capture process or synchronous capture enqueues LCRs. For apply process rules, this is the queue from which an apply process dequeues messages. For messaging client rules, this is the queue from which a messaging client dequeues messages. |
|
If |
|
If The generated rule evaluates to |
|
If
If the rules are added to a positive rule set, then setting this parameter to If
If the rules are added to a positive rule set, then setting this parameter to A setting of In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
The global name of the source database. If For capture process rules, specify For synchronous capture rules, specify the name of the local database. For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify |
|
If If |
|
If If |
|
If If In either case, the system creates the rule set if it does not exist. |
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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, synchronous capture, apply process, or messaging client uses the rules created for filtering.
See Also:
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' )
This procedure removes an Oracle 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.
Attention:
When theCLEANUP_INSTANTIATION_SETUP
procedure is run, the parameter values must match the parameter values specified when the corresponding PRE_INSTANTIATION_SETUP
and POST_INSTANTIATION_SETUP
procedures were run, except for the values of the following parameters: perform_actions
, script_name
, and script_directory_object
.See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedure
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);
Table 130-14 CLEANUP_INSTANTIATION_SETUP Procedure Parameters
Parameter | Description |
---|---|
|
Specify one of the following:
|
|
If The tablespaces in the tablespace set must exist at the source database, but these tablespaces must not exist at the destination database. A directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have If Regardless of the See Also: TABLESPACE_SET Table Type |
|
The global name of the source database. If |
|
The global name of the destination database. A database link from the local database to the destination database with the same name as the global name of the destination database must exist and must be accessible to the user who runs the procedure. If |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
|
The name of the capture processes configured to capture changes in the Oracle Streams configuration. Do not specify an owner. If |
|
The name of the queue table for each queue used by a capture process, specified as If |
|
The name of each queue used by a capture process, specified as If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. If |
|
The name of the user who has |
|
The name of the propagations configured to propagate changes in the Oracle Streams configuration. Do not specify an owner. If |
|
The name of the apply processes configured to apply changes in the Oracle Streams configuration. Do not specify an owner. If |
|
The name of the queue table for each queue used by an apply process, specified as If |
|
The name of each queue used by an apply process, specified as If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. If |
|
The name of the user who has |
|
Specify Specify |
|
If If |
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 an Oracle Streams client. Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients.
Note:
The DELETE_COLUMN
procedure supports the same datatypes supported by Oracle Streams capture processes.
Declarative transformations can transform row LCRs only. These row LCRs can be captured by a capture process, captured by a synchronous capture, or constructed and enqueued by an application. Therefore, a DML rule must be specified when you run this procedure. If a DDL is specified, then the procedure raises an error.
See Also:
Oracle Streams Concepts and Administration for more information about declarative rule-based transformations and about the data types supported by Oracle Streams capture processesDBMS_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');
Table 130-15 DELETE_COLUMN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule, specified as For example, to specify a rule in the |
|
The name of the table from which the column is deleted in the row LCR, specified as |
|
The name of the column deleted from each row LCR that satisfies the rule. |
|
Specify Specify Specify |
|
The order of execution of the transformation. See Also: Oracle Streams Concepts and Administration for more information about transformation ordering |
|
Specify Specify |
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
:
table_name | column_name | step_number | Result |
---|---|---|---|
NULL |
NULL |
NULL |
Remove all delete column transformations for the specified rule. |
NULL |
NULL |
non-NULL |
Remove all delete column transformations with the specified step_number for the specified rule. |
NULL |
non-NULL |
non-NULL |
Remove all delete column transformations with the specified column_name and step_number for the specified rule. |
non-NULL |
NULL |
non-NULL |
Remove all delete column transformations with the specified table_name and step_number for the specified rule. |
NULL |
non-NULL |
NULL |
Remove all delete column transformations with the specified column_name for the specified rule. |
non-NULL |
non-NULL |
NULL |
Remove all delete column transformations with the specified table_name and column_name for the specified rule. |
non-NULL |
NULL |
NULL |
Remove all delete column transformations with the specified table_name for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
Remove all delete column transformations with the specified table_name , column_name , and step_number for the specified rule. |
Returns the tracking label for the current session.
See Also:
SET_MESSAGE_TRACKING ProcedureDBMS_STREAMS_ADM.GET_MESSAGE_TRACKING RETURN VARCHAR2;
This procedure gets information about the system change number (SCN) values to use for Oracle Streams capture and apply processes in an Oracle 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 Oracle Streams environment
To run flashback queries for the corresponding SCN at a source database and destination database in an Oracle Streams single source replication environment
See Also:
Oracle Streams Replication Administrator's Guide for information about point-in-time recovery and flashback queries in an Oracle Streams replication environmentDBMS_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);
Table 130-16 GET_SCN_MAPPING Procedure Parameters
Parameter | Description |
---|---|
|
Name of the apply process which applies logical change records (LCRs) from the source database. The procedure raises an error if the specified apply process does not exist. |
|
The SCN at the source database. For point-in-time recovery, specify the point-in-time recovery SCN at the source database. If the specified SCN is greater than the source commit SCN of the last applied transaction, then |
|
The SCN at the destination database that corresponds to the specified For point-in-time recovery, use this value for the instantiation SCNs at the source database during recovery. |
|
For point in time recovery, the SCN to use for the |
|
Transaction IDs of transactions that were skipped at the For point in time recovery, these transaction IDs should be ignored by the recovery apply process. This parameter is relevant only if the |
This procedure configures an Oracle 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.
Note:
This procedure automatically excludes database objects that are not supported by Oracle Streams from the replication environment by adding rules to the negative rule set of each capture and apply process. Query the DBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.
If the bi_directional
parameter is set to TRUE
, then do not allow data manipulation language (DML) or data definition language (DDL) changes to the destination database while the MAINTAIN_GLOBAL
procedure, or the script generated by the procedure, is running. This restriction does not apply to the source database.
A capture process never captures changes in the SYS
, SYSTEM
, or CTXSYS
schemas. This procedure does not configure replication for these schemas.
See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedureDBMS_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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parametersTable 130-17 MAINTAIN_GLOBAL Procedure Parameters
Parameter | Description |
---|---|
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file is placed. This file remains in this directory after the procedure completes. This parameter is ignored if If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file is transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. This parameter is ignored if If |
|
The name of the Data Pump export dump file. If a file with the specified file name exists in the specified directory for the This parameter is ignored if If |
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. This parameter is ignored if If |
|
Specify whether to perform instantiation and, if instantiation is performed, the type of instantiation:
If this parameter is set to If an instantiated database object does not exist at the destination database, then it is imported at the destination database, including its supplemental logging specifications from the source database and its supporting database objects, such as indexes and triggers. However, if the database object already exists at the destination database before instantiation, then it is not imported at the destination database. Therefore, the supplemental logging specifications from the source database are not specified for the database object at the destination database, and the supporting database objects are not imported. |
This procedure configures an Oracle 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.
Note:
This procedure automatically excludes database objects that are not supported by Oracle Streams in the schemas from the replication environment by adding rules to the negative rule set of each capture and apply process. Query the DBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.
If the bi_directional
parameter is set to TRUE
, then do not allow data manipulation language (DML) or data definition language (DDL) changes to the shared database objects at the destination database while the MAINTAIN_SCHEMAS
procedure, or the script generated by the procedure, is running. This restriction does not apply to the source database.
See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedureDBMS_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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-18Table 130-18 MAINTAIN_SCHEMAS Procedure Parameters
Parameter | Description |
---|---|
|
The schemas to be configured for replication and maintained by Oracle Streams after configuration. The schemas can be specified in the following ways:
If a specified schema does not exist at the source database, then the procedure raises an error. If this parameter is set to |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file is placed. This file remains in this directory after the procedure completes. This parameter is ignored if If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file is transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. This parameter is ignored if If |
|
The name of the Data Pump export dump file. If a file with the specified file name exists in the specified directory for the This parameter is ignored if If |
|
The name of the user who requires If |
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. This parameter is ignored if If |
|
Specify whether to perform instantiation and, if instantiation is performed, the type of instantiation:
If this parameter is set to If an instantiated database object does not exist at the destination database, then it is imported at the destination database, including its supplemental logging specifications from the source database and its supporting database objects, such as indexes and triggers. However, if the database object already exists at the destination database before instantiation, then it is not imported at the destination database. Therefore, the supplemental logging specifications from the source database are not specified for the database object at the destination database, and the supporting database objects are not imported. |
This procedure clones a simple tablespace from a source database at a destination database and uses Oracle 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.
Note:
This procedure is deprecated. It is replaced by theMAINTAIN_SIMPLE_TTS
procedure.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);
Table 130-19 MAINTAIN_SIMPLE_TABLESPACE Procedure Parameters
Parameter | Description |
---|---|
|
The local simple tablespace to be cloned at the destination database and maintained by Oracle Streams. The tablespace must exist at the source database, but it must not exist at the destination database. A directory object must exist for the directory that contains the datafile for the tablespace. The user who invokes this procedure must have If |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file and the datafile for the cloned tablespace are placed. These files remain in this directory after the procedure completes. If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file and the datafile for the cloned tablespace are transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. If |
|
The global name of the destination database. A database link from the source database to the destination database with the same name as the global name of the destination database must exist. If |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
|
Specify Specify |
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.
This procedure does not configure the Oracle Streams environment to maintain DDL changes to the tablespace nor to the database objects in the tablespace. For example, the Oracle 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 Oracle 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:
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 an Oracle Streams administrator at each database, and each database link should be should be created in the Oracle 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 or later databases when this procedure is run under the following conditions:
The procedure is run at an Oracle Database 10g Release 2 or later database.
The setup_streams
parameter is set to TRUE
to configure the Oracle 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 or later 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 or later, 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.
See Also:
Oracle Streams Concepts and Administration for information about configuring an Oracle Streams administratorDefault 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.
See Also:
MAINTAIN_TABLESPACES ProcedureConfiguration 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.
Note:
When this procedure is run with thesetup_streams
parameter set to FALSE
, these views are not populated. Also, the views are not populated when a script generated by this procedure is run.See Also:
"RECOVER_OPERATION Procedure"This procedure clones a simple tablespace from a source database at a destination database and uses Oracle 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.
Note:
This procedure automatically excludes database objects that are not supported by Oracle Streams in the tablespace from the replication environment by adding rules to the negative rule set of each capture and apply process. Query the DBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.
This procedure replaces the deprecated MAINTAIN_SIMPLE_TABLESPACE
procedure.
See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedureDBMS_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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-20Table 130-20 MAINTAIN_SIMPLE_TTS Procedure Parameters
Parameter | Description |
---|---|
|
The local simple tablespace to be cloned at the destination database and maintained by Oracle Streams. The tablespace must exist at the source database, but it must not exist at the destination database. A directory object must exist for the directory that contains the datafile for the tablespace. The user who invokes this procedure must have If |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file and the datafile for the cloned tablespace are placed. These files remain in this directory after the procedure completes. If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file and the datafile for the cloned tablespace are transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. If |
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.
This procedure does not configure the Oracle Streams environment to maintain DDL changes to the tablespace nor to the database objects in the tablespace. For example, the Oracle 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 Oracle 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
.
See Also:
MAINTAIN_TTS ProcedureThis procedure configures an Oracle 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.
Note:
If thebi_directional
parameter is set to TRUE
, then do not allow data manipulation language (DML) or data definition language (DDL) changes to the shared database objects at the destination database while the MAINTAIN_TABLES
procedure, or the script generated by the procedure, is running. This restriction does not apply to the source database.See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedureDBMS_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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-21Table 130-21 MAINTAIN_TABLES Procedure Parameters
Parameter | Description |
---|---|
|
The tables to be configured for replication and maintained by Oracle Streams after configuration. The tables can be specified in the following ways:
Each table should be specified as If a specified table does not exist at the source database, then the procedure raises an error. If this parameter is set to |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file is placed. This file remain in this directory after the procedure completes. This parameter is ignored if If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file is transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. This parameter is ignored if If |
|
The name of the Data Pump export dump file. If a file with the specified file name exists in the specified directory for the This parameter is ignored if If |
|
The name of the user who requires If |
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. This parameter is ignored if If |
|
Specify whether to perform instantiation and, if instantiation is performed, the type of instantiation:
If this parameter is set to If an instantiated database object does not exist at the destination database, then it is imported at the destination database, including its supplemental logging specifications from the source database and its supporting database objects, such as indexes and triggers. However, if the database object already exists at the destination database before instantiation, then it is not imported at the destination database. Therefore, the supplemental logging specifications from the source database are not specified for the database object at the destination database, and the supporting database objects are not imported. Also, if an instantiated table does not exist at the destination database, then this procedure sets the instantiation SCN for the table. However, if an instantiated table already exist at the destination database before instantiation, then this procedure does not set the instantiation SCN for the table. In this case, you must set the instantiation SCN for the table manually after the procedure completes. |
This procedure clones a set of tablespaces from a source database at a destination database and uses Oracle 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.
Note:
This procedure is deprecated. It is replaced by theMAINTAIN_TTS
procedure.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);
Table 130-22 MAINTAIN_TABLESPACES Procedure Parameters
Parameter | Description |
---|---|
|
The local tablespace set to be cloned at the destination database and maintained by Oracle Streams. The tablespaces in the tablespace set must exist at the source database, but these tablespaces must not exist at the destination database. A directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have If See Also: TABLESPACE_SET Table Type |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file and the datafiles that comprise the cloned tablespace set are placed. These files remain in this directory after the procedure completes. If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file and the datafiles that comprise the cloned tablespace set are transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. If |
|
The global name of the destination database. A database link from the source database to the destination database with the same name as the global name of the destination database must exist and must be accessible to the user who runs the procedure. If |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
|
The name of the Data Pump export dump file that contains the specified tablespace set. If a file with the specified file name exists in the specified directory for the If |
|
The name of the queue table for the queue at the source database, specified as |
|
The name of the queue at the source database that will function as the If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. |
|
The name of the user who requires If |
|
The name of the queue table for the queue at the destination database, specified as |
|
The name of the queue at the destination database that will function as the If the schema is not specified, then the queue table owner is the default. The queue owner automatically has privileges to perform all queue operations on the queue. |
|
The name of the user who requires If |
|
The name of each capture process configured to capture changes to the database objects in the tablespace set. Do not specify an owner. If the specified name matches the name of an existing capture process, then the procedure uses the existing capture process and adds the rules for capturing changes to the database objects in the tablespace set to the positive capture process rule set. Note: The capture process name cannot be altered after the capture process is created. |
|
The name of each propagation configured to propagate changes to the database objects in the tablespace set. Do not specify an owner. If the specified name matches the name of an existing propagation, then the procedure uses the existing propagation and adds the rules for propagating changes to the database objects in the tablespace set to the positive propagation rule set. If Note: The propagation name cannot be altered after the propagation is created. |
|
The name of each apply process configured to apply changes to the database objects in the tablespace set. Do not specify an owner. If the specified name matches the name of an existing apply process, then the procedure uses the existing apply process and adds the rules for applying changes to the database objects in the tablespace set to the positive apply process rule set. The specified name must not match the name of an existing messaging client at the destination database. If Note: The apply process name cannot be altered after the apply process is created. |
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. If |
|
Specify Specify |
|
Specify Specify |
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.
See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespace setsAdditional Documentation for this Procedure
The documentation in the following sections applies to the MAINTAIN_TABLESPACES
procedure:
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 an Oracle Streams administrator at each database, and each database link should be should be created in the Oracle 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 or later databases when this procedure is run under the following conditions:
The procedure is run at an Oracle Database 10g Release 2 or later database.
The setup_streams
parameter is set to TRUE
to configure the Oracle 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 or later 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 or later, 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.
See Also:
Oracle Streams Concepts and Administration for information about configuring an Oracle Streams administratorConfiguration 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.
Note:
When this procedure is run with thesetup_streams
parameter set to FALSE
, these views are not populated. Also, the views are not populated when a script generated by this procedure is run.See Also:
"RECOVER_OPERATION Procedure"This procedure clones a set of tablespaces from a source database at a destination database and uses Oracle 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.
Note:
This procedure automatically excludes database objects that are not supported by Oracle Streams in the tablespaces from the replication environment by adding rules to the negative rule set of each capture and apply process. Query the DBA_STREAMS_UNSUPPORTED
data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.
This procedure replaces the deprecated MAINTAIN_TABLESPACES
procedure.
See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedureDBMS_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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-23Table 130-23 MAINTAIN_TTS Procedure Parameters
Parameter | Description |
---|---|
|
The local tablespace set to be cloned at the destination database and maintained by Oracle Streams. The tablespaces in the tablespace set must exist at the source database, but these tablespaces must not exist at the destination database. A directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have If See Also: TABLESPACE_SET Table Type |
|
The directory object for the directory on the computer system running the source database into which the generated Data Pump export dump file and the datafiles that comprise the cloned tablespace set are placed. These files remain in this directory after the procedure completes. If |
|
The directory object for the directory on the computer system running the destination database into which the generated Data Pump export dump file and the datafiles that comprise the cloned tablespace set are transferred. If the source database and destination database are running on the same computer system, then the source and destination directories must be different. If |
|
The name of the Data Pump export dump file that contains the specified tablespace set. If a file with the specified file name exists in the specified directory for the If |
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. If |
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.
See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespace setsAdditional 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.
This procedure merges a stream that is flowing from one capture process with a stream that is flowing from another capture process.
Typically, this procedure is used to merge two streams that were split using the SPLIT_STREAMS
procedure in this package. The SPLIT_STREAMS
procedure clones components of the original stream when it splits the streams. Therefore, the information in this section uses the following terminology:
The stream before it was split off has the original queue, original capture process, and original propagation.
The stream that was split off by the SPLIT_STREAMS
procedure has a cloned queue, cloned capture process, and cloned propagation.
This procedure is called by the MERGE_STREAMS_JOB
procedure. The MERGE_STREAMS_JOB
procedure determines whether the streams are within a user-specified merge threshold so that the streams can be merged safely. If the streams are not within the merge threshold, then the MERGE_STREAMS_JOB
procedure does nothing. Typically, it is best to run the MERGE_STREAMS_JOB
procedure instead of running the MERGE_STREAMS
procedure directly.
However, you can choose to run the MERGE_STREAMS
procedure directly when the following conditions are met:
The problem at the destination of the split stream has been corrected, and the destination queue can accept changes.
The cloned capture process used by the split stream is started and is capturing changes.
The apply process at the destination database is applying the changes captured by the cloned capture process.
The CAPTURE_MESSAGE_CREATE_TIME
in the GV$STREAMS_CAPTURE
view of the cloned capture process has caught up to, or nearly caught up to, the CAPTURE_MESSAGE_CREATE_TIME
of the original capture process. The cloned capture process might never completely catch up to the original capture process. Therefore, you can merge the split stream when the cloned capture process has nearly caught up to the original capture process.
The MERGE_STREAMS
procedure performs the following actions:
Stops the cloned capture process.
Stops the original capture process.
Copies the cloned propagation back to the original propagation. The propagation has the same name as the original propagation after it is copied back.
Starts the original capture process from the lower SCN value of these two SCN values:
The acknowledged SCN of the cloned propagation.
The lowest acknowledged SCN of the other propagations that propagate changes captured by the original capture process.
When the original capture process is started, it might recapture changes that it already captured, or it might capture changes that were already captured by the cloned capture process. In either case, the relevant apply processes will discard any duplicate changes they receive.
Drops the cloned propagation.
Drops the cloned capture process.
Drops the cloned queue.
DBMS_STREAMS_ADM.MERGE_STREAMS( cloned_propagation_name IN VARCHAR2, propagation_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT NULL, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL);
Table 130-24 MERGE_STREAMS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the cloned propagation used by the stream that was split off from the original stream using the You must specify an existing propagation name. Do not specify an owner. |
|
The name of the propagation that is merged back to the original stream. If Specify a non- If a non-
|
|
The name of the queue that is the source queue for the propagation that is merged back. If Specify a non- |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
You can use the MERGE_STREAMS
procedure to merge two streams that were not split using the SPLIT_STREAMS
procedure. Merging streams in this way can save resources and improve performance when a single database is running two or more capture processes.
This procedure determines whether the original capture process and the cloned capture process are within the specified merge threshold. If they are within the merge threshold, then this procedure runs the MERGE_STREAMS
procedure to merge the two streams.
Typically, this procedure is used to merge two streams that were split using the SPLIT_STREAMS
procedure in this package. The SPLIT_STREAMS
procedure clones components of the original stream when it splits the streams. Therefore, the information in this section uses the following terminology:
The stream before it was split off has the original queue, original capture process, and original propagation.
The stream that was split off by the SPLIT_STREAMS
procedure has a cloned queue, cloned capture process, and cloned propagation.
If the auto_merge_threshold
parameter was set to a positive number in the SPLIT_STREAMS
procedure that split the streams, then a merge job runs the MERGE_STREAMS_JOB
procedure automatically according to its schedule. The schedule name is specified for the schedule_name
parameter, and the merge job name is specified for the merge_job_name
parameter when the MERGE_STREAMS_JOB
procedure is run automatically. The merge job and its schedule were created by the SPLIT_STREAMS
procedure.
If the auto_merge_threshold
parameter was set to NULL
or 0
(zero) in the SPLIT_STREAMS
procedure that split the streams, then you can run the MERGE_STREAMS_JOB
procedure manually. In this case, it is not run automatically.
See Also:
Oracle Streams Replication Administrator's Guide for instructions on using the MERGE_STREAMS_JOB
procedure
DBMS_STREAMS_ADM.MERGE_STREAMS_JOB( cloned_propagation_name IN VARCHAR2, propagation_name IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT NULL, merge_threshold IN NUMBER, schedule_name IN VARCHAR2 DEFAULT NULL, merge_job_name IN VARCHAR2 DEFAULT NULL);
Table 130-25 MERGE_STREAMS_JOB Procedure Parameters
Parameter | Description |
---|---|
|
The name of the cloned propagation used by the stream that was split off from the original stream using the You must specify an existing propagation name. Do not specify an owner. |
|
The name of the propagation that is merged back to the original stream. If Specify a non- If a non-
|
|
The name of the queue that is the source queue for the propagation that is merged back. If Specify a non- |
|
The merge threshold in seconds. The value of the Specifically, if the difference, in seconds, between the |
|
The name of the schedule for the merge job. If Specify |
|
The name of the job that merges the streams. If Specify |
You can use the MERGE_STREAMS_JOB
procedure to merge two streams that were not split using the SPLIT_STREAMS
procedure. Merging streams in this way can save resources and improve performance when a single database is running two or more capture processes.
After the MERGE_STREAMS_JOB
procedure completes, you can query the DBA_CAPTURE
and DBA_PROPAGATION
views to determine whether the streams were merged. If the streams were merged, then the cloned capture process and cloned propagation do not appear in these views.
If the streams were merged and the schedule_name
and merge_job_name
parameters were non-NULL
, then the specified schedule and merge job are deleted automatically.
This procedure performs the actions required after instantiation to configure an Oracle 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 Oracle Streams replication configuration, follow these steps:
Run the PRE_INSTANTIATION_SETUP
procedure at the source database.
Perform any necessary instantiation actions.
Run the POST_INSTANTIATION_SETUP
procedure at the source database.
Typically, the Oracle 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.
Attention:
When thePOST_INSTANTIATION_SETUP
procedure is run, the parameter values must match the parameter values specified when the corresponding PRE_INSTANTIATION_SETUP
procedure was run, except for the values of the following parameters: perform_actions
, script_name
, script_directory_object
, and start_processes
.Note:
A capture process never captures changes in theSYS
, SYSTEM
, or CTXSYS
schemas. This procedure does not configure replication for these schemas.See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedure
Oracle Streams Replication Administrator's Guide for information about setting up an Oracle Streams replication environment
Oracle Streams Concepts and Administration for information about completing database maintenance operations
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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-26Table 130-26 POST_INSTANTIATION_SETUP Procedure Parameters
Parameter | Description |
---|---|
|
Specify one of the following:
|
|
If The tablespaces in the tablespace set must exist at the source database, but these tablespaces must not exist at the destination database. Also, a directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have If Regardless of the See Also: TABLESPACE_SET Table Type |
|
If If |
|
Specify the instantiation SCN for the database objects at the destination database if the instantiation SCN was not set during instantiation. The instantiation SCN is not set automatically during RMAN instantiations, but the correct instantiation SCN value should be determined during an RMAN instantiation. See the Oracle Streams Replication Administrator's Guide for more information. Specify |
|
A comma-delimited list of schemas to exclude from the Oracle Streams configuration. Schema rules are added to the negative rule sets of each capture process to exclude these schemas. Specify an asterisk ( If This parameter is valid only if the |
|
Specify what is excluded from the replication configuration in the schemas specified by the |
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.
See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespace setsDestination 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:
Rename the global name of the destination database back to the name specified in the destination_database
parameter.
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
).
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
.
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 Oracle 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.
Note:
When the RMANDUPLICATE
or CONVERT
DATABASE
command is used for database instantiation, the destination database cannot be the capture database.Oracle 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, Oracle Streams components created by the PRE_INSTANTIATION_SETUP
procedure, such as Oracle 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.
Note:
ThePOST_INSTANTIATION_SETUP
procedure only removes Oracle Streams components that were created by the PRE_INSTANTIATION_SETUP
procedure. It does not remove Oracle Streams components that were created in a different way.This procedure performs the actions required before instantiation to configure an Oracle 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 Oracle Streams replication configuration, follow these steps:
Run the PRE_INSTANTIATION_SETUP
procedure at the database that will be the source database in the Stream replication environment.
Perform any necessary instantiation actions.
Run the POST_INSTANTIATION_SETUP
procedure at the source database.
Typically, the Oracle 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.
Note:
A capture process never captures changes in the SYS
, SYSTEM
, or CTXSYS
schemas. This procedure does not configure replication for these schemas.
When the RMAN DUPLICATE
or CONVERT
DATABASE
command is used for database instantiation, the destination database cannot be the capture database.
See Also:
"Procedures That Configure an Oracle Streams Replication Environment" for more information about this procedure
Oracle Streams Replication Administrator's Guide for information about setting up an Oracle Streams replication environment
Oracle Streams Concepts and Administration for information about completing database maintenance operations
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);
See Also:
"Common Parameters for the Configuration Procedures" for descriptions of the procedure parameters that are not in Table 130-27Table 130-27 PRE_INSTANTIATION_SETUP Procedure Parameters
Parameter | Description |
---|---|
|
Specify one of the following:
|
|
If The tablespaces in the tablespace set must exist at the source database, but these tablespaces must not exist at the destination database. Also, a directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have If Regardless of the See Also: TABLESPACE_SET Table Type |
|
The name of the queue table for each queue used by a capture process, specified as If |
|
If If |
|
A comma-delimited list of schemas to exclude from the Oracle Streams configuration. Schema rules are added to the negative rule sets of each capture process to exclude these schemas. Specify an asterisk ( If This parameter is valid only if the |
|
Specify what to exclude from the replication configuration in the schemas specified by the |
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.
See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespace setsSpecify 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 Oracle 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 Oracle Streams from the Oracle 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.
This procedure removes all Oracle Streams data dictionary information at the local database for the specified object. You can use this procedure to remove Oracle Streams metadata that is not needed currently and will not be needed in the future.
DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG( source_database IN VARCHAR2, source_object_name IN VARCHAR2, source_object_type IN VARCHAR2);
Table 130-28 PURGE_SOURCE_CATALOG Procedure Parameters
Parameter | Description |
---|---|
|
The global name of the source database containing the object. If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify |
|
The name of the object specified as |
|
Type of the object. Currently, |
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.This procedure provides options for an Oracle 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:
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:
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.
Note:
To run theRECOVER_OPERATION
procedure, both databases must be Oracle Database 10g Release 2 or later databases.DBMS_STREAMS_ADM.RECOVER_OPERATION( script_id IN RAW, operation_mode IN VARCHAR2 DEFAULT 'FORWARD');
Table 130-29 RECOVER_OPERATION Procedure Parameters
Parameter | Description |
---|---|
|
The operation id of the procedure invocation that is being rolled forward, rolled back, or purged. Query the |
|
If If If |
This procedure removes the specified ANYDATA
queue.
Specifically, this procedure performs the following actions:
Waits until all current enqueue and dequeue transactions commit.
Stops the queue, which means that no further enqueues into the queue or dequeues from the queue are allowed.
Drops the queue.
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.
If the cascade
parameter is set to TRUE
, then drops all of the Oracle Streams clients that are using the queue.
Note:
The specified queue must be aANYDATA
queue.DBMS_STREAMS_ADM.REMOVE_QUEUE( queue_name IN VARCHAR2, cascade IN BOOLEAN DEFAULT FALSE, drop_unused_queue_table IN BOOLEAN DEFAULT TRUE);
Table 130-30 REMOVE_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the queue to remove, specified as |
|
If If |
|
If If |
This procedure removes the specified rule or all rules from the rule set associated with the specified capture process, synchronous capture, 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.
Note:
If a rule was automatically created by the system, and you want to drop the rule, then you should use this procedure to remove the rule instead of theDBMS_RULE_ADM.DROP_RULE
procedure. If you use the DBMS_RULE_ADM.DROP_RULE
procedure, then some metadata about the rule might remain.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);
Table 130-31 REMOVE_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule to remove, specified as For example, to specify a rule in the |
|
The type of Oracle Streams client:
|
|
The name of the Oracle Streams client, which can be a capture process, synchronous capture, propagation, apply process, or messaging client. Do not specify an owner. If the specified Oracle Streams client does not exist, but there is metadata in the data dictionary that associates the rule with this client, then the procedure removes the metadata. If the specified Oracle Streams client does not exist, and there is no metadata in the data dictionary that associates the rule with this client, then the procedure raises an error. |
|
If If If |
|
If If |
This procedure removes the Oracle Streams configuration at the local database.
DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
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 rule sets and rules that were created using the DBMS_STREAMS_ADM
package.
Drops unused rule sets that were used by capture processes, propagations, apply processes, and messaging clients, and removes the rules in these rule sets. These rules and rule sets are removed regardless of whether they were created using the DBMS_STREAMS_ADM
package or the DBMS_RULE_ADM
package.
This procedure stops capture processes and apply processes before it drops them.
This procedure does not drop rule sets or rules if they meet both of the following conditions:
The rule sets or rules were created using the DBMS_RULE_ADM
package.
The rule sets or rules were not used by a capture process, propagation, apply process, or messaging client.
Attention:
Running this procedure is dangerous. You should run this procedure only if you are sure you want to remove the entire Oracle Streams configuration at a database.Note:
Running this procedure repeatedly does not cause errors. If the procedure fails to complete, then you can run it again.
This procedure commits multiple times.
See Also:
STOP_CAPTURE Procedure in the DBMS_CAPTURE_ADM
package
STOP_APPLY Procedure in the DBMS_APPLY_ADM
package
REMOVE_RULE Procedure in the DBMS_STREAMS_ADM
package
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 an Oracle Streams client. Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients.
Note:
The RENAME_COLUMN
procedure supports the same datatypes supported by Oracle Streams capture processes.
Declarative transformations can transform row LCRs only. These row LCRs can be captured by a capture process, captured by a synchronous capture, or constructed and enqueued by an application. Therefore, a DML rule must be specified when you run this procedure. If a DDL is specified, then the procedure raises an error.
See Also:
Oracle Streams Concepts and Administration for more information about declarative rule-based transformations and about the data types supported by Oracle Streams capture processesDBMS_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');
Table 130-32 RENAME_COLUMN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule, specified as For example, to specify a rule in the |
|
The name of the table in which the column is renamed in the row LCR, specified as |
|
The name of the column to be renamed in each row LCR that satisfies the rule. |
|
The new name of the column in each row LCR that satisfies the rule. |
|
Specify Specify Specify |
|
The order of execution of the transformation. See Also: Oracle Streams Concepts and Administration for more information about transformation ordering |
|
Specify Specify |
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
:
table_name | from_column_name | to_column_name | step_number | Result |
---|---|---|---|---|
NULL |
NULL |
NULL |
NULL |
Remove all rename column transformations for the specified rule. |
NULL |
NULL |
NULL |
non-NULL |
Remove all rename column transformations with the specified step_number for the specified rule. |
NULL |
NULL |
non-NULL |
non-NULL |
Remove all rename column transformations with the specified to_column_name and step_number for the specified rule. |
NULL |
non-NULL |
non-NULL |
non-NULL |
Remove all rename column transformations with the specified table_name and step_number for the specified rule. |
NULL |
NULL |
non-NULL |
NULL |
Remove all rename column transformations with the specified column_name for the specified rule. |
non-NULL |
NULL |
non-NULL |
NULL |
Remove all rename column transformations with the specified table_name and column_name for the specified rule. |
NULL |
non-NULL |
NULL |
NULL |
Remove all rename column transformations with the specified table_name for the specified rule. |
NULL |
non-NULL |
non-NULL |
NULL |
Remove all rename column transformations with the specified table_name , column_name, and step_number for the specified rule. |
non-NULL |
NULL |
non-NULL |
NULL |
Remove all rename column transformations with the specified table_name , column_name, and step_number for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
NULL |
Remove all rename column transformations with the specified table_name , column_name, and step_number for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
non-NULL |
Remove all rename column transformations with the specified table_name , column_name, and step_number for the specified rule. |
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 an Oracle Streams client. Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients.
Note:
Declarative transformations can transform row LCRs only. These row LCRs can be captured by a capture process, captured by a synchronous capture, or constructed and enqueued by an application. Therefore, a DML rule must be specified when you run this procedure. If a DDL is specified, then the procedure raises an error.See Also:
Oracle Streams Concepts and Administration for more information about declarative rule-based transformationsDBMS_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');
Table 130-33 RENAME_SCHEMA Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule, specified as For example, to specify a rule in the |
|
The name of the schema to be renamed in each row LCR that satisfies the rule. |
|
The new name of the schema in each row LCR that satisfies the rule. |
|
The order of execution of the transformation. See Also: Oracle Streams Concepts and Administration for more information about transformation ordering |
|
Specify Specify |
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
:
from_schema_name | to_schema_name | step_number | Result |
---|---|---|---|
NULL |
NULL |
NULL |
Remove all rename schema transformations for the specified rule. |
NULL |
NULL |
non-NULL |
Remove all rename schema transformations with the specified step_number for the specified rule. |
NULL |
non-NULL |
non-NULL |
Remove all rename schema transformations with the specified to_schema_name and step_number for the specified rule. |
non-NULL |
NULL |
non-NULL |
Remove all rename schema transformations with the specified from_schema_name and step_number for the specified rule. |
NULL |
non-NULL |
NULL |
Remove all rename schema transformations with the specified to_schema_name for the specified rule. |
non-NULL |
non-NULL |
NULL |
Remove all rename schema transformations with the specified from_schema_name and to_schema_name for the specified rule. |
non-NULL |
NULL |
NULL |
Remove all rename schema transformations with the specified from_schema_name for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
Remove all rename schema transformations with the specified from_schema_name , to_schema_name , and step_number for the specified rule. |
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 an Oracle Streams client. Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients.
Note:
Declarative transformations can transform row LCRs only. These row LCRs can be captured by a capture process, captured by a synchronous capture, or constructed and enqueued by an application. Therefore, a DML rule must be specified when you run this procedure. If a DDL is specified, then the procedure raises an error.See Also:
Oracle Streams Concepts and Administration for more information about declarative rule-based transformationsDBMS_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');
Table 130-34 RENAME_TABLE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule, specified as For example, to specify a rule in the |
|
The name of the table to be renamed in each row LCR that satisfies the rule, specified as |
|
The new name of the table in each row LCR that satisfies the rule, specified as The transformation can rename the table only, the schema only, or the table and the schema. If the schema is not specified, then the current user is the default. |
|
The order of execution of the transformation. See Also: Oracle Streams Concepts and Administration for more information about transformation ordering |
|
Specify Specify |
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
:
from_table_name | to_table_name | step_number | Result |
---|---|---|---|
NULL |
NULL |
NULL |
Remove all rename table transformations for the specified rule. |
NULL |
NULL |
non-NULL |
Remove all rename table transformations with the specified step_number for the specified rule. |
NULL |
non-NULL |
non-NULL |
Remove all rename table transformations with the specified to_table_name and step_number for the specified rule. |
non-NULL |
NULL |
non-NULL |
Remove all rename table transformations with the specified from_table_name and step_number for the specified rule. |
NULL |
non-NULL |
NULL |
Remove all rename table transformations with the specified to_table_name for the specified rule. |
non-NULL |
non-NULL |
NULL |
Remove all rename table transformations with the specified from_table_name and to_table_name for the specified rule. |
non-NULL |
NULL |
NULL |
Remove all rename table transformations with the specified from_table_name for the specified rule. |
non-NULL |
non-NULL |
non-NULL |
Remove all rename table transformations with the specified from_table_name , to_table_name , and step_number for the specified rule. |
This procedure sets a notification for messages that can be dequeued by a specified Oracle 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.
Note:
Currently, messaging clients cannot dequeue buffered messages.
The DBMS_AQ
package can also configure notifications. The DBMS_AQ
package provides some notification features that are not available in DBMS_STREAMS_ADM
package, such as buffered message notifications and notification grouping by time.
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');
Table 130-35 SET_MESSAGE_NOTIFICATION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the Oracle Streams messaging client. Do not specify an owner. For example, if the user |
|
The action to be performed on message notification. Specify one of the following:
See Also: Examples for more information about message notification procedures |
|
The type of notification. Specify one of the following:
The type must match the specification for the |
|
The context of the notification. The context must be specified using ANYDATA.ConvertRaw(HEXTORAW('FF')) The notification context is passed the PL/SQL procedure in procedure notifications and is not relevant for mail or HTTP notifications. |
|
If If |
|
The name of a local For example, to specify a queue named |
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 Database 2 Day + Data Replication and Integration Guide for an example that configures message notification to automatically dequeue of messages of interest
Oracle Streams Concepts and Administration for an example that configures e-mail notifications
Oracle Streams Advanced Queuing User's Guide 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 Oracle Streams
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information aboutPLSQLCALLBACK
data structuresSets the tracking label for logical change records (LCRs) produced by the current session. This procedure affects only the current session. Any LCRs produced by the current session are tracked, including captured LCRs and persistent LCRs.
Note:
The tracking label set by this procedure does not track non-LCR messages.See Also:
GET_MESSAGE_TRACKING FunctionDBMS_STREAMS_ADM.SET_MESSAGE_TRACKING( tracking_label IN VARCHAR2 DEFAULT 'Streams_tracking', actions IN NUMBER DEFAULT DBMS_STREAMS_ADM.ACTION_MEMORY);
Table 130-36 SET_MESSAGE_TRACKING Procedure Parameters
Parameter | Description |
---|---|
|
The label used to track the LCRs produced by the session. Set this parameter to The size limit for a label is 4,000 bytes. |
|
If If The values specified for this parameter are enumerated constants. Enumerated constants must be prefixed with the package name. |
This procedure sets or removes the transformation function name for a custom rule-based transformation.
DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION( rule_name IN VARCHAR2, transform_function IN VARCHAR2);
Table 130-37 SET_RULE_TRANSFORM_FUNCTION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule whose rule-based transformation function you are setting or removing, specified as For example, to specify a rule in the |
|
Either the name of the transformation function to be used in the rule-based transformation for the rule or If you specify a transformation function name, then specify an existing function in one of the following forms:
If the function is in a package, then you must specify the If the If you specify |
The following sections contain usage notes for this procedure:
Transformation Function Signature
A custom rule-based transformation function always operates on one message, but it can return one message or many messages. A custom rule-based transformation function that returns one message is a one-to-one transformation function. A one-to-one transformation function must have the following signature:
FUNCTION user_function ( parameter_name IN ANYDATA) RETURN ANYDATA;
Here, user_function
stands for the name of the function and parameter_name
stands for the name of the parameter passed to the function. The parameter passed to the function is an ANYDATA
encapsulation of a message, and the function must return an ANYDATA
encapsulation of a message.
A custom rule-based transformation function that can return more than one message is a one-to-many transformation function. A one-to-many transformation function must have the following signature:
FUNCTION user_function ( parameter_name IN ANYDATA) RETURN STREAMS$_ANYDATA_ARRAY;
Here, user_function
stands for the name of the function and parameter_name
stands for the name of the parameter passed to the function. The parameter passed to the function is an ANYDATA
encapsulation of a message, and the function must return an array that contains zero or more ANYDATA
encapsulations of a message. If the array contains zero ANYDATA
encapsulations of a message, then the original message is discarded.
The STREAMS$_ANYDATA_ARRAY
type is an Oracle-supplied type that has the following definition:
CREATE OR REPLACE TYPE SYS.STREAMS$_ANYDATA_ARRAY AS VARRAY(2147483647) of ANYDATA /
The following restrictions apply to custom rule-based transformations that use one-to-many functions:
Rules that are associated with one-to-many functions are supported for Oracle Streams capture processes only. These rules must not be added to rule sets used by other Oracle Streams clients, including propagations, apply processes, and messaging clients.
One-to-many functions only can operate on row logical change records (row LCRs). They cannot operate on DDL LCRs.
Row LCRs returned by a one-to-many function cannot contain piecewise LOB, LONG
, or LONG
RAW
operations.
The one-to-many function must return row LCRs in the correct order. The order of row LCRs in the array (starting from index 1) is the order that the row LCRs will be executed in the transaction.
When an apply process dequeues row LCRs that are the result of a transformation by a one-to-many function, the apply process uses the instantiation SCN of the LCR passed to the one-to-many function for all of row LCRs.
Note:
An error is raised if a one-to-one or one-to-many transformation function returns NULL
.
Only one custom rule-based transformation can be specified for a particular rule. You cannot specify both a one-to-one and a one-to-many transformation function for the same rule.
For any LCR constructed and returned by a custom rule-based transformation, the source_database_name
, transaction_id
, and scn
parameter values must match the values in the original LCR. Oracle automatically specifies the values in the original LCR for these parameters, even if an attempt is made to construct LCRs with different values.
This procedure modifies the specified rule's action context to specify the transformation. A rule's action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE
for a message. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Oracle Streams. The Oracle Streams clients include capture processes, synchronous captures, propagations, apply processes, and messaging clients. The information in an action context is an object of type SYS.RE$NV_LIST
, which consists of a list of name-value pairs.
A custom rule-based transformation in Oracle Streams always consists of the following name-value pair in an action context:
If the function is a one-to-one transformation function, then the name is STREAMS$_TRANSFORM_FUNCTION
. If the function is a one-to-many transformation function, then the name is STREAMS$_ARRAY_TRANS_FUNCTION
.
The value is a ANYDATA
instance containing a PL/SQL function name specified as a VARCHAR2
. This function performs the transformation.
User Who Calls the Transformation Function
The user that calls the transformation function must have EXECUTE
privilege on the function. The following list describes which user calls the transformation function:
If a transformation is specified for a rule used by a capture process, then the user who calls the transformation function is the capture user for the capture process.
If a transformation is specified for a rule used by a synchronous capture, then the user who calls the transformation function is the capture user for the synchronous capture.
If a transformation is specified for a rule used by a propagation, then the user who calls the transformation function is the owner of the source queue for the propagation.
If a transformation is specified on a rule used by an apply process, then the user who calls the transformation function is the apply user for the apply process.
If a transformation is specified on a rule used by a messaging client, then the user who calls the transformation function is the user who invokes the messaging client.
This procedure does not verify that the specified transformation function exists. If the function does not exist, then an error is raised when an Oracle Streams client tries to invoke the transformation function.
This procedure creates a queue table and a ANYDATA
queue.
DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table IN VARCHAR2 DEFAULT 'streams_queue_table', storage_clause IN VARCHAR2 DEFAULT NULL, queue_name IN VARCHAR2 DEFAULT 'streams_queue', queue_user IN VARCHAR2 DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);
Table 130-38 SET_UP_QUEUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the queue table specified as If the queue table owner is not specified, then the procedure specifies the user who runs this procedure automatically as the queue table owner. Queue table names can be a maximum of 24 bytes. |
|
The storage clause for queue table The storage parameter is included in the If a tablespace is not specified here, then the procedure creates the queue table and all its related objects in the default user tablespace of the user who runs this procedure. If a tablespace is specified here, then the procedure creates the queue table and all its related objects in the tablespace specified in the storage clause. If See Also: Oracle Database SQL Language Reference for more information about storage clauses |
|
The name of the queue that will function as the If the schema is not specified, then the procedure uses the queue table owner. The owner of the queue table must also be the owner of the queue. The queue owner automatically has privileges to perform all queue operations on the queue. If the schema is not specified for this parameter, and the queue table owner is not specified in Queue names can be a maximum of 24 bytes. |
|
The name of the user who requires If |
|
The comment for the queue |
Set up includes the following actions:
If the specified queue table does not exist, then this procedure runs the CREATE_QUEUE_TABLE
procedure in the DBMS_AQADM
package to create the queue table with the specified storage clause. If this procedure creates the queue table, then it creates a multiple consumer ANYDATA
queue that is both a secure queue and a transactional queue.
Also, if the database is Oracle Database 10g release 2 or later, the sort_list
setting in CREATE_QUEUE_TABLE
is set to commit_time
. If the database is a release prior to Oracle Database 10g release 2, the sort_list
setting in CREATE_QUEUE_TABLE
is set to enq_time
.
If the specified queue table already exists, then the queue uses the properties of the existing queue table.
If the specified queue name does not exist, then this procedure runs the CREATE_QUEUE
procedure in the DBMS_AQADM
package to create the queue.
This procedure starts the queue.
If a queue user is specified, then this procedure configures this user as a secure queue user of the queue and grants ENQUEUE
and DEQUEUE
privileges on the queue to the specified queue user.
To configure the queue user as a secure queue user, this procedure creates an Advanced Queuing agent with the same name as the user name, if one does not already exist. If an agent with this name already exists and is associated with the queue user only, then it is used. SET_UP_QUEUE
then runs the ENABLE_DB_ACCESS
procedure in the DBMS_AQADM
package, specifying the agent and the user.
Note:
To enqueue messages into and dequeue messages from a queue, a queue user must have EXECUTE
privilege on the DBMS_STREAMS_MESSAGING
package or the DBMS_AQ
package. The SET_UP_QUEUE
procedure does not grant this privilege.
If the agent that SET_UP_QUEUE
tries to create already exists and is associated with a user other than the user specified by queue_user
, then the procedure raises an error. In this case, rename or remove the existing agent, and retry SET_UP_QUEUE
.
See Also:
Oracle Streams Concepts and Administration for more information about secure queue usersThis procedure splits one stream flowing from a capture process off from all of the other streams flowing from the capture process.
This procedure is intended for an Oracle Streams replication environment in which a capture process captures changes that are propagated to two or more destination databases. When one destination of a propagation stops accepting the captured changes, the changes remain in the capture process queue. The queue can grow and begin to spill messages to the hard disk, degrading the performance of the Oracle Streams environment. A destination might stop accepting changes for several reasons. For example, the destination database might be down.
Specifically, this procedure performs the following actions:
Creates a new queue at the database running the capture process. The new queue is called the cloned queue because it is a clone of the queue used by the original stream. The new queue will be used by the new, cloned capture process, and it will be the source queue for the new, cloned propagation.
Creates a new propagation that propagates messages from the source queue created in Step 1 to the existing destination queue. The new propagation is called the cloned propagation because it is a clone of the propagation used by the original stream. The cloned propagation uses the same rule set as the original propagation.
Stops the capture process.
Queries the acknowledge SCN for the original propagation. The acknowledged SCN is the last SCN acknowledged by the apply process that applies the changes sent by the propagation.
Creates a new capture process. The new capture process is called the cloned capture process because it is a clone of the capture process used by the original stream. The procedure sets the start SCN for the cloned capture process to the value of the acknowledged SCN queried in Step 4. The cloned capture process uses the same rule set as the original capture process.
Drops the original propagation.
Starts the original capture process with the start SCN set to the acknowledged SCN queried in Step 4.
If the auto_merge_threshold
parameter is set to a positive number, then creates an Oracle Scheduler job to run the MERGE_STREAMS_JOB
procedure at set intervals according to its schedule. When the two streams are within the specified merge threshold, the MERGE_STREAMS_JOB
procedure runs the MERGE_STREAMS
procedure to merge the streams automatically.
After the SPLIT_STREAMS
procedure has finished running, the cloned capture process is disabled. When the problem at the destination database is solved, and the destination queue can accept changes, you should start the cloned capture process using the START_CAPTURE
procedure in the DBMS_CAPTURE_ADM
package.
Note:
If the original capture process is a downstream capture process, then you must configure the cloned capture process to read the redo log from the source database before you start the cloned capture process.See Also:
Oracle Streams Replication Administrator's Guide for instructions on using the SPLIT_STREAMS
procedure
DBMS_STREAMS_ADM.SPLIT_STREAMS( propagation_name IN VARCHAR2, cloned_propagation_name IN VARCHAR2 DEFAULT NULL, cloned_queue_name IN VARCHAR2 DEFAULT NULL, cloned_capture_name IN VARCHAR2 DEFAULT NULL, perform_actions IN BOOLEAN DEFAULT TRUE, script_name IN VARCHAR2 DEFAULT NULL, script_directory_object IN VARCHAR2 DEFAULT NULL, auto_merge_threshold IN NUMBER DEFAULT NULL, schedule_name IN OUT VARCHAR2, merge_job_name IN OUT VARCHAR2);
Table 130-39 SPLIT_STREAMS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the propagation that cannot send messages to its destination queue. The specified propagation is the propagation for the stream that is being split off from the other streams. You must specify an existing propagation name. Do not specify an owner. |
|
The name of the new propagation created by this procedure for the stream that is split off. If |
|
The name of the new queue created by this procedure for the stream that is split off. If |
|
The name of the new capture process created by this procedure for the stream that is split off. If |
|
If If Specify
|
|
If non- If non- If If |
|
The directory object for the directory on the local computer system into which the generated script is placed. If the If |
|
If a positive number is specified, then the stream that was split off is automatically merged back into all of the other streams flowing from the capture process by an Oracle Scheduler job. The job runs the If The |
|
The Oracle Scheduler schedule name, specified as If If If If this procedure creates a schedule, the schedule starts when the procedure completes. You can modify the schedule to control how often the merge job is run. If an existing schedule name is specified, an error is raised. |
|
The Oracle Scheduler job name, specified as If If If If an existing job name is specified, an error is raised. |
See Also:
Oracle Database Administrator's Guide for information about Oracle Scheduler