Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

96
DBMS_STREAMS_ADM

The DBMS_STREAMS_ADM package, one of a set of Streams packages, provides administrative interfaces for adding and removing simple rules for capture, propagation, apply, and dequeue at the table, schema, and database level.

See Also:

This chapter contains the following topics:


Using DBMS_STREAMS_ADM


Overview

The DBMS_STREAMS_ADM package, one of a set of Streams packages, provides administrative interfaces 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 Streams metadata, such as data dictionary information.

If you require more sophisticated rules, then you can use the DBMS_RULE_ADM package.


Rules

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

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

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

The following sections describe each type of rule in detail:

Capture Process Rules for Changes in the Redo Log

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

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 this 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:

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 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:

"Summary of DBMS_CAPTURE_ADM Subprograms" for more information about the CREATE_CAPTURE procedure and the procedures that prepare database objects for instantiation

Propagation Rules for LCRs

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

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

Propagation Rules for User Messages

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

See Also:

"ADD_MESSAGE_PROPAGATION_RULE Procedures"

Apply Process Rules for LCRs

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

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 events 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 events only at the local database and can apply only captured events. To create an apply process that applies events at a remote database or an apply process that applies user-enqueued events, use the CREATE_APPLY procedure in the DBMS_APPLY_ADM package.

You also can 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.

See Also:

"ALTER_APPLY Procedure" and "CREATE_APPLY Procedure"

Apply Process Rules for User Messages

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

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

See Also:

Messaging Client Rules for LCRs

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

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

Messaging Client Rules for User Messages

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

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

See Also:

"ADD_MESSAGE_RULE Procedures"


Users

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

Capture User

The following procedures can create a capture process:

If one of these procedures creates a capture process, then it configures the current user as the capture_user. This user captures changes that satisfy the capture process rule sets. This user must have the necessary privileges to capture changes. The procedure grants the capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue.

See Also:

"CREATE_CAPTURE Procedure" for information about the privileges required to capture changes

Propagation User

The following procedures can create a propagation:

When a propagation is created, a propagation job is created also if one does not exist for the database link used by the propagation. If a propagation job is created when one of these procedures is run, then the user who runs the procedure owns the propagation job.


Note:

The source queue owner performs the propagation, but the propagation job is owned by the user who creates it. These two users may or may not be the same.


See Also:

"CREATE_PROPAGATION Procedure" for more information about the required privileges

Apply User

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. This user applies changes that satisfy the apply process rule sets and runs user-defined apply handlers. 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 the apply_user parameter)

Messaging Client User

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.


Summary of DBMS_STREAMS_ADM Subprograms

Table 96-1  DBMS_STREAMS_ADM Package Subprograms
Subprogram Description

ADD_GLOBAL_PROPAGATION_RULES Procedures

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

ADD_GLOBAL_RULES Procedures

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

ADD_MESSAGE_PROPAGATION_RULE Procedures

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

ADD_MESSAGE_RULE Procedures

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

ADD_SCHEMA_PROPAGATION_RULES Procedures

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

ADD_SCHEMA_RULES Procedures

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

ADD_SUBSET_PROPAGATION_RULES Procedures

Adds subset rules to the positive rule set for a propagation, and creates the specified propagation if it does not exist

ADD_SUBSET_RULES Procedures

Adds subset rules to the positive 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

ADD_TABLE_PROPAGATION_RULES Procedure

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

ADD_TABLE_RULES Procedures

Adds table 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

GET_SCN_MAPPING Procedure

Gets information about the system change number (SCN) values to use for Streams capture and apply processes in a Streams replication environment

MAINTAIN_SIMPLE_TABLESPACE Procedure

Clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases

MAINTAIN_TABLESPACES Procedure

Clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases

PURGE_SOURCE_CATALOG Procedure

Removes all Streams data dictionary information at the local database for the specified object

REMOVE_QUEUE Procedure

Removes the specified SYS.AnyData queue

REMOVE_RULE Procedure

Removes the specified rule or all rules from the rule set associated with the specified capture process, apply process, or propagation

REMOVE_STREAMS_CONFIGURATION Procedure

Removes the Streams configuration at the local database.

SET_MESSAGE_NOTIFICATION Procedure

Sets a notification for messages that can be dequeued by a specified Streams messaging client from a specified queue

SET_RULE_TRANSFORM_FUNCTION Procedure

Sets or removes the transformation function name for a rule-based transformation

SET_UP_QUEUE Procedure

Creates a queue table and a queue for use with the capture, propagate, and apply functionality of Streams


Note:

All procedures commit unless specified otherwise.



ADD_GLOBAL_PROPAGATION_RULES Procedures

These procedures either add global rules to the positive rule set for a propagation, or add global rules to the negative rule set for a propagation, and create the specified propagation if it does not exist.


Note:

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


Syntax

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

Parameters

Table 96-2  ADD_GLOBAL_PROPAGATION_RULES Procedure Parameters
Parameter Description

streams_name

The name of the propagation. Do not specify an owner.

If the specified propagation does not exist, then it is created automatically.

If NULL and a propagation exists for the same source queue and destination queue (including database link), then this propagation is used.

If NULL and no propagation exists for the same source queue and destination queue (including database link), then a propagation is created automatically with a system-generated name.

source_queue_name

The name of the source queue, specified as [schema_name.]queue_name. The current database must contain the source queue, and the queue must be SYS.AnyData type.

For example, to specify a source queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter.

If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the destination queue, including a database link, specified as [schema_name.]queue_name[@dblink_name], if the destination queue is in a remote database. The queue must be SYS.AnyData type.

For example, to specify a destination queue named streams_queue in the strmadmin schema and use a database link named dbs2.net, enter strmadmin.streams_queue@dbs2.net for this parameter.

If the schema is not specified, then the current user is the default.

If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database.

Note: Connection qualifiers are not allowed.

include_dml

If true, then creates a rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether an LCR has a non-NULL tag. If the rules are added to the positive rule set for the propagation, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the propagation, then whether an LCR is discarded does not depend on the LCR's tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if an LCR has a NULL Streams tag. If the rules are added to the positive rule set for the propagation, then an LCR is considered for propagation only when the LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the propagation, then an LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. The source database is where the changes originated. If NULL, then no condition regarding the source database is added to the generated rules.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

Oracle recommends that you specify a source database for propagation rules.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the propagation.

If inclusion_rule is false, then the rules are added to the negative rule set for the propagation.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the global rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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. The overloaded ADD_GLOBAL_PROPAGATION_RULES procedure generates the rule names for DML and DDL changes. A propagation uses the rules created for filtering.

If no propagation job exists for the database link specified in the destination_queue_name parameter when this procedure is run, then a propagation job is created for use by the propagation. If a propagation job already exists for the specified database link, then the propagation uses the existing propagation job.

This procedure also configures propagation using the current user, and establish a default propagation schedule. Only one propagation is allowed between the source queue and destination queue.


Note:
  • Currently, a single propagation job propagates all events that use a particular database link, even if the database link is used by multiple propagations to propagate events to multiple destination queues.
  • For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

See Also:

Examples

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

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

ADD_GLOBAL_RULES Procedures

These procedures add rules to a rule set of one of the following types of Streams clients:

Syntax

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

Parameters

Table 96-3  ADD_GLOBAL_RULES Procedure Parameters
Parameter Description

streams_type

The type of Streams client:

  • Specify capture for a capture process.
  • Specify apply for an apply process.
  • Specify dequeue for a messaging client.

streams_name

The name of the capture process, apply process, or messaging client. Do not specify an owner.

If the specified Streams client does not exist, then it is created automatically.

If NULL, if streams_type is capture or dequeue, and if one relevant capture process or messaging client for the queue exists, then the relevant Streams client is used. If no relevant Streams client exists for the queue, then a Streams client is created automatically with a system-generated name. If NULL and multiple Streams clients of the specified streams_type for the queue exist, then an error is raised.

