[Chapter 17] 17.2 DBMS_DEFER_SYS: Managing Deferred Transactions

17.2 DBMS_DEFER_SYS: Managing Deferred Transactions

The DBMS_DEFER_SYS package provides a number of programs for administrative tasks associated with deferred transactions.

17.2.1 Getting Started with DBMS_DEFER_SYS

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.

17.2.1.1 DBMS_DEFER_SYS programs

Table 17.10 lists the programs available in the DBMS_DEFER_SYS package.

Table 17.10: DBMS_DEFER_SYS Programs
Name	Description	Use in SQL?
ADD_DEFAULT_DEST	Adds a destination to the DEFDEFAULTDEST data dictionary view	No
COPY	Creates a copy of an RPC with a different destination	No
DELETE_DEFAULT_DEST	Deletes a destination from the DEFDEFAULTDEST data dictionary view	No
DELETE_ERROR	Deletes an error from the DEFERROR data dictionary view	No
DELETE_TRAN	Deletes a transaction from the DEFTRANDEST data dictionary view	No
DISABLED	Returns a BOOLEAN indicating whether deferred transactions from the current site to the destination site are disabled	No
EXCLUDE_PUSH	Acquires a lock to disable deferred pushes	No
EXECUTE	Executes an RPC immediately	No
EXECUTE_ERROR	Reexecutes an RPC that failed previously	No
EXECUTE_ERROR_AS_USER	Re-executes a failed RPC under security context of connected user	No
PURGE	Purges transactions that have been propagated from the deferred transaction queue	No
PUSH	Pushes queued transaction to destination node	No
REGISTER_PROPAGATOR	Makes designated user the propagator for the local database	No
SCHEDULE_EXECUTION	Schedules automatic RPC pushes between a master or snapshot site another master site	No
SCHEDULE_PURGE	Schedules automatic purge of transactions that have been propagated from the queue	No
SCHEDULE_PUSH	Schedules automatic pushes to destination node	No
SET_DISABLED	Disables deferred transactions between the current site and a destination site	No
UNREGISTER_PROPAGATOR	Complement to REGISTER_PROPAGATOR; revokes privileges granted to make user the local database's propagator	No
UNSCHEDULE_EXECUTION	Stops automatic RPC pushes between a master or snapshot site and another master site	No
UNSCHEDULE_PURGE	Complement to SCHEDULE_PURGE; unschedules automatic purge of transactions that have been propagated to the queue	No
UNSCHEDULE_PUSH	Complement to SCHEDULE_PUSH; unschedules automatic pushes to destination node	No

17.2.1.2 DBMS_DEFER_SYS exceptions

The DBMS_DEF_SYS package may raise the following exception:

Name	Number	Description
crt_err_err	-23324	Parameter type does not match actual type

17.2.1.3 DBMS_DEFER_SYS nonprogram elements

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

17.2.2 Adding and Deleting Default Destinations

The DBMS_DEFER_SYS package's ADD_DEFAULT_DEST and DELETE_DEFAULT_DEST procedures add and delete records in the DEFDEFAULTDEST data dictionary view.

17.2.2.1 The DBMS_DEFER.SYS.ADD_DEFAULT_DEST procedure

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.

17.2.2.1.1 Exceptions

The ADD_DEFAULT_DEST procedure may raise the following exception:

Name	Number	Description
None	-23352	Specified destination is already in DEFDEFAULTDEST data dictionary view

17.2.2.1.2 Example

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.

17.2.2.2 The DBMS_DEFER.SYS.DELETE_DEFAULT_DEST procedure

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.

17.2.2.2.1 Example

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.

17.2.3 Copying Deferred Transactions to New Destinations

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

17.2.3.1 The DBMS_DEFER_SYS.COPY procedure

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.

17.2.3.1.1 Exceptions

The COPY procedure may raise the following exception:

Name	Number	Description
NO_DATA_FOUND	-1403	Specified deferred_tran_id does not exist

17.2.3.1.2 Example

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;

17.2.4 Maintenance Procedures

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.

17.2.4.1 The DBMS_DEFER_SYS.DELETE_ERROR procedure

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.

17.2.4.1.1 Exceptions

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

17.2.4.1.2 Examples

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;

17.2.4.2 The DBMS_DEFER_SYS.DELETE_TRAN procedure

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.

17.2.4.2.1 Exceptions

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

17.2.4.3 The DBMS_DEFER_SYS.DISABLED function

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.

17.2.4.3.1 Exceptions

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

17.2.4.4 The DBMS_DEFER_SYS.EXECUTE_ERROR procedure

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

17.2.4.4.1 Exceptions

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.

17.2.4.4.2 Restrictions

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.

17.2.4.4.3 Example

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.

17.2.4.5 The DBMS_DEFER_SYS.SET_DISABLED procedure

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)

17.2.4.5.1 Exceptions

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

17.2.4.5.2 Restrictions

You must execute a COMMIT after a call to the SET_DISABLED procedure for the changes to take effect.

17.2.4.5.3 Example

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;

17.2.5 Propagating Deferred RPCs

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.

17.2.5.1 Scheduling strategies

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?

Efficiency: 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.
Redo volume: 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.
Resources: 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.

17.2.5.2 The DBMS_DEFER_SYS.EXECUTE procedure

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.

17.2.5.2.1 Exceptions

If execution stops because of an exception, the EXECUTE procedure raises the last exception encountered.

17.2.5.2.2 Examples

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

17.2.5.2.3 Advanced usage: using the EXECUTE parameters

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.

stop_on_error: 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.
transaction_count and execution_seconds: 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.
execute_as_user: 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.
delay_seconds: 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.
batch_size: 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.

17.2.5.3 The DBMS_DEFER_SYS. SCHEDULE_EXECUTION procedure

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.

17.2.5.3.1 Example

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.

17.2.5.4 The DBMS_DEFER_SYS.UNSCHEDULE_EXECUTION procedure

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.

17.2.5.4.1 Exceptions

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

17.2.6 Scheduling Propagation (Oracle8 only)

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

17.2.6.1 The DBMS_DEFER_SYS.EXCLUDE_PUSH function (Oracle8 only)

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

17.2.6.2 The DBMS_DEFER_SYS.PURGE function (Oracle8 only)

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

17.2.6.2.1 Exceptions

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.

17.2.6.3 The DBMS_DEFER_SYS.PUSH function

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

17.2.6.3.1 Exceptions

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	A propagator does not exist

17.2.6.4 The DBMS_DEFER_SYS.SCHEDULE_PURGE procedure (Oracle8 only)

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	If TRUE, record result in a trace file

17.2.6.5 The DBMS_DEFER_SYS.SCHEDULE_PUSH procedure (Oracle8 only)

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	Maximum number of transactions to push per execution

17.2.6.6 The DBMS_DEFER_SYS.UNSCHEDULE_PURGE procedure (Oracle8 only)

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;

17.2.6.7 The DBMS_DEFER_SYS.UNSCHEDULE_PUSH procedure (Oracle8 only)

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.

17.2.6.7.1 Exceptions

UNSCHEDULE_PUSH raises the following exception:

Name	Number	Description
NO_DATA_FOUND	-100	No pushes to dblink exist

17.2.6.8 The DBMS_DEFER_SYS.REGISTER_PROPAGATOR procedure (Oracle8 only)

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.

17.2.6.8.1 Exceptions

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

17.2.6.9 The DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR procedure (Oracle8 only)

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

17.2.6.9.1 Exceptions

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.