PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
The DBMS_APPLY_ADM
package, one of a set of Streams packages, provides administrative interfaces to start, stop, and configure an apply process. This package includes subprograms for configuring apply handlers, setting enqueue destinations for events, and specifying execution directives for events. 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:
This procedure alters an apply process.
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);
Parameter | Description |
---|---|
|
The name of the apply process being altered. You must specify an existing apply process name. Do not specify an owner. |
|
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 events. If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form 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 If you specify |
|
If 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 events in its queue that are not discarded by the negative rule set. If If the |
|
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: If the procedure is in a package, then the The user who invokes the |
|
If If If the |
|
A user-defined procedure that processes DDL LCRs in the queue for the apply process. You must specify an existing procedure in the form The user who invokes the All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the |
|
If If If the |
|
The user who applies all DML and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. If To change the apply user, the user who invokes the 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, make sure the apply user has the following privileges:
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 Note: If the specified user is dropped using |
|
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 The following is an example of a tag with a hexadecimal value of HEXTORAW('17') See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
If If If the |
|
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 into a queue when a capture process captures the commit directive for a transaction that contains row LCRs that were captured, and when a user or application enqueues messages into a queue and then issues a You must specify an existing procedure in the form The user who invokes the The precommit handler procedure must conform to the following restrictions:
If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the events in the transaction are moved to the error queue. |
|
If If If the |
|
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 events. If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form 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 If you specify If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first. |
|
If 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 events in its queue that are not discarded by the positive rule set. If If the |
An apply process is stopped and restarted automatically when you change the value of one or more of the following ALTER_APPLY
procedure parameters:
This procedure specifies whether to compare the old value of one or more columns in a 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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about conflict detection and resolution in a Streams environment |
DBMS_APPLY_ADM.COMPARE_OLD_VALUES( object_name IN VARCHAR2, { column_list 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);
Note: This procedure is overloaded. The |
By default, an apply process uses the old column values in a row LCR to detect conflicts. You may 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 nonkey and non time columns.
This procedure creates an apply process.
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);
Parameter | Description |
---|---|
|
The name of the queue from which the apply process dequeues LCRs and user messages. You must specify an existing queue in the form Note: The |
|
The name of the apply process being created. A The specified name must not match the name of an existing apply process or messaging client. Note: The |
|
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 events. If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form If you specify 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 |
|
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: If the procedure is in a package, then the The user who invokes the See "Usage Notes" for more information about a message handler procedure. |
|
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: If the procedure is in a package, then the The user who invokes the All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the See "Usage Notes" for more information about a DDL handler procedure. |
|
The user who applies all DML and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. If Only a user who is granted Note: If the specified user is dropped using |
|
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 The following is an example of a tag with a hexadecimal value of HEXTORAW('17') If See Also: Oracle Streams Replication Administrator's Guide for more information about tags |
|
If If To apply both captured and user-enqueued events in a queue, you must create at least two apply processes. Note: The See Also: Oracle Streams Concepts and Administration for more information about processing captured or user-enqueued events with an apply process |
|
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 into a queue when a capture process captures the commit directive for a transaction that contains row LCRs that were captured, and when a user or application enqueues messages into a queue and then issues a You must specify an existing procedure in one of the following forms: If the procedure is in a package, then the The user who invokes the If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the events in the transaction are moved to the error queue. The precommit handler procedure must conform to the following restrictions:
See "Usage Notes" for more information about a precommit handler procedure. |
|
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 events. If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form If you specify 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 If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first. |
|
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 events, then the apply process can apply events from only one capture process at one source database. If If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify The rules in the apply process rule sets determine which events 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 |
The user who invokes this procedure must be granted DBA
role.
The apply_user
parameter specifies the user who applies changes that satisfy the apply process rule sets and who runs user-defined apply handlers. 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, make sure the apply user has the following privileges:
These privileges must be granted directly to the apply user. They cannot be granted through roles.
The following sections describe the PL/SQL procedures that are specified as message handlers, DDL handlers, and precommit handlers.
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 174, "Logical Change Record TYPEs" for information about DDL LCRs |
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.
This procedure deletes all the error transactions for the specified apply process.
DBMS_APPLY_ADM.DELETE_ALL_ERRORS( apply_name IN VARCHAR2 DEFAULT NULL);
This procedure deletes the specified error transaction.
DBMS_APPLY_ADM.DELETE_ERROR( local_transaction_id IN VARCHAR2);
Parameter | Description |
---|---|
|
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. |
This procedure drops an apply process.
DBMS_APPLY_ADM.DROP_APPLY( apply_name IN VARCHAR2, drop_unused_rule_sets IN BOOLEAN DEFAULT false);
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 Streams rules. Information about such a rule is removed even if the rule is not in either rule set for the apply process. The following are the data dictionary views for Streams rules:
ALL_STREAMS_GLOBAL_RULES
DBA_STREAMS_GLOBAL_RULES
ALL_STREAMS_MESSAGE_RULES
DBA_STREAMS_MESSAGE_RULES
ALL_STREAMS_SCHEMA_RULES
DBA_STREAMS_SCHEMA_RULES
ALL_STREAMS_TABLE_RULES
DBA_STREAMS_TABLE_RULES
See Also:
Oracle Streams Concepts and Administration for more information about Streams data dictionary views |
This procedure reexecutes the error transactions for the specified apply process.
The transactions are reexecuted in commit SCN order. Error reexecution stops if an error is raised.
DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS( apply_name IN VARCHAR2 DEFAULT NULL, execute_as_user IN BOOLEAN DEFAULT false);
This procedure reexecutes the specified error transaction.
DBMS_APPLY_ADM.EXECUTE_ERROR( local_transaction_id IN VARCHAR2, execute_as_user IN BOOLEAN DEFAULT false);
This function returns the message payload from the error queue for the specified message number and transaction identifier. The message is an event, which may be a logical change record (LCR) or a non-LCR event.
One version of this function contains two OUT
parameters. These OUT
parameters contain the destination queue into which the event should be enqueued, if one exists, and whether or not the event 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.
DBMS_APPLY_ADM.GET_ERROR_MESSAGE( message_number IN NUMBER, local_transaction_id IN VARCHAR2, destination_queue_name OUT VARCHAR2, execute OUT BOOLEAN) RETURN SYS.AnyData;
Note: This function is overloaded. One version of this function contains two |
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.
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);
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.
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:
handler_hr
is specified for an apply process named apply_hr
for UPDATE
operations on the hr.employees
table.handler_gen
also exists for UPDATE
operations on the hr.employees
table.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 |
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 following sections describe the signature of a 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:
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:
COMMIT
or ROLLBACK
statements. Doing so may endanger the consistency of the transaction that contains the LCR.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.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 or the smallest unique index that has at least one NOT
NULL
column, unless a substitute key has been specified by the SET_KEY_COLUMNS
procedure. If there is no specified key, then the key consists of all non LOB
, non LONG
, and non LONG
RAW
columns.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 or the smallest unique index that has at least one NOT
NULL
column, unless a substitute key has been specified by the SET_KEY_COLUMNS
procedure. If there is no specified key, then the key consists of all non LOB, non LONG
, and non LONG
RAW
columns.
See Also:
Oracle Streams Replication Administrator's Guide for information about and restrictions regarding DML handlers and LOB, |
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 174, "Logical Change Record TYPEs" for more information about LCRs |
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);
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)
.
Running an error handler results in one of the following outcomes:
This procedure sets the queue where an event that satisfies the specified rule is enqueued automatically by an apply process.
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 an event. In this case, the client of the rules engine is a 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:
APPLY$_ENQUEUE
.SYS.AnyData
instance containing the queue name specified as a VARCHAR2
.DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION( rule_name IN VARCHAR2, destination_queue_name IN VARCHAR2);
If an apply handler, such as a DML handler, DDL handler, or message handler, processes an event that is also enqueued into a destination queue, then the apply handler processes the event before it is enqueued.
The following are considerations for using this procedure:
This procedure specifies whether an event that satisfies the specified rule is executed by an apply process.
This procedure modifies the specified rule's action context to specify event 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 an event. In this case, the client of the rules engine is a 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.
An event execution directive specified by this procedure always consists of the following name-value pair in an action context:
APPLY$_EXECUTE
.SYS.AnyData
instance that contains NO
as a VARCHAR2
. When the value is NO
, then an apply process does not execute the event and does not send the event to any apply handler.DBMS_APPLY_ADM.SET_EXECUTE( rule_name IN VARCHAR2, execute IN BOOLEAN);
If the event is a logical change record (LCR) and the event is not executed, then the change encapsulated in the LCR is not made to the relevant local database object. Also, if the event is not executed, then it is not sent to any apply handler.
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 LCRs from a source database are ignored and which DDL LCRs are applied by an apply process.
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);
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.
See Also:
|
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. Unlike true primary keys, these columns may contain NULL
s.
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);
Note: This procedure is overloaded. The |
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.
This procedure sets an apply parameter to the specified value.
DBMS_APPLY_ADM.SET_PARAMETER ( apply_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2);
Parameter | Description |
---|---|
|
The apply process name. Do not specify an owner. |
|
The name of the parameter you are setting. See "Apply Process Parameters" for a list of these parameters. |
|
The value to which the parameter is set |
The following table lists the parameters for the apply process.
When you alter a parameter value, a short amount of time may pass before the new value for the parameter takes effect.
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 LCRs for a schema are ignored and which DDL LCRs are applied by an apply process.
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);
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:
command_type
of CREATE
TABLE
NULL
object_owner
specified and no base_table_owner
nor base_table_name
specified.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. |
See Also:
|
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 LCRs for a table are ignored and which LCRs are applied by an apply process.
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);
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:
NULL
base_table_owner
and base_table_name
specified, except for DDL LCRs with a command_type
of CREATE
TABLE
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. |
See Also:
|
This procedure adds, modifies, or removes an update conflict handler for the specified object.
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);
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:
Note: Currently, setting an update conflict handler for an apply process that is applying to a non-Oracle database is not supported. |
See Also:
|
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.
This procedure directs the apply process to start applying events.
DBMS_APPLY_ADM.START_APPLY( apply_name IN VARCHAR2);
Parameter | Description |
---|---|
|
The apply process name. A |
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
This procedure stops the apply process from applying events and rolls back any unfinished transactions being applied.
DBMS_APPLY_ADM.STOP_APPLY( apply_name IN VARCHAR2, force IN BOOLEAN DEFAULT false);
Parameter | Description |
---|---|
|
The apply process name. A |
|
If If The behavior of the apply process depends on the setting specified for the |
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.
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.
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 |