DBMS_APPLY_ADM, 2 of 2

Summary of DBMS_APPLY_ADM Subprograms

Table 4-1 DBMS_APPLY_ADM Subprograms (Page 1 of 2)

Subprogram	Description
"ALTER_APPLY Procedure"	Alters an apply process
"CREATE_APPLY Procedure"	Creates an apply process
"DELETE_ALL_ERRORS Procedure"	Deletes all the error transactions for the specified apply process from the error queue
"DELETE_ERROR Procedure"	Deletes the specified error transaction from the error queue
"DROP_APPLY Procedure"	Drops an apply process
"EXECUTE_ALL_ERRORS Procedure"	Reexecutes the error queue transactions for the specified apply process.
"EXECUTE_ERROR Procedure"	Reexecutes the specified error queue 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_GLOBAL_INSTANTIATION_SCN Procedure"	Records the specified instantiation SCN for the specified source database
"SET_KEY_COLUMNS Procedure"	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
"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
"START_APPLY Procedure"	Directs the apply process to start applying events
"STOP_APPLY Procedure"	Stops the apply process from applying any events and rolls back any unfinished transactions being applied

Note:

All procedures and functions commit unless specified otherwise.

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

Parameters

Table 4-2 ALTER_APPLY Procedure Parameters (Page 1 of 5)

Parameter	Description
`apply_name`	The name of the apply process being altered. You must specify an existing apply process name.
`rule_set_name`	The name of the rule set that contains the apply rules for this apply process. If you want to use a 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 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_RULE_ADM` package. If you specify `NULL`, then the apply process applies all LCRs and user messages in its queue.
`remove_rule_set`	If `true`, then removes the rule set for the specified apply process. If `false`, then retains any 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. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` 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_msgs`, enter `hr.apply_pkg.process_msgs`. An error is returned if the specified procedure does not exist. If the schema is not specified, then the user who invokes the `ALTER_APPLY` procedure is the default. This user must have `EXECUTE` privilege on a specified message handler.
`remove_message_handler`	If `true`, then removes the message handler for the specified apply process. If `false`, then 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 LCRs in the queue for the apply process. You must specify an existing procedure in the form `[``schema_name``.]``procedure_name`. For example, to specify a procedure in the `hr` schema named `process_ddls`, enter `hr.process_ddls`. An error is returned if the specified procedure does not exist. If the schema is not specified, then the user who invokes the `ALTER_APPLY` procedure is the default. This user must have `EXECUTE` privilege on a specified DDL handler. 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.
`remove_ddl_handler`	If `true`, then removes the DDL handler for the specified apply process. If `false`, then 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 who applies all DML and DDL changes and who runs user-defined apply handlers. If `NULL`, then the apply user is not changed. The specified user must have the necessary privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. The specified user must also have dequeue privileges on the queue used by the apply process and privileges to execute the rule set and transformation functions used 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 one of the following procedures in the `DBMS_STREAMS_ADM` package with the `streams_type` parameter set to `apply`: `ADD_GLOBAL_RULES` `ADD_SCHEMA_RULES` `ADD_TABLE_RULES` `ADD_SUBSET_RULES` 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: Oracle9i Streams for more information about tags
`remove_apply_tag`	If `true`, then sets the apply tag for the specified apply process to `NULL`, and the apply process generated redo entries with `NULL` tags. If `false`, then 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`.

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:

message_handler
ddl_handler
apply_user
apply_tag

CREATE_APPLY Procedure

Creates an apply process.

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

Parameters

Table 4-3 CREATE_APPLY Procedure Parameters (Page 1 of 4)

