14 DBMS_DEFER_SYS

DBMS_DEFER_SYS procedures manage default replication node lists. This package is the system administrator interface to a replicated transactional deferred remote procedure call facility. Administrators and replication daemons can execute transactions queued for remote nodes using this facility, and administrators can control the nodes to which remote calls are destined.

This chapter contains this topic:

Summary of DBMS_DEFER_SYS Subprograms

Table 14-1 DBMS_DEFER_SYS Package Subprograms

Subprogram Description

"ADD_DEFAULT_DEST Procedure"

Adds a destination database to the DEFDEFAULTDEST view.

"CLEAR_PROP_STATISTICS Procedure"

Clears the propagation statistics in the DEFSCHEDULE data dictionary view.

"DELETE_DEFAULT_DEST Procedure"

Removes a destination database from the DEFDEFAULTDEST view.

"DELETE_DEF_DESTINATION Procedure"

Removes a destination database from the DEFSCHEDULE view.

"DELETE_ERROR Procedure"

Deletes a transaction from the DEFERROR view.

"DELETE_TRAN Procedure"

Deletes a transaction from the DEFTRANDEST view.

"DISABLED Function"

Determines whether propagation of the deferred transaction queue from the current site to a specified site is enabled.

EXCLUDE_PUSH Function

Acquires an exclusive lock that prevents deferred transaction PUSH.

"EXECUTE_ERROR Procedure"

Reexecutes a deferred transaction that did not initially complete successfully in the security context of the original receiver of the transaction.

"EXECUTE_ERROR_AS_USER Procedure"

Reexecutes a deferred transaction that did not initially complete successfully in the security context of the user who executes this procedure.

"PURGE Function"

Purges pushed transactions from the deferred transaction queue at your current master site or materialized view site.

"PUSH Function"

Forces a deferred remote procedure call queue at your current master site or materialized view site to be pushed to a remote site.

"REGISTER_PROPAGATOR Procedure"

Registers the specified user as the propagator for the local database.

"SCHEDULE_PURGE Procedure"

Schedules a job to purge pushed transactions from the deferred transaction queue at your current master site or materialized view site.

"SCHEDULE_PUSH Procedure"

Schedules a job to push the deferred transaction queue to a remote site.

"SET_DISABLED Procedure"

Disables or enables propagation of the deferred transaction queue from the current site to a specified destination site.

"UNREGISTER_PROPAGATOR Procedure"

Unregisters a user as the propagator from the local database.

"UNSCHEDULE_PURGE Procedure"

Stops automatic purges of pushed transactions from the deferred transaction queue at a master site or materialized view site.

"UNSCHEDULE_PUSH Procedure"

Stops automatic pushes of the deferred transaction queue from a master site or materialized view site to a remote site.


ADD_DEFAULT_DEST Procedure

This procedure adds a destination database to the DEFDEFAULTDEST data dictionary view.

Syntax

DBMS_DEFER_SYS.ADD_DEFAULT_DEST (
   dblink   IN   VARCHAR2);

Parameters

Table 14-2 ADD_DEFAULT_DEST Procedure Parameters

Parameter Description
dblink

The fully qualified database name of the node that you want to add to the DEFDEFAULTDEST view.


Exceptions

Table 14-3 ADD_DEFAULT_DEST Procedure Exceptions

Exception Description
ORA-23352

The dblink that you specified is already in the default list.


CLEAR_PROP_STATISTICS Procedure

This procedure clears the propagation statistics in the DEFSCHEDULE data dictionary view. When this procedure is executed successfully, all statistics in this view are returned to zero and statistic gathering starts fresh.

Specifically, this procedure clears statistics from the following columns in the DEFSCHEDULE data dictionary view:

  • TOTAL_TXN_COUNT

  • AVG_THROUGHPUT

  • AVG_LATENCY

  • TOTAL_BYTES_SENT

  • TOTAL_BYTES_RECEIVED

  • TOTAL_ROUND_TRIPS

  • TOTAL_ADMIN_COUNT

  • TOTAL_ERROR_COUNT

  • TOTAL_SLEEP_TIME

Syntax


DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (
   dblink   IN   VARCHAR2);

Parameters

Table 14-4 CLEAR_PROP_STATISTICS Procedure Parameters

Parameter Description
dblink

The fully qualified database name of the node whose statistics you want to clear. The statistics to be cleared are the statistics for propagation of deferred transactions from the current node to the node you specify for dblink.