If NULL, if streams_type is apply, and if one relevant apply process exists, then the relevant apply process is used. The relevant apply process is identified in one of the following ways:

  • If one existing apply process has the source database specified in source_database and uses the queue specified in queue_name, then this apply process is used.
  • If source_database is NULL and one existing apply process is using the queue specified in queue_name, then this apply process is used.

If NULL and no relevant apply process exists, then an apply process is created automatically with a system-generated name. If NULL and multiple relevant apply processes exist, then an error is raised.

An apply process and a messaging client cannot have the same name.

queue_name

The name of the local queue, specified as [schema_name.]queue_name. The current database must contain the queue, and the queue must be SYS.AnyData type.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

For capture process rules, this is the queue into which a capture process enqueues events. For apply process rules, this is the queue from which an apply process dequeues events. For messaging client rules, this is the queue from which a messaging client dequeues events.

include_dml

If true, then creates a rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether a redo entry or LCR has a non-NULL tag. If the rules are added to the positive rule set for the process, then a redo entry is always considered for capture, and an LCR is always considered for apply, regardless of whether the redo entry or LCR has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the process, then whether a redo entry or LCR is discarded does not depend on the tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if a redo entry or LCR has a NULL Streams tag. If the rules are added to the positive rule set for the process, then a redo entry is considered for capture, and an LCR is considered for apply, only when the redo entry or LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the process, then a redo entry or LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules.

For capture process rules, specify NULL or the global name of the local database if you are creating a capture process locally at the source database. If you are creating a capture process at a downstream database, then specify the source database of the changes that will be captured.

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 events, then the apply process can apply events from only one capture process at one source database.

For messaging client rules, specify NULL if you do not want the rules created by this procedure to have a condition for the source database. Specify a source database if you want the rules created by this procedure to have a condition for the source database. The source database is part of the information in an LCR, and user-constructed LCRs may or may not have this information.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the Streams client.

If inclusion_rule is false, then the rules are added to the negative rule set for the Streams client.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the global rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

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

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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. The overloaded ADD_GLOBAL_RULES procedure generates the rule names for DML and DDL changes. A capture process, apply process, or messaging client uses the rules created for filtering.

See Also:

Examples

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

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

ADD_MESSAGE_PROPAGATION_RULE Procedures

These procedures add a message rule to the positive rule set for a propagation, or add a message rule to the negative rule set for a propagation, and create the specified propagation if it does not exist.


Note:

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


Syntax

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

Parameters

Table 96-4  ADD_MESSAGE_PROPAGATION_RULE Procedure Parameters
Parameter Description

message_type

The type of the message. If the type is not an Oracle built-in type, then specified as [schema_name.]type_name. If the schema is not specified, then the current user is the default.

For example, to specify a type named usr_msg in the strmadmin schema, enter strmadmin.usr_msg for this parameter.

rule_condition

The rule condition for this message type. The rule variable name specified in the rule condition must be the following:

:msg

streams_name

The name of the propagation. Do not specify an owner.

If the specified propagation does not exist, then it is created automatically.

If NULL and a propagation exists for the same source queue and destination queue (including database link), then this propagation is used.

If NULL and no propagation exists for the same source queue and destination queue (including database link), then a propagation is created automatically with a system-generated name.

source_queue_name

The name of the source queue, specified as [schema_name.]queue_name. The current database must contain the source queue, and the queue must be SYS.AnyData type.

For example, to specify a source queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter.

If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the destination queue, including a database link, specified as [schema_name.]queue_name[@dblink_name], if the destination queue is in a remote database. The queue must be SYS.AnyData type.

For example, to specify a destination queue named streams_queue in the strmadmin schema and use a database link named dbs2.net, enter strmadmin.streams_queue@dbs2.net for this parameter.

If the schema is not specified, then the current user is the default.

If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database.

Note: Connection qualifiers are not allowed.

inclusion_rule

If inclusion_rule is true, then the rule is added to the positive rule set for the propagation.

If inclusion_rule is false, then the rule is added to the negative rule set for the propagation.

In either case, the system creates the rule set if it does not exist.

rule_name

Contains the rule name

Usage Notes

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.

If no propagation job exists for the database link specified in the destination_queue_name parameter when this procedure is run, then a propagation job is created for use by the propagation. If a propagation job already exists for the specified database link, then the propagation uses the existing propagation job.

This procedure also configures propagation using the current user, and establishes a default propagation schedule. Only one propagation is allowed between the source queue and destination queue.


Note:
  • Currently, a single propagation job propagates all events that use a particular database link, even if the database link is used by multiple propagations to propagate events to multiple destination queues.
  • For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

See Also:

Examples

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 may be specified:

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

Note:

The quotation marks in the preceding example are all single quotation marks.


This rule condition evaluates to TRUE if a user-enqueued message of type usr_msg has dbs1.net for its source_dbname attribute, hr for its owner attribute, and employees for its name attribute.


ADD_MESSAGE_RULE Procedures

These procedures a message rule to a rule set of one of the following types of Streams clients:

Syntax

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

Parameters

Table 96-5  ADD_MESSAGE_RULE Procedure Parameters
Parameter Description

message_type

The type of the message. If the type is not an Oracle built-in type, then specified as [schema_name.]type_name. If the schema is not specified, then the current user is the default.

For example, to specify a type named usr_msg in the strmadmin schema, enter strmadmin.usr_msg for this parameter.

rule_condition

The rule condition for the message type. The rule variable name specified in the rule condition must be the following:

:msg

streams_type

The type of message consumer, either apply for apply process or dequeue for messaging client

streams_name

The name of the Streams apply process or messaging client.

If the specified streams_type is apply, then specify the name of the apply process. Do not specify an owner. If the specified apply process does not exist, then it is created automatically with a system-generated name.

If the specified streams_type is dequeue, then specify the messaging client. For example, if the user strmadmin is the messaging client, then specify strmadmin.

If NULL and a relevant apply process or messaging client for the queue exists, then the relevant apply process or messaging client is used. If NULL and multiple relevant apply processes or messaging clients for the queue exist, then an error is raised.

If NULL and no Streams client of the specified streams_type exists for the queue, then an apply process or messaging client is created automatically with a system-generated name.

An apply process and a messaging client cannot have the same name.

queue_name

The name of the local queue from which messages will be dequeued, specified as [schema_name.]queue_name. The current database must contain the queue, and the queue must be SYS.AnyData type.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

inclusion_rule

If inclusion_rule is true, then the rule is added to the positive rule set for the apply process or messaging client.

If inclusion_rule is false, then the rule is added to the negative rule set for the apply process or messaging client.

In either case, the system creates the rule set if it does not exist.

rule_name

Contains the rule name

Usage Notes

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

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

See Also:

Examples

You specify the condition for this rule using the rule_condition parameter. For example, 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 may be specified:

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

Note:

The quotation marks in the preceding example are all single quotation marks.



ADD_SCHEMA_PROPAGATION_RULES Procedures

These procedures either add schema rules to the positive rule set for a propagation, or add schema rules to the negative rule set for a propagation, and create the specified propagation if it does not exist.


Note:

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


Syntax

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

Parameters

Table 96-6  ADD_SCHEMA_PROPAGATION_RULES Procedure Parameters
Parameter Description

schema_name

The name of the schema. For example, hr.

streams_name

The name of the propagation. Do not specify an owner.

If the specified propagation does not exist, then it is created automatically.

If NULL and a propagation exists for the same source queue and destination queue (including database link), then this propagation is used.

If NULL and no propagation exists for the same source queue and destination queue (including database link), then a propagation is created automatically with a system-generated name.

source_queue_name

