| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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:
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.
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:
| See Also:
Oracle Streams Concepts and Administration for more information about how rules are used in Streams |
The following procedures add rules to a rule set of a capture process when you specify capture for the streams_type parameter:
ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all changes made to a source database. See "ADD_GLOBAL_RULES Procedures".ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for changes made to a specified schema. See "ADD_SCHEMA_RULES Procedures".ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_RULES Procedures".ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for changes made to a specified table. See "ADD_TABLE_RULES Procedures".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:
ADD_GLOBAL_RULES invokes the PREPARE_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.ADD_SCHEMA_RULES invokes the PREPARE_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.ADD_SUBSET_RULES and ADD_TABLE_RULES invoke the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package at the source database.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 |
The following procedures add propagation rules to a rule set of a propagation:
ADD_GLOBAL_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in a source queue. See "ADD_GLOBAL_PROPAGATION_RULES Procedures".ADD_SCHEMA_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in a source queue containing changes made to a specified schema. See "ADD_SCHEMA_PROPAGATION_RULES Procedures".ADD_SUBSET_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in a source queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_PROPAGATION_RULES Procedures".ADD_TABLE_PROPAGATION_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in a source queue containing changes made to a specified table. See "ADD_TABLE_PROPAGATION_RULES Procedure".If one of these procedures adds rules to the positive rule set for the propagation, then the rules specify that the propagation propagates LCRs in a source queue to a destination queue. If one of these procedures adds rules to the negative rule set for the propagation, then the rules specify that the propagation discards LCRs in a source queue. When you create rules with one of these procedures, and you specify a value for the source_databse parameter, then the rules include conditions for the specified source database.
The ADD_MESSAGE_PROPAGATION_RULE procedure adds a message rule to a rule set of a propagation. If this procedure adds a rule to the positive rule set for the propagation, then the rule specifies that the propagation propagates the user-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.
The following procedures add rules to a rule set of an apply process when you specify apply for the streams_type parameter:
ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in the apply process queue. See "ADD_GLOBAL_RULES Procedures".ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the apply process queue containing changes made to a specified schema. See "ADD_SCHEMA_RULES Procedures".ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in the apply process queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_RULES Procedures".ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the apply process queue containing changes made to a specified table. See "ADD_TABLE_RULES Procedures".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.
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.
The following procedures add rules to a rule set of a messaging client when you specify dequeue for the streams_type parameter:
ADD_GLOBAL_RULES procedure adds rules whose rule condition evaluates to TRUE for all LCRs in the messaging client queue. See "ADD_GLOBAL_RULES Procedures".ADD_SCHEMA_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the messaging client queue containing changes made to a specified schema. See "ADD_SCHEMA_RULES Procedures".ADD_SUBSET_RULES procedure adds rules whose rule condition evaluates to TRUE for row LCRs in the messaging client queue containing the results of DML changes made to a subset of rows in a specified table. See "ADD_SUBSET_RULES Procedures".ADD_TABLE_RULES procedure adds rules whose rule condition evaluates to TRUE for LCRs in the messaging client queue containing changes made to a specified table. See "ADD_TABLE_RULES Procedures".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.
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.
A user is associated with each Streams client. The following sections describe these users.
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 |
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 |
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 |
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.
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 |
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);
| Parameter | Description |
|---|---|
|
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then it is created automatically. If If |
|
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. The source database is where the changes originated. If If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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.
See Also:
|
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'
These procedures add rules to a rule set of one of the following types of Streams clients:
streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.streams_type parameter is set to apply. The rules may specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.streams_type parameter is set to dequeue. The rules may specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.
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);
| Parameter | Description |
|---|---|
|
|
The type of Streams client: |
|
|
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 If
If An apply process and a messaging client cannot have the same name. |
|
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
This procedure creates 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.
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'
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 |
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);
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.
See Also:
|
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'''
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.
These procedures a message rule to a rule set of one of the following types of Streams clients:
streams_type parameter is set to apply. See "Apply Process Rules for User Messages" for more information about such rules.streams_type parameter is set to dequeue. See "Messaging Client Rules for User Messages" for more information about such rules.
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);
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:
|
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'''
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 |
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);
| Parameter | Description |
|---|---|
|
|
The name of the schema. For example, |
|
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then it is created automatically. If If |
|
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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.
See Also:
|
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'
These procedures add rules to a rule set of one of the following types of Streams clients:
streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.streams_type parameter is set to apply. The rules may specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.streams_type parameter is set to dequeue. The rules may specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.
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);
| Parameter | Description |
|---|---|
|
|
The name of the schema. For example, You can specify a schema that does not yet exist, because Streams does not validate the existence of the schema. |
|
|
The type of Streams client: |
|
|
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 If
If An apply process and a messaging client cannot have the same name. |
|
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
This procedure creates 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.
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'
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 |
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);
| Parameter | Description |
|---|---|
|
|
The name of the table specified as The specified table must exist in the same database as the propagation. Also, the specified table cannot have any LOB, |
|
|
The subset condition. You specify this condition similar to the way you specify conditions in a For example, to specify rows in the
Note: The quotation marks in the preceding example are all single quotation marks. |
|
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then it is created automatically. If If |
|
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the 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. |
|
|
If If See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
|
Contains the system-generated |
|
|
Contains the system-generated |
|
|
Contains the system-generated |
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:
dml_condition, then the row LCR is propagated without any changes.dml_condition, then the row LCR is not propagated.dml_condition, but the new values do not, then the update row LCR is converted into a delete row LCR.dml_condition, but the old values do not, then the update row LCR is converted to an insert row LCR.When an update is converted into an insert or a delete, it is called row migration.
A propagation uses the rules created for filtering. If the propagation does not have a positive rule set, then 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.
See Also:
|
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)
These procedures add rules to a rule set of one of the following types of Streams clients:
streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.streams_type parameter is set to apply. The rules may specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.streams_type parameter is set to dequeue. The rules may specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.
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);
| Parameter | Description |
|---|---|
|
|
The name of the table specified as 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, |
|
|
The subset condition. You specify this condition similar to the way you specify conditions in a For example, to specify rows in the
Note: The quotation marks in the preceding example are all single quotation marks. |
|
|
The type of Streams client: |
|
|
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 If
If An apply process and a messaging client cannot have the same name. |
|
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues 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. |
|
|
If If See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify |
|
|
Contains the system-generated |
|
|
Contains the system-generated |
|
|
Contains the system-generated |
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:
dml_condition, then the DML change is captured, applied, or dequeued without any changes.dml_condition, then the DML change is not captured, applied, or dequeued.dml_condition, but the new values do not, then the DML change is converted into a delete.dml_condition, but the old values do not, then the DML change is converted to an insert.When an update is converted into an insert or a delete, it is called row migration.
A capture process, apply process, or messaging client uses the rules created for filtering. If the Streams client does not have a positive rule set, then 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.
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)
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 |
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);
| Parameter | Description |
|---|---|
|
|
The name of the table specified as |
|
|
The name of the propagation. Do not specify an owner. If the specified propagation does not exist, then it is created automatically. If If |
|
|
The name of the source queue, specified as For example, to specify a source queue named If the schema is not specified, then the current user is the default. |
|
|
The name of the destination queue, including a database link, specified as For example, to specify a destination queue named If the schema is not specified, then the current user is the default. If the database link is omitted, then the 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. The source database is where the change originated. If If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify Oracle recommends that you specify a source database for propagation rules. |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
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.
See Also:
|
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'
These procedures add rules to a rule set of one of the following types of Streams clients:
streams_type parameter is set to capture. See "Capture Process Rules for Changes in the Redo Log" for more information about these rules.streams_type parameter is set to apply. The rules may specify that the LCRs must be from a particular source database. See "Apply Process Rules for LCRs" for more information about these rules.streams_type parameter is set to dequeue. The rules may specify that the LCRs must be from a particular source database. See "Messaging Client Rules for LCRs" for more information about these rules.
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);
| Parameter | Description |
|---|---|
|
|
The name of the table specified as You can specify a table that does not yet exist, because Streams does not validate the existence of the table. |
|
|
The type of Streams client: |
|
|
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 If
If An apply process and a messaging client cannot have the same name. |
|
|
The name of the local queue, specified as For example, to specify a queue named For capture process rules, this is the queue into which a capture process enqueues 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. |
|
|
If |
|
|
If |
|
|
If If In most cases, specify See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
|
The global name of the source database. If For capture process rules, specify For apply process rules, specify the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. For messaging client rules, specify If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify |
|
|
If If |
|
|
If If |
|
|
If If In either case, the system creates the rule set if it does not exist. |
|
|
If non- (system_condition) AND (and_condition) The variable in the specified condition must be :lcr.get_tag() = HEXTORAW(''02'') The Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify |
This procedure creates 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.
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'
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:
| See Also:
Oracle Streams Replication Administrator's Guide for information about point-in-time recovery and flashback queries in a Streams replication environment |
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);
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.
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);
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.
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.
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.
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:
SYS.AnyData queues, capture processes, propagations, and apply processes.DBMS_STREAMS_ADM, DBMS_STREAMS_TABLESPACES_ADM, DBMS_FILE_TRANSFER, and DBMS_AQADM packagesCLONE_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_SIMPLE_TABLESPACE Procedure for the list of required privileges.ATTACH_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_SIMPLE_TABLESPACE Procedure for the list of required privileges.destination_database parameter through a database link. This database link should have the same name as the global name of the destination database.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 |
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.
See Also:
|
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.
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);
| Parameter | Description |
|---|---|
|
|
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 If See Also: TABLESPACE_SET Type |
|
|
The directory on the computer system running the source database into which the generated Data Pump export dump file and the datafiles that comprise the cloned tablespace set are placed. These files remain in this directory after the procedure completes. If |
|
|
The directory 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 |
|
|
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 |
|
|
If If You specify |
|
|
If non- If non- If |
|
|
The directory on the local computer system into which the generated script is placed. If If |
|
|
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 If |
|
|
The name of the queue table for the queue at the source database, specified as If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner. |
|
|
The name of the queue at the source database that will function as the 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. |
|
|
The name of the user who requires If |
|
|
The name of the queue table for the queue at the destination database, specified as If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner. |
|
|
The name of the queue at the destination database that will function as the 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 |
|
|
The name of the user who requires If |
|
|
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 |
|
|
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 Note: The |
|
|
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 Note: The |
|
|
The name of the Data Pump export log file. This log file is placed in the same directory as the Data Pump export dump file. If |
|
|
Specify Specify |
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.
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:
bi_directional is set to true, then run this procedure at the database that contains the tablespace set to be cloned.destination_database parameter does not need to be open when you run this procedure, but both databases must be open when you run the generated script.
|
Note: You may need to configure conflict resolution if this procedure configures bi-directional replication. |
See Also:
|
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:
sfdb.net and nydb.net for bi-directional replication, then assume that the apply tag for the apply process at sfdb.net is the hexidecimal equivalent of '1', and assume that the apply tag for the apply process at nydb.net is the hexidecimal equivalent of '2'.sfdb.net propagates all changes to nydb.net, except for changes with a tag value that is the hexidecimal equivalent of '1', because these changes originated at nydb.net. Similarly, the propagation at nydb.net propagates all changes to sfdb.net, except for changes with a tag value that is the hexidecimal equivalent of '2'.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 |
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.
The user who runs the MAINTAIN_TABLESPACES procedure should have DBA role. This user must have the necessary privileges to complete the following actions:
SYS.AnyData queues, capture processes, propagations, and apply processes.DBMS_STREAMS_ADM, DBMS_STREAMS_TABLESPACES_ADM, DBMS_FILE_TRANSFER, and DBMS_AQADM packagesCLONE_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the source database. See CLONE_TABLESPACES Procedure for the list of required privileges.ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACES_ADM package at the destination database. See ATTACH_TABLESPACES Procedure for the list of required privileges.destination_database parameter through a database link. This database link should have the same name as the global name of the destination database.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 |
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:
SYS.AnyData queue as a source queue that will stage changes to the database objects in the tablespace setNext, 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):
SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM packageNext, this procedure performs the following actions at the source database:
bi_directional parameter is set to true, then the rule sets filter out changes that originated at the destination database.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.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):
Next, this procedure performs the following action at the source database:
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 databaseNext, this procedure performs the following actions at the destination database:
ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM packageSET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM packageSYS.AnyData queue as a destination queue that will stage propagated DML changes to the tables in the tablespace setNext, 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):
Next, this procedure performs the following actions at the destination database:
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):
Next, this procedure performs the final action at the source database:
To monitor the progress of the configuration, query the V$SESSION_LONGOPS dynamic performance view at the source database.
| See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespace sets |
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.
DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG( source_database IN VARCHAR2, source_object_name IN VARCHAR2, source_object_type IN VARCHAR2);
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:
This procedure removes the specified SYS.AnyData queue.
Specifically, this procedure performs the following actions:
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.cascade parameter is set to true, then drops all of the Streams clients that are using the queue.
DBMS_STREAMS_ADM.REMOVE_QUEUE( queue_name IN VARCHAR2, cascade IN BOOLEAN DEFAULT false, drop_unused_queue_table IN BOOLEAN DEFAULT true);
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.
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);
This procedure removes the Streams configuration at the local database.
DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
Specifically, this procedure performs the following actions at the local database:
ABORT_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM packageABORT_SCHEMA_INSTANTIATION procedure in the DBMS_CAPTURE_ADM packageABORT_GLOBAL_INSTANTIATION procedure in the DBMS_CAPTURE_ADM packageDBMS_STREAMS_ADM package or the DBMS_PROPAGATION_ADM package. Before a propagation is dropped, its propagation job is disabled. Does not drop propagations that were created using the DBMS_AQADM package.SET_MESSAGE_NOTIFICATION procedure in the DBMS_STREAMS_ADM packageDBMS_STREAMS_ADM package. Does not drop rules that were created using the DBMS_RULE_ADM package.This procedure stops capture processes and apply processes before it drops them.
|
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:
|
See Also:
|
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.
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');
| Parameter | Description |
|---|---|
|
|
The name of the Streams messaging client. Do not specify an owner. For example, if the user |
|
|
The action to be performed on message notification. Specify one of the following:
See Also: Examples for more information about message notification procedures |
|
|
The type of notification. Specify one of the following:
The type must match the specification for the |
|
|
The context of the notification. The context must be specified using 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. |
|
|
If If |
|
|
The name of a local For example, to specify a queue named |
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:
notification_options, which includes the following:
message_set - The set of message properties
See Also:
|
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 |
This procedure sets or removes the transformation function name for a rule-based transformation.
DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION( rule_name IN VARCHAR2, transform_function IN VARCHAR2);
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:
STREAMS$_TRANSFORM_FUNCTION.SYS.AnyData instance containing a PL/SQL function name specified as a VARCHAR2. This function performs the transformation.The user that calls the transformation function must have EXECUTE privilege on the function. The following list describes which user calls the transformation function:
This procedure creates a queue table and a SYS.AnyData queue for use with the capture, propagate, and apply functionality of Streams.
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);
| Parameter | Description |
|---|---|
|
|
The name of the queue table specified as If the queue table owner is not specified, then the user who runs this procedure is automatically specified as the queue table owner. |
|
|
The storage clause for queue table The storage parameter is included in the 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 See Also: Oracle Database SQL Reference for more information about storage clauses |
|
|
The name of the queue that will function as the 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 |
|
|
The name of the user who requires If |
|
|
The comment for the queue |
Set up includes the following actions:
CREATE_QUEUE_TABLE procedure in the DBMS_AQADM package to create the queue table with the specified storage clause.CREATE_QUEUE procedure in the DBMS_AQADM package to create the queue.ENQUEUE and DEQUEUE privileges on the queue to the specified queue user.
To configure the queue user as a secure queue user, this procedure creates an Advanced Queuing agent with the same name as the user name, if one does not already exist. If an agent with this name already exists and is associated with the queue user only, then it is used. SET_UP_QUEUE then runs the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package, specifying the agent and the user.
This procedure creates a SYS.AnyData queue that is both a secure queue and a transactional queue.
| See Also:
Oracle Streams Concepts and Administration for more information about secure queue users |
|
 Copyright © 1998, 2003 Oracle Corporation All Rights Reserved. |
|