Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-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
Contact Us

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

20 DBMS_APPLY_ADM

The DBMS_APPLY_ADM package, one of a set of Oracle Streams packages, provides subprograms to start, stop, and configure an apply process. This package includes subprograms for configuring apply handlers, setting enqueue destinations for messages, and specifying execution directives for messages. This package also provides administrative subprograms that set the instantiation SCN for objects at a destination database. This package also includes subprograms for managing apply errors.

See Also:

Oracle Streams Concepts and Administration and Oracle Streams Replication Administrator's Guide for more information about this package and apply processes

This chapter contains the following topic:


Summary of DBMS_APPLY_ADM Subprograms

Table 20-1 DBMS_APPLY_ADM Package Subprograms

Subprogram Description
ALTER_APPLY Procedure
Alters an apply process
COMPARE_OLD_VALUES Procedure
Specifies whether to compare the old value of one or more columns in a row logical change record (row LCR) with the current value of the corresponding columns at the destination site during apply
CREATE_APPLY Procedure
Creates an apply process
CREATE_OBJECT_DEPENDENCY Procedure
Creates an object dependency
DELETE_ALL_ERRORS Procedure
Deletes all the error transactions for the specified apply process
DELETE_ERROR Procedure
Deletes the specified error transaction
DROP_APPLY Procedure
Drops an apply process
DROP_OBJECT_DEPENDENCY Procedure
Drops an object dependency
EXECUTE_ALL_ERRORS Procedure
Reexecutes the error transactions for the specified apply process.
EXECUTE_ERROR Procedure
Reexecutes the specified error transaction
GET_ERROR_MESSAGE Function
Returns the message payload from the error queue for the specified message number and transaction identifier
SET_DML_HANDLER Procedure
Alters operation options for a specified object with a specified apply process
SET_ENQUEUE_DESTINATION Procedure
Sets the queue where the apply process automatically enqueues a message that satisfies the specified rule
SET_EXECUTE Procedure
Specifies whether a message that satisfies the specified rule is executed by an apply process
SET_GLOBAL_INSTANTIATION_SCN Procedure
Records the specified instantiation SCN for the specified source database and, optionally, for the schemas at the source database and the tables owned by these schemas
SET_KEY_COLUMNS Procedures
Records the set of columns to be used as the substitute primary key for local apply purposes and removes existing substitute primary key columns for the specified object if they exist
SET_PARAMETER Procedure
Sets an apply parameter to the specified value
SET_SCHEMA_INSTANTIATION_SCN Procedure
Records the specified instantiation SCN for the specified schema in the specified source database and, optionally, for the tables owned by the schema at the source database
SET_TABLE_INSTANTIATION_SCN Procedure
Records the specified instantiation SCN for the specified table in the specified source database
SET_UPDATE_CONFLICT_HANDLER Procedure
Adds, updates, or drops an update conflict handler for the specified object
SET_VALUE_DEPENDENCY Procedure
Sets or removes a value dependency
START_APPLY Procedure
Directs the apply process to start applying messages
STOP_APPLY Procedure
Stops the apply process from applying any messages and rolls back any unfinished transactions being applied

Note:

All procedures commit unless specified otherwise. However, the GET_ERROR_MESSAGE function does not commit.

ALTER_APPLY Procedure

This procedure alters an apply process.

Syntax

DBMS_APPLY_ADM.ALTER_APPLY(
     apply_name                IN  VARCHAR2,
     rule_set_name             IN  VARCHAR2  DEFAULT NULL,
     remove_rule_set           IN  BOOLEAN   DEFAULT FALSE,
     message_handler           IN  VARCHAR2  DEFAULT NULL
     remove_message_handler    IN  BOOLEAN   DEFAULT FALSE,
     ddl_handler               IN  VARCHAR2  DEFAULT NULL,
     remove_ddl_handler        IN  BOOLEAN   DEFAULT FALSE,
     apply_user                IN  VARCHAR2  DEFAULT NULL,
     apply_tag                 IN  RAW       DEFAULT NULL,
     remove_apply_tag          IN  BOOLEAN   DEFAULT FALSE,
     precommit_handler         IN  VARCHAR2  DEFAULT NULL,
     remove_precommit_handler  IN  BOOLEAN   DEFAULT FALSE,
     negative_rule_set_name    IN  VARCHAR2  DEFAULT NULL,
     remove_negative_rule_set  IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-2 ALTER_APPLY Procedure Parameters

Parameter Description
apply_name The name of the apply process being altered. You must specify an existing apply process name. Do not specify an owner.
rule_set_name The name of the positive rule set for the apply process. The positive rule set contains the rules that instruct the apply process to apply messages.

If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form [schema_name.]rule_set_name. For example, to specify a positive rule set in the hr schema named job_apply_rules, enter hr.job_apply_rules. If the schema is not specified, then the current user is the default.

An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

If you specify NULL and the remove_rule_set parameter is set to FALSE, then this procedure retains any existing positive rule set. If you specify NULL and the remove_rule_set parameter is set to TRUE, then this procedure removes any existing positive rule set.

remove_rule_set If TRUE, then the procedure removes the positive rule set for the specified apply process. If you remove the positive rule set for an apply process, and the apply process does not have a negative rule set, then the apply process dequeues all messages in its queue.

If you remove the positive rule set for an apply process, and a negative rule set exists for the apply process, then the apply process dequeues all messages in its queue that are not discarded by the negative rule set.

If FALSE, then the procedure retains the positive rule set for the specified apply process.

If the rule_set_name parameter is non-NULL, then this parameter should be set to FALSE.

message_handler A user-defined procedure that processes non-LCR messages in the queue for the apply process.

See "Usage Notes" in the CREATE_APPLY Procedure for more information about a message handler procedure.

remove_message_handler If TRUE, then the procedure removes the message handler for the specified apply process.

If FALSE, then the procedure retains any message handler for the specified apply process.

If the message_handler parameter is non-NULL, then this parameter should be set to FALSE.

ddl_handler A user-defined procedure that processes DDL logical change records (DDL LCRs) in the queue for the apply process.

All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

See "Usage Notes" in the CREATE_APPLY Procedure for more information about a DDL handler procedure.

remove_ddl_handler If TRUE, then the procedure removes the DDL handler for the specified apply process.

If FALSE, then the procedure retains any DDL handler for the specified apply process.

If the ddl_handler parameter is non-NULL, then this parameter should be set to FALSE.

apply_user The user in whose security domain an apply process dequeues messages that satisfy its rule sets, applies messages directly to database objects, runs custom rule-based transformations configured for apply process rules, and runs apply handlers configured for the apply process. If NULL, then the apply user is not changed.

If a non-NULL value is specified to change the apply user, then the user who invokes the ALTER_APPLY procedure must be granted DBA role. Only the SYS user can set the apply_user to SYS.

If you change the apply user, then this procedure grants the new apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue.

In addition to the privileges granted by this procedure, you also should grant the following privileges to the apply user:

  • The necessary privileges to perform DML and DDL changes on the apply objects

  • EXECUTE privilege on the rule sets used by the apply process

  • EXECUTE privilege on all rule-based transformation functions used in the rule set

  • EXECUTE privilege on all apply handler procedures

  • EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the apply process

These privileges must be granted directly to the apply user. They cannot be granted through roles.

By default, this parameter is set to the user who created the apply process by running either the CREATE_APPLY procedure in this package or a procedure in the DBMS_STREAMS_ADM package.

Note: If the specified user is dropped using DROP USER... CASCADE, then the apply_user for the apply process is set to NULL automatically. You must specify an apply user before the apply process can run.

apply_tag A binary tag that is added to redo entries generated by the specified apply process. The tag is a binary value that can be used to track LCRs.

The tag is relevant only if a capture process at the database where the apply process is running will capture changes made by the apply process. If so, then the captured changes will include the tag specified by this parameter.

If NULL, the default, then the apply tag for the apply process is not changed.

The following is an example of a tag with a hexadecimal value of 17:

HEXTORAW('17')

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

remove_apply_tag If TRUE, then the procedure sets the apply tag for the specified apply process to NULL, and the apply process generates redo entries with NULL tags.

If FALSE, then the procedure retains any apply tag for the specified apply process.

If the apply_tag parameter is non-NULL, then this parameter should be set to FALSE.

precommit_handler A user-defined procedure that can receive internal commit directives in the queue for the apply process before they are processed by the apply process. Typically, precommit handlers are used for auditing commit information for transactions processed by an apply process.

An internal commit directive is enqueued in the following ways:

  • When a capture process captures row LCRs, the capture process enqueues the commit directive for the transaction that contains the row LCRs.

  • When a user or application enqueues messages and then issues a COMMIT statement, the commit directive is enqueued automatically.

For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database. For a user message, the commit SCN is generated by the apply process.

The precommit handler procedure must conform to the following restrictions:

  • Any work that commits must be an autonomous transaction.

  • Any rollback must be to a named savepoint created in the procedure.

If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the messages in the transaction are moved to the error queue.

See "Usage Notes" in the CREATE_APPLY Procedure for more information about a precommit handler procedure.

remove_precommit_handler If TRUE, then the procedure removes the precommit handler for the specified apply process.

If FALSE, then the procedure retains any precommit handler for the specified apply process.

If the precommit_handler parameter is non-NULL, then this parameter should be set to FALSE.

negative_rule_set_name The name of the negative rule set for the apply process. The negative rule set contains the rules that instruct the apply process to discard messages.

If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form [schema_name.]rule_set_name. For example, to specify a negative rule set in the hr schema named neg_apply_rules, enter hr.neg_apply_rules. If the schema is not specified, then the current user is the default.

An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

If you specify NULL and the remove_negative_rule_set parameter is set to FALSE, then the procedure retains any existing negative rule set. If you specify NULL and the remove_negative_rule_set parameter is set to TRUE, then the procedure removes any existing negative rule set.

If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first.

remove_negative_rule_set If TRUE, then the procedure removes the negative rule set for the specified apply process. If you remove the negative rule set for an apply process, and the apply process does not have a positive rule set, then the apply process dequeues all messages in its queue.

If you remove the negative rule set for an apply process, and a positive rule set exists for the apply process, then the apply process dequeues all messages in its queue that are not discarded by the positive rule set.

If FALSE, then the procedure retains the negative rule set for the specified apply process.

If the negative_rule_set_name parameter is non-NULL, then this parameter should be set to FALSE.


Usage Notes

An apply process is stopped and restarted automatically when you change the value of one or more of the following ALTER_APPLY procedure parameters:


COMPARE_OLD_VALUES Procedure

This procedure specifies whether to compare the old value of one or more columns in a row logical change record (row LCR) with the current value of the corresponding columns at the destination site during apply. This procedure is relevant only for UPDATE and DELETE operations because only these operations result in old column values in row LCRs. The default is to compare old values for all columns.

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.

See Also:

Oracle Streams Replication Administrator's Guide for more information about conflict detection and resolution in an Oracle Streams environment

Syntax

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
   object_name         IN VARCHAR2,
   column_list         IN VARCHAR2,
   operation           IN VARCHAR2 DEFAULT 'UPDATE',
   compare             IN BOOLEAN  DEFAULT TRUE,
   apply_database_link IN VARCHAR2 DEFAULT NULL);

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
   object_name         IN VARCHAR2,
   column_table        IN DBMS_UTILITY.LNAME_ARRAY,
   operation           IN VARCHAR2 DEFAULT 'UPDATE',
   compare             IN BOOLEAN  DEFAULT TRUE,
   apply_database_link IN VARCHAR2 DEFAULT NULL);