The name of the source queue, specified as [schema_name.]queue_name. The current database must contain the source queue, and the queue must be SYS.AnyData type.

For example, to specify a source queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter.

If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the destination queue, including a database link, specified as [schema_name.]queue_name[@dblink_name], if the destination queue is in a remote database. The queue must be SYS.AnyData type.

For example, to specify a destination queue named streams_queue in the strmadmin schema and use a database link named dbs2.net, enter strmadmin.streams_queue@dbs2.net for this parameter.

If the schema is not specified, then the current user is the default.

If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database.

Note: Connection qualifiers are not allowed.

include_dml

If true, then creates a rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether an LCR has a non-NULL tag. If the rules are added to the positive rule set for the propagation, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the propagation, then whether an LCR is discarded does not depend on the LCR's tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if an LCR has a NULL Streams tag. If the rules are added to the positive rule set for the propagation, then an LCR is considered for propagation only when the LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the propagation, then an LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. The source database is where the change originated. If NULL, then no condition regarding the source database is added to the generated rules.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

Oracle recommends that you specify a source database for propagation rules.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the propagation.

If inclusion_rule is false, then the rules are added to the negative rule set for the propagation.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the schema rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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. For the overloaded ADD_SCHEMA_PROPAGATION_RULES procedure this generates the rule names for DML and DDL changes. A propagation uses the rules created for filtering.

If no propagation job exists for the database link specified in the destination_queue_name parameter when this procedure is run, then a propagation job is created for use by the propagation. If a propagation job already exists for the specified database link, then the propagation uses the existing propagation job.

This procedure also configures propagation using the current user, and establishes a default propagation schedule. Only one propagation is allowed between the source queue and the destination queue.


Note:
  • Currently, a single propagation job propagates all events that use a particular database link, even if the database link is used by multiple propagations to propagate events to multiple destination queues.
  • For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

See Also:

Examples

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

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

ADD_SCHEMA_RULES Procedures

These procedures add rules to a rule set of one of the following types of Streams clients:

Syntax

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

Parameters

Table 96-7  ADD_SCHEMA_RULES Procedure Parameters
Parameter Description

schema_name

The name of the schema. For example, hr.

You can specify a schema that does not yet exist, because Streams does not validate the existence of the schema.

streams_type

The type of Streams client:

  • Specify capture for a capture process.
  • Specify apply for an apply process.
  • Specify dequeue for a messaging client.

streams_name

The name of the capture process, apply process, or messaging client. Do not specify an owner.

If the specified Streams client does not exist, then it is created automatically.

If NULL, if streams_type is capture or dequeue, and if one relevant capture process or messaging client for the queue exists, then the relevant Streams client is used. If no relevant Streams client exists for the queue, then a Streams client is created automatically with a system-generated name. If NULL and multiple Streams clients of the specified streams_type for the queue exist, then an error is raised.

If NULL, if streams_type is apply, and if one relevant apply process exists, then the relevant apply process is used. The relevant apply process is identified in one of the following ways:

  • If one existing apply process has the source database specified in source_database and uses the queue specified in queue_name, then this apply process is used.
  • If source_database is NULL and one existing apply process is using the queue specified in queue_name, then this apply process is used.

If NULL and no relevant apply process exists, then an apply process is created automatically with a system-generated name. If NULL and multiple relevant apply processes exist, then an error is raised.

An apply process and a messaging client cannot have the same name.

queue_name

The name of the local queue, specified as [schema_name.]queue_name. The current database must contain the queue, and the queue must be SYS.AnyData type.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

For capture process rules, this is the queue into which a capture process enqueues events. For apply process rules, this is the queue from which an apply process dequeues events. For messaging client rules, this is the queue from which a messaging client dequeues events.

include_dml

If true, then creates a rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether a redo entry or LCR has a non-NULL tag. If the rules are added to the positive rule set for the process, then a redo entry is always considered for capture, and an LCR is always considered for apply, regardless of whether the redo entry or LCR has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the process, then whether a redo entry or LCR is discarded does not depend on the tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if a redo entry or LCR has a NULL Streams tag. If the rules are added to the positive rule set for the process, then a redo entry is considered for capture, and an LCR is considered for apply, only when the redo entry or LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the process, then a redo entry or LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules.

For capture process rules, specify NULL or the global name of the local database if you are creating a capture process locally at the source database. If you are creating a capture process at a downstream database, then specify the source database of the changes that will be captured.

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 events, then the apply process can apply events from only one capture process at one source database.

For messaging client rules, specify NULL if you do not want the rules created by this procedure to have a condition for the source database. Specify a source database if you want the rules created by this procedure to have a condition for the source database. The source database is part of the information in an LCR, and user-constructed LCRs may or may not have this information.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the Streams client.

If inclusion_rule is false, then the rules are added to the negative rule set for the Streams client.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the schema rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

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

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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. The overloaded ADD_SCHEMA_RULES procedure generates the rule names for DML and DDL changes. A capture process, apply process, or messaging client uses the rules created for filtering.

See Also:

Examples

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

:dml.get_object_owner() = 'HR' AND :dml.is_null_tag() = 'Y'

ADD_SUBSET_PROPAGATION_RULES Procedures

These procedures add 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. They also configure propagation using the current user and establishes a default propagation schedule. The procedures enables propagation of LCRs for the specified table, subject to filtering conditions.


Note:

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


Syntax

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

Parameters

Table 96-8  ADD_SUBSET_PROPAGATION_RULES Procedure Parameters
Parameter Description

table_name

The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

The specified table must exist in the same database as the propagation. Also, the specified table cannot have any LOB, LONG, or LONG RAW columns currently or in the future.

dml_condition

The subset condition. You specify this condition similar to the way you specify conditions in a WHERE clause in SQL.

For example, to specify rows in the hr.employees table where the salary is greater than 4000 and the job_id is SA_MAN, enter the following as the condition:

' salary > 4000 and job_id = ''SA_MAN'' '

Note: The quotation marks in the preceding example are all single quotation marks.

streams_name

The name of the propagation. Do not specify an owner.

If the specified propagation does not exist, then it is created automatically.

If NULL and a propagation exists for the same source queue and destination queue (including database link), then this propagation is used.

If NULL and no propagation exists for the same source queue and destination queue (including database link), then a propagation is created automatically with a system-generated name.

source_queue_name

The name of the source queue, specified as [schema_name.]queue_name. The current database must contain the source queue, and the queue must be SYS.AnyData type.

For example, to specify a source queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter.

If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the destination queue, including a database link, specified as [schema_name.]queue_name[@dblink_name], if the destination queue is in a remote database. The queue must be SYS.AnyData type.

For example, to specify a destination queue named streams_queue in the strmadmin schema and use a database link named dbs2.net, enter strmadmin.streams_queue@dbs2.net for this parameter.

If the schema is not specified, then the current user is the default.

If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database.

Note: Connection qualifiers are not allowed.

include_tagged_lcr

If true, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database.

If false, then an LCR is considered for propagation only when the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. The source database is where the change originated. If NULL, then no condition regarding the source database is added to the generated rules.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

Oracle recommends that you specify a source database for propagation rules.

insert_rule_name

Contains the system-generated INSERT rule name. This rule handles inserts, as well as updates that must be converted into inserts.

update_rule_name

Contains the system-generated UPDATE rule name. This rule handles updates that remain updates.

delete_rule_name

Contains the system-generated DELETE rule name. This rule handles deletes, as well as updates that must be converted into deletes

Usage Notes

Only one propagation is allowed between the source queue and the 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:

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 a positive rule set is created 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. The system-generated rule name is 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.

For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

If no propagation job exists for the database link specified in the destination_queue_name parameter when this procedure is run, then a propagation job is created for use by the propagation. If a propagation job already exists for the specified database link, then the propagation uses the existing propagation job.


