Oracle DBMS_DEFER_SYS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose 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.
AUTHID DEFINER
Constants
Name Data Type Value
Result of Push or Purge Constants
result_ok BINARY_INTEGER 0
result_startup_seconds BINARY_INTEGER 1
result_execution_seconds BINARY_INTEGER 2
result_transaction_count BINARY_INTEGER 3
result_delivery_order_limit BINARY_INTEGER 4
result_errors BINARY_INTEGER 5
result_push_disabled BINARY_INTEGER 6
result_purge_disabled BINARY_INTEGER 6
result_cant_get_sr_enq BINARY_INTEGER 7
Purge Method in Purge Queue Constants
purge_method_quick BINARY_INTEGER 1
purge_method_precise BINARY_INTEGER 2
Result of Push or Purge Constants
seconds_infinity BINARY_INTEGER 60*60*24*180
transactions_infinity BINARY_INTEGER 1000000000
delivery_order_infinity NUMBER 100000000000000000000
Dependencies
DBMS_ASYNCRPC_PUSH DBMS_DEFER_SYS_DEFINER DBMS_REPCAT_MIG_INTERNAL
DBMS_DEFER_IMPORT_INTERNAL DBMS_DEFER_SYS_PART1 DBMS_SNA_UTL
DBMS_DEFER_INTERNAL_SYS DBMS_LOCK DBMS_STANDARD
DBMS_DEFER_REPCAT DBMS_REPCAT_ADMIN  
Documented Yes
Exceptions
Error Code Reason
ORA-02094 norepoption: Replication is not linked as an option
ORA-23324 crt_err_err: Parameter type doesn't match actual type
ORA-23352 dblink is already in the default list
ORA-23357 missingpropagator: Missing propagator
ORA-23362 missinguser: Invalid user
ORA-23388 incompleteparallelpush: Incomplete parallel propagation/push
ORA-23393 alreadypropagator: Already the propagator
ORA-23394 duplicatepropagator: Duplicate propagator
ORA-23418 propagator_inuse: Propagator in use
ORA-23426 notemptyqueue: Deferred RPC for some destination
ORA-23427 argoutofrange: Purge queue argument is out of range
ORA-23495 serialpropnotallowed: Serial propagation can not be used
ORA-23496 cantsetdisabled: Can't set disabled
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to WMSYS and the DBA role. By default, this package is owned by user SYS and execution should be granted only to administrators and daemons that perform replication administration and execute deferred transactions. See the security considerations for the dbms_defer package for related considerations.
Source {ORACLE_HOME}/rdbms/admin/dbmsdefr.sql
Subprograms
 
ADD_DEFAULT_DEST
Adds a destination database to the DEFDEFAULTDEST view dbms_defer_sys.add_default_dest(dblink IN VARCHAR2);
exec dbms_defer_sys.add_default_dest('REMOTEDB');
 
CLEAR_PROP_STATISTICS
Clear the propagation statistics in the DEFSCHEDULE view dbms_defer_sys.clear_prop_statistics(dblink IN VARCHAR2);
exec dbms_defer_sys.clear_prop_statistics('REMOTEDB');
 
DELETE_DEFAULT_DEST
Removes a destination database from the DEFDEFAULTDEST view dbms_defer_sys.delete_default_dest(dblink IN VARCHAR2);
exec dbms_defer_sys.delete_default_dest('REMOTEDB');
 
DELETE_DEF_DESTINATION
Removes a destination database from the DEFSCHEDULE view dbms_defer_sys.delete_def_destination(
destination IN VARCHAR2,
force       IN BOOLEAN := FALSE);

TRUE: ignore safety checks
exec dbms_defer_sys.delete_def_destination('REMOTEDB', FALSE);
 
DELETE_ERROR
Deletes a transaction from the DEFERROR view dbms_defer_sys.delete_error(
deferred_tran_id IN VARCHAR2,
destination      IN VARCHAR2);
TBD
 
DELETE_TRAN
Deletes a transaction from the DEFTRANDEST view dbms_defer_sys.delete_tran(
deferred_tran_id IN VARCHAR2,
destination      IN VARCHAR2);
TBD
 
DISABLED
Determines whether propagation of the deferred transaction queue from the current site to a specified site is enabled dbms_defer_sys.disabled(destination IN VARCHAR2) RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_defer_sys.delete_def_destination('REMOTEDB') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  ENDIF;
END;
/
 