Parameters

Table 20-3 COMPARE_OLD_VALUES Procedure Parameters

Parameter Description
object_name The name of the source table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.
column_list A comma-delimited list of column names in the table. There must be no spaces between entries.

Specify * to include all nonkey columns.

column_table A PL/SQL index-by table of type DBMS_UTILITY.LNAME_ARRAY that contains names of columns in the table. The first column name should be at position 1, the second at position 2, and so on. The table does not need to be NULL terminated.
operation The name of the operation, which can be specified as:
  • UPDATE for UPDATE operations

  • DELETE for DELETE operations

  • * for both UPDATE and DELETE operations

compare If compare is TRUE, the old values of the specified columns are compared during apply. If compare is FALSE, the old values of the specified columns are not compared during apply.
apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.

Usage Notes

By default, an apply process uses the old column values in a row LCR to detect conflicts. You can choose not to compare old column values to avoid conflict detection for specific tables. For example, if you use a time column for conflict detection, then an apply process does not need to check old values for non-key and nontime columns.

Note:

An apply process always compares old values for key columns when they are present in a row LCR. This procedure raises an error if a key column is specified in column_list or column_table and the compare parameter is set to FALSE.

CREATE_APPLY Procedure

This procedure creates an apply process.

Note:

The user who invokes this procedure must be granted DBA role.

Syntax