Attention:

Subset rules should only reside in positive rule sets. You should not add subset rules to negative rule sets. Doing so may have unpredictable results because row migration would not be performed on LCRs that are not discarded by the negative rule set.



Note:

Currently, a single propagation job propagates all events that use a particular database link, even if the database link is used by multiple propagations to propagate events to multiple destination queues.


See Also:

Examples

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

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

ADD_SUBSET_RULES Procedures

These procedures add rules to a rule set of one of the following types of Streams clients:

Syntax

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

Parameters

Table 96-9  ADD_SUBSET_RULES Procedure Parameters
Parameter Description

table_name

The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

The specified table must exist in the same database as the capture process, apply process, or messaging client. Also, the specified table cannot have any LOB, LONG, or LONG RAW columns currently or in the future.

dml_condition

The subset condition. You specify this condition similar to the way you specify conditions in a WHERE clause in SQL.

For example, to specify rows in the hr.employees table where the salary is greater than 4000 and the job_id is SA_MAN, enter the following as the condition:

' salary > 4000 and job_id = ''SA_MAN'' '

Note: The quotation marks in the preceding example are all single quotation marks.

streams_type

The type of Streams client:

  • Specify capture for a capture process.
  • Specify apply for an apply process.
  • Specify dequeue for a messaging client.

streams_name

The name of the capture process, apply process, or messaging client. Do not specify an owner.

If the specified Streams client does not exist, then it is created automatically.

If NULL, if streams_type is capture or dequeue, and if one relevant capture process or messaging client for the queue exists, then the relevant Streams client is used. If no relevant Streams client exists for the queue, then a Streams client is created automatically with a system-generated name. If NULL and multiple Streams clients of the specified streams_type for the queue exist, then an error is raised.

If NULL, if streams_type is apply, and if one relevant apply process exists, then the relevant apply process is used. The relevant apply process is identified in one of the following ways:

  • If one existing apply process has the source database specified in source_database and uses the queue specified in queue_name, then this apply process is used.
  • If source_database is NULL and one existing apply process is using the queue specified in queue_name, then this apply process is used.

If NULL and no relevant apply process exists, then an apply process is created automatically with a system-generated name. If NULL and multiple relevant apply processes exist, then an error is raised.

An apply process and a messaging client cannot have the same name.

queue_name

The name of the local queue, specified as [schema_name.]queue_name. The current database must contain the queue, and the queue must be SYS.AnyData type.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

For capture process rules, this is the queue into which a capture process enqueues events. For apply process rules, this is the queue from which an apply process dequeues events. For messaging client rules, this is the queue from which a messaging client dequeues events.

include_tagged_lcr

If true, then a redo entry is always considered for capture and an LCR is always considered for apply or dequeue, regardless of whether redo entry or LCR has a non-NULL tag. This setting is appropriate for a full (for example, standby) copy of a database.

If false, then a redo entry is considered for capture and an LCR is considered for apply or dequeue only when the redo entry or the LCR contains a NULL tag. A setting of false is often specified in update-anywhere configurations to avoid sending a change back to its source database.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules.

For capture process rules, specify NULL or the global name of the local database if you are creating a capture process locally at the source database. If you are creating a capture process at a downstream database, then specify the source database of the changes that will be captured.

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 events, then the apply process can apply events from only one capture process at one source database.

For messaging client rules, specify NULL if you do not want the rules created by this procedure to have a condition for the source database. Specify a source database if you want the rules created by this procedure to have a condition for the source database. The source database is part of the information in an LCR, and user-constructed LCRs may or may not have this information.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

insert_rule_name

Contains the system-generated INSERT rule name. This rule handles inserts, as well as updates that must be converted into inserts.

update_rule_name

Contains the system-generated UPDATE rule name. This rule handles updates that remain updates.

delete_rule_name

Contains the system-generated DELETE rule name. This rule handles deletes, as well as updates that must be converted into deletes

Usage Notes

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

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

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

A capture process, apply process, or messaging client uses the rules created for filtering. If the Streams client does not have a positive rule set, then a positive rule set is created automatically, and the rules for 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 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. The system-generated rule name is 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 may have unpredictable results because row migration would not be performed on LCRs that are not discarded by the negative rule set.


See Also:

Examples

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

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

ADD_TABLE_PROPAGATION_RULES Procedure

These procedures add table rules to the positive rule set for a propagation, or add table rules to the negative rule set for a propagation, and create the specified propagation if it does not exist.


Note:

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


Syntax

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

Parameters

Table 96-10  ADD_TABLE_PROPAGATION_RULES Procedure Parameters
Parameter Description

table_name

The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

streams_name

The name of the propagation. Do not specify an owner.

If the specified propagation does not exist, then it is created automatically.

If NULL and a propagation exists for the same source queue and destination queue (including database link), then this propagation is used.

If NULL and no propagation exists for the same source queue and destination queue (including database link), then a propagation is created automatically with a system-generated name.

source_queue_name

The name of the source queue, specified as [schema_name.]queue_name. The current database must contain the source queue, and the queue must be SYS.AnyData type.

For example, to specify a source queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter.

If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the destination queue, including a database link, specified as [schema_name.]queue_name[@dblink_name], if the destination queue is in a remote database. The queue must be SYS.AnyData type.

For example, to specify a destination queue named streams_queue in the strmadmin schema and use a database link named dbs2.net, enter strmadmin.streams_queue@dbs2.net for this parameter.

If the schema is not specified, then the current user is the default.

If the database link is omitted, then the global name of the current database is used, and the source queue and destination queue must be in the same database.

Note: Connection qualifiers are not allowed.

include_dml

If true, then creates a rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether an LCR has a non-NULL tag. If the rules are added to the positive rule set for the propagation, then an LCR is always considered for propagation, regardless of whether it has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the propagation, then whether an LCR is discarded does not depend on the LCR's tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if an LCR has a NULL Streams tag. If the rules are added to the positive rule set for the propagation, then an LCR is considered for propagation only when the LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the propagation, then an LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. The source database is where the change originated. If NULL, then no condition regarding the source database is added to the generated rules.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

Oracle recommends that you specify a source database for propagation rules.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the propagation.

If inclusion_rule is false, then the rules are added to the negative rule set for the propagation.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the table rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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 overloaded ADD_TABLE_PROPAGATION_RULES procedure generates the rule names for DML and DDL changes. A propagation uses the rules created for filtering.

If no propagation job exists for the database link specified in the destination_queue_name parameter when this procedure is run, then a propagation job is created for use by the propagation. If a propagation job already exists for the specified database link, then the propagation uses the existing propagation job.

This procedure also configures propagation using the current user, and establishes a default propagation schedule. Only one propagation is allowed between the source queue and the destination queue.


Note:
  • Currently, a single propagation job propagates all events that use a particular database link, even if the database link is used by multiple propagations to propagate events to multiple destination queues.
  • For a propagation to work properly, the owner of the source queue must have the necessary privileges to propagate events.

See Also:

Examples

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

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

ADD_TABLE_RULES Procedures

These procedures add rules to a rule set of one of the following types of Streams clients:

Syntax

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

Parameters

Table 96-11  ADD_TABLE_RULES Procedure Parameters
Parameter Description

table_name

The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

You can specify a table that does not yet exist, because Streams does not validate the existence of the table.

streams_type

The type of Streams client:

  • Specify capture for a capture process.
  • Specify apply for an apply process.
  • Specify dequeue for a messaging client.

streams_name

The name of the capture process, apply process, or messaging client. Do not specify an owner.

If the specified Streams client does not exist, then it is created automatically.

If NULL, if streams_type is capture or dequeue, and if one relevant capture process or messaging client for the queue exists, then the relevant Streams client is used. If no relevant Streams client exists for the queue, then a Streams client is created automatically with a system-generated name. If NULL and multiple Streams clients of the specified streams_type for the queue exist, then an error is raised.