EXCLUDE_PUSH
Acquires an exclusive lock that prevents deferred transaction PUSH (either serial or parallel) dbms_defer_sys.exclude_push(timeout IN INTEGER) RETURN INTEGER;

Description Return Value
Success (Y) 0
Timeout (N) 1
Deadlock (N) 2
Already own the lock (Y) 4
set serveroutput on

DECLARE
 i INTEGER;
BEGIN
  i := dbms.defer_sys.exclude_push(10);
  dbms_output.put_line(i);
END;
/
 
EXECUTE
Execute transactions queued for destination_node using the security context of the propagator. stop_on_error determines whether processing of subsequent transaction continues after an error is detected.

The execute_as_user parameter is obsolete and ignored.

Overload 1
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,
delay_seconds     IN NATURAL := 0,
batch_size        IN NATURAL := 0);
TBD
Overload 2 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,
delay_seconds     IN NATURAL := 0,
batch_size        IN NATURAL := 0);
TBD
 
EXECUTE_ERROR
(Re)Execute transactions that previously encountered conflicts. Each transaction is executed in the security context of the original receiver of the transaction. dbms_defer_sys.execute_error(
deferred_tran_id IN VARCHAR2,
destination      IN VARCHAR2);
TBD
 
EXECUTE_ERROR_AS_USER
(Re)Execute transactions that previously encountered conflicts. 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);
TBD
 
EXECUTE_ERROR_CALL
For internal use only dbms_defer_sys.execute_error_call_as_user(
deferred_tran_id IN VARCHAR2,
callno           IN NUMBER);
TBD
 
EXECUTE_ERROR_CALL_AS_USER
For internal use only dbms_defer_sys.execute_error_call_as_user(
deferred_tran_id IN VARCHAR2,
callno           IN NUMBER);
TBD
 
NULLIFY_ALL_TRANS
For internal use only dbms_defer_sys.nullify_all_trans;
exec dbms_defer_sys.nullify_all_trans;
 
NULLIFY_TRANS_TO_DESTINATION
For internal use only dbms_defer_sys.nullify_trans_to_destination(dblink IN VARCHAR2, catchup IN RAW := NULL);
TBD
 
PURGE
Purge pushed transactions from the queue 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;
TBD
 
PUSH
Push transactions queued for destination node, choosing 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;
TBD
 
PUSH_WITH_CATCHUP
For internal use only dbms_defer_sys.push_with_catchup(
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,
catchup              IN RAW := NULL)
RETURN BINARY_INTEGER;
TBD
 
REGISTER_PROPAGATOR
Register the given user as the propagator for the local database dbms_defer_sys.register_propagator(username IN VARCHAR2);
exec dbms_defer_sys.register_propagator('UWCLASS);
 
SCHEDULE_EXECUTION
Insert or update a defschedule entry and signal the background process 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,
delay_seconds     IN NATURAL := NULL,
batch_size        IN NATURAL := NULL);
TBD
 
SCHEDULE_PURGE
Schedule a job to invoke purge 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);
TBD
 
SCHEDULE_PUSH
Schedule a job to invoke push 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);
TBD
 
SET_DISABLE
Turn on/off the disabled state for a destination set_disabled(destination IN VARCHAR2,
disabled IN BOOLEAN := TRUE,
catchup  IN RAW := '00',
override IN BOOLEAN := FALSE);
exec dbms_defer_sys.set_disable;
 
UNREGISTER_PROPAGATOR
Unregisters the given user as the given propagator for the local database dbms_defer_sys.unregister_propagator(
username IN VARCHAR2,
timeout  IN INTEGER DEFAULT dbms_lock.maxwait);
exec dbms_defer_sys.unregister_propagator('UWCLASS');
 
UNSCHEDULE_EXECUTION
Deprecated: Use UNSCHEDULE_PUSH below dbms_defer_sys.unschedule_execution(dblink IN VARCHAR2);
TBD
 
UNSCHEDULE_PURGE
Delete defschedule entry for purge. Signal to background process to stop servicing purge dbms_defer_sys.unschedule_purge;
exec dbms_defer_sys.unschedule_purge;
 
UNSCHEDULE_PUSH
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);
exec dbms_defer_sys.unschedule_push('FIXED_USER');

Related Topics
Advanced Replication
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved