Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

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

Summary of Subprograms

Table 10-1 DBMS_DEFER_SYS Package Subprograms
Subprogram  Description  
ADD_DEFAULT_DEST 
procedure
 

Adds a destination database to the DEFDEFAULTDEST 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 given site is enabled.  

 
EXCLUDE_PUSH function
 

Acquires an exclusive lock that prevents deferred transaction PUSH.  

 
EXECUTE_ERROR procedure
 

Re-executes a deferred transaction that did not initially complete successfully.  

 
EXECUTE_ERROR_AS_USER 
procedure
 

Re-executes a deferred transaction that did not initially complete successfully.  

 
PURGE function
 

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

 
PUSH function
 

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

 
REGISTER_PROPAGATOR 
procedure
 

Registers the given 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 or snapshot site.  

 
SCHEDULE_PUSH procedure
 

Schedules a job to push the deferred transaction queue to a remote master destination.  

 
SET_DISABLED procedure
 

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

 
UNREGISTER_PROPAGATOR 
procedure
 

Unregister 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 snapshot or master site.  

 
UNSCHEDULE_PUSH 
procedure
 

Stops automatic pushes of the deferred transaction queue from a snapshot or master site to another master site.  

ADD_DEFAULT_DEST procedure

This procedure adds a destination database to the DEFDEFAULTDEST view.

Syntax

DBMS_DEFER_SYS.ADD_DEFAULT_DEST (
   dblink   IN   VARCHAR2);

Parameters

Table 10-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 10-3 ADD_DEFAULT_DEST Procedure Exceptions
Exception  Description  
ORA-23352
 

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

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

To delete a transaction from the DEFERROR view.

Syntax

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

Parameters

Table 10-6 DELETE_ERROR Procedure Parameters
Parameter  Description  
deferred_tran_id
 

ID 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

To delete 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 10-7 DELETE_TRAN Procedure Parameters
Parameter  Description  
deferred_tran_id
 

ID 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

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

Syntax

DBMS_DEFER_SYS.DISABLED (
   destination  IN   VARCHAR2)
  RETURN BOOLEAN;

Parameters

Table 10-8 DISABLED Function Parameters
Parameter  Description  
destination
 

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

Returns

Table 10-9 DISABLED Function Returns
Return  Description  
TRUE
 

Propagation to this site from the current site is disabled.  

 
FALSE
 

Propagation to this site from the current site is enabled.  

Exceptions

Table 10-10 DISABLED Function Exceptions
Exception  Description  
NO_DATA_FOUND
 

DESTINATION does not appear in the DEFSCHEDULE view.  

EXCLUDE_PUSH function

To acquire an exclusive lock that prevents deferred transaction PUSH (either serial or parallel). This function does 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 10-11 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 10-12 EXCLUDE_PUSH Function Returns
Return  Description   
0
 

Success, lock acquired.  

 
1
 

Timeout, no lock acquired.  

 
2
 

Deadlock, no lock acquired.  

 
4
 

Already own lock.  

EXECUTE_ERROR procedure

To reexecute a deferred transaction that did not initially complete successfully. This procedure raises an ORA-24275 error when illegal combinations of NULL and non-NULL parameters are used.

Syntax

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

Parameters

Table 10-13 EXECUTE_ERROR Procedure Parameters
Parameter  Description  
deferred_tran_id
 

ID number from the DEFERROR view of the deferred transaction that you want to re-execute. If this is NULL, then all transactions queued for destination are re-executed.  

 
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 10-14 EXECUTE_ERROR Procedure Exceptions
Exception  Description  
badparam
 

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

 
missinguser
 

Invalid user.  

EXECUTE_ERROR_AS_USER procedure

To reexecute a deferred transaction that did not initially complete successfully. Each transaction is executed in the security context of the connected user. This procedure raises an ORA-24275 error when illegal combinations of NULL and non-NULL parameters are used.

Syntax

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

Parameters

Table 10-15 EXECUTE_ERROR_AS_USER Procedure Parameters
Parameter  Description  
deferred_tran_id
 

ID number from the DEFERROR view of the deferred transaction that you want to re-execute. If this is NULL, then all transactions queued for destination are re-executed.  

 
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 10-16 EXECUTE_ERROR_AS_USER Procedure Exceptions
Exception  Description  
badparam
 

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

 
missinguser
 

Invalid user.  

PURGE function

To purge pushed transactions from the deferred transaction queue at your current master or snapshot 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 10-17 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.  

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