If NULL, if streams_type is apply, and if one relevant apply process exists, then the relevant apply process is used. The relevant apply process is identified in one of the following ways:

  • If one existing apply process has the source database specified in source_database and uses the queue specified in queue_name, then this apply process is used.
  • If source_database is NULL and one existing apply process is using the queue specified in queue_name, then this apply process is used.

If NULL and no relevant apply process exists, then an apply process is created automatically with a system-generated name. If NULL and multiple relevant apply processes exist, then an error is raised.

An apply process and a messaging client cannot have the same name.

queue_name

The name of the local queue, specified as [schema_name.]queue_name. The current database must contain the queue, and the queue must be SYS.AnyData type.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

For capture process rules, this is the queue into which a capture process enqueues events. For apply process rules, this is the queue from which an apply process dequeues events. For messaging client rules, this is the queue from which a messaging client dequeues events.

include_dml

If true, then creates a DML rule for DML changes. If false, then does not create a DML rule. NULL is not permitted.

include_ddl

If true, then creates a DDL rule for DDL changes. If false, then does not create a DDL rule. NULL is not permitted.

include_tagged_lcr

If true, then no condition regarding Streams tags is added to the generated rules. Therefore, these rules can evaluate to TRUE regardless of whether a redo entry or LCR has a non-NULL tag. If the rules are added to the positive rule set for the process, then a redo entry is always considered for capture, and an LCR is always considered for apply, regardless of whether the redo entry or LCR has a non-NULL tag. If the rules are added to a positive rule set, then setting this parameter to true is appropriate for a full (for example, standby) copy of a database. If the rules are added to the negative rule set for the process, then whether a redo entry or LCR is discarded does not depend on the tag.

If false, then adds a condition to each generated rule that causes the rule to evaluate to TRUE only if a redo entry or LCR has a NULL Streams tag. If the rules are added to the positive rule set for the process, then a redo entry is considered for capture, and an LCR is considered for apply, only when the redo entry or LCR contains a NULL tag. If the rules are added to a positive rule set, then setting this parameter to false may be appropriate in update-anywhere configurations to avoid sending a change back to its source database. If the rules are added to the negative rule set for the process, then a redo entry or LCR can be discarded only if it has a NULL tag.

In most cases, specify true for this parameter if the inclusion_rule parameter is set to false.

See Also: Oracle Streams Replication Administrator's Guide for more information about tags

source_database

The global name of the source database. If NULL, then no condition regarding the source database is added to the generated rules.

For capture process rules, specify NULL or the global name of the local database if you are creating a capture process locally at the source database. If you are creating a capture process at a downstream database, then specify the source database of the changes that will be captured.

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 events, then the apply process can apply events from only one capture process at one source database.

For messaging client rules, specify NULL if you do not want the rules created by this procedure to have a condition for the source database. Specify a source database if you want the rules created by this procedure to have a condition for the source database. The source database is part of the information in an LCR, and user-constructed LCRs may or may not have this information.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

dml_rule_name

If include_dml is true, then contains the DML rule name.

If include_dml is false, then contains a NULL.

ddl_rule_name

If include_ddl is true, then contains the DDL rule name.

If include_ddl is false, then contains a NULL.

inclusion_rule

If inclusion_rule is true, then the rules are added to the positive rule set for the Streams client.

If inclusion_rule is false, then the rules are added to the negative rule set for the Streams client.

In either case, the system creates the rule set if it does not exist.

and_condition

If non-NULL, appends the specified condition to the system-generated rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the table rules generated by the procedure evaluate to true only if the Streams tag is the hexadecimal equivalent of '02', specify the following condition:

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

The :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule.

Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also: Chapter 174, "Logical Change Record TYPEs"

Usage Notes

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

This procedure creates DML and DDL rules automatically based on include_dml and include_ddl parameter values, respectively. A system-generated rule name is 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 overloaded ADD_TABLE_RULES procedure generates the rule names for DML and DDL changes. A capture process, apply process, or messaging client uses the rules created for filtering.

See Also:

Examples

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

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

GET_SCN_MAPPING Procedure

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

Syntax

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

Parameters

Table 96-12  GET_SCN_MAPPING Procedure Parameters
Parameter Description

apply_name

Name of the apply process which applies LCRs from the source database. An error is raised if the specified apply process does not exist.

src_pit_scn

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 NULL is returned for both dest_start_scn and dest_instantiation_scn. In this case, no values can be returned for these parameters because the corresponding transaction has not been applied at the destination database yet.

dest_instantiation_scn

The SCN at the destination database that corresponds to the specified src_pit_scn at the source database.

For point-in-time recovery, use this value for the instantiation SCNs at the source database during recovery.

dest_start_scn

For point in time recovery, the SCN to use for the start_scn parameter for the recovery capture process.

dest_skip_txn_ids

Transaction IDs of transactions that were skipped at the dest_instantiation_scn because the apply process was applying non-dependent transactions out of order.

For point in time recovery, these transaction IDs should be ignored by the recovery apply process.

This parameter is relevant only if the commit_serialization for the apply process that applied these transactions was set to none, and the transactions were applied out of order.


MAINTAIN_SIMPLE_TABLESPACE Procedure

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

Syntax

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

Parameters

Table 96-13  MAINTAIN_SIMPLE_TABLESPACE Procedure Parameters
Parameter Description

tablespace_name

The local simple tablespace to be cloned at the destination database and maintained by Streams.

A directory object must exist for the directory that contains the datafile for the tablespace. The user who invokes this procedure must have READ privilege on this directory object.

If NULL, then an error is raised.

source_directory_object

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 NULL, then an error is raised.

destination_directory_object

The directory on the computer system running the destination database into which the generated Data Pump dump file and the datafile for the cloned tablespace are transferred.

If NULL, then an error is raised.

destination_database

The global name of the destination database. A database link from the source database to the destination database with the same name must exist.

If NULL, then an error is raised.

setup_streams

If true, then the MAINTAIN_SIMPLE_TABLESPACE procedure performs the necessary actions to maintain the tablespace directly.

If false, then the MAINTAIN_SIMPLE_TABLESPACE procedure does not perform the necessary actions to maintain the tablespace directly.

You specify false when this procedure is generating a file that you will edit and then run. An error is raised if you specify false and either of the following parameters is NULL:

  • script_name
  • script_directory_object

script_name

If non-NULL and the setup_streams parameter is false, then the name of the script generated by this procedure. The script contains all of the statements used to maintain the specified tablespace. If a file with the specified file name exists in the specified directory for the script_directory_object parameter, then the statements are appended to the existing file.

If non-NULL and the setup_streams parameter is true, then this procedure generates the specified script and performs the actions to maintain the specified tablespace directly.

If NULL and the setup_streams parameter is true, then this procedure does not generate a file and performs the actions to maintain the specified tablespace directly. If NULL and the setup_streams parameter is false, then an error is raised.

script_directory_object

The directory on the local computer system into which the generated script is placed.

If the script_name parameter is NULL, then this parameter is ignored, and this procedure does not generate a file.

If NULL and the script_name parameter is non-NULL, then an error is raised.

bi_directional

Specify true to configure bi-directional replication between the current database and the database specified in destination_database. Both databases are configured as source and destination databases, and propagations are configured between the databases to propagate events.

Specify false to configure one way replication from the current database to the database specified in destination_database. A capture process is configured at the current database, a propagation is configured to propagate events from the current database to the destination database, and an apply process is configured at the destination database.

Usage Notes

Single Source and Bi-Directional Configurations