Parameter	Description
`queue_name`	The name of the queue from which the apply process dequeues LCRs and user 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. Note: The `apply_name` setting cannot be altered after the apply process is created.
`rule_set_name`	The name of the rule set that contains the apply rules for this apply process. If you want to use a 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 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_RULE_ADM` package. If you specify `NULL`, then the apply process applies all LCRs and user messages in its queue.
`message_handler`	A user-defined procedure that processes non-LCR messages in the queue for the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` 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_msgs`, enter `hr.apply_pkg.process_msgs`. An error is returned if the specified procedure does not exist. If the schema is not specified, then the user who invokes the `CREATE_APPLY` procedure is the default. This user must have `EXECUTE` privilege on a specified message handler. See "Usage Notes" for more information about a message handler procedure.
`ddl_handler`	A user-defined procedure that processes DDL LCRs in the queue for the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` 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. If the schema is not specified, then the user who invokes the `CREATE_APPLY` procedure is the default. This user must have `EXECUTE` privilege on a specified DDL handler. 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 and who runs user-defined apply handlers. If `NULL`, then the user who runs the `CREATE_APPLY` procedure is used. The user must have the necessary privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. The specified user must also have dequeue privileges on the queue used by the apply process and privileges to execute the rule set and transformation functions used 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. See Also: Oracle9i Streams for more information about the privileges required to apply changes
`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: Oracle9i Streams for more information about tags
`apply_captured`	Either `true` or `false`. If `true`, then the apply process applies only the events in a queue that were captured by a Streams capture process. If `false`, then the apply process applies only the user-enqueued events in a queue. These events are user messages that were not captured by a Streams capture process. These messages may or may not contain a user-created LCR. To apply both captured and user-enqueued events in a 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: Oracle9i Streams for more information about captured and user-enqueued events

Usage Notes

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  SYS.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 SYS.AnyData encapsulation of a user message. For the DDL handler procedure, the parameter passed to the procedure is a SYS.AnyData encapsulation of a DDL LCR.

See Also:

Chapter 108, "Logical Change Record Types" for information DDL LCRs

DELETE_ALL_ERRORS Procedure

Deletes all the error transactions for the specified apply process from the error queue.

Syntax

DBMS_APPLY_ADM.DELETE_ALL_ERRORS(
   apply_name    IN VARCHAR2 DEFAULT NULL);

Parameter

Table 4-4 DELETE_ALL_ERRORS Procedure Parameter

Parameter Description

Parameter	Description
`apply_name`	The name of the apply process that raised the errors while processing the transactions. If `NULL`, then all error transactions for all apply processes are deleted.

apply_name

The name of the apply process that raised the errors while processing the transactions.

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

DELETE_ERROR Procedure

Deletes the specified error transaction from the error queue.

Syntax

DBMS_APPLY_ADM.DELETE_ERROR(
   local_transaction_id    IN VARCHAR2);

Parameter

Table 4-5 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

Drops an apply process.

Syntax

  DBMS_APPLY_ADM.DROP_APPLY(
     apply_name         IN VARCHAR2);

Parameter

Table 4-6 DROP_APPLY Procedure Parameter

Parameter	Description
`apply_name`	The name of the apply process being dropped. You must specify an existing apply process name.

EXECUTE_ALL_ERRORS Procedure

Reexecutes the error queue transactions for the specified apply process.

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

Syntax

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

Parameters

Table 4-7 EXECUTE_ALL_ERRORS Procedure Parameters

Parameter Description

Parameter	Description
`apply_name`	The name of the apply process that raised the errors while processing the transactions. If `NULL`, then all error transactions for all apply processes are reexecuted.
`execute_as_user`	If `TRUE`, then reexecutes the transactions in the security context of the current user. If `FALSE`, then 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 transaction in the error queue. 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.

apply_name

The name of the apply process that raised the errors while processing the transactions.

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

execute_as_user

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

If FALSE, then 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 transaction in the error queue.

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

Reexecutes the specified error queue transaction.

Syntax

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

Parameters

Table 4-8 EXECUTE_ERROR Procedure Parameters

Parameter Description

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 reexecutes the transaction in the security context of the current user. If `FALSE`, then 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 transaction in the error queue. 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.

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 reexecutes the transaction in the security context of the current user.

If FALSE, then 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 transaction in the error queue.

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.

GET_ERROR_MESSAGE Function

Returns the message payload from the error queue for the specified message number and transaction identifier.

Syntax

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

Parameters

Table 4-9 GET_ERROR_MESSAGE Function Parameters

Parameter	Description
`local_transaction_id`	Identifier of the error transaction for which to return a message

SET_DML_HANDLER 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. 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 may 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.

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

Note:

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

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

Parameters

Table 4-10 SET_DML_HANDLER Procedure Parameters (Page 1 of 2)

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.
`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 LCR involving the specified operation on the specified object raises an apply process error. The user procedure may try to resolve possible error conditions, or it may simply notify administrators of the error or log the error. If `false`, then the handler being set is run for all row LCRs involving the specified operation on the specified object. Note: Currently, error handlers are not supported when applying changes to a non-Oracle database.
`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.
`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

The SET_DML_HANDLER procedure can be used to set either a general DML handler or an error handler for row LCRs that perform a specified operation on a specified object. The following sections describe the signature of a general DML handler procedure and the signature of an error handler procedure.

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

