|
Chapter 17 Deferred Transactions and Remote Procedure Calls |
|
The DBMS_DEFER_SYS package provides a number of programs for administrative tasks associated with deferred transactions.
The DBMS_DEFER_SYS package is created when the Oracle database is installed. The dbmsdefr.sql script (found in the built-in packages source directory, as described in Chapter 1, Introduction ) contains the source code for this package's specification. This script is called by catrep.sql , which must be run to install the advanced replication packages. The wrapped sql script prvtrctf.sql creates the public synonym DBMS_DEFER_SYS. No EXECUTE privileges are granted on DBMS_DEFER_SYS; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.
Table 17.10 lists the programs available in the DBMS_DEFER_SYS package.
|
Name |
Description |
Use in SQL? |
|---|---|---|
|
Adds a destination to the DEFDEFAULTDEST data dictionary view |
No |
|
|
Creates a copy of an RPC with a different destination |
No |
|
|
Deletes a destination from the DEFDEFAULTDEST data dictionary view |
No |
|
|
Deletes an error from the DEFERROR data dictionary view |
No |
|
|
Deletes a transaction from the DEFTRANDEST data dictionary view |
No |
|
|
Returns a BOOLEAN indicating whether deferred transactions from the current site to the destination site are disabled |
No |
|
|
Acquires a lock to disable deferred pushes |
No |
|
|
Executes an RPC immediately |
No |
|
|
Reexecutes an RPC that failed previously |
No |
|
|
Re-executes a failed RPC under security context of connected user |
No |
|
|
Purges transactions that have been propagated from the deferred transaction queue |
No |
|
|
Pushes queued transaction to destination node |
No |
|
|
Makes designated user the propagator for the local database |
No |
|
|
Schedules automatic RPC pushes between a master or snapshot site another master site |
No |
|
|
Schedules automatic purge of transactions that have been propagated from the queue |
No |
|
|
Schedules automatic pushes to destination node |
No |
|
|
Disables deferred transactions between the current site and a destination site |
No |
|
|
Complement to REGISTER_PROPAGATOR; revokes privileges granted to make user the local database's propagator |
No |
|
|
Stops automatic RPC pushes between a master or snapshot site and another master site |
No |
|
|
Complement to SCHEDULE_PURGE; unschedules automatic purge of transactions that have been propagated to the queue |
No |
|
|
Complement to SCHEDULE_PUSH; unschedules automatic pushes to destination node |
No |
The DBMS_DEF_SYS package may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
crt_err_err |
-23324 |
Parameter type does not match actual type |
The following table defines the constants defined in the DBMS_DEFER_SYS package. These constants are used internally in the package.
|
Type/Name |
Description |
|---|---|
|
CONSTANT parm_buffer_size |
Size of long buffer used for packing parameters (= 4096) |
|
CONSTANT default_alert_name |
VARCHAR2(30):= ORA$DEFER_ALERT |
The DBMS_DEFER_SYS package's ADD_DEFAULT_DEST and DELETE_DEFAULT_DEST procedures add and delete records in the DEFDEFAULTDEST data dictionary view.
The ADD_DEFAULT_DEST procedure adds records in the DEFDEFAULTDEST data dictionary view. Adding a record to this view effectively specifies a default destination for deferred RPCs. The specification is,
PROCEDURE DBMS_DEFER_SYS.ADD_DEFAULT_DEST (dblink IN VARCHAR2);
where dblink is the global name of the destination site being added.
There are no restrictions on calling ADD_DEFAULT_DEST.
The ADD_DEFAULT_DEST procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
None |
-23352 |
Specified destination is already in DEFDEFAULTDEST data dictionary view |
The following call adds the default destination named D7NY.BIGWHEEL.COM to DEFDEFAULTDEST:
BEGIN DBMS_DEFER_SYS.ADD_DEFAULT_DEST('D7NY.BIGWHEEL.COM'); END;
Of course, the appropriate database links must be in place for deferred transactions to reach your intended destinations. Also, remember that the DBMS_DEFER package queues RPCs to the locations in DEFDEFAULTDEST if and only if you have not passed the nodes parameter to DBMS_DEFER.CALL or DBMS_DEFER.TRANSACTION.
NOTE: Changes you make to DEFDEFAULTDEST affect future calls only, not calls that may already be queued.
The DELETE_DEFAULT_DEST procedure deletes records in the DEFDEFAULTDEST data dictionary view. Deleting a record effectively removes a default destination for deferred RPCs. The specification is,
PROCEDURE DBMS_DEFER_SYS.DELETE_DEFAULT_DEST (dblink IN VARCHAR2);
where dblink is the global name of the destination site being deleted.
There are no restrictions on calling DELETE_DEFAULT_DEST, and the procedure raises no exceptions.
The following example removes the default destination named D7OH.BIGWHEEL.COM from DEFDEFAULTDEST:
BEGIN DBMS_DEFER_SYS.DELETE_DEFAULT_DEST('D7OH.BIGWHEEL.COM'); END;
As with other DBMS_DEFER_SYS programs, these deletions affect only future calls.
If you want the deferred RPCs that are already in the queue to be propagated to the newly added destinations, you can use the DBMS_DEFER_SYS.COPY procedure to make a copy of the existing deferred transaction(s).
The COPY procedure copies a specified deferred transaction. Oracle queues the copied transaction to the new destinations that you specify. Here's the specification:
PROCEDURE DBMS_DEFER_SYS.COPY (deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination_list IN dbms_defer.node_list_t, destination_count IN BINARY_INTEGER);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
deferred_tran_id |
ID from DEFTRAN data dictionary view to be copied |
|
deferred_tran_db |
Global name of the originating database |
|
destination_list |
PL/SQL table listing global names of databases to which the transaction is to be sent |
|
destination_count |
Number or entries in destination_list |
There are no restrictions on calling COPY.
NOTE: This procedure is available only in Oracle7.
The COPY procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-1403 |
Specified deferred_tran_id does not exist |
Suppose that you have a new site in Hawaii, and you want to include it as a destination for RPCs that are already queued. First, add the Hawaiian site to the list of default destinations like this:
BEGIN DBMS_DEFER_SYS.ADD_DEFAULT_DESTINATION('D7HI.BIGWHEEL.COM'); END;
Next, query DEFCALLDEST to get the transaction ID of a the queued RPC(s). You need this information to copy the transaction:
SQL> select * from defcalldest; Deferred Deferred Tran Tran Call No ID DB DB Link -------------- -------- ------------------- -------------------- 6631429919536 2.59.13 D7CA.BIGWHEEL.COM D7OR.BIGWHEEL.COM 6631429919536 2.59.13 D7CA.BIGWHEEL.COM D7WA.BIGWHEEL.COM 2 rows selected.
Now, use DBMS_DEFER_SYS.COPY to queue this transaction to the destination named D7HI.BIGWHEEL.COM:
DECLARE vNodes DBMS_DEFER.NODE_LIST_T; BEGIN vNodes(1) := 'D7HI.BIGWHEEL.COM'; dbms_defer_sys.copy( '2.59.13', 'D7CA.BIGWHEEL.COM', vNodes, 1); END;
There are several maintenance procedures available in the DBMS_DEFER_SYS package. These procedures round out the deferred RPC repertoire by providing a means of cleaning up errors and temporarily disabling queue pushes.
The DELETE_ERROR procedure allows you to delete transactions from the DEFERROR data dictionary view. The procedure also deletes the related entries from DEFCALL, DEFTRAN, and DEFTRANDEST. Use DELETE_ERROR if you have manually resolved a transaction that initially failed.
Here is the specification:
PROCEDURE DBMS_DEFER_SYS.DELETE_ERROR (deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
deferred_tran_id |
ID from DEFTRAN data dictionary view of transaction to be deleted from DEFERROR. If NULL, all entries for the specified deferred_tran_db and destination are deleted. |
|
deferred_tran_db |
Global name of the originating database. If NULL, all entries for the specified deferred_tran_id and destination are deleted. |
|
destination |
Global name of the destination database. If NULL, all entries for the specified deferred_tran_id and deferred_tran_db are deleted. |
There are no restrictions on calling DELETE_ERROR.
The DELETE_ERROR procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-1403 |
Specified deferred_tran_id does not exist, specified deferred_tran_db does not exist, and/or specified destination does not exist |
The nice thing about the DELETE_ERROR procedure is that you can pass NULL to any or all of the three parameters to treat it as a wildcard.
Here's how to delete all errors:
BEGIN DBMS_DEFER_SYS.DELETE_ERROR( null, null, null); END;
Here's how to delete all errors having D7NY.BIGWHEEL.COM as a destination:
BEGIN DBMS_DEFER_SYS.DELETE_ERROR(null, null, 'D7NY.BIGWHEEL.COM' ); END;
Here's how to delete all errors from RPC calls that originated at D7CA.BIGWHEEL.COM:
BEGIN DBMS_DEFER_SYS.DELETE_ERROR(NULL, 'D7CA.BIGWHEEL.COM', NULL); END;
The DELETE_TRAN procedure deletes deferred transactions. You might want to do this if you have applied the call manually or if you remove a node from your environment. The procedure deletes the call from the DEFTRANDEST data dictionary view and also from DEFCALLDEST (if it is an RPC). If the original call has been applied to all other destinations, then the procedure also removes the entries from DEFCALL and DEFTRAN.
As with the DELETE_ERROR procedure, DELETE_TRAN also treats NULL parameter values as wildcards (see the examples under DELETE_ERROR).
Here is the specification:
PROCEDURE DBMS_DEFER_SYS.DELETE_TRAN (deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
deferred_tran_id |
ID from DEFTRAN data dictionary view of transaction to be deleted from DEFERROR. If NULL, all entries for the specified deferred_tran_db and destination are deleted. |
|
deferred_tran_db |
Global name of the originating database. If NULL, all entries for the specified deferred_tran_id and destination are deleted. |
|
destination |
Global name of the destination database. If NULL, all entries for the specified deferred_tran_id and deferred_tran_db are deleted. |
There are no restrictions on calling DELETE_TRAN.
The DELETE_TRAN procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-1403 |
Specified deferred_tran_id does not exist, specified deferred_tran_db does not exist, and/or specified destination does not exist |
The DISABLED function returns the BOOLEAN value TRUE if the deferred RPC calls to the specified destination have been disabled (with SET_DISABLED), and returns FALSE otherwise. The specification is,
FUNCTION DBMS_DEFER_SYS.DISABLED (destination IN VARCHAR2) RETURN BOOLEAN;
where destination is the global name of the destination database.
There are no restrictions on calling the DISABLED function.
The DISABLED function may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-1403 |
Specified destination is not in the DEFSCHEDULE data dictionary view |
The EXECUTE_ERROR procedure forces execution of a transaction that originally failed, leaving a record in DEFERROR. You might call this procedure if you have repaired the error (for example, a conflict in the advanced replication option) and you now wish to re-attempt the transaction. If another error occurs during EXECUTE_ERROR, the attempt is aborted and the last error encountered is returned as an exception. Upon successful completion, the procedure deletes the entries from the DEFERROR data dictionary view. If the original call has been applied to all other destinations, then the procedure also removes the entries from DEFCALL and DEFTRAN.
As with the DELETE_ERROR and DELETE_TRAN procedures, you may pass NULLs to indicate wildcards.
Here is the specification for this procedure:
PROCEDURE DBMS_DEFER_SYS.EXECUTE_ERROR (deferred_tran_id IN VARCHAR2, deferred_tran_db IN VARCHAR2, destination IN VARCHAR2);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
deferred_tran_id |
ID of transaction in DEFERROR data dictionary view |
|
deferred_tran_db |
Global name of database that originated or copied the transaction originally |
|
destination |
Global name of destination database |
EXECUTE_ERROR may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
None |
-24275 |
Destination is null, or deferred_tran_id and deferred_tran_db are neither both NULL nor both NOT NULL |
If execution stops because of an exception, the EXECUTE_ERROR procedure raises the last exception encountered.
Note the following restrictions on calling EXECUTE_ERROR:
The destination parameter may not be NULL.
The deferred_tran_id and deferred_tran_db parameters must either both be NULL or both be NOT NULL. If they are NULL, all transactions in DEFERROR destined for destination are applied.
For an example, see the fixdefer.sql file on the companion disk. The example lists all deferred transactions that have encountered errors, and generates calls to DBMS_DEFER_SYS.EXECUTE_ERROR to reexecute the calls.
The SET_DISABLED procedure disables or enables propagation to the specified destination. If you are managing a replicated environment, you might want to disable propagation to a given site while you perform maintenance.
NOTE: If you disable propagation while RPCs are being delivered to the destination database, the delivery will be allowed to complete.
The specification follows:
PROCEDURE DBMS_DEFER_SYS.SET_DISABLED (destination IN VARCHAR2, disabled IN BOOLEAN := TRUE);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
destination |
Global name of the destination database |
|
disabled |
Flag indicating whether calls are to be disabled (TRUE) or enabled (FALSE) |
The SET_DISABLED procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-1403 |
Specified destination is not in the DEFSCHEDULE data dictionary view |
You must execute a COMMIT after a call to the SET_DISABLED procedure for the changes to take effect.
The following example disables propagation of deferred RPCs to D7NY.BIGWHEEL.COM:
BEGIN DBMS_DEFER_SYS.SET_DISABLED('D7NY.BIGWHEEL.COM', FALSE); END
The following example enables propagation:
BEGIN DBMS_DEFER_SYS.SET_DISABLED('D7NY.BIGWHEEL.COM', TRUE);
END;
The DBMS_DEFER.CALL procedure, which we'll discuss later in this chapter, neither executes nor pushes transactions to the destination databases: it simply queues them. In order to propagate the deferred call to the destinations and to execute it there, you must use the DBMS_DEFER_SYS package's EXECUTE procedure. Use SCHEDULE_EXECUTION to schedule execution at prescribed intervals, and UNSCHEDULE_EXECUTION to stop propagation.
NOTE: We describe the EXECUTE, SCHEDULE_EXECUTION, and UNSCHEDULE_EXECUTION procedures here because they are a part of the DBMS_DEFER_SYS package. However, because the discussion assumes knowledge of the DBMS_DEFER.CALL procedure (and related procedures), you may find these sections more understandable if you first read the subsequent DBMS_DEFER package section.
The granularity of the DBMS_JOB facility is one second, so you could schedule propagation of your deferred RPC calls for once per second if you wanted to. However, such an aggressive schedule is not advisable. In fact, scheduling propagation to occur more often than once every five minutes is rarely wise. Of course, your ideal schedule is a function of your application, business requirements, and resources. Nevertheless, a lengthy interval is seldom justifiable. Why the restrictions?
You don't go to the grocery store every time you need a particular item; you add items to a list and wait until you've accumulated a list that justifies the trip to the store. Shopping in this way uses less of your time and resources. Similarly, it is far more efficient to accumulate transactions in the DEFTRAN queue, and to propagate several to a given destination than it is to check the queue frequently and/or make several calls to the remote database to deliver only one or two transactions. You should be sure, however, that the time it takes to deliver n transactions does not exceed the time it takes for n transactions to accumulate.
Whenever an SNP background process wakes up to check the job queue for work to do, Oracle updates the table SYS.JOB$ to reflect the fact that the queue has been checked. This update, as with all updates, generates redo log entries. We have found that scheduling a job that does nothing to run once per minute generates more than one megabyte of redo per hour. Do not incur the overhead of these redo log entries unnecessarily.
If you schedule a job to run once per minute, you must also set the INIT.ORA parameter JOB_QUEUE_INTERVAL to 60 seconds or less, because the job can run only as frequently as the background processes wake up to check them. However, just as redo activity increases, so does CPU utilization for the SNP background processes that check the job queue.
In short, you should avoid the temptation to schedule deferred transactions to be propagated on a subminute interval unless your application truly requires it. Five-minute intervals are the shortest that Oracle Corporation recommends.
The EXECUTE procedure propagates a deferred call to the destination database and executes it there. Here is the specification:
PROCEDURE DBMS_DEFER_SYS.EXECUTE (destination IN VARCHAR2, stop_on_error IN BOOLEAN := FALSE, transaction_count IN BINARY_INTEGER := 0, execution_seconds IN BINARY_INTEGER := 0, execute_as_user IN BOOLEAN := FALSE, delay_seconds IN NATURAL := 0, batch_size IN NATURAL := 0);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
destination |
Global name of the destination database. |
|
stop_on_error |
If TRUE, execution of queued transactions stops if an error is encountered. If FALSE (the default), execution continues unless destination is unavailable. |
|
transaction_count |
If > 0, maximum number of transactions to execute. |
|
execution_seconds |
If > 0, maximum number of seconds to spend executing transactions. |
|
execute_as_user |
IF TRUE, the execution of deferred transactions is authenticated at the remote system using the authentication context of the session user. If FALSE (the default), the execution is authenticated at the remote system using the authentication contexts of the users that originally queued the deferred transactions (indicated in the origin_user column of the DEFTRAN data dictionary view). This parameter is obsolete in Oracle8, which executes transactions under the context of the propagator. |
|
delay_seconds |
If > 0, routine sleeps for this many seconds before resuming when there are no more transactions to push to destination. |
|
batch_size |
The number of deferred transactions executed before committing. If batch_size = 0, a commit occurs after each deferred transaction. If batch_size > 0, a commit occurs when the total number of deferred calls executed exceeds batch_size and a complete transaction has been executed. |
There are no restrictions on calling the EXECUTE procedure.
If execution stops because of an exception, the EXECUTE procedure raises the last exception encountered.
Although the EXECUTE procedure includes several parameters, you can use it in its simplest form to push all queued transactions to a given destination. For example, to send the transaction that was queued to D7TX.BIGWHEEL.COM in the example of DBMS_DEFER.TRANSACTION, we would simply make this call:
BEGIN DBMS_DEFER_SYS.EXECUTE('D7TX.BIGWHEEL.COM'); END;
This call propagates and executes all deferred RPCs bound for D7TX.BIGWHEEL.COM.
The EXECUTE procedure includes various optional parameters (described in the next section) to accommodate applications that may queue tens or hundreds or thousands of deferred RPC calls. The advanced replication option has this potential. (In such cases, you may need to control the rate and volume of transactions, privilege domains, and error handling.)
The items in the following list describe in greater detail how you use the EXECUTE parameters:
NOTE: If you are queuing a relatively low volume of deferred RPC calls, these additional parameters controlling the volume and timing of deliveries are not especially relevant. They are provided for fine-tuning the behavior and performance of automatically scheduled RPCs, such as those associated with the advanced replication option.
Setting the Boolean parameter stop_on_error to FALSE (the default) causes Oracle to continue propagating and executing deferred RPC calls at a destination even if one or more of the calls encounters an error. Setting this parameter to TRUE causes execution of deferred RPCs to stop if an error occurs.
These two parameters are usually used in tandem. They cause propagation of RPCs to the destination to cease after transaction_count transactions or execution_seconds seconds, whichever comes first. These parameters provide a method of throttling the time and resources that are consumed during any one call to the EXECUTE procedure. Since these settings may cause the propagation to stop before all deferred RPCs are sent, it is your responsibility to monitor the DEFTRANDEST data dictionary view and/or to schedule automatic propagation at intervals. The default for both of these parameters is 0, which means that no such limits are set.
This parameter determines the privilege domain under which the procedure call executes at the destination. Setting execute_as_user to FALSE (the default) causes the call to execute under the privilege domain of the user who queued the call originally, as seen in the ORIGIN_USER column of the DEFTRAN data dictionary view. Setting the parameter to TRUE executes the call under the privilege domain of the session that calls the EXECUTE procedure. The user in execute_as_user refers to the user calling EXECUTE, not the user who queued the call.
This parameter causes EXECUTE to sleep for delay_seconds seconds before returning when it finishes propagating the queued transactions to the destination. The primary purpose of this parameter is to delay the next call to EXECUTE; the idea is that more transactions will have a chance to accumulate. It is more efficient to propagate five deferred RPCs with one call to EXECUTE than to issue five separate calls. This parameter is relevant only if you have scheduled automatic propagation.
This parameter is the number of deferred calls to execute between COMMITs. The default is 0, which means that a commit should occur for each deferred call that is propagated.
If you are using the advanced replication option, or if your application queues deferred RPC calls on a continual basis, then you should schedule the calls to the DBMS_DEFER_SYS.EXECUTE procedure at prescribed intervals for each destination. The SCHEDULE_EXECUTION procedure does just that by placing calls to the EXECUTE procedure in the job queue. Here is the specification:
PROCEDURE DBMS_DEFER_SYS.SCHEDULE EXECUTION (dblink IN VARCHAR2, interval IN VARCHAR2, next_date IN DATE, reset IN BOOLEAN default FALSE, stop_on_error IN BOOLEAN := NULL, transaction_count IN BINARY_INTEGER := NULL, execution_seconds IN BINARY_INTEGER := NULL, execute_as_user IN BOOLEAN := NULL, delay_seconds IN NATURAL := NULL, batch_size IN NATURAL := NULL);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
db_link |
Global name of the destination database |
|
interval |
Frequency with which to execute the RPC |
|
next_date |
First time to execute transactions queued for db_link |
|
reset |
If TRUE, then last_txn_count, last_error, and last_msg are nulled in DEFSCHEDULE data dictionary view for this db_link |
|
stop_on_error |
If not NULL, value is used by the call to EXECUTE |
|
transaction_count |
If not NULL, value is used by the call to EXECUTE |
|
execution_seconds |
If not NULL, value is used by the call to EXECUTE |
|
execute_as_user |
If not NULL, value is used by the call to DBMS_DEFER_SYS.EXECUTE (obsolete in Oracle8) |
|
delay_seconds |
If not NULL, value is used by the call to EXECUTE |
|
batch_size |
If not NULL, value is used by the call to EXECUTE |
This procedure looks like a cross between DBMS_JOB.SUBMIT and DBMS_DEFER.EXECUTE, because it is. The interval and next_date parameters behave in exactly the same way as the parameters by the same names passed to DBMS_JOB.SUBMIT; the parameters stop_on_error, transaction_count, execution_seconds, execute_as_user, delay_seconds, and batch_size are passed directly to the DBMS_DEFER_SYS.EXECUTE call that is put in the job queue (dblink is passed to the destination). Setting the reset parameter to TRUE sets columns LAST_TXN_COUNT, LAST_ERROR, and LAST_MSG to NULL in the DEFSCHEDULE data dictionary view.
The SCHEDULE_EXECUTION procedure does not raise any exceptions, nor are there any restrictions on calling this procedure.
The following example shows how to schedule automatic propagation of deferred RPC calls to D7WA.BIGWHEEL.COM. These calls will be propagated every 15 minutes, starting at midnight tonight.
BEGIN DBMS_DEFER_SYS.SCHEDULE_EXECUTION( - db_link => 'D7WA.BIGWHEEL.COM', - interval => 'SYSDATE + 15/1440', - /* 1440 minutes in a day*/ next_date => TRUNC(SYSDATE + 1), - reset => TRUE); END; /
For additional examples, see the defsched.sql and jobs.sql files on the companion disk. The defsched.sql example lists RPCs that have been scheduled with DBMS_DEFER_SYS.SCHEDULE_EXECUTION, including last and next execution times in hours, minutes, and seconds. The jobs.sql example lists all jobs in the job queue, including last and next execution times in hours, minutes, and seconds, aslong with the package call that is being executed.
When you need to stop the propagation of deferred calls to a given destination, you can do so with the UNSCHEDULE_EXECUTION procedure. The specification is,
PROCEDURE DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION (dblink IN VARCHAR2);
where dblink is the global name of the destination database.
Calling this procedure is analogous to calling DBMS_JOB.REMOVE to remove the job that DBMS_DEFER_SYS.SCHEDULE_EXECUTION scheduled. The job is removed from the queue, and automatic propagation to the database specified by dblink ceases.
There are no restrictions on calling UNSCHEDULE_EXECUTION.
The UNSCHEDULE_EXECUTION procedure may raise the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-01403 |
Specified destination is not in the DEFSCHEDULE data dictionary view |
Oracle8 uses a slightly different mechanism to propagate transactions to remote databases. Instead of deleting transactions from the local queue as soon as they are delivered to a remote site, Oracle purges the queue as a separate process. This strategy enhances performance because there is no need for a two-phase commit when transactions are propagated. In addition, Oracle8 includes support for parallel propagation, which means that multiple transactions can be delivered to the destinations simultaneously if they are not dependent on each other.
NOTE: The Oracle8 documentation refers to scheduled propagation as " scheduled links."
Here are the DBMS_DEFER_SYS programs that support propagation in Oracle8 are:
| DBMS_DEFER_SYS.EXCLUDE_PUSH |
| DBMS_DEFER_SYS.PURGE |
| DBMS_DEFER_SYS.PUSH |
| DBMS_DEFER_SYS.REGISTER_PROPAGATOR |
| DBMS_DEFER_SYS.SCHEDULE_PURGE |
| DBMS_DEFER_SYS.SCHEDULE_PUSH |
| DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR |
| DBMS_DEFER_SYS.UNSCHEDULE_PURGE |
| DBMS_DEFER_SYS.UNSCHEDULE_PUSH |
The EXCLUDE_PUSH function acquires a lock to disable deferred pushes. The specification is,
FUNCTION DBMS_DEFER_SYS.EXCLUDE_PUSH (timeout IN INTEGER) RETURN INTEGER;
where timeout is the time to wait to acquire a lock that disables pushes. Specify DBMS_LOCK.MAXWAIT to wait indefinitely.
The EXCLUDE_PUSH function may return the values shown in the following table.
|
Value |
Meaning |
|---|---|
|
0 |
Normal successful completion |
|
1 |
Timed out waiting for lock |
|
2 |
Unsuccessful due to deadlock |
|
4 |
Lock is already owned |
The PURGE procedure purges transactions that have been propagated from the deferred transaction queue. Here is the specification:
FUNCTION DBMS_DEFER_SYS.PURGE( purge_method IN BINARY_INTEGER := purge_method_quick, rollback_segment IN VARCHAR2 := NULL, startup_seconds IN BINARY_INTEGER := 0, execution_seconds IN BINARY_INTEGER := seconds_infinity, delay_seconds IN BINARY_INTEGER := 0, transaction_count IN BINARY_INTEGER := transactions_infinity, write_trace IN BOOLEAN := FALSE ) RETURN BINARY_INTEGER;
Parameters are summarized in the following tables.
|
Name |
Description |
|---|---|
|
purge_method |
1 = purge_method_quick (not necessarily complete, but faster) 2 = purge_method_precise (complete purge) |
|
rollback_segment |
Which rollback segment should be used |
|
startup_seconds |
Maximum number of seconds to wait for the completion of a previous push to the same destination |
|
delay_seconds |
If > 0, routine sleeps for this many seconds before resuming when there are no more transactions to push to destination |
|
transaction_count |
Maximum number of transactions to push per execution |
|
write_trace |
If TRUE, record result in a trace file |
The return values for PURGE are listed in the following table.
|
Value |
Meaning |
|---|---|
|
0 |
Normal completion after delay_seconds expired |
|
1 |
Terminated by lock timeout while starting |
|
2 |
Terminated by exceeding execution_seconds |
|
3 |
Terminated by exceeding transaction_count |
|
4 |
Terminated at delivery_order_limit |
|
5 |
Terminated after errors |
The PURGE function raises the following exceptions:
|
Name |
Number |
Description |
|---|---|---|
|
argoutofrange |
-23427 |
A parameter value is out of range. |
|
executiondisabled |
-23354 |
Execution is disabled at destination. |
|
dbms_defererror |
-23305 |
An internal error occured. |
The PUSH function pushes a queued transaction to a destination node. Here is the specification:
FUNCTION DBMS_DEFER_SYS.PUSH( destination IN VARCHAR2, parallelism IN BINARY_INTEGER := 0, heap_size IN BINARY_INTEGER := 0, stop_on_error IN BOOLEAN := FALSE, write_trace IN BOOLEAN := FALSE, startup_seconds IN BINARY_INTEGER := 0, execution_seconds IN BINARY_INTEGER := seconds_infinity, delay_seconds IN BINARY_INTEGER := 0, transaction_count IN BINARY_INTEGER := transactions_infinity, delivery_order_limit IN NUMBER := delivery_order_infinity ) RETURN BINARY_INTEGER;
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
destination |
Global name of the destination database |
|
parallelism |
Degree of parallelism: 0 = serial (no parallelism) 1 = parallel propagation with one slave N = parallel propagation with N slaves |
|
heap_size |
If > 0, maximum number of transactions to examine simultaneously for parallel scheduling computation If 0, compute this number based on parallelism parameter |
|
stop_on_error |
If TRUE, then stop on the first error, even if not fatal |
|
write_trace |
If TRUE, record result in a trace file |
|
startup_seconds |
Maximum number of seconds to wait for the completion of a previous push to the same destination |
|
execution_seconds |
Maximum number of seconds to spend on the push before shutting down; defaults to seconds_infiinity (i.e., unlimited) |
|
delay_seconds |
Shutdown push cleanly if queue is empty for this many seconds |
|
transaction_count |
Maximum number of transactions to push per execution |
|
delivery_order_limit |
Shut down cleanly before pushing a transaction with delivery_order > delivery_order_limit |
Return values for PUSH are listed in the following table.
|
Value |
Meaning |
|---|---|
|
0 |
Normal completion after delay_seconds expired |
|
1 |
Terminated by lock timeout while starting |
|
2 |
Terminated by exceeding execution_seconds |
|
3 |
Terminated by exceeding transaction_count |
|
4 |
Terminated at delivery_order_limit |
|
5 |
Terminated after errors |
PUSH raises the following exceptions:
|
Name |
Number |
Description |
|---|---|---|
|
incompleteparallelpush |
-23388 |
Internal error |
|
executiondisabled |
-23354 |
Execution is disabled at destination |
|
crt_err_err |
-23324 |
Error creating DEFERROR entry |
|
deferred_rpc_quiesce |
-23326 |
The system is being quiesced |
|
commfailure |
-23302 |
Communication failure |
|
missingpropagator |
-23357 |
The SCHEDULE_PURGE procedure schedules the automatic purge of transactions that have been propagated from the queue. Here is the specification:
PROCEDURE DBMS_DEFER_SYS.SCHEDULE_PURGE( interval IN VARCHAR2, next_date IN DATE, reset IN BOOLEAN := FALSE, purge_method IN BINARY_INTEGER := NULL, rollback_segment IN VARCHAR2 := NULL, startup_seconds IN BINARY_INTEGER := NULL, execution_seconds IN BINARY_INTEGER := NULL, delay_seconds IN BINARY_INTEGER := NULL, transaction_count IN BINARY_INTEGER := NULL, write_trace IN BOOLEAN := NULL );
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
interval |
Frequency with which to execute the call |
|
next_date |
First time to execute the purge |
|
reset |
If TRUE, last_txn_count, last_error, and last_msg are nulled in DEFSCHEDULE data dictionary view |
|
purge_method |
1 = purge_method_quick (not necessarily complete, but faster) 2 = purge_method_precise (complete purge) |
|
rollback_segment |
Which rollback segment should be used |
|
startup_seconds |
Maximum number of seconds to wait for the completion of a previous push to the same destination |
|
execution_seconds |
Maximum number of seconds to spend on the push before shutting down; defaults to seconds_infiinity (i.e., unlimited) |
|
delay_seconds |
If > 0, routine sleeps for this many seconds before resuming when there are no more transactions to push to destination |
|
transaction_count |
Maximum number of transactions to push per execution |
|
write_trace |
The SCHEDULE_PUSH procedure schedules automatic pushes to the destination node. Here is the specification:
PROCEDURE DBMS_DEFER_SYS.SCHEDULE_PUSH( destination IN VARCHAR2, interval IN VARCHAR2, next_date IN DATE, reset IN BOOLEAN := FALSE, parallelism IN BINARY_INTEGER := NULL, heap_size IN BINARY_INTEGER := NULL, stop_on_error IN BOOLEAN := NULL, write_trace IN BOOLEAN := NULL, startup_seconds IN BINARY_INTEGER := NULL, execution_seconds IN BINARY_INTEGER := NULL, delay_seconds IN BINARY_INTEGER := NULL, transaction_count IN BINARY_INTEGER := NULL );
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
destination |
Global name of the destination database |
|
interval |
Frequency with which to execute the call |
|
next_date |
First time to push transactions queued for destination |
|
reset |
If TRUE, last_txn_count, last_error, and last_msg are nulled in DEFSCHEDULE data dictionary view for this destination |
|
parallelism |
Degree of parallelism: 0 = serial (no parallelism) 1 = parallel propagation with one slave N = parallel propagation with N slaves. |
|
heap_size |
If > 0, maximum number of transactions to examine simultaneously for parallel scheduling computation; if 0, compute this number based on parallelism parameter |
|
stop_on_error |
If TRUE, stop on the first error, even if not fatal |
|
write_trace |
If TRUE, record result in a trace file |
|
startup_seconds |
Maximum number of seconds to wait for the completion of a previous push to the same destination |
|
execution_seconds |
Maximum number of seconds to spend on the push before shutting down.; defaults to seconds_infiinity (i.e., unlimited) |
|
delay_seconds |
If > 0, routine sleeps for this many seconds before resuming when there are no more transactions to push to destination |
|
transaction_count |
The UNSCHEDULE_PURGE procedure is the complement to the SCHEDULE_PURGE procedure. This procedure unschedules the automatic purge of transactions that have been propagated to the queue. The specification is simply:
PROCEDURE DBMS_DEFER_SYS.UNSCHEDULE_PURGE;
The UNSCHEDULE_PUSH procedure is the complement to the SCHEDULE_PUSH procedure. This procedure unschedules automatic pushes to the destination node. The specification is,
PROCEDURE DBMS_DEFER_SYS.UNSCHEDULE_PUSH(dblink IN VARCHAR2);
where dblink is the global name of the database to which pushes are to be unscheduled.
UNSCHEDULE_PUSH raises the following exception:
|
Name |
Number |
Description |
|---|---|---|
|
NO_DATA_FOUND |
-100 |
No pushes to dblink exist |
The REGISTER_PROPAGATOR procedure makes a designated user the propagator for the local database. The specification is,
PROCEDURE DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username IN VARCHAR2);
where username is the name of the account to which privileges are to be granted.
REGISTER_PROPAGATOR raises the following exceptions:
|
Name |
Number |
Description |
|---|---|---|
|
missinguser |
-23362 |
User username does not exist |
|
alreadypropagator |
-23393 |
User username is already the propagator for this database |
|
duplicatepropagator |
-23394 |
Database already has a propagator account |
The UNREGISTER_PROPAGATOR procedure revokes the privileges granted to make a particular user the local database propagator. The specification follows:
PROCEDURE DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR (username IN VARCHAR2, timeout IN INTEGER DEFAULT dbms_lock.maxwait);
Parameters are summarized in the following table.
|
Name |
Description |
|---|---|
|
username |
Name of the account to which privileges are to be revoked |
|
timeout |
Number of seconds to wait if the propagator account is in use when the call to UNREGISTER_PROPAGATOR is made |
UNREGISTER_PROPAGATOR raises the following exceptions:
|
Name |
Number |
Description |
|---|---|---|
|
missingpropagator |
-23357 |
User username is not a propagator |
|
propagator_inuse |
-23418 |
The propagator account is in use, and timeout seconds have elapsed |
TIP: We recommend using the same username as the propagator at all database sites. Also, make sure that the account is the same as the replication administrator (REPADMIN) account.
|
|
|
|
| 17.1 About Deferred Transactions and RPCs |
|
17.3 DBMS_DEFER: Building Deferred Calls |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.