This procedure either sets up a single source Streams configuration with the local database as the source database, or it sets up a bi-directional Streams configuration with both databases acting as source and destination databases. The bi_directional parameter controls whether the Streams configuration is single source or bi-directional. The information about single source, bi-directional, and multi-directional Streams environments in the description for the MAINTAIN_TABLESPACES procedure also applies to this procedure.

Change Cycling and the MAINTAIN_SIMPLE_TABLESPACE Procedure

If the bi_directional is set to true, then this procedure configures bi-directional replication, but this procedure cannot be used to configure multi-directional replication where changes may be cycled back to a source database by a third database in the environment. The information about change cycling in the description for the MAINTAIN_TABLESPACES procedure also applies to this procedure.

DDL Changes Not Maintained

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

Privileges and Database Links Required by the MAINTAIN_SIMPLE_TABLESPACE Procedure

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

In addition, 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.

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

See Also:

Oracle Streams Concepts and Administration for information about configuring a Streams administrator

Actions Performed by the MAINTAIN_SIMPLE_TABLESPACE Procedure

The actions performed by this procedure are the same as the actions performed by the MAINTAIN_TABLESPACES procedure, except that this procedure only can be used for a simple tablespace. A simple tablespace is a single, self-contained tablespace that uses only one datafile. This procedure cannot be used for a non simple tablespace or a set of tablespaces.

This procedure uses the default values for the parameters in the MAINTAIN_TABLESPACES procedure that do not exist in the MAINTAIN_SIMPLE_TABLESPACES 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.


Note:
  • To view all of the statements run by this procedure in detail, you can use the procedure to generate a script and then view the script in a text editor.
  • Each specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes the MAINTAIN_SIMPLE_TABLESPACE procedure must have READ and WRITE privilege on each one.
  • If the source and destination databases are running on different platforms, then this procedure, or the script generated by this procedure, converts transferred datafile to the appropriate platform automatically.

See Also:

MAINTAIN_TABLESPACES Procedure

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

Syntax

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

Parameters

Table 96-14  MAINTAIN_TABLESPACES Procedure Parameters
Parameter Description

tablespace_names

The local tablespace set to be cloned at the destination database and maintained by Streams.

A directory object must exist for each directory that contains the datafiles for the tablespace set. The user who invokes this procedure must have READ privilege on these directory objects.

If NULL, then an error is raised.

See Also: TABLESPACE_SET Type

source_directory_object

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 NULL, then an error is raised.

destination_directory_object

The directory on the computer system running the destination database into which the generated Data Pump dump file and the datafiles that comprise the cloned tablespace set are transferred.

If NULL, then an error is raised.

destination_database

The global name of the destination database. A database link from the source database to the destination database with the same name must exist and must be accessible to the user who runs the procedure.

If NULL, then an error is raised.

setup_streams

If true, then the MAINTAIN_TABLESPACES procedure performs the necessary actions to maintain the tablespaces directly.

If false, then the MAINTAIN_TABLESPACES procedure does not perform the necessary actions to maintain the tablespaces directly.

You specify false when this procedure is generating a file that you will edit and then run. An error is raised if you specify false and either of the following parameters is NULL:

  • script_name
  • script_directory_object

script_name

If non-NULL and the setup_streams parameter is false, then the name of the script generated by this procedure. The script contains all of the statements used to maintain the specified tablespace set. If a file with the specified file name exists in the specified directory for the script_directory_object parameter, then the statements are appended to the existing file.

If non-NULL and the setup_streams parameter is true, then this procedure generates the specified script and performs the actions to maintain the specified tablespace directly.

If NULL and the setup_streams parameter is true, then this procedure does not generate a file and performs the actions to maintain the specified tablespace set directly. If NULL and the setup_streams parameter is false, then an error is raised.

script_directory_object

The directory on the local computer system into which the generated script is placed.

If the script_name parameter is NULL, then this parameter is ignored, and this procedure does not generate a file.

If NULL and the script_name parameter is non-NULL, then an error is raised.

dump_file_name

The name of the Data Pump dump file that contains the specified tablespace set. If a file with the specified file name exists in the specified directory for the source_directory_object or destination_directory_object parameter, then an error is raised.

If NULL, then the dump file name is generated by the system. In this case, the dump file name is expatnn.dmp, where nn is a sequence number. The sequence number is incremented to produce a dump file with a unique name in the source directory.

source_queue_table

The name of the queue table for the queue at the source database, specified as [schema_name.]queue_table_name. For example, strmadmin.streams_queue_table. If the schema is not specified, then the current user is the default.

If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner.

source_queue_name

The name of the queue at the source database that will function as the SYS.AnyData queue, specified as [schema_name.]queue_name. For example, strmadmin.streams_queue.

If the schema is not specified, then it defaults to the queue table owner. The queue owner automatically has privileges to perform all queue operations on the queue.

source_queue_user

The name of the user who requires ENQUEUE and DEQUEUE privileges for the queue at the source database. This user is also configured as a secure queue user of the queue. The queue user cannot grant these privileges to other users because they are not granted with the GRANT option.

If NULL, then no privileges are granted. You can also grant queue privileges to the appropriate users using the DBMS_AQADM package.

destination_queue_table

The name of the queue table for the queue at the destination database, specified as [schema_name.]queue_table_name. For example, strmadmin.streams_queue_table. If the schema is not specified, then the current user is the default.

If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner.

destination_queue_name

The name of the queue at the destination database that will function as the SYS.AnyData queue, specified as [schema_name.]queue_name. For example, strmadmin.streams_queue.

If the schema is not specified, then it defaults to 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 destination_queue_table, then the current user is the default.

destination_queue_user

The name of the user who requires ENQUEUE and DEQUEUE privileges for the queue at the destination database. This user is also configured as a secure queue user of the queue. The queue user cannot grant these privileges to other users because they are not granted with the GRANT option.

If NULL, then no privileges are granted. You can also grant queue privileges to the appropriate users using the DBMS_AQADM package.

capture_name

The name of the capture process configured to capture DML changes to the tables in the tablespace set at the source database. Do not specify an owner.

If the specified name matches the name of an existing capture process at the source database, then the existing capture process is used, and the rules for DML changes to the tables in the tablespaces are added to the positive capture process rule set.

Note: The capture_name setting cannot be altered after the capture process is created.

propagation_name

The name of the propagation configured to propagate DML changes to the tables in the tablespace set. Do not specify an owner.

If the specified name matches the name of an existing propagation at the source database, then the existing propagation is used, and the rules for DML changes to the tables in the tablespaces are added to the positive propagation rule set.

If NULL, then the system generates a name for each propagation it creates.

Note: The propagation_name setting cannot be altered after the propagation is created.

apply_name

The name of the apply process configured to apply DML changes to the tables in the tablespace set at the destination database. Do not specify an owner.

If the specified name matches the name of an existing apply process at the destination database, then the existing apply process is used, and the for DML changes to the tables in the tablespaces are added 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 NULL, then the system generates a name for each apply process it creates.

Note: The apply_name setting cannot be altered after the apply process is created.

log_file

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 NULL, then the log file name is the same name as the export dump file name with an extension of .clg.

bi_directional

Specify true to configure bi-directional replication between the current database and the database specified in destination_database. Both databases are configured as source and destination databases, and propagations are configured between the databases to propagate events.

Specify false to configure one way replication from the current database to the database specified in destination_database. A capture process is configured at the current database, a propagation is configured to propagate events from the current database to the destination database, and an apply process is configured at the destination database.

Usage Notes

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

Single Source and Bi-Directional Configurations

This procedure either sets up a single source Streams configuration with the local database as the source database, or it sets up a bi-directional Streams configuration with both databases acting as source and destination databases. The bi_directional parameter controls whether the Streams configuration is single source or bi-directional. If bi_directional is false, then a capture process at the local database captures DML changes to the tables in the specified tablespace set, a propagation propagates these changes to the destination database, and an apply process at the destination database applies these changes. If bi_directional is true, then each database captures changes and propagates them to the other database, and each database applies changes from the other database.