[schema_name.]package_name.procedure_name
[schema_name.]procedure_name

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

For example, suppose the procedure_name has the following properties:

hr is the schema_name.
apply_pkg is the package_name.
employees_default is the procedure_name.

In this case, specify the following:

hr.apply_pkg.employees_default

The following restrictions apply to the user procedure:

Do not execute COMMIT or ROLLBACK statements. Doing so may endanger the consistency of the transaction that contains the LCR.
If you are manipulating a row using the EXECUTE member procedure for the row LCR, then do not attempt to manipulate more than one row in a row operation. You must construct and execute manually any DML statements that manipulate more than one row.
If the command type is UPDATE or DELETE, then row operations resubmitted using the EXECUTE member procedure for the LCR must include the entire key in the list of old values. The key is the primary key, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure.
If the command type is INSERT, then row operations resubmitted using the EXECUTE member procedure for the LCR should include the entire key in the list of new values. Otherwise, duplicate rows are possible. The key is the primary key, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure.

Signature of a General DML Handler Procedure

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

PROCEDURE user_procedure (
   parameter_name   IN  SYS.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 SYS.AnyData encapsulation of a row LCR.

See Also:

Chapter 108, "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 SYS.AnyData,
     error_stack_depth   IN NUMBER,
     error_numbers       IN DBMS_UTILITY.NUMBER_ARRAY,
     error_messages      IN emsg_array);

Note:

Each parameter is required and must have the specified datatype. However, you can change the names of the parameters.
The emsg_array parameter 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:

The error handler successfully resolves the error and returns control to the apply process.
The error handler fails to resolve the error, and the error is raised. The raised error causes the transaction to be rolled back and placed in the error queue.

If you want to retry the DML operation, then have the error handler procedure run the EXECUTE member procedure for the LCR.

SET_GLOBAL_INSTANTIATION_SCN Procedure

Records the specified instantiation SCN for the specified source database. This procedure overwrites any existing instantiation SCN for the database.

This procedure gives you precise control over which DDL LCRs for a database are ignored and which DDL LCRs are applied by an apply process. 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 database at some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

The 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 instantiation SCN set by this procedure is used for DDL LCRs with a command_type of CREATE USER.

Attention:

If you run the SET_GLOBAL_INSTANTIATION_SCN for a database, then you should run SET_SCHEMA_INSTANTIATION_SCN for all of the existing schemas in the database and SET_TABLE_INSTANTIATION_SCN for all of the existing tables in the database. If you add new schemas and tables to the database in the future, then you need not run these procedures for the new schemas and tables.

Note:

This procedure sets the instantiation SCN only for DDL LCRs. To set the instantiation SCN for row LCRs, which record the results of DML changes, use SET_TABLE_INSTANTIATION_SCN.
The instantiation SCN set by the SET_SCHEMA_INSTANTIATION_SCN procedure is used for DDL LCRs that have object_owner specified.
The instantiation SCN set by the SET_TABLE_INSTANTIATION_SCN procedure is used for DDL LCRs that have both base_table_owner and base_table_name specified, except for DDL LCRs with a command_type of CREATE TABLE.
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.

See Also:

"SET_SCHEMA_INSTANTIATION_SCN Procedure"
"SET_TABLE_INSTANTIATION_SCN Procedure"
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle9i Streams

Syntax

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

Parameters

Table 4-11 SET_GLOBAL_INSTANTIATION_SCN Procedure Parameters

Parameter Description

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 local domain is appended to the database name automatically. For example, if you specify `DBS1` and the local domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN number. 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.

source_database_name

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

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

instantiation_scn

The instantiation SCN number. 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.

SET_KEY_COLUMNS 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. Unlike true primary keys, these columns may contain NULLs.

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:

Oracle Corporation recommends that each column you specify as a substitute key column be a NOT NULL column. You should also 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.

Note:

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, | 
     column_table         IN   DBMS_UTILITY.NAME_ARRAY, } 
   apply_database_link    IN   VARCHAR2  DEFAULT NULL);

Parameters

Table 4-12 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.

SET_PARAMETER Procedure

Sets an apply parameter to the specified value.

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

Syntax

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

Parameters

Table 4-13 SET_PARAMETER Procedure Parameters