DBMS_APPLY_ADM.CREATE_APPLY(
     queue_name              IN  VARCHAR2,
     apply_name              IN  VARCHAR2,
     rule_set_name           IN  VARCHAR2  DEFAULT NULL,
     message_handler         IN  VARCHAR2  DEFAULT NULL,
     ddl_handler             IN  VARCHAR2  DEFAULT NULL,
     apply_user              IN  VARCHAR2  DEFAULT NULL,
     apply_database_link     IN  VARCHAR2  DEFAULT NULL,
     apply_tag               IN  RAW       DEFAULT '00',
     apply_captured          IN  BOOLEAN   DEFAULT FALSE,
     precommit_handler       IN  VARCHAR2  DEFAULT NULL,
     negative_rule_set_name  IN  VARCHAR2  DEFAULT NULL,
     source_database         IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 20-4 CREATE_APPLY Procedure Parameters

Parameter Description
queue_name The name of the queue from which the apply process dequeues messages. You must specify an existing queue in the form [schema_name.]queue_name. For example, to specify a queue in the hr schema named streams_queue, enter hr.streams_queue. If the schema is not specified, then the current user is the default.

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

apply_name The name of the apply process being created. A NULL specification is not allowed. Do not specify an owner.

The specified name must not match the name of an existing apply process or messaging client.

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

rule_set_name The name of the positive rule set for the apply process. The positive rule set contains the rules that instruct the apply process to apply messages.

If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form [schema_name.]rule_set_name. For example, to specify a positive rule set in the hr schema named job_apply_rules, enter hr.job_apply_rules. If the schema is not specified, then the current user is the default.

If you specify NULL, and no negative rule set is specified, then the apply process applies either all captured messages or all messages in the persistent queue, depending on the setting of the apply_captured parameter.

An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

message_handler A user-defined procedure that processes non-LCR messages in the queue for the apply process.

See "Usage Notes" for more information about a message handler procedure.

ddl_handler A user-defined procedure that processes DDL logical change record (DDL LCRs) in the queue for the apply process.

All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the EXECUTE member procedure of a DDL LCR, then a commit is performed automatically.

See "Usage Notes" for more information about a DDL handler procedure.

apply_user The user who applies all DML and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. If NULL, then the user who runs the CREATE_APPLY procedure is used.

Only a user who is granted DBA role can set an apply user. Only the SYS user can set the apply_user to SYS.

The apply user is the user in whose security domain an apply process dequeues messages that satisfy its rule sets, applies messages directly to database objects, runs custom rule-based transformations configured for apply process rules, and runs apply handlers configured for the apply process. This user must have the necessary privileges to apply changes. This 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.

In addition to the privileges granted by this procedure, you also should grant the following privileges to the apply user:

  • The necessary privileges to perform DML and DDL changes on the apply objects

  • EXECUTE privilege on the rule sets used by the apply process

  • EXECUTE privilege on all rule-based transformation functions used in the rule set

  • EXECUTE privilege on all apply handler procedures

  • EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the apply process

These privileges must be granted directly to the apply user. They cannot be granted through roles.

Note: If the specified user is dropped using DROP USER... CASCADE, then the apply_user setting for the apply process is set to NULL automatically. You must specify an apply user before the apply process can run.

apply_database_link The database at which the apply process applies messages. This parameter is used by an apply process when applying changes from Oracle to non-Oracle systems, such as Sybase. Set this parameter to NULL to specify that the apply process applies messages at the local database.

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

apply_tag A binary tag that is added to redo entries generated by the specified apply process. The tag is a binary value that can be used to track LCRs.

The tag is relevant only if a capture process at the database where the apply process is running will capture changes made by the apply process. If so, then the captured changes will include the tag specified by this parameter.

By default, the tag for an apply process is the hexadecimal equivalent of '00' (double zero).

The following is an example of a tag with a hexadecimal value of 17:

HEXTORAW('17')

If NULL, then the apply process generates redo entries with NULL tags.

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

apply_captured Either TRUE or FALSE.

If TRUE, then the apply process applies only the captured LCRs in the queue. Captured LCRs are LCRs that were captured by an Oracle Streams capture process.

If FALSE, then the apply process applies only the messages in a persistent queue. These are messages that were not captured by an Oracle Streams capture process, such as persistent LCRs or user messages.

To apply both captured LCRs and messages in a persistent queue, you must create at least two apply processes.

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

See Also: Oracle Streams Concepts and Administration for more information about processing messages with an apply process

precommit_handler A user-defined procedure that can receive internal commit directives in the queue for the apply process before they are processed by the apply process. Typically, precommit handlers are used for auditing commit information for transactions processed by an apply process.

An internal commit directive is enqueued in the following ways:

  • When a capture process captures row LCRs, the capture process enqueues the commit directive for the transaction that contains the row LCRs.

  • When a synchronous capture captures row LCRs, the the persistent LCRs that were enqueued by the synchronous capture are organized into a message group. The synchronous capture records the transaction identifier in each persistent LCR in a transaction.

  • When a user or application enqueues messages and then issues a COMMIT statement, the commit directive is enqueued automatically.

For a row LCR captured by a capture process or synchronous capture, a commit directive contains the commit SCN of the transaction from the source database. For message enqueued by a user or application, the commit SCN is generated by the apply process.

The precommit handler procedure must conform to the following restrictions:

  • Any work that commits must be an autonomous transaction.

  • Any rollback must be to a named savepoint created in the procedure.

If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the messages in the transaction are moved to the error queue.

See "Usage Notes" for more information about a precommit handler procedure.

negative_rule_set_name The name of the negative rule set for the apply process. The negative rule set contains the rules that instruct the apply process to discard messages.

If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form [schema_name.]rule_set_name. For example, to specify a negative rule set in the hr schema named neg_apply_rules, enter hr.neg_apply_rules. If the schema is not specified, then the current user is the default.

If you specify NULL, and no positive rule set is specified, then the apply process applies either all captured LCRs or all of the messages in the persistent queue, depending on the setting of the apply_captured parameter.

An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the DBMS_STREAMS_ADM package or the DBMS_RULE_ADM package.

If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first.

source_database The global name of the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured messages, then the apply process can apply messages from only one capture process at one source database.

If NULL, then the source database name of the first LCR received by the apply process is used for the source database.

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

The rules in the apply process rule sets determine which messages are dequeued by the apply process. 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. You can determine the source database for an apply process by querying the DBA_APPLY_PROGRESS data dictionary view.


Usage Notes

The following sections describe usage notes for this procedure:

Handler Procedure Names

For the message_handler, ddl_handler, and precommit_handler parameters, specify an existing procedure in one of the following forms:

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

The user who invokes the CREATE_APPLY procedure must have EXECUTE privilege on a specified handler procedure. Also, if the schema_name is not specified, then the user who invokes the CREATE_APPLY procedure is the default.

Message Handler and DDL Handler Procedure

The procedure specified in both the message_handler parameter and the ddl_handler parameter must have the following signature:

PROCEDURE handler_procedure (
   parameter_name  IN  ANYDATA);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. For the message handler, the parameter passed to the procedure is a ANYDATA encapsulation of a user message. For the DDL handler procedure, the parameter passed to the procedure is a ANYDATA encapsulation of a DDL LCR.

See Also:

Chapter 229, "Logical Change Record TYPEs" for information about DDL LCRs

Precommit Handler Procedure

The procedure specified in the precommit_handler parameter must have the following signature:

PROCEDURE handler_procedure (
   parameter_name   IN  NUMBER);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is the commit SCN of a commit directive.


CREATE_OBJECT_DEPENDENCY Procedure

This procedure creates an object dependency. An object dependency is a virtual dependency definition that defines a parent-child relationship between two objects at a destination database.

An apply process schedules execution of transactions that involve the child object after all transactions with a lower commit system change number (commit SCN) that involve the parent object have been committed. An apply process uses the object identifier of the objects in the logical change records (LCRs) to detect dependencies. The apply process does not use column values in the LCRs to detect dependencies.

Note:

An error is raised if NULL is specified for either of the procedure parameters.

Syntax

DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
     object_name         IN  VARCHAR2,
     parent_object_name  IN  VARCHAR2);

Parameters

Table 20-5 CREATE_OBJECT_DEPENDENCY Procedure Parameters

Parameter Description
object_name The name of the child database object, specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.
parent_object_name The name of the parent database object, specified as [schema_name.]object_name. For example, hr.departments. If the schema is not specified, then the current user is the default.


DELETE_ALL_ERRORS Procedure

This procedure deletes all the error transactions for the specified apply process.

Syntax

DBMS_APPLY_ADM.DELETE_ALL_ERRORS(
   apply_name  IN  VARCHAR2 DEFAULT NULL);

Parameter

Table 20-6 DELETE_ALL_ERRORS Procedure Parameter

Parameter Description
apply_name The name of the apply process that raised the errors while processing the transactions. Do not specify an owner.

If NULL, then all error transactions for all apply processes are deleted.



DELETE_ERROR Procedure

This procedure deletes the specified error transaction.

Syntax

DBMS_APPLY_ADM.DELETE_ERROR(
   local_transaction_id  IN  VARCHAR2);

Parameter

Table 20-7 DELETE_ERROR Procedure Parameter

Parameter Description
local_transaction_id The identification number of the error transaction to delete. If the specified transaction does not exist in the error queue, then an error is raised.


DROP_APPLY Procedure

This procedure drops an apply process.

Syntax

DBMS_APPLY_ADM.DROP_APPLY(
     apply_name             IN  VARCHAR2,
     drop_unused_rule_sets  IN  BOOLEAN  DEFAULT FALSE);

Parameters

Table 20-8 DROP_APPLY Procedure Parameters

Parameter Description
apply_name The name of the apply process being dropped. You must specify an existing apply process name. Do not specify an owner.
drop_unused_rule_sets If TRUE, then the procedure drops any rule sets, positive and negative, used by the specified apply process if these rule sets are not used by any other Oracle Streams client. Oracle Streams clients include capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set.

If FALSE, then the procedure does not drop the rule sets used by the specified apply process, and the rule sets retain their rules.


Usage Notes

When you use this procedure to drop an apply process, information about rules created for the apply process using the DBMS_STREAMS_ADM package is removed from the data dictionary views for Oracle Streams rules. Information about such a rule is removed even if the rule is not in either the positive or negative rule set for the apply process. The following are the data dictionary views for Oracle Streams rules:


DROP_OBJECT_DEPENDENCY Procedure

This procedure drops an object dependency. An object dependency is a virtual dependency definition that defines a parent-child relationship between two objects at a destination database.

Note:

  • An error is raised if an object dependency does not exist for the specified database objects.

  • An error is raised if NULL is specified for either of the procedure parameters.

Syntax

DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
     object_name         IN  VARCHAR2,
     parent_object_name  IN  VARCHAR2);

Parameters

Table 20-9 CREATE_OBJECT_DEPENDENCY Procedure Parameters