If bi_directional is set to false, then this procedure does not configure bi-directional information sharing. Therefore, changes made to the tables in the tablespace set at the destination database are not shared with the source database, and the tablespaces are not kept in sync at the two databases, unless no changes are made to the tablespace objects at the destination database. However, if bi_directional is set to true, then Streams is configured to keep the tablespaces in sync at the two databases, even if both databases allow DML changes to the tablespace objects.

Both databases must be open when the actions are performed. Meet the following requirements when you use this procedure:

Change Cycling and the MAINTAIN_TABLESPACES Procedure

If the bi_directional is set to true, then this procedure configures bi-directional replication, but this procedure cannot be used to configure multi-directional replication where changes may be cycled back to a source database by a third database in the environment. For example, this procedure cannot be used to configure a Streams replication environment with three databases where each database shares changes with the other two databases in the environment. If this procedure is 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.

To prevent change cycling in a bi-directional Streams replication environment, this procedure configures the environment in the following way:

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

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

See Also:

Oracle Streams Replication Administrator's Guide for an example of a hub and spoke environment and for information about configuring a multiple source environment manually

DDL Changes Not Maintained

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

Privileges and Database Links Required by the MAINTAIN_TABLESPACES Procedure

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

In addition, 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.

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

See Also:

Oracle Streams Concepts and Administration for information about configuring a Streams administrator

Actions Performed by the MAINTAIN_TABLESPACES Procedure

This section describes the specific actions performed by this procedure. In the description, the source database is the database is the database where the MAINTAIN_TABLESPACES procedure is run, and the destination database is the database specified in the destination_database parameter.

First, at the source database, this procedure performs the following actions:

  1. Adds supplemental log groups for all tables supported by Streams in the tablespace set
  2. Configures a SYS.AnyData queue as a source queue that will stage changes to the database objects in the tablespace set

Next, if the bi_directional parameter is set to true, then this procedure performs the following actions at the source database (If the bi_directional parameter is set to false, then these actions are not performed, and the procedure continues at Step 5):

  1. Configures a local apply process and adds rules to its positive rule set that instruct the apply process to apply DML changes to tables that are supported by Streams in the tablespace set. The apply process is configured to mark redo records with a tag value that is unique to the destination database, which is the source database of the changes.
  2. Sets the key columns for tables that do not have a primary key using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package

Next, this procedure performs the following actions at the source database:

  1. Configures a propagation, and configures its rule sets to instruct the propagation to propagate changes from the source database to the destination database. If the bi_directional parameter is set to true, then the rule sets filter out changes that originated at the destination database.
  2. Disables the propagation schedule for the propagation created in Step 5
  3. Configures a local capture process and adds rules to its positive rule set that instruct the capture process to capture DML changes to tables that are supported by Streams in the tablespace set
  4. Makes the specified tablespace set read-only
  5. Clones the specified tablespace set using the CLONE_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package. The Data Pump export dump file and the datafiles that comprise the cloned tablespace set are placed in the specified source directory as part of the clone tablespaces operation.
  6. Makes the specified tablespace set read/write. Users and applications can resume operations on the tablespace set.
  7. Starts the capture process configured in Step 7

Next, if the bi_directional parameter is set to true, then this procedure performs the following action at the source database (If the bi_directional parameter is set to false, then this action is not performed, and the procedure continues at Step 13):

  1. Starts the apply process configured in Step 3

Next, this procedure performs the following action at the source database:

  1. Uses the DBMS_FILE_TRANSFER package to copy the Data Pump export dump file and the datafiles for the tablespace set to the specified destination directory at the computer system running the destination database

Next, this procedure performs the following actions at the destination database:

  1. Attaches the tablespace set using the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package
  2. Configures a local apply process and adds rules to its positive rule set that instruct the apply process to apply DML changes to tables that are supported by Streams in the tablespace set. The apply process is configured to mark redo records with a tag value that is unique to the source database of the changes.
  3. Sets the key columns for tables that do not have a primary key using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package
  4. Configures a SYS.AnyData queue as a destination queue that will stage propagated DML changes to the tables in the tablespace set

Next, if the bi_directional parameter is set to true, then this procedure performs the following actions at the destination database (If the bi_directional parameter is set to false, then these actions are not performed, and the procedure continues at Step 24):

  1. Adds supplemental log groups for all tables supported by Streams in the tablespace set
  2. Configures a propagation, and configures its rule sets to instruct the propagation to propagate changes from this database to the source database. The rule sets filter out changes that originated at the source database.
  3. Disables the propagation schedule for the propagation created in Step 19
  4. Configures a local capture process and adds rules to its positive rule set that instruct the capture process to capture DML changes to tables that are supported by Streams in the tablespace set
  5. Records the value of the current database SCN, and uses this value to set the instantiation SCNs of the objects in the tablespace set at the source database
  6. Starts the capture process created in Step 21

Next, this procedure performs the following actions at the destination database:

  1. Starts the apply process created in Step 15
  2. Makes the tablespace set read/write

Next, if the bi_directional parameter is set to true, then this procedure performs the following action at the destination database (If the bi_directional parameter is set to false, then this action is not performed, and the procedure continues at Step 24):

  1. Enables the propagation schedule for the propagation created in Step 19

Next, this procedure performs the final action at the source database:

  1. Enables the propagation schedule for the propagation created in Step 5

To monitor the progress of the configuration, query the V$SESSION_LONGOPS dynamic performance view at the source database.


Note:
  • To view all of the statements run by this procedure in detail, you can use the procedure to generate a script and then view the script in a text editor.
  • Each specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes the MAINTAIN_TABLESPACES procedure must have READ and WRITE privilege on each one.
  • If the source and destination databases are running on different platforms, then this procedure, or the script generated by this procedure, converts transferred datafiles to the appropriate platform automatically.

See Also:

Oracle Database Administrator's Guide for more information about self-contained tablespace sets


PURGE_SOURCE_CATALOG Procedure

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

Syntax

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

Parameters

Table 96-15 PURGE_SOURCE_CATALOG Procedure Parameters
Parameter Description

source_database

The global name of the source database containing the object.

If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify DBS1 and the domain is .NET, then DBS1.NET is specified automatically.

source_object_name

The name of the object specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

source_object_type

Type of the object. Currently, TABLE is the only possible object type.

Usage Notes

The global name of the source database containing the object must be specified for the source_database parameter. If the current database is not the source database for the object, then data dictionary information about the object is removed at 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:


REMOVE_QUEUE Procedure

This procedure removes the specified SYS.AnyData queue.

Specifically, this procedure performs the following actions:

  1. Waits until all current enqueue and dequeue transactions commit.
  2. Stops the queue, which means that no further enqueues into the queue or dequeues from the queue are allowed.
  3. Drops the queue.
  4. If the drop_unused_queue_table parameter is set to true, then drops the queue table if it is empty and no other queues are using it.
  5. If the cascade parameter is set to true, then drops all of the Streams clients that are using the queue.


    Note:

    The specified queue must be a SYS.AnyData queue.


Syntax

DBMS_STREAMS_ADM.REMOVE_QUEUE(
   queue_name               IN  VARCHAR2,
   cascade                  IN  BOOLEAN  DEFAULT false,
   drop_unused_queue_table  IN  BOOLEAN  DEFAULT true);

Parameters

Table 96-16 REMOVE_QUEUE Procedure Parameters
Parameter Description

queue_name

The name of the queue to remove, specified as [schema_name.]queue_name. For example, strmadmin.streams_queue. If the schema is not specified, then the current user is the default.

cascade

If true, then drops any Streams clients that use the queue