Parameter	Description
`apply_name`	The apply process name
`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 4-14 Apply Process Parameters (Page 1 of 3)

Parameter Name	Possible Values	Default	Description
`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 may commit transactions may commit in any order. Performance is best if you specify `none`. Regardless of the specification, applied transactions may 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 may be concurrently applied Note: When you change the value of this parameter, the apply process is stopped and restarted automatically. This may take some time depending on the size of the transactions currently being applied. Setting the parallelism parameter to a number higher than the number of available parallel execution servers may 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.
`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.

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.

SET_SCHEMA_INSTANTIATION_SCN Procedure

Records the specified instantiation SCN for the specified schema in the specified source database. This procedure overwrites any existing instantiation SCN for the particular schema.

This procedure gives you precise control over which DDL LCRs for a schema are ignored and which DDL LCRs are applied by an apply process. 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 some destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

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

DDL LCRs with a command_type of CREATE TABLE
DDL LCRs with a non-NULL object_owner specified and no base_table_owner nor base_table_name specified.

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

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

Attention:

If you run the SET_SCHEMA_INSTANTIATION_SCN for a schema, then you should run SET_TABLE_INSTANTIATION_SCN for all of the existing tables in the schema. If you add new tables to the schema in the future, then you need not run SET_TABLE_INSTANTIATION_SCN for these tables.

Note:

This procedure sets the instantiation SCN only for DDL LCRs. To set the instantiation SCN for row LCRs, which record the results of DML changes, use SET_TABLE_INSTANTIATION_SCN.
The instantiation SCN set by the SET_TABLE_INSTANTIATION_SCN procedure is used for DDL LCRs that have both base_table_owner and base_table_name specified, except for DDL LCRs with a command_type of CREATE TABLE.
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.

See Also:

"SET_GLOBAL_INSTANTIATION_SCN Procedure"
"SET_TABLE_INSTANTIATION_SCN Procedure"
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle9i Streams

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

Parameters

Table 4-15 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 local is appended to the database name automatically. For example, if you specify `DBS1` and the local domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN number. 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.

SET_TABLE_INSTANTIATION_SCN 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 LCRs for a table are ignored and which LCRs are applied by an apply process. 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 instantiation SCN specified by this procedure is used on the following types of LCRs:

Row LCRs for the table
DDL LCRs that have a non-NULL base_table_owner and base_table_name specified, except for DDL LCRs with a command_type of CREATE TABLE

For example, the 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.

See Also:

"SET_GLOBAL_INSTANTIATION_SCN Procedure"
"SET_SCHEMA_INSTANTIATION_SCN Procedure"
"LCR$_ROW_RECORD Type" for more information about row LCRs
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle9i Streams

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 4-16 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 local domain name is appended to the database name automatically. For example, if you specify `DBS1` and the global domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN number. 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.

SET_UPDATE_CONFLICT_HANDLER Procedure

Adds, modifies, or removes an update conflict handler for the specified object.

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:

Calls the appropriate update conflict handler to resolve the conflict
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
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

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

See Also:
"Signature of an Error Handler Procedure" for information about setting an error handler

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 4-17 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 built-in 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 removes any existing update conflict handler with the same `object_name`, `resolution_column`, and `column_list`. If non-`NULL`, then 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 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 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: Conflict resolution does not support LOB columns. Therefore, you should not include LOB 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

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.

START_APPLY Procedure

Directs the apply process to start applying events.

The start status is persistently recorded. Hence, if the status is START, then the apply process is started upon database instance startup. Each apply process is an Oracle background process and is prefixed by AP.

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.

You can create the apply process using the following procedures:

DBMS_APPLY_ADM.CREATE_APPLY
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
DBMS_STREAMS_ADM.ADD_TABLE_RULES
DBMS_STREAMS_ADM.ADD_SUBSET_RULES

See Also:
Chapter 73, "DBMS_STREAMS_ADM"

Syntax

DBMS_APPLY_ADM.START_APPLY(
   apply_name  IN VARCHAR2);

Parameter

Table 4-18 START_APPLY Procedure Parameter

Parameter	Description
`apply_name`	The apply process name. A `NULL` setting is not allowed.

STOP_APPLY Procedure

Stops the apply process from applying events and rolls back any unfinished transactions being applied.

The stop status is persistently recorded. Hence, if the status is STOP, 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.

Syntax

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

Parameters

Table 4-19 STOP_APPLY Procedure Parameters

Parameter Description

apply_name

The apply process name. A NULL setting is not allowed.

force

If true, then stops the apply process as soon as possible.

If false, then 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 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 may 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