Parameter Description
object_name The name of the child database object, specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.
parent_object_name The name of the parent database object, specified as [schema_name.]object_name. For example, hr.departments. If the schema is not specified, then the current user is the default.


EXECUTE_ALL_ERRORS Procedure

This procedure reexecutes the error transactions in the error queue for the specified apply process.

The transactions are reexecuted in commit SCN order. Error reexecution stops if an error is raised.

See Also:

Oracle Streams Concepts and Administration for more information about the error queue

Syntax

DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
   apply_name       IN  VARCHAR2  DEFAULT NULL,
   execute_as_user  IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-10 EXECUTE_ALL_ERRORS Procedure Parameters

Parameter Description
apply_name The name of the apply process that raised the errors while processing the transactions. Do not specify an owner.

If NULL, then all error transactions for all apply processes are reexecuted.

execute_as_user If TRUE, then the procedure reexecutes the transactions in the security context of the current user.

If FALSE, then the procedure reexecutes each transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The DBA_APPLY_ERROR data dictionary view lists the original receiver for each error transaction.

The user who executes the transactions must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.



EXECUTE_ERROR Procedure

This procedure reexecutes the specified error transaction in the error queue.

See Also:

Oracle Streams Concepts and Administration for more information about the error queue

Syntax

DBMS_APPLY_ADM.EXECUTE_ERROR(
   local_transaction_id  IN  VARCHAR2,
   execute_as_user       IN  BOOLEAN   DEFAULT FALSE,
   user_procedure        IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 20-11 EXECUTE_ERROR Procedure Parameters

Parameter Description
local_transaction_id The identification number of the error transaction to execute. If the specified transaction does not exist in the error queue, then an error is raised.
execute_as_user If TRUE, then the procedure reexecutes the transaction in the security context of the current user.

If FALSE, then the procedure reexecutes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The DBA_APPLY_ERROR data dictionary view lists the original receiver for each error transaction.

The user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

user_procedure A user-defined procedure that modifies the error transaction so that it can be successfully executed.

Specify NULL to execute the error transaction without running a user procedure.

See Also: "Usage Notes" for more information about the user procedure


Usage Notes

You must specify the full procedure name for the user_procedure parameter in one of the following forms:

If the procedure is in a package, then the package_name must be specified. The user who invokes the EXECUTE_ERROR procedure must have EXECUTE privilege on the specified procedure. Also, if the schema_name is not specified, then the user who invokes the EXECUTE_ERROR procedure is the default.

For example, suppose the procedure_name has the following properties:

In this case, specify the following:

strmadmin.fix_errors.fix_hr_errors

The procedure you create for error handling must have the following signature:

PROCEDURE user_procedure (
     in_anydata                    IN      ANYDATA,
     error_record                  IN      DBA_APPLY_ERROR%ROWTYPE,
     error_message_number          IN      NUMBER,
     messaging_default_processing  IN OUT  BOOLEAN,
     out_anydata                   OUT     ANYDATA);

The user procedure has the following parameters:

If an LCR is executed using the EXECUTE LCR member procedure in the user procedure, then the LCR is executed directly, and the messaging_default_processing parameter should be set to FALSE. In this case, the LCR is not passed to any apply handlers.

Processing an error transaction with a user procedure results in one of the following outcomes:

The following restrictions apply to the user procedure:

Note:

LCRs containing transactional directives, such as COMMIT and ROLLBACK, are not passed to the user procedure.

GET_ERROR_MESSAGE Function

This function returns the message payload from the error queue for the specified message number and transaction identifier. The message can be a logical change record (LCR) or a non-LCR message.

This function is overloaded. One version of this function contains two OUT parameters. These OUT parameters contain the destination queue into which the message should be enqueued, if one exists, and whether or not the message should be executed. The destination queue is specified using the SET_ENQUEUE_DESTINATION procedure, and the execution directive is specified using the SET_EXECUTE procedure.

Syntax

DBMS_APPLY_ADM.GET_ERROR_MESSAGE(
   message_number          IN   NUMBER, 
   local_transaction_id    IN   VARCHAR2,
   destination_queue_name  OUT  VARCHAR2, 
   execute                 OUT  BOOLEAN)
RETURN ANYDATA;

DBMS_APPLY_ADM.GET_ERROR_MESSAGE(
   message_number          IN   NUMBER, 
   local_transaction_id    IN   VARCHAR2)
RETURN ANYDATA;

Parameters

Table 20-12 GET_ERROR_MESSAGE Function Parameters

Parameter Description
message_number The identification number of the message. This number identifies the position of the message in the transaction. Query the DBA_APPLY_ERROR data dictionary view to view the message number of each apply error.
local_transaction_id Identifier of the error transaction for which to return a message
destination_queue_name Contains the name of the queue into which the message should be enqueued. If the message should not be enqueued into a queue, then this parameter contains NULL.
execute Contains TRUE if the message should be executed

Contains FALSE if the message should not be executed



SET_DML_HANDLER Procedure

This procedure sets a user procedure as a DML handler for a specified operation on a specified object. The user procedure alters the apply behavior for the specified operation on the specified object.

Syntax

DBMS_APPLY_ADM.SET_DML_HANDLER(
   object_name          IN  VARCHAR2,
   object_type          IN  VARCHAR2,
   operation_name       IN  VARCHAR2,
   error_handler        IN  BOOLEAN   DEFAULT FALSE,
   user_procedure       IN  VARCHAR2,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL,
   apply_name           IN  VARCHAR2  DEFAULT NULL,
   assemble_lobs        IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-13 SET_DML_HANDLER Procedure Parameters

Parameter Description
object_name The name of the source object 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 object does not need to exist when you run this procedure.
object_type The type of the source object. Currently, TABLE is the only possible source object type.
operation_name The name of the operation, which can be specified as:
  • INSERT

  • UPDATE

  • DELETE

  • LOB_UPDATE

For example, suppose you run this procedure twice for the hr.employees table. In one call, you set operation_name to UPDATE and user_procedure to employees_update. In another call, you set operation_name to INSERT and user_procedure to employees_insert. Both times, you set error_handler to FALSE. In this case, the employees_update procedure is run for UPDATE operations on the hr.employees table, and the employees_insert procedure is run for INSERT operations on the hr.employees table.

error_handler If TRUE, then the specified user procedure is run when a row logical change record (row LCR) involving the specified operation on the specified object raises an apply process error. You can code the user procedure to resolve possible error conditions, notify administrators of the error, log the error, or any combination of these actions.

If FALSE, then the handler being set is run for all row LCRs involving the specified operation on the specified object.

user_procedure A user-defined procedure that is invoked during apply for the specified operation on the specified object. If the procedure is a DML handler, then it is invoked instead of the default apply performed by Oracle. If the procedure is an error handler, then it is invoked when the apply process encounters an error.

Specify NULL to unset a DML handler that is set for the specified operation on the specified object.

apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.
apply_name The name of the apply process that uses the DML handler or error handler.

If NULL, then the procedure sets the DML handler or error handler as a general handler for all apply processes in the database.

If the user_procedure parameter is set to NULL to unset a handler, and the handler being unset is set for a specific apply process, then use the apply_name parameter to specify the apply process to unset the handler.

assemble_lobs If TRUE, then LOB assembly is used for LOB columns in LCRs processed by the handler. LOB assembly combines multiple LCRs for a LOB column resulting from a single row change into one row LCR before passing the LCR to the handler. Database compatibility must be 10.2.0 or higher to use LOB assembly.

If FALSE, then LOB assembly is not used for LOB columns in LCRs processed by the handler.


Usage Notes

Run this procedure at the destination database. The SET_DML_HANDLER procedure provides a way for users to apply logical change records containing DML changes (row LCRs) using a customized apply.

If the error_handler parameter is set to TRUE, then it specifies that the user procedure is an error handler. An error handler is invoked only when a row LCR raises an apply process error. Such an error can result from a data conflict if no conflict handler is specified or if the update conflict handler cannot resolve the conflict. If the error_handler parameter is set to FALSE, then the user procedure is a DML handler, not an error handler, and a DML handler is always run instead of performing the specified operation on the specified object.

This procedure either sets a DML handler or an error handler for a particular operation on an object. It cannot set both a DML handler and an error handler for the same object and operation.

If the apply_name parameter is non-NULL, then the DML handler or error handler is set for the specified apply process. In this case, this handler is not invoked for other apply processes at the local destination database. If the apply_name parameter is NULL, the default, then the handler is set as a general handler for all apply processes at the destination database. When a handler is set for a specific apply process, then this handler takes precedence over any general handlers. For example, consider the following scenario:

In this case, the apply_hr apply process uses the handler_hr DML handler for UPDATE operations on the hr.employees table.

At the source database, you must specify an unconditional supplemental log group for the columns needed by a DML or error handler.

Attention:

Do not modify LONG, LONG RAW, or nonassembled LOB column data in an LCR with DML handlers, error handlers, or custom rule-based transformation functions. DML handlers and error handlers can modify LOB columns in row LCRs that have been constructed by LOB assembly.

Note:

Currently, setting an error handler for an apply process that is applying changes to a non-Oracle database is not supported.

The SET_DML_HANDLER procedure can be used to set either a DML handler or an error handler for row LCRs that perform a specified operation on a specified object. The signatures of a DML handler procedure and of an error handler procedure are described following this section.

In either case, you must specify the full procedure name for the user_procedure parameter in one of the following forms:

If the procedure is in a package, then the package_name must be specified. The user who invokes the SET_DML_HANDLER procedure must have EXECUTE privilege on the specified procedure. Also, if the schema_name is not specified, then the user who invokes the SET_DML_HANDLER procedure is the default.

For example, suppose the procedure_name has the following properties:

In this case, specify the following:

hr.apply_pkg.employees_default

The following restrictions apply to the user procedure:

See Also:

Oracle Streams Replication Administrator's Guide for information about and restrictions regarding DML handlers and LOB, LONG, and LONG RAW datatypes

Signature of a DML Handler Procedure

The procedure specified in the user_procedure parameter must have the following signature:

PROCEDURE user_procedure (
   parameter_name  IN  ANYDATA);

Here, user_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is a ANYDATA encapsulation of a row LCR.

See Also:

Chapter 229, "Logical Change Record TYPEs" for more information about LCRs

Signature of an Error Handler Procedure

The procedure you create for error handling must have the following signature:

PROCEDURE user_procedure (
     message            IN  ANYDATA,
     error_stack_depth  IN  NUMBER,
     error_numbers      IN  DBMS_UTILITY.NUMBER_ARRAY,
     error_messages     IN  emsg_array);

If you want to retry the DML operation within the error handler, then have the error handler procedure run the EXECUTE member procedure for the LCR. The last error raised is on top of the error stack. To specify the error message at the top of the error stack, use error_numbers(1) and error_messages(1).

Note:

  • Each parameter is required and must have the specified datatype. However, you can change the names of the parameters.

  • The emsg_array value must be a user-defined array that is a table of type VARCHAR2 with at least 76 characters.

Running an error handler results in one of the following outcomes:


SET_ENQUEUE_DESTINATION Procedure

This procedure sets the queue where the apply process automatically enqueues a message that satisfies the specified rule.

This procedure modifies the specified rule's action context to specify the queue. 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 a message. In this case, the client of the rules engine is an Oracle Streams apply process. 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 queue destination specified by this procedure always consists of the following name-value pair in an action context:

Syntax

DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
  rule_name               IN  VARCHAR2,
  destination_queue_name  IN  VARCHAR2);

Parameters

Table 20-14 SET_ENQUEUE_DESTINATION Procedure Parameters

Parameter Description
rule_name The name of the rule, specified as [schema_name.]rule_name. For example, to specify a rule named hr5 in the hr schema, enter hr.hr5 for this parameter. If the schema is not specified, then the current user is the default.
destination_queue_name The name of the queue into which the apply process should enqueue the message. Specify the queue in the form [schema_name.]queue_name. Only local queues can be specified.

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

If NULL, then an existing name-value pair with the name APPLY$_ENQUEUE is removed. If no name-value pair exists with the name APPLY$_ENQUEUE for the rule, then no action is taken.

If non-NULL and a name-value pair already exists for the rule with the name APPLY$_ENQUEUE, then it is removed, and a new name-value pair with the value specified by this parameter is added.


Usage Notes

If an apply handler, such as a DML handler, DDL handler, or message handler, processes a message that also is enqueued into a destination queue, then the apply handler processes the message before it is enqueued.

The following are considerations for using this procedure:


SET_EXECUTE Procedure

This procedure specifies whether a message that satisfies the specified rule is executed by an apply process.

This procedure modifies the specified rule's action context to specify message execution. 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 a message. In this case, the client of the rules engine is an Oracle Streams apply process. 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 message execution directive specified by this procedure always consists of the following name-value pair in an action context:

Syntax

DBMS_APPLY_ADM.SET_EXECUTE(
  rule_name  IN  VARCHAR2,
  execute    IN  BOOLEAN);

Parameters

Table 20-15 SET_EXECUTE Procedure Parameters

Parameter Description
rule_name The name of the rule, specified as [schema_name.]rule_name. For example, to specify a rule named hr5 in the hr schema, enter hr.hr5 for this parameter. If the schema is not specified, then the current user is the default.
execute If TRUE, then the procedure removes the name-value pair with the name APPLY$_EXECUTE for the specified rule. Removing the name-value pair means that the apply process executes messages that satisfy the rule. If no name-value pair with name APPLY$_EXECUTE exists for the rule, then no action is taken.

If FALSE, then the procedure adds a name-value pair to the rule's action context. The name is APPLY$_EXECUTE and the value is NO. An apply process does not execute a message that satisfies the rule and does not send the message to any apply handler. If a name-value pair already exists for the rule with the name APPLY$_EXECUTE, then it is removed, and a new one with the value NO is added.

If NULL, then the procedure raises an error.


Usage Notes

If the message is a logical change record (LCR) and the message is not executed, then the change encapsulated in the LCR is not made to the relevant local database object. Also, if the message is not executed, then it is not sent to any apply handler.

Note:

  • Oracle Streams capture processes, propagations, and messaging clients ignore the action context created by this procedure.

  • The specified rule must be in the positive rule set for an apply process for the apply process to follow the execution directive. If the rule is in the negative rule set for an apply process, then the apply process ignores the execution directive for the rule.


SET_GLOBAL_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified source database and, optionally, for the schemas at the source database and the tables owned by these schemas. This procedure overwrites any existing instantiation SCN for the database, and, if it sets the instantiation SCN for a schema or a table, then it overwrites any existing instantiation SCN for the schema or table.

This procedure gives you precise control over which DDL logical change records (DDL LCRs) from a source database are ignored and which DDL LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL,
  recursive             IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-16 SET_GLOBAL_INSTANTIATION_SCN Procedure Parameters

Parameter Description
source_database_name The global name of the source database. For example, DBS1.NET.

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

instantiation_scn The instantiation SCN. Specify NULL to remove the instantiation SCN metadata for the source database from the data dictionary.
apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.
recursive If TRUE, then the procedure sets the instantiation SCN for the source database, all schemas in the source database, and all tables owned by the schemas in the source database. This procedure selects the schemas and tables from the ALL_USERS and ALL_TABLES data dictionary views, respectively, at the source database under the security context of the current user.

If FALSE, then the procedure sets the global instantiation SCN for the source database, but does not set the instantiation SCN for any schemas or tables

Note: If recursive is set to TRUE, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the current user. Also, a table must be accessible to the current user in either the ALL_TABLES or DBA_TABLES data dictionary view at the source database for this procedure to set the instantiation SCN for the table at the destination database.


Usage Notes

If the commit SCN of a DDL LCR for a database object from a source database is less than or equal to the instantiation SCN for that source database at a destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

The global instantiation SCN specified by this procedure is used for a DDL LCR only if the DDL LCR does not have object_owner, base_table_owner, and base_table_name specified. For example, the global instantiation SCN set by this procedure is used for DDL LCRs with a command_type of CREATE USER.

If the recursive parameter is set to TRUE, then this procedure sets the instantiation SCN for each schema at a source database and for the tables owned by these schemas. This procedure uses the SET_SCHEMA_INSTANTIATION_SCN procedure to set the instantiation SCN for each schema, and it uses the SET_TABLE_INSTANTIATION_SCN procedure to set the instantiation SCN for each table. Each schema instantiation SCN is used for DDL LCRs on the schema, and each table instantiation SCN is used for DDL LCRs and row LCRs on the table.

If the recursive parameter is set to FALSE, then this procedure does not set the instantiation SCN for any schemas or tables.

Note:

  • Any instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.

  • The instantiation SCN is not set for the SYS or SYSTEM schemas.


SET_KEY_COLUMNS Procedures

This procedure records the set of columns to be used as the substitute primary key for apply purposes and removes existing substitute primary key columns for the specified object if they exist.

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.

Syntax

DBMS_APPLY_ADM.SET_KEY_COLUMNS(
   object_name          IN  VARCHAR2,
   column_list          IN  VARCHAR2,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL);

DBMS_APPLY_ADM.SET_KEY_COLUMNS(
   object_name          IN  VARCHAR2,
   column_table         IN  DBMS_UTILITY.NAME_ARRAY,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 20-17 SET_KEY_COLUMNS Procedure Parameters

Parameter Description
object_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. If the apply process is applying changes to a non-Oracle database in a heterogeneous environment, then the object name is not verified.
column_list A comma-delimited list of the columns in the table that you want to use as the substitute primary key, with no spaces between the column names.

If the column_list parameter is empty or NULL, then the current set of key columns is removed.

column_table A PL/SQL index-by table of type DBMS_UTILITY.NAME_ARRAY of the columns in the table that you want to use as the substitute primary key. The index for column_table must be 1-based, increasing, dense, and terminated by a NULL.

If the column_table parameter is empty or NULL, then the current set of key columns is removed.

apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.

Usage Notes

When not empty, this set of columns takes precedence over any primary key for the specified object. Do not specify substitute key columns if the object already has primary key columns and you want to use those primary key columns as the key.

Run this procedure at the destination database. At the source database, you must specify an unconditional supplemental log group for the substitute key columns.

Note:

  • Unlike true primary keys, columns specified as substitute key column columns can contain NULLs. However, Oracle recommends that each column you specify as a substitute key column be a NOT NULL column. You also should create a single index that includes all of the columns in a substitute key. Following these guidelines improves performance for updates, deletes, and piecewise updates to LOBs because Oracle can locate the relevant row more efficiently.

  • You should not permit applications to update the primary key or substitute key columns of a table. This ensures that Oracle can identify rows and preserve the integrity of the data.

  • If there is neither a primary key, nor a unique index that has at least one NOT NULL column, nor a substitute key for a table, then the key consists of all non LOB, non LONG, and non LONG RAW columns.


SET_PARAMETER Procedure

This procedure sets an apply parameter to the specified value.

Syntax

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name  IN  VARCHAR2, 
   parameter   IN  VARCHAR2, 
   value       IN  VARCHAR2);

Parameters

Table 20-18 SET_PARAMETER Procedure Parameters

Parameter Description
apply_name The apply process name. Do not specify an owner.
parameter The name of the parameter you are setting. See "Apply Process Parameters" for a list of these parameters.
value The value to which the parameter is set

Apply Process Parameters

The following table lists the parameters for the apply process.

Table 20-19 Apply Process Parameters

Parameter Name Possible Values Default Description
allow_duplicate_rows Y or N N If Y and more than one row is changed by a single row logical change record (row LCR) with an UPDATE or DELETE command type, then the apply process only updates or deletes one of the rows.

If N, then the apply process raises an error when it encounters a single row LCR with an UPDATE or DELETE command type that changes more than one row in a table.

Note: Regardless of the setting for this parameter, apply processes do not allow changes to duplicate rows for tables with LOB, LONG, or LONG RAW columns.

See Also: "Duplicate Rows and Substitute Primary Key Columns"

commit_serialization full or none full The order in which applied transactions are committed.

If full, then the apply process commits applied transactions in the order in which they were committed at the source database.

If none, then the apply process can commit transactions in any order. Performance is best if you specify none.

Regardless of the specification, applied transactions can execute in parallel subject to data dependencies and constraint dependencies.

Logical standby environments typically specify full.

disable_on_error Y or N Y If Y, then the apply process is disabled on the first unresolved error, even if the error is not fatal.

If N, then the apply process continues regardless of unresolved errors.

disable_on_limit Y or N N If Y, then the apply process is disabled if the apply process terminates because it reached a value specified by the time_limit parameter or transaction_limit parameter.

If N, then the apply process is restarted immediately after stopping because it reached a limit.

maximum_scn A valid SCN or infinite infinite The apply process is disabled before applying a transaction with a commit SCN greater than or equal to the value specified.

If infinite, then the apply process runs regardless of the SCN value.

parallelism A positive integer 1 The number of transactions that can be concurrently applied.

Setting the parallelism parameter to a number higher than the number of available parallel execution servers can disable the apply process. Make sure the PROCESSES and PARALLEL_MAX_SERVERS initialization parameters are set appropriately when you set the parallelism apply process parameter.

Note: When the value of this parameter is changed for a running apply process, the apply process is stopped and restarted automatically. This can take some time depending on the size of the transactions currently being applied.

preserve_encryption Y or N Y Whether to preserve encryption for columns encrypted using transparent data encryption.

If Y, then columns in tables at the destination database must be encrypted when corresponding columns in row LCRs are encrypted. If columns are encrypted in row LCRs but the corresponding columns are not encrypted in the tables at the destination database, then an error is raised when the apply process tries to apply the row LCRs.

If N, then columns in tables at the destination database do not need to be encrypted when corresponding columns in row LCRs are encrypted. If columns are encrypted in row LCRs but the corresponding columns are not encrypted in the tables at the destination database, then the apply process applies the changes in the row LCRs.

Note: When the value of this parameter is changed for a running apply process, the apply process is stopped and restarted automatically. This can take some time depending on the size of the transactions currently being applied.

startup_seconds 0, a positive integer, or infinite 0 The maximum number of seconds to wait for another instantiation of the same apply process to finish. If the other instantiation of the same apply process does not finish within this time, then the apply process does not start.

If infinite, then an apply process does not start until another instantiation of the same apply process finishes.

time_limit A positive integer or infinite infinite The apply process stops as soon as possible after the specified number of seconds since it started.

If infinite, then the apply process continues to run until it is stopped explicitly.

trace_level 0 or a positive integer 0 Set this parameter only under the guidance of Oracle Support Services.
transaction_limit A positive integer or infinite infinite The apply process stops after applying the specified number of transactions.

If infinite, then the apply process continues to run regardless of the number of transactions applied.

txn_lcr_spill_threshold A positive integer or infinite 10000 The apply process begins to spill messages from memory to hard disk for a particular transaction when the number of messages in memory for the transaction exceeds the specified number. The number of messages in first chunk of messages spilled from memory equals the number specified for this parameter, and the number of messages spilled in future chunks is either 100 or the number specified for this parameter, whichever is less.

If the reader server of an apply process has the specified number of messages in memory for a particular transaction, then when it detects the next message for this transaction, it spills the messages that are in memory to the hard disk. For example, if this parameter is set to 10000, and a transaction has 10,200 messages, then the reader server handles the transaction in the following way:

  1. Reads the first 10,000 messages in the transaction into memory

  2. Spills messages 1 - 10,000 to hard disk when it detects message 10,000

  3. Reads the next 100 messages in the transaction into memory

  4. Spills messages 10,001 - 10,100 to hard disk when it detects message 10,100

  5. Reads the next 100 messages in the transaction into memory

The apply process applies the first 10,100 messages from the hard disk and the last 100 messages from memory.

When the reader server spills messages from memory, the messages are stored in a database table on the hard disk. These messages are not spilled from memory to a queue table.

Message spilling occurs at the transaction level. For example, if this parameter is set to 10000, and the reader server of an apply process is assembling two transactions, one with 7,500 messages and another with 8,000 messages, then it does not spill any messages.

If infinite, then the apply process does not spill messages to the hard disk.

Query the DBA_APPLY_SPILL_TXN data dictionary view for information about transactions spilled by an apply process.

Note: When the value of this parameter is changed for a running apply process, the new setting does not take effect until the apply process is restarted.

write_alert_log Y or N Y If Y, then the apply process writes a message to the alert log on exit.

If N, then the apply process does not write a message to the alert log on exit.

The message specifies the reason why the apply process stopped.


Usage Notes

When you alter a parameter value, a short amount of time might pass before the new value for the parameter takes effect.

Note:

  • For all parameters that are interpreted as positive integers, the maximum possible value is 4,294,967,295. Where applicable, specify infinite for larger values.

  • For parameters that require an SCN setting, any valid SCN value can be specified.

Duplicate Rows and Substitute Primary Key Columns

A table has duplicate rows when the all of the column values are identical for two or more rows in the table, excluding LOB, LONG, and LONG RAW columns. You can specify substitute primary key columns for a table at a destination database using by the SET_KEY_COLUMNS procedure. When substitute primary key columns are specified for a table with duplicate rows at a destination database, and the allow_duplicate_rows apply process parameter is set to Y, meet the following requirements to keep the table data synchronized at the source and destination databases:

The rest of this section provides more details about these requirements.

If a table does not have a primary key, a unique index that has at least one NOT NULL column, or a substitute key, then the key consists of all non LOB, non LONG, and non LONG RAW columns. When there is no key for a table and the allow_duplicate_rows apply process parameter is set to Y, a single row LCR with an UPDATE or DELETE command type only is applied to one of the duplicate rows. In this case, if the table at the source database and the table at the destination database have corresponding duplicate rows, then a change that changes all of the duplicate rows at the source database also changes all the duplicate rows at the destination database when the row LCRs resulting from the change are applied.

For example, suppose a table at a source database has two duplicate rows. An update is performed on the duplicate rows, resulting in two row LCRs. At the destination database, one row LCR is applied to one of the duplicate rows. At this point, the rows are no longer duplicate at the destination database because one of the rows has changed. When the second row LCR is applied at the destination database, the rows are duplicate again. Similarly, if a delete is performed on these duplicate rows at the source database, then both rows are deleted at the destination database when the row LCRs resulting from the change are applied.

When substitute primary key columns are specified for a table, row LCRs are identified with rows in the table during apply using the substitute primary key columns. If substitute primary key columns are specified for a table with duplicate rows at a destination database, and the allow_duplicate_rows apply process parameter is set to Y, then an update performed on duplicate rows at the source database can result in different changes when the row LCRs are applied at the destination database. Specifically, if the update does not change one of the columns specified as a substitute primary key column, then the same duplicate row can be updated multiple times at the destination database, while other duplicate rows might not be updated.

Also, if the substitute key columns do not identify each row in the table at the destination database uniquely, then a row LCR identified with multiple rows can update any one of the rows. In this case, the update in the row LCR might not be applied to the correct row in the table at the destination database.

An apply process ignores substitute primary key columns when it determines whether rows in a table are duplicates. An apply process determines that rows are duplicates only if all of the column values in the rows are identical (excluding LOB, LONG, and LONG RAW columns). Therefore, an apply process always raises an error if a single update or delete changes two or more nonduplicate rows in a table.

For example, consider a table with columns c1, c2, and c3 on which the SET_KEY_COLUMNS procedure is used to designate column c1 as the substitute primary key. If two rows have the same key value for the c1 column, but different value for the c2 or c3 columns, then an apply process does not treat the rows as duplicates. If an update or delete modifies more than one row because the c1 values in the rows are the same, then the apply process raises an error regardless of the setting for the allow_duplicate_rows apply process parameter.


SET_SCHEMA_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified schema in the specified source database and, optionally, for the tables owned by the schema at the source database. This procedure overwrites any existing instantiation SCN for the schema, and, if it sets the instantiation SCN for a table, it overwrites any existing instantiation SCN for the table.

This procedure gives you precise control over which DDL logical change records (LCRs) for a schema are ignored and which DDL LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
  source_schema_name    IN  VARCHAR2,
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL,
  recursive             IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-20 SET_SCHEMA_INSTANTIATION_SCN Procedure Parameters

Parameter Description
source_schema_name The name of the source schema. For example, hr.
source_database_name The global name of the source database. For example, DBS1.NET.

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

instantiation_scn The instantiation SCN. Specify NULL to remove the instantiation SCN metadata for the source schema from the data dictionary.
apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.
recursive If TRUE, then the procedure sets the instantiation SCN for the specified schema and all tables owned by the schema in the source database. This procedure selects the tables owned by the specified schema from the ALL_TABLES data dictionary view at the source database under the security context of the current user.

If FALSE, then the procedure sets the instantiation SCN for specified schema, but does not set the instantiation SCN for any tables

Note: If recursive is set to TRUE, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the current user. Also, a table must be accessible to the current user in either the ALL_TABLES or DBA_TABLES data dictionary view at the source database for this procedure to set the instantiation SCN for the table at the destination database.


Usage Notes

If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at a destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

The schema instantiation SCN specified by this procedure is used on the following types of DDL LCRs:

For example, the schema instantiation SCN set by this procedure is used for a DDL LCR with a command_type of CREATE TABLE and ALTER USER.

The schema instantiation SCN specified by this procedure is not used for DDL LCRs with a command_type of CREATE USER. A global instantiation SCN is needed for such DDL LCRs.

If the recursive parameter is set to TRUE, then this procedure sets the table instantiation SCN for each table at the source database owned by the schema. This procedure uses the SET_TABLE_INSTANTIATION_SCN procedure to set the instantiation SCN for each table. Each table instantiation SCN is used for DDL LCRs and row LCRs on the table.

If the recursive parameter is set to FALSE, then this procedure does not set the instantiation SCN for any tables.

Note:

Any instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.

SET_TABLE_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified table in the specified source database. This procedure overwrites any existing instantiation SCN for the particular table.

This procedure gives you precise control over which logical change records (LCRs) for a table are ignored and which LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name    IN  VARCHAR2,
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 20-21 SET_TABLE_INSTANTIATION_SCN Procedure Parameters

Parameter Description
source_object_name The name of the source 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_database_name The global name of the source database. For example, DBS1.NET.

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

instantiation_scn The instantiation SCN. Specify NULL to remove the instantiation SCN metadata for the source table from the data dictionary.
apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.

Usage Notes

If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at some destination database, then the apply process at the destination database disregards the LCR. Otherwise, the apply process applies the LCR.

The table instantiation SCN specified by this procedure is used on the following types of LCRs:

For example, the table instantiation SCN set by this procedure is used for DDL LCRs with a command_type of ALTER TABLE or CREATE TRIGGER.

Note:

The instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.

SET_UPDATE_CONFLICT_HANDLER Procedure

This procedure adds, modifies, or removes a prebuilt update conflict handler for the specified object.

Syntax

DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
   object_name          IN  VARCHAR2,
   method_name          IN  VARCHAR2,
   resolution_column    IN  VARCHAR2,
   column_list          IN  DBMS_UTILITY.NAME_ARRAY,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 20-22 SET_UPDATE_CONFLICT_HANDLER Procedure Parameters

Parameter Description
object_name The schema and name of the table, specified as [schema_name.]object_name, for which an update conflict handler is being added, modified, or removed.

For example, if an update conflict handler is being added for table employees owned by user hr, then specify hr.employees. If the schema is not specified, then the current user is the default.

method_name Type of update conflict handler to create.

You can specify one of the prebuilt handlers, which determine whether the column list from the source database is applied for the row or whether the values in the row at the destination database are retained:

  • MAXIMUM: Applies the column list from the source database if it has the greater value for the resolution column. Otherwise, retains the values at the destination database.

  • MINIMUM: Applies the column list from the source database if it has the lesser value for the resolution column. Otherwise, retains the values at the destination database.

  • OVERWRITE: Applies the column list from the source database, overwriting the column values at the destination database.

  • DISCARD: Retains the column list from the destination database, discarding the column list from the source database.

If NULL, then the procedure removes any existing update conflict handler with the same object_name, resolution_column, and column_list. If non-NULL, then the procedure replaces any existing update conflict handler with the same object_name and resolution_column.

resolution_column Name of the column used to uniquely identify an update conflict handler. For the MAXIMUM and MINIMUM prebuilt methods, the resolution column is also used to resolve the conflict. The resolution column must be one of the columns listed in the column_list parameter.

NULL is not allowed for this parameter. For the OVERWRITE and DISCARD prebuilt methods, you can specify any column in the column list.

column_list List of columns for which the conflict handler is called.

If a conflict occurs for one or more of the columns in the list when an apply process tries to apply a row logical change record (row LCR), then the conflict handler is called to resolve the conflict. The conflict handler is not called if a conflict occurs only for columns that are not in the list.

Note: Prebuilt update conflict handlers do not support LOB, LONG, LONG RAW, and user-defined type columns. Therefore, you should not include these types of columns in the column_list parameter.

apply_database_link The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.

Note: Currently, conflict handlers are not supported when applying changes to a non-Oracle database.


Usage Notes

If you want to modify an existing update conflict handler, then you specify the table and resolution column of an the existing update conflict handler. You can modify the prebuilt method or the column list.

If you want to remove an existing update conflict handler, then specify NULL for the prebuilt method and specify the table, column list, and resolution column of the existing update conflict handler.

If an update conflict occurs, then Oracle completes the following series of actions:

  1. Calls the appropriate update conflict handler to resolve the conflict

  2. If no update conflict handler is specified or if the update conflict handler cannot resolve the conflict, then calls the appropriate error handler for the apply process, table, and operation to handle the error

  3. If no error handler is specified or if the error handler cannot resolve the error, then raises an error and moves the transaction containing the row LCR that caused the error to the error queue

If you cannot use a prebuilt update conflict handler to meet your requirements, then you can create a PL/SQL procedure to use as a custom conflict handler. You use the SET_DML_HANDLER procedure to designate one or more custom conflict handlers for a particular table. In addition, a custom conflict handler can process LOB columns and use LOB assembly.

Note:

Currently, setting an update conflict handler for an apply process that is applying to a non-Oracle database is not supported.

See Also:

Examples

The following is an example for setting an update conflict handler for the employees table in the hr schema:

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'salary';
  cols(2) := 'commission_pct';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.employees',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'salary',
    column_list           =>  cols);