If false, then raises an error if there are any Streams clients that use the queue. Before you run this procedure with the cascade parameter set to false, make sure no Streams clients are using the queue currently.

drop_unused_queue_table

If true and the queue table for the queue is empty, then the queue table is dropped. The queue table is not dropped if it contains any messages or if it is used by another queue.

If false, then the queue table is not dropped


REMOVE_RULE Procedure

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

If this procedure results in an empty positive rule set for a messaging client, then the messaging client is dropped 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 the DBMS_RULE_ADM.DROP_RULE procedure. If you use the DBMS_RULE_ADM.DROP_RULE procedure, then some metadata about the rule may remain.


Syntax

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

Parameters

Table 96-17  REMOVE_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule to remove, specified as [schema_name.]rule_name. If NULL, then removes all rules from the specified capture process, apply process, propagation, or messaging client rule set.

For example, to specify a rule in the hr schema named prop_rule1, enter hr.prop_rule1. If the schema is not specified, then the current user is the default.

streams_type

The type of Streams client, either capture for a capture process, apply for an apply process, propagation for a propagation, or dequeue for a messaging client

streams_name

The name of the Streams client, which may be a capture process, apply process, propagation, or messaging client. Do not specify an owner.

If the specified Streams client does not exist, but there is metadata in the data dictionary that associates the rule with this client, then the metadata is removed.

If the specified Streams client does not exist, and there is no metadata in the data dictionary that associates the rule with this client, then an error is raised.

drop_unused_rule

If false, then the rule is not dropped from the database.

If true and the rule is not in any rule set, then the rule is dropped from the database.

If true and the rule exists in any rule set, then the rule is not dropped from the database.

inclusion_rule

If inclusion_rule is true, then the rule is removed from the positive rule set for the Streams client.

If inclusion_rule is false, then the rule is removed from the negative rule set for the Streams client.


REMOVE_STREAMS_CONFIGURATION Procedure

This procedure removes the Streams configuration at the local database.

Syntax

DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

Usage Notes

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

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


Attention:

Running this procedure is dangerous. You should run this procedure only if you are sure you want to remove the entire 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:

SET_MESSAGE_NOTIFICATION Procedure

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

Syntax

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

Parameters

Table 96-18  SET_MESSAGE_NOTIFICATION Procedure Parameters
Parameter Description

streams_name

The name of the Streams messaging client. Do not specify an owner.

For example, if the user strmadmin is the messaging client, then specify strmadmin.

notification_action

The action to be performed on message notification. Specify one of the following:

  • For URL notifications, specify a URL without the prefix http://.

    For example, to specify the URL http://www.company.com:8080, enter the following:

    www.company.com:8080
    
  • For email notifications, specify an email address.

    For example, to specify an the email address xyz@company.com, enter the following:

    xyz@company.com
    
  • For PL/SQL procedure notifications, specify an existing user-defined PL/SQL procedure in the form [schema_name.]procedure_name. If the schema_name is not specified, then the user who invokes the SET_MESSAGE_NOTIFICATION procedure is the default. The procedure must be a PLSQLCALLBACK data structure.

    For example, to specify a procedure named notify_orders in the oe schema, enter the following:

    oe.notify_orders
    
    

See Also: Examples for more information about message notification procedures

notification_type

The type of notification. Specify one of the following:

  • HTTP if you specified a URL for notification_action
  • MAIL if you specified an email address for notification_action
  • PROCEDURE if you specified a user-defined procedure for notification_action

The type must match the specification for the notification_action parameter.

notification_context

The context of the notification. The context must be specified using RAW datatype information. For example, to specify the hexidecimal equivalent of 'FF', enter the following:

SYS.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.

include_notification

If true, then this notification is added for the specified streams_name and queue_name. That is, specifying true turns on the notification for the streams_name and queue_name.

If false, then this notification is removed for the specified streams_name and queue_name. That is, specifying false turns off the notification for the streams_name and queue_name. If you specify false, then this procedure ignores any specified values for the notification_action or notification_context parameters.

queue_name

The name of a local SYS.AnyData queue, specified as [schema_name.]queue_name. The current database must contain the queue. The specified queue must be a SYS.AnyData queue.

For example, to specify a queue named streams_queue in the strmadmin schema, enter strmadmin.streams_queue for this parameter. If the schema is not specified, then the current user is the default.

Usage Notes

You can specify one of the following types of notifications:

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 may 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:

Examples

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

PROCEDURE procedure_name(
  context  IN  SYS.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:

CREATE OR REPLACE PROCEDURE oe.notification_dequeue(
  context  SYS.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             oe.user_msg;
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);
  COMMIT;
END;
/


See Also:

PL/SQL Packages and Types Reference for more information about PLSQLCALLBACK data structures


SET_RULE_TRANSFORM_FUNCTION Procedure

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

Syntax

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

Parameters

Table 96-19 SET_RULE_TRANSFORM_FUNCTION Procedure Parameters
Parameter Description

rule_name

The name of the rule whose rule-based transformation function you are setting or removing, specified as [schema_name.]rule_name.

For example, to specify a rule in the hr schema named prop_rule1, enter hr.prop_rule1. If the schema is not specified, then the current user is the default.

transform_function

Either the name of the transformation function to be used in the rule-based transformation for the rule or NULL.

If you specify a transformation function name, then you must specify an existing function in one of the following forms:

  • [schema_name.]function_name
  • [schema_name.]package_name.function_name

If the function is in a package, then the package_name must be specified. For example, to specify a function in the transform_pkg package in the hr schema named executive_to_management, enter hr.transform_pkg.executive_to_management. An error is returned if the specified procedure does not exist.

If the schema_name is not specified, then the user who invokes the rule-based transformation function is the default.

If you specify NULL, then removes the current rule-based transformation from the rule.

Usage Notes

This procedure modifies the specified rule's action context to specify the transformation. A rule 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 an event. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Streams. The Streams clients include capture processes, 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 rule-based transformation in Streams always consists of the following name-value pair in an action context:

The user that calls the transformation function must have EXECUTE privilege on the function. The following list describes which user calls the transformation function:


SET_UP_QUEUE Procedure

This procedure creates a queue table and a SYS.AnyData queue for use with the capture, propagate, and apply functionality of Streams.

Syntax

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

Parameters

Table 96-20  SET_UP_QUEUE Procedure Parameters
Parameter Description

queue_table

The name of the queue table specified as [schema_name.]queue_table_name. For example, strmadmin.streams_queue_table. If the schema is not specified, then the current user is the default.

If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner.

storage_clause

The storage clause for queue table

The storage parameter is included in the CREATE TABLE statement when the queue table is created. You can specify any valid table storage clause.

If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace of the user who runs this procedure. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause.

If NULL, then Oracle uses the storage characteristics of the tablespace in which the queue table is created.

See Also: Oracle Database SQL Reference for more information about storage clauses

queue_name

The name of the queue that will function as the SYS.AnyData queue, specified as [schema_name.]queue_name. For example, strmadmin.streams_queue.

If the schema is not specified, then it defaults to 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_table, then the current user is the default.

queue_user

The name of the user who requires ENQUEUE and DEQUEUE privileges for the queue. This user is also configured as a secure queue user of the queue. The queue user cannot grant these privileges to other users because they are not granted with the GRANT option.

If NULL, then no privileges are granted. You can also grant queue privileges to the appropriate users using the DBMS_AQADM package.

comment

The comment for the queue

Usage Notes

Set up includes the following actions:

This procedure creates a SYS.AnyData queue that is both a secure queue and a transactional queue.


Note:
  • To enqueue events into and dequeue events 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 an error is raised. In this case, rename or remove the existing agent, and retry SET_UP_QUEUE.
  • Queue names and queue table names can be a maximum of 24 bytes.

See Also:

Oracle Streams Concepts and Administration for more information about secure queue users