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:
Table 14-1 DBMS_DEFER_SYS Package Subprograms
Subprogram | Description |
---|---|
Adds a destination database to the |
|
Clears the propagation statistics in the |
|
Removes a destination database from the |
|
Removes a destination database from the |
|
Deletes a transaction from the |
|
Deletes a transaction from the |
|
Determines whether propagation of the deferred transaction queue from the current site to a specified site is enabled. |
|
Acquires an exclusive lock that prevents deferred transaction |
|
Reexecutes a deferred transaction that did not initially complete successfully in the security context of the original receiver of the transaction. |
|
Reexecutes a deferred transaction that did not initially complete successfully in the security context of the user who executes this procedure. |
|
Purges pushed transactions from the deferred transaction queue at your current master site or materialized view site. |
|
Forces a deferred remote procedure call queue at your current master site or materialized view site to be pushed to a remote site. |
|
Registers the specified user as the propagator for the local database. |
|
Schedules a job to purge pushed transactions from the deferred transaction queue at your current master site or materialized view site. |
|
Schedules a job to push the deferred transaction queue to a remote site. |
|
Disables or enables propagation of the deferred transaction queue from the current site to a specified destination site. |
|
Unregisters a user as the propagator from the local database. |
|
Stops automatic purges of pushed transactions from the deferred transaction queue at a master site or materialized view site. |
|
Stops automatic pushes of the deferred transaction queue from a master site or materialized view site to a remote site. |
This procedure adds a destination database to the DEFDEFAULTDEST
data dictionary view.
DBMS_DEFER_SYS.ADD_DEFAULT_DEST ( dblink IN VARCHAR2);
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 |
Table 14-3 ADD_DEFAULT_DEST Procedure Exceptions
Exception | Description |
---|---|
ORA-23352 |
The |
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
DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS ( dblink IN VARCHAR2);
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 |
This procedure removes a destination database from the DEFDEFAULTDEST
view.
DBMS_DEFER_SYS.DELETE_DEFAULT_DEST ( dblink IN VARCHAR2);
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 |
This procedure removes a destination database from the DEFSCHEDULE
view.
DBMS_DEFER_SYS.DELETE_DEF_DESTINATION ( destination IN VARCHAR2, force IN BOOLEAN := false);
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 |
force |
When set to |
This procedure deletes a transaction from the DEFERROR
view.
DBMS_DEFER_SYS.DELETE_ERROR( deferred_tran_id IN VARCHAR2, destination IN VARCHAR2);
Table 14-7 DELETE_ERROR Procedure Parameters
Parameter | Description |
---|---|
deferred_tran_id |
Identification number from the |
destination |
The fully qualified database name from the |
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.
DBMS_DEFER_SYS.DELETE_TRAN ( deferred_tran_id IN VARCHAR2, destination IN VARCHAR2);
Table 14-8 DELETE_TRAN Procedure Parameters
Parameter | Description |
---|---|
deferred_tran_id |
Identification number from the |
destination |
The fully qualified database name from the |
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.
DBMS_DEFER_SYS.DISABLED ( destination IN VARCHAR2) RETURN BOOLEAN;
Table 14-9 DISABLED Function Parameters
Parameter | Description |
---|---|
destination |
The fully qualified database name of the node whose propagation status you want to check. |
Table 14-10 DISABLED Function Exceptions
Exception | Description |
---|---|
NO_DATA_FOUND |
Specified |
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. |
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.
DBMS_DEFER_SYS.EXCLUDE_PUSH ( timeout IN INTEGER) RETURN INTEGER;
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 |
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. |
This procedure reexecutes a deferred transaction that did not initially complete successfully in the security context of the original receiver of the transaction.
DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id IN VARCHAR2, destination IN VARCHAR2);
Table 14-14 EXECUTE_ERROR Procedure Parameters
Parameter | Description |
---|---|
deferred_tran_id |
Identification number from the |
destination |
The fully qualified database name from the |
Table 14-15 EXECUTE_ERROR Procedure Exceptions
Exception | Description |
---|---|
ORA-24275 error |
Illegal combinations of |
badparam |
Parameter value missing or invalid (for example, if |
missinguser |
Invalid user. |
This procedure reexecutes a deferred transaction that did not initially complete successfully. Each transaction is executed in the security context of the connected user.
DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER ( deferred_tran_id IN VARCHAR2, destination IN VARCHAR2);
Table 14-16 EXECUTE_ERROR_AS_USER Procedure Parameters
Parameter | Description |
---|---|
deferred_tran_id |
Identification number from the |
destination |
The fully qualified database name from the |
Table 14-17 EXECUTE_ERROR_AS_USER Procedure Exceptions
Exception | Description |
---|---|
ORA-24275 error |
Illegal combinations of |
badparam |
Parameter value missing or invalid (for example, if |
missinguser |
Invalid user. |
This function purges pushed transactions from the deferred transaction queue at your current master site or materialized view site.
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;
Table 14-18 PURGE Function Parameters
Parameter | Description |
---|---|
purge_method |
Controls how to purge the deferred transaction queue: Specify the following for this parameter to use
Specify the following for this parameter to user
If you use |
rollback_segment |
Name of rollback segment to use for the purge, or |
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 |
transaction_count |
If > 0, then shut down cleanly after purging |
write_trace |
When set to |
Table 14-19 Purge Function Returns
Value | Description |
---|---|
result_ok |
OK, terminated after |
result_startup_seconds |
Terminated by lock timeout while starting. |
result_execution_seconds |
Terminated by exceeding |
result_transaction_count |
Terminated by exceeding |
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. |
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. |
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 more than one database link 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.
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.
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;
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, |
write_trace |
When set to |
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 The |
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 |
transaction_count |
If > 0, then the maximum number of transactions to be pushed before stopping. If |
delivery_order_limit |
Stop execution cleanly before pushing a transaction where |
Table 14-22 PUSH Function Returns
Value | Description |
---|---|
result_ok |
OK, terminated after |
result_startup_seconds |
Terminated by lock timeout while starting. |
result_execution_seconds |
Terminated by exceeding |
result_transaction_count |
Terminated by exceeding |
result_delivery_order_limit |
Terminated by exceeding |
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. |
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 |
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. |
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
DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username IN VARCHAR2);
Table 14-24 REGISTER_PROPAGATOR Procedure Parameter
Parameter | Description |
---|---|
username |
Name of the user. |
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. |
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 queueDBMS_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);
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 |
next_date |
Allows you to specify a time to purge pushed transactions from the site's queue. This value is stored in the |
reset |
Set to |
purge_method |
Controls how to purge the deferred transaction queue: Specify the following for this parameter to use
Specify the following for this parameter to user
If you use |
rollback_segment |
Name of rollback segment to use for the purge, or |
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 |
transaction_count |
If > 0, then shut down cleanly after purging |
write_trace |
When set to |
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 queueDBMS_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);
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 |
next_date |
Allows you to specify a time to push deferred transactions to the remote site. This value is stored in the |
reset |
Set to |
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, |
write_trace |
When set to |
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 |
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 |
transaction_count |
If > 0, then the maximum number of transactions to be pushed before stopping. If |
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.
DBMS_DEFER_SYS.SET_DISABLED ( destination IN VARCHAR2, disabled IN BOOLEAN := true, catchup IN RAW := '00', override IN BOOLEAN := false);
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 |
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 |
override |
A A Note: Do not set this parameter unless directed to do so by Oracle Support Services. |
Table 14-29 SET_DISABLED Procedure Exceptions
Exception | Description |
---|---|
NO_DATA_FOUND |
No entry was found in the |
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. |
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.
DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR ( username IN VARCHAR2 timeout IN INTEGER DEFAULT DBMS_LOCK.MAXWAIT);
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 |
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. |
This procedure stops automatic purges of pushed transactions from the deferred transaction queue at a master site or materialized view site.
DBMS_DEFER_SYS.UNSCHEDULE_PURGE();
None
This procedure stops automatic pushes of the deferred transaction queue from a master site or materialized view site to a remote site.
DBMS_DEFER_SYS.UNSCHEDULE_PUSH ( dblink IN VARCHAR2);