END;
/

This example sets a conflict handler that is called if a conflict occurs for the salary or commission_pct column in the hr.employees table. If such a conflict occurs, then the salary column is evaluated to resolve the conflict. If a conflict occurs only for a column that is not in the column list, such as the job_id column, then this conflict handler is not called.


SET_VALUE_DEPENDENCY Procedure

This procedure sets or removes a value dependency. A value dependency is a virtual dependency definition that defines a relationship between the columns of two or more tables.

An apply process uses the name of a value dependencies to detect dependencies between row logical change records (row LCRs) that contain the columns defined in the value dependency. Value dependencies can define virtual foreign key relationships between tables, but, unlike foreign key relationships, value dependencies can involve more than two database objects.

This procedure is overloaded. The attribute_list and attribute_table parameters are mutually exclusive.

Syntax

DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
   dependency_name IN VARCHAR2,
   object_name     IN VARCHAR2,
   attribute_list  IN VARCHAR2);

DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
   dependency_name IN VARCHAR2,
   object_name     IN VARCHAR2,
   attribute_table IN DBMS_UTILITY.NAME_ARRAY);

Parameters

Table 20-23 SET_VALUE_DEPENDENCY Procedure Parameters

Parameter Description
dependency_name The name of the value dependency.

If a dependency with the specified name does not exist, then it is created.