Returns

Table 10-18 PURGE Function Returns
Return  Description   
0
 

OK, terminated after delay_seconds expired.  

 
1
 

Terminated by lock timeout while starting.  

 
2
 

Terminated by exceeding execution_seconds.  

 
3
 

Terminated by exceeding transaction_count.  

 
5
 

Terminated after errors.  

Exceptions

Table 10-19 PURGE Function Exceptions
Exception  Description  
argoutofrange
 

Parameter value is out of a valid range.  

 
executiondisabled
 

Execution of purging is disabled.  

 
defererror
 

Internal error.  

PUSH function

This function forces a deferred remote procedure call queue at your current master or snapshot site to be pushed (executed, propagated) to another master 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 10-20 PUSH Function Parameters
Parameter  Description  
destination
 

The fully qualified database name of the master to which you are forwarding changes.  

 
parallelism
 

0 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

 
heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set the parameter unless so directed by Oracle Worldwide Support.  

 
stop_on_error
 

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

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

 
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 need to be pushed.  

 
delivery_order_
limit
 

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

Returns

Table 10-21 PUSH Function Returns
Return  Description   
0
 

OK, terminated 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 by exceeding delivery_order_limit.  

 
5
 

Terminated after errors.  

Exceptions

Table 10-22 PUSH Function Exceptions
Exception  Description  
deferror 
incompleteparallelpush
 

Serial propagation requires that parallel propagation shuts down cleanly.  

 
executiondisabled
 

Execution of deferred RPCs is disabled at the destination.  

 
crt_err_err
 

Error while creating entry in DEFERROR.  

 
deferred_rpc_qiesce
 

Replication activity for object group is suspended.  

 
commfailure
 

Communication failure during deferred RPC.  

 
missingpropator
 

A propagator does not exist.  

REGISTER_PROPAGATOR procedure

This procedure registers the given user as the propagator for the local database. It also grants to the given user CREATE SESSION, CREATE PROCEDURE, CREATE DATABASE LINK, and EXECUTE ANY PROCEDURE privileges (so that the user can create wrappers).

Syntax

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
   username  IN  VARCHAR2);

Parameters

Table 10-23 REGISTER_PROPAGATOR Procedure Parameters
Parameter  Description  
username
 

Name of the user.  

Exceptions

Table 10-24 REGISTER_PROPAGATOR Procedure Exceptions
Exception  Description  
missinguser
 

Given user does not exist.  

 
alreadypropagator
 

Given 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 or snapshot site. You should schedule one purge job.

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 10-25 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, 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 next_date.  

 
next_date
 

Allows you to specify a given 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, 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, 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.  

 
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 shutdown 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 master destination. This procedure does a COMMIT.

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 10-26 SCHEDULE_PUSH Procedure Parameters
Parameter  Description  
destination
 

The fully qualified database name of the master 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, 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 next_date.  

 
next_date
 

Allows you to specify a given time to push deferred transactions to the master site destination. 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 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

 
heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set the parameter unless so directed by Oracle Worldwide Support.  

 
stop_on_error
 

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

 
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 need to be pushed.  

SET_DISABLED procedure

To disable or enable propagation of the deferred transaction queue from the current site to a given destination site. If the disabled parameter is TRUE, then the procedure disables propagation to the given 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 given 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 given destination and, although this does not push the queue, it permits future invocations to PUSH to push the queue to the given 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);

Parameters

Table 10-27 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 given destination. Set this to FALSE to enable propagation.  

Exceptions

Table 10-28 SET_DISABLED Procedure Exceptions
Exception  Description  
NO_DATA_FOUND
 

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

UNREGISTER_PROPAGATOR procedure

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

Syntax

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

Parameters

Table 10-29 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 10-30 UNREGISTER_PROPAGATOR Procedure Exceptions
Parameter  Description  
missingpropagator
 

Given 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 snapshot or master site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PURGE;

Parameters

None

UNSCHEDULE_PUSH procedure

This procedure stops automatic pushes of the deferred transaction queue from a snapshot or master site to another master site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PUSH (
   dblink   IN   VARCHAR2);

Parameters

Table 10-31 UNSCHEDULE_PUSH Procedure Parameters
Parameter  Description  
dblink
 

Fully qualified pathname to master database site at which you want to unschedule periodic execution of deferred remote procedure calls.  
Table 10-32 UNSCHEDULE_PUSH Procedure Exceptions
Exception  Description  
NO_DATA_FOUND
 

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


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index