DELETE_DEFAULT_DEST Procedure

This procedure removes a destination database from the DEFDEFAULTDEST view.

Syntax

DBMS_DEFER_SYS.DELETE_DEFAULT_DEST (
   dblink   IN   VARCHAR2);

Parameters

Table 14-5 DELETE_DEFAULT_DEST Procedure Parameters

Parameter Description
dblink

The fully qualified database name of the node that you want to delete from the DEFDEFAULTDEST view. If Oracle does not find this dblink in the view, then no action is taken.


DELETE_DEF_DESTINATION Procedure

This procedure removes a destination database from the DEFSCHEDULE view.

Syntax

DBMS_DEFER_SYS.DELETE_DEF_DESTINATION (
   destination   IN   VARCHAR2,
   force         IN   BOOLEAN := FALSE);

Parameters

Table 14-6 DELETE_DEF_DESTINATION Procedure Parameters

Parameter Description
destination

The fully qualified database name of the destination that you want to delete from the DEFSCHEDULE view. If Oracle does not find this destination in the view, then no action is taken.

force

When set to TRUE, Oracle ignores all safety checks and deletes the destination.


DELETE_ERROR Procedure

This procedure deletes a transaction from the DEFERROR view.

Syntax

DBMS_DEFER_SYS.DELETE_ERROR(
   deferred_tran_id     IN   VARCHAR2,
   destination          IN   VARCHAR2);

Parameters

Table 14-7 DELETE_ERROR Procedure Parameters

Parameter Description
deferred_tran_id

Identification number from the DEFERROR view of the deferred transaction that you want to remove from the DEFERROR view. If this parameter is NULL, then all transactions meeting the requirements of the other parameter are removed.

destination

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. If this parameter is NULL, then all transactions meeting the requirements of the other parameter are removed from the DEFERROR view.


DELETE_TRAN Procedure

This procedure deletes a transaction from the DEFTRANDEST view. If there are no other DEFTRANDEST or DEFERROR entries for the transaction, then the transaction is deleted from the DEFTRAN and DEFCALL views as well.

Syntax

DBMS_DEFER_SYS.DELETE_TRAN (
   deferred_tran_id     IN   VARCHAR2,
   destination          IN   VARCHAR2);

Parameters

Table 14-8 DELETE_TRAN Procedure Parameters

Parameter Description
deferred_tran_id

Identification number from the DEFTRAN view of the deferred transaction that you want to delete. If this is NULL, then all transactions meeting the requirements of the other parameter are deleted.

destination

The fully qualified database name from the DEFTRANDEST view of the database to which the transaction was originally queued. If this is NULL, then all transactions meeting the requirements of the other parameter are deleted.


DISABLED Function

This function determines whether propagation of the deferred transaction queue from the current site to a specified site is enabled. The DISABLED function returns TRUE if the deferred remote procedure call (RPC) queue is disabled for the specified destination.

Syntax


DBMS_DEFER_SYS.DISABLED (
   destination  IN   VARCHAR2)
  RETURN BOOLEAN;

Parameters

Table 14-9 DISABLED Function Parameters

Parameter Description
destination

The fully qualified database name of the node whose propagation status you want to check.


Exceptions

Table 14-10 DISABLED Function Exceptions

Exception Description
NO_DATA_FOUND

Specified destination does not appear in the DEFSCHEDULE view.


Returns

Table 14-11 DISABLED Function Return Values

Value Description
TRUE

Propagation to this site from the current site is disabled.

FALSE

Propagation to this site from the current site is enabled.


EXCLUDE_PUSH Function

This function acquires an exclusive lock that prevents deferred transaction PUSH (either serial or parallel). This function performs a commit when acquiring the lock. The lock is acquired with RELEASE_ON_COMMIT => TRUE, so that pushing of the deferred transaction queue can resume after the next commit.

Syntax

DBMS_DEFER_SYS.EXCLUDE_PUSH (
   timeout   IN   INTEGER)
  RETURN INTEGER;

Parameters

Table 14-12 EXCLUDE_PUSH Function Parameters

Parameter Description
timeout

Timeout in seconds. If the lock cannot be acquired within this time period (either because of an error or because a PUSH is currently under way), then the call returns a value of 1. A timeout value of DBMS_LOCK.MAXWAIT waits indefinitely.


Returns