If a dependency with the specified name exists, then the specified object and attributes are added to the dependency.

If NULL, an error is raised.

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

If NULL and the specified dependency exists, then the dependency is removed. If NULL and the specified dependency does not exist, then an error is raised.

If NULL, then attribute_list and attribute_table also must be NULL.

attribute_list A comma-delimited list of column names in the table. There must be no spaces between entries.
attribute_table A PL/SQL index-by table of type DBMS_UTILITY.NAME_ARRAY that contains names of columns in the table. The first column name should be at position 1, the second at position 2, and so on. The table does not need to be NULL terminated.


START_APPLY Procedure

This procedure directs the apply process to start applying messages.

Syntax

DBMS_APPLY_ADM.START_APPLY(
   apply_name  IN  VARCHAR2);

Parameter

Table 20-24 START_APPLY Procedure Parameter

Parameter Description
apply_name The apply process name. A NULL setting is not allowed. Do not specify an owner.

Usage Notes

The apply process status is persistently recorded. Hence, if the status is ENABLED, then the apply process is started upon database instance startup. An apply process (annn) is an Oracle background process. The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the start status of an apply process.


STOP_APPLY Procedure

This procedure stops the apply process from applying messages and rolls back any unfinished transactions being applied.

Syntax

DBMS_APPLY_ADM.STOP_APPLY(
   apply_name  IN  VARCHAR2,
   force       IN  BOOLEAN   DEFAULT FALSE);