Table 14-13 EXCLUDE_PUSH Function Return Values

Value Description
0

Success, lock acquired.

1

Timeout, no lock acquired.

2

Deadlock, no lock acquired.

4

Already own lock.


EXECUTE_ERROR Procedure

This procedure reexecutes a deferred transaction that did not initially complete successfully in the security context of the original receiver of the transaction.

Syntax


DBMS_DEFER_SYS.EXECUTE_ERROR ( 
   deferred_tran_id IN   VARCHAR2,
   destination      IN   VARCHAR2);

Parameters

Table 14-14 EXECUTE_ERROR Procedure Parameters

Parameter Description
deferred_tran_id

Identification number from the DEFERROR view of the deferred transaction that you want to reexecute. If this is NULL, then all transactions queued for destination are reexecuted.

destination

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. This must not be NULL. If the provided database name is not fully qualified or is invalid, no error will be raised.


Exceptions

Table 14-15 EXECUTE_ERROR Procedure Exceptions

Exception Description
ORA-24275 error

Illegal combinations of NULL and non-NULL parameters were used.

badparam

Parameter value missing or invalid (for example, if destination is NULL).

missinguser

Invalid user.


EXECUTE_ERROR_AS_USER Procedure

This procedure reexecutes a deferred transaction that did not initially complete successfully. Each transaction is executed in the security context of the connected user.

Syntax


DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER ( 
   deferred_tran_id IN   VARCHAR2,
   destination      IN   VARCHAR2);

Parameters

Table 14-16 EXECUTE_ERROR_AS_USER Procedure Parameters

Parameter Description
deferred_tran_id

Identification number from the DEFERROR view of the deferred transaction that you want to reexecute. If this is NULL, then all transactions queued for destination are reexecuted.

destination

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. This must not be NULL.


Exceptions

Table 14-17 EXECUTE_ERROR_AS_USER Procedure Exceptions

Exception Description
ORA-24275 error

Illegal combinations of NULL and non-NULL parameters were used.

badparam

Parameter value missing or invalid (for example, if destination is NULL).

missinguser

Invalid user.


PURGE Function

This function purges pushed transactions from the deferred transaction queue at your current master site or materialized view site.

Syntax


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        := NULL);
  RETURN BINARY_INTEGER;

Parameters

Table 14-18 PURGE Function Parameters

Parameter Description
purge_method

Controls how to purge the deferred transaction queue: purge_method_quick costs less, while purge_method_precise offers better precision.

Specify the following for this parameter to use purge_method_quick:

dbms_defer_sys.purge_method_quick

Specify the following for this parameter to user purge_method_precise:

dbms_defer_sys.purge_method_precise

If you use purge_method_quick, deferred transactions and deferred procedure calls that have been successfully pushed can remain in the DEFTRAN and DEFCALL data dictionary views for longer than expected before they are purged. See "Usage Notes" for more information.

rollback_segment

Name of rollback segment to use for the purge, or NULL for default.

startup_seconds

Maximum number of seconds to wait for a previous purge of the same deferred transaction queue.

execution_seconds

If > 0, then stop purge cleanly after the specified number of seconds of real time.

delay_seconds

Stop purge cleanly after the deferred transaction queue has no transactions to purge for delay_seconds.

transaction_count

If > 0, then shut down cleanly after purging transaction_count number of transactions.

write_trace

When set to TRUE, Oracle records the result value returned by the PURGE function in the server's trace file. When set to FALSE, Oracle does not record the result value.


Returns

Table 14-19 Purge Function Returns

Value Description
result_ok

OK, terminated after delay_seconds expired.

result_startup_seconds

Terminated by lock timeout while starting.

result_execution_seconds

Terminated by exceeding execution_seconds.

result_transaction_count

Terminated by exceeding transaction_count.

result_errors

Terminated after errors.

result_split_del_order_limit

Terminated after failing to acquire the enqueue in exclusive mode. If you receive this return code, then retry the purge. If the problem persists, then contact Oracle Support Services.

result_purge_disabled

Queue purging is disabled internally for synchronization when adding new master sites without quiesce.


Exceptions

Table 14-20 PURGE Function Exceptions

Exception Description
argoutofrange

Parameter value is out of a valid range.

executiondisabled

Execution of purging is disabled.

defererror

Internal error.


Usage Notes

When you use the purge_method_quick for the purge_method parameter in the DBMS_DEFER_SYS.PURGE function, deferred transactions and deferred procedure calls can remain in the DEFCALL and DEFTRAN data dictionary views after they have been successfully pushed. This behavior occurs in replication environments that have multiple database links and the push is executed to only one database link.

To purge the deferred transactions and deferred procedure calls, perform one of the following actions:

  • Use purge_method_precise for the purge_method parameter instead of the purge_method_quick. Using purge_method_precise is more expensive, but it ensures that the deferred transactions and procedure calls are purged after they have been successfully pushed.

  • Using purge_method_quick for the purge_method parameter, push the deferred transactions to all database links. The deferred transactions and deferred procedure calls are purged efficiently when the push to the last database link is successful.

PUSH Function

This function forces a deferred remote procedure call (RPC) queue at your current master site or materialized view site to be pushed (propagated) to a remote site using either serial or parallel propagation.

Syntax


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

Table 14-21 PUSH Function Parameters

Parameter Description
destination

The fully qualified database name of the master site or master materialized view site to which you are forwarding changes.

parallelism

0 specifies serial propagation.

n > 1 specifies parallel propagation with n parallel processes.

1 specifies parallel propagation using only one parallel process.

heap_size

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance.

Note: Do not set the parameter unless so directed by Oracle Support Services.

stop_on_error

The default, FALSE, indicates that the executor should continue even if errors, such as conflicts, are encountered. If TRUE, then stops propagation at the first indication that a transaction encountered an error at the destination site.

Note: If stop_on_error is set to TRUE and the parallelism parameter is greater than 0 (zero), then transactions might continue to be propagated and applied for a period of time after an error is encountered.

write_trace

When set to TRUE, Oracle records the result value returned by the function in the server's trace file. When set to FALSE, Oracle does not record the result value.

startup_seconds

Maximum number of seconds to wait for a previous push to the same destination.

execution_seconds

If > 0, then stop push cleanly after the specified number of seconds of real time. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue.

The execution_seconds parameter only controls the duration of time that operations can be started. It does not include the amount of time that the transactions require at remote sites. Therefore, the execution_seconds parameter is not intended to be used as a precise control to stop the propagation of transactions to a remote site. If a precise control is required, use the transaction_count or delivery_order parameters.

delay_seconds

Do not return before the specified number of seconds have elapsed, even if the queue is empty. Useful for reducing execution overhead if PUSH is called from a tight loop.

transaction_count

If > 0, then the maximum number of transactions to be pushed before stopping. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue that must be pushed.

delivery_order_limit

Stop execution cleanly before pushing a transaction where delivery_order >= delivery_order_limit.


Returns

Table 14-22 PUSH Function Returns

Value Description
result_ok

OK, terminated after delay_seconds expired.

result_startup_seconds

Terminated by lock timeout while starting.

result_execution_seconds

Terminated by exceeding execution_seconds.

result_transaction_count

Terminated by exceeding transaction_count.

result_delivery_order_limit

Terminated by exceeding delivery_order_limit.

result_errors

Terminated after errors.

result_push_disabled

Push was disabled internally. Typically, this return value means that propagation to the destination was set to disabled internally by Oracle for propagation synchronization when adding a new master site to a master group without quiescing the master group. Oracle will enable propagation automatically at a later time.

result_split_del_order_limit

Terminated after failing to acquire the enqueue in exclusive mode. If you receive this return code, then retry the push. If the problem persists, then contact Oracle Support Services.


Exceptions

Table 14-23 PUSH Function Exceptions

Exception Description
incompleteparallelpush

Serial propagation requires that parallel propagation shuts down cleanly.

executiondisabled

Execution of deferred remote procedure calls (RPCs) is disabled at the destination.

crt_err_err

Error while creating entry in DEFERROR.

deferred_rpc_quiesce

Replication activity for replication group is suspended.

commfailure

Communication failure during deferred remote procedure call (RPC).

missingpropagator

A propagator does not exist.


REGISTER_PROPAGATOR Procedure

This procedure registers the specified user as the propagator for the local database. It also grants the following privileges to the specified user (so that the user can create wrappers):

  • CREATE SESSION

  • CREATE PROCEDURE

  • CREATE DATABASE LINK

  • EXECUTE ANY PROCEDURE

Syntax


DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
   username  IN  VARCHAR2);

Parameter

Table 14-24 REGISTER_PROPAGATOR Procedure Parameter

Parameter Description
username

Name of the user.


Exceptions

Table 14-25 REGISTER_PROPAGATOR Procedure Exceptions

Exception Description
missinguser

Specified user does not exist.

alreadypropagator

Specified user is already the propagator.

duplicatepropagator

There is already a different propagator.


SCHEDULE_PURGE Procedure

This procedure schedules a job to purge pushed transactions from the deferred transaction queue at your current master site or materialized view site. You should schedule one purge job.

See Also:

Oracle Database Advanced Replication for information about using this procedure to schedule continuous or periodic purge of your deferred transaction queue

Syntax


DBMS_DEFER_SYS.SCHEDULE_PURGE (
   interval             IN  VARCHAR2,
   next_date            IN  DATE,
   reset                IN  BOOLEAN        := NULL,
   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

Table 14-26 SCHEDULE_PURGE Procedure Parameters

Parameter Description
interval

Allows you to provide a function to calculate the next time to purge. This value is stored in the interval field of the DEFSCHEDULE view and calculates the next_date field of this view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If the field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, you must supply a value for next_date.

next_date

Allows you to specify a time to purge pushed transactions from the site's queue. This value is stored in the next_date field of the DEFSCHEDULE view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If this field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for interval.

reset

Set to TRUE to reset LAST_TXN_COUNT, LAST_ERROR, and LAST_MSG to NULL.

purge_method

Controls how to purge the deferred transaction queue: purge_method_quick costs less, while purge_method_precise offers better precision.

Specify the following for this parameter to use purge_method_quick:

dbms_defer_sys.purge_method_quick

Specify the following for this parameter to user purge_method_precise:

dbms_defer_sys.purge_method_precise

If you use purge_method_quick, deferred transactions and deferred procedure calls that have been successfully pushed can remain in the DEFTRAN and DEFCALL data dictionary views for longer than expected before they are purged. For more information, see "Usage Notes". These usage notes are for the DBMS_DEFER_SYS.PURGE function, but they also apply to the DBMS_DEFER_SYS.SCHEDULE_PURGE procedure.

rollback_segment

Name of rollback segment to use for the purge, or NULL for default.

startup_seconds

Maximum number of seconds to wait for a previous purge of the same deferred transaction queue.

execution_seconds

If >0, then stop purge cleanly after the specified number of seconds of real time.

delay_seconds

Stop purge cleanly after the deferred transaction queue has no transactions to purge for delay_seconds.

transaction_count

If > 0, then shut down cleanly after purging transaction_count number of transactions.

write_trace

When set to TRUE, Oracle records the result value returned by the PURGE function in the server's trace file.


SCHEDULE_PUSH Procedure

This procedure schedules a job to push the deferred transaction queue to a remote site. This procedure performs a COMMIT.

See Also:

Oracle Database Advanced Replication for information about using this procedure to schedule continuous or periodic push of your deferred transaction queue

Syntax


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

Table 14-27 SCHEDULE_PUSH Procedure Parameters

Parameter Description
destination

The fully qualified database name of the master site or master materialized view site to which you are forwarding changes.

interval

Allows you to provide a function to calculate the next time to push. This value is stored in the interval field of the DEFSCHEDULE view and calculates the next_date field of this view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If the field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for next_date.

next_date

Allows you to specify a time to push deferred transactions to the remote site. This value is stored in the next_date field of the DEFSCHEDULE view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If this field had no previous value, then it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for interval.

reset

Set to TRUE to reset LAST_TXN_COUNT, LST_ERROR, and LAST_MSG to NULL.

parallelism

0 specifies serial propagation.

n > 1 specifies parallel propagation with n parallel processes.

1 specifies parallel propagation using only one parallel process.

heap_size

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance.

Note: Do not set the parameter unless so directed by Oracle Support Services.

stop_on_error

The default, FALSE, indicates that the executor should continue even if errors, such as conflicts, are encountered. If TRUE, then stops propagation at the first indication that a transaction encountered an error at the destination site.

Note: If stop_on_error is set to TRUE and the parallelism parameter is greater than 0 (zero), then transactions might continue to be propagated and applied for a period of time after an error is encountered.

write_trace

When set to TRUE, Oracle records the result value returned by the function in the server's trace file.

startup_seconds

Maximum number of seconds to wait for a previous push to the same destination.

execution_seconds

If >0, then stop execution cleanly after the specified number of seconds of real time. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue.

delay_seconds

Do not return before the specified number of seconds have elapsed, even if the queue is empty. Useful for reducing execution overhead if PUSH is called from a tight loop.

transaction_count

If > 0, then the maximum number of transactions to be pushed before stopping. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue that must be pushed.


SET_DISABLED Procedure

To disable or enable propagation of the deferred transaction queue from the current site to a specified destination site. If the disabled parameter is TRUE, then the procedure disables propagation to the specified destination and future invocations of PUSH do not push the deferred remote procedure call (RPC) queue. SET_DISABLED eventually affects a session already pushing the queue to the specified destination, but does not affect sessions appending to the queue with DBMS_DEFER.

If the disabled parameter is FALSE, then the procedure enables propagation to the specified destination and, although this does not push the queue, it permits future invocations of PUSH to push the queue to the specified destination. Whether the disabled parameter is TRUE or FALSE, a COMMIT is required for the setting to take effect in other sessions.

Syntax

DBMS_DEFER_SYS.SET_DISABLED (
   destination   IN   VARCHAR2,
   disabled      IN   BOOLEAN := TRUE,
   catchup       IN   RAW := '00',
   override      IN   BOOLEAN := FALSE);

Parameters

Table 14-28 SET_DISABLED Procedure Parameters

Parameter Description
destination

The fully qualified database name of the node whose propagation status you want to change.

disabled

By default, this parameter disables propagation of the deferred transaction queue from your current site to the specified destination. Set this to FALSE to enable propagation.

catchup

The extension identifier for adding new master sites to a master group without quiescing the master group. The new master site is the destination. Query the DEFSCHEDULE data dictionary view for the existing extension identifiers.

override

A FALSE setting, the default, specifies that Oracle raises the cantsetdisabled exception if the disabled parameter is set to FALSE and propagation was disabled internally by Oracle.

A TRUE setting specifies that Oracle ignores whether the disabled state was set internally for synchronization and always tries to set the state as specified by the disabled parameter.

Note: Do not set this parameter unless directed to do so by Oracle Support Services.


Exceptions

Table 14-29 SET_DISABLED Procedure Exceptions

Exception Description
NO_DATA_FOUND

No entry was found in the DEFSCHEDULE view for the specified destination.

cantsetdisabled

The disabled status for this site is set internally by Oracle for synchronization during adding a new master site to a master group without quiescing the master group. Ensure that adding a new master site without quiescing finished before invoking this procedure.


UNREGISTER_PROPAGATOR Procedure

To unregister a user as the propagator from the local database. This procedure:

  • Deletes the specified propagator from DEFPROPAGATOR.

  • Revokes privileges granted by REGISTER_PROPAGATOR from the specified user (including identical privileges granted independently).

  • Drops any generated wrappers in the schema of the specified propagator, and marks them as dropped in the replication catalog.

Syntax

DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR (
   username  IN  VARCHAR2
   timeout   IN  INTEGER DEFAULT DBMS_LOCK.MAXWAIT);

Parameters

Table 14-30 UNREGISTER_PROPAGATOR Procedure Parameters

Parameter Description
username

Name of the propagator user.

timeout

Timeout in seconds. If the propagator is in use, then the procedure waits until timeout. The default is DBMS_LOCK.MAXWAIT.


Exceptions

Table 14-31 UNREGISTER_PROPAGATOR Procedure Exceptions

Parameter Description
missingpropagator

Specified user is not a propagator.

propagator_inuse

Propagator is in use, and thus cannot be unregistered. Try later.


UNSCHEDULE_PURGE Procedure

This procedure stops automatic purges of pushed transactions from the deferred transaction queue at a master site or materialized view site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PURGE();

Parameters

None

UNSCHEDULE_PUSH Procedure

This procedure stops automatic pushes of the deferred transaction queue from a master site or materialized view site to a remote site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PUSH (
   dblink   IN   VARCHAR2);

Parameters

Table 14-32 UNSCHEDULE_PUSH Procedure Parameters

Parameter Description
dblink

Fully qualified path name for the database at which you want to unschedule periodic execution of deferred remote procedure calls.


Exceptions

Table 14-33 UNSCHEDULE_PUSH Procedure Exceptions

Exception Description
NO_DATA_FOUND

No entry was found in the DEFSCHEDULE view for the specified dblink.