Parameters

Table 20-25 STOP_APPLY Procedure Parameters

Parameter Description
apply_name The apply process name. A NULL setting is not allowed. Do not specify an owner.
force If TRUE, then the procedure stops the apply process as soon as possible.

If FALSE, then the procedure stops the apply process after ensuring that there are no gaps in the set of applied transactions.

The behavior of the apply process depends on the setting specified for the force parameter and the setting specified for the commit_serialization apply process parameter. See "Usage Notes" for more information.


Usage Notes

The apply process status is persistently recorded. Hence, if the status is DISABLED or ABORTED, then the apply process is not started upon database instance startup.

The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the STOP status of an apply process.

The following table describes apply process behavior for each setting of the force parameter in the STOP_APPLY procedure and the commit_serialization apply process parameter. In all cases, the apply process rolls back any unfinished transactions when it stops.

force commit_serialization Apply Process Behavior
TRUE full The apply process stops immediately and does not apply any unfinished transactions.
TRUE none When the apply process stops, some transactions that have been applied locally might have committed at the source database at a later point in time than some transactions that have not been applied locally.
FALSE full The apply process stops after applying the next uncommitted transaction in the commit order, if any such transaction is in progress.
FALSE none Before stopping, the apply process applies all of the transactions that have a commit time that is earlier than the applied transaction with the most recent commit time.

For example, assume that the commit_serialization apply process parameter is set to none and there are three transactions: transaction 1 has the earliest commit time, transaction 2 is committed after transaction 1, and transaction 3 has the latest commit time. Also assume that an apply process has applied transaction 1 and transaction 3 and is in the process of applying transaction 2 when the STOP_APPLY procedure is run. Given this scenario, if the force parameter is set to TRUE, then transaction 2 is not applied, and the apply process stops (transaction 2 is rolled back). If, however, the force parameter is set to FALSE, then transaction 2 is applied before the apply process stops.

A different scenario would result if the commit_serialization apply process parameter is set to full. For example, assume that the commit_serialization apply process parameter is set to full and there are three transactions: transaction A has the earliest commit time, transaction B is committed after transaction A, and transaction C has the latest commit time. In this case, the apply process has applied transaction A and is in the process of applying transactions B and C when the STOP_APPLY procedure is run. Given this scenario, if the force parameter is set to TRUE, then transactions B and C are not applied, and the apply process stops (transactions B and C are rolled back). If, however, the force parameter is set to FALSE, then transaction B is applied before the apply process stops, and transaction C is rolled back.

See Also:

"SET_PARAMETER Procedure" for more information about the commit_serialization apply process parameter