26 Deferred Transaction Views

Oracle provides several views for you to use when administering deferred transactions. These views provide information about each deferred transaction, such as the transaction destinations, the deferred calls that comprise the transactions, and any errors encountered during attempted execution of the transaction.

This chapter contains these topics:

Caution:

You should not modify the tables directly. Instead, use the procedures provided in the DBMS_DEFER and DBMS_DEFER_SYS packages.

DEFCALL

Records all deferred remote procedure calls.

For calls placed in the queue using asynchronous replication, Oracle uses null compression for column objects and object tables that contain three or more consecutive nulls. Therefore, this view might show fewer attributes than the total number of attributes in a column object and fewer columns than the total number for an object table. For example, null compression can cause a column object with eight attributes to show only five attributes.

Null compression does not apply to error transactions.

Column Data Type NULL Description
CALLNO NUMBER - The unique ID of a call within a transaction.
DEFERRED_TRAN_ID VARCHAR2(30) - The unique ID of the associated transaction.
SCHEMANAME VARCHAR2(30) - The schema name of the deferred call.
PACKAGENAME VARCHAR2(30) - The package name of the deferred call. For a replicated table, this can refer to the table name.
PROCNAME VARCHAR2(30) - The procedure name of the deferred call. For a replicated table, this can refer to an operation name.
ARGCOUNT NUMBER - The number of arguments in the deferred call.

DEFCALLDEST

Lists the destinations for each deferred remote procedure call.

Column Data Type NULL Description
CALLNO NUMBER NOT NULL Unique ID of a call within a transaction.
DEFERRED_TRAN_ID VARCHAR2(30) NOT NULL Corresponds to the DEFERRED_TRAN_ID column in the DEFTRAN view. Each deferred transaction is made up of one or more deferred calls.
DBLINK VARCHAR2(128) NOT NULL The fully qualified database name of the destination database.

DEFDEFAULTDEST

If you are not using Advanced Replication and do not supply a destination for a deferred transaction or the calls within that transaction, then Oracle uses the DEFDEFAULTDEST view to determine the destination databases to which you want to defer a remote procedure call.

Column Data Type NULL Description
DBLINK VARCHAR2(128) NOT NULL The fully qualified database name to which a transaction is replicated.

DEFERRCOUNT

Contains information about the error transactions for a destination.

Column Data Type NULL Description
ERRCOUNT NUMBER - Number of existing transactions that caused an error for the destination.
DESTINATION VARCHAR2(128) - Database link used to address destination.

DEFERROR

Contains the ID of each transaction that could not be applied. You can use this ID to locate the queued calls associated with this transaction. These calls are stored in the DEFCALL view. You can use the procedures in the DBMS_DEFER_QUERY package to determine the arguments to the procedures listed in the DEFCALL view.

Column Data Type NULL Description
DEFERRED_TRAN_ID VARCHAR2(22) NOT NULL The ID of the transaction causing the error.
ORIGIN_TRAN_DB VARCHAR2(128) - The database originating the deferred transaction.
ORIGIN_TRAN_ID VARCHAR2(22) - The original ID of the transaction.
CALLNO NUMBER - Unique ID of the call at DEFERRED_TRAN_ID.
DESTINATION VARCHAR2(128) - Database link used to address destination.
START_TIME DATE - Time when the original transaction was enqueued.
ERROR_NUMBER NUMBER - Oracle error number.
ERROR_MSG VARCHAR2(2000) - Error message text.
RECEIVER VARCHAR2(30) - Original receiver of the deferred transaction.

DEFLOB

Contains the LOB parameters to deferred remote procedure calls (RPCs).

Column Data Type NULL Description
ID RAW(16) NOT NULL Identifier of the LOB parameter.
DEFERRED_TRAN_ID VARCHAR2(22) - Transaction ID for deferred remote procedure calls (RPCs) with this LOB parameter.
BLOB_COL BLOB(4000) - The binary LOB parameter.
CLOB_COL CLOB(4000) - The character LOB parameter.
NCLOB_COL NCLOB(4000) - The national character LOB parameter.

DEFPROPAGATOR

Contains information about the local propagator.

Column Data Type NULL Description
USERNAME VARCHAR2(30) NOT NULL User name of the propagator.
USERID NUMBER NOT NULL User ID of the propagator.
STATUS VARCHAR2(7) - Status of the propagator.
CREATED DATE NOT NULL Time when the propagator was registered.

DEFSCHEDULE

Contains information about when a job is next scheduled to be executed and also includes propagation statistics. The propagation statistics are for propagation of deferred transactions from the current site to the site specified in the DBLINK column.

To clear the propagation statistics for a remote site and start fresh, use the CLEAR_PROP_STATISTICS procedure in the DBMS_DEFER_SYS package.

Note:

The statistics in this view are populated only if parallel propagation is used with a database link. To use parallel propagation, set the parallelism parameter to 1 or greater when you run the SCHEDULE_PUSH procedure in the DBMS_DEFER_SYS package.
Column Data Type NULL Description
DBLINK VARCHAR2(128) NOT NULL Fully qualified path name to the master site for which you have scheduled periodic execution of deferred remote procedure calls.
JOB NUMBER - Number assigned to job when you created it by calling DBMS_DEFER_SYS.SCHEDULE_PUSH. Query the WHAT column of the USER_JOBS view to determine what is executed when the job is run.
INTERVAL VARCHAR2(200) - Function used to calculate the next time to push the deferred transaction queue to destination.
NEXT_DATE DATE - Next date that job is scheduled to be executed.
LAST_DATE DATE - Last time the queue was pushed (or attempted to push) remote procedure calls to this destination.
DISABLED CHAR(1) - If Y then propagation to destination is disabled.

If N then propagation to the destination is enabled.

LAST_TXN_COUNT NUMBER - Number of transactions pushed during last attempt.
LAST_ERROR_NUMBER NUMBER - Oracle error number from last push.
LAST_ERROR_MESSAGE VARCHAR2(2000) - Error message from last push.
CATCHUP RAW(16) NOT NULL The extension identifier associated with a new master site that is being added to a master group without quiescing the master group. If there is no extension identifier for a master site, then the value is 00.
TOTAL_TXN_COUNT NUMBER - Total combined number of successful transactions and error transactions.
AVG_THROUGHPUT NUMBER - The average number of transactions for each second that are propagated using parallel propagation. The transactions include both successfully applied transactions and error transactions created on the remote site. Time that has elapsed when the propagation coordinator is inactive (sleeping) is included in the calculation.
AVG_LATENCY NUMBER - If the transaction is successfully applied at the remote site, then the average number of seconds between the first call of a transaction on the current site and the confirmation that the transaction was applied at the remote site. The first call begins when the user makes the first data manipulation language (DML) change, not when the transaction is committed.

If the transaction is an error transaction, then the average number of seconds between the first call of a transaction on the current site and the confirmation that the error transaction is committed on the remote site.

TOTAL_BYTES_SENT NUMBER - Total number of bytes sent, including replicated data and metadata.
TOTAL_BYTES_RECEIVED NUMBER - Total number of bytes received in propagation confirmation messages.
TOTAL_ROUND_TRIPS NUMBER - Total number of network round trips completed to replicate data. A round trip is one or more consecutively sent messages followed by one or more consecutively received messages. So, if site A sends 20 messages to site B and then site B sends one message to site A, then that is that one round trip.
TOTAL_ADMIN_COUNT NUMBER - Total number of administrative requests sent to maintain information about transactions applied at the receiving site. The receiving site is the site specified in the DBLINK column. This special administration is only required for parallel propagation.
TOTAL_ERROR_COUNT NUMBER - Total number of unresolved conflicts for which a remote error was created.
TOTAL_SLEEP_TIME NUMBER - Total number of seconds the propagation coordinator was inactive (sleeping). You control the amount of time that the propagation coordinator sleeps using the delay_seconds parameter in the DBMS_DEFER_SYS.PUSH function.
DISABLED_INTERNALLY_SET VARCHAR2(1) - This value is relevant only if DISABLED is Y.

If DISABLED_INTERNALLY_SET is Y then propagation to 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.

If DISABLED_INTERNALLY_SET is N then propagation was not disabled internally.


DEFTRAN

Records all deferred transactions in the deferred transactions queue at the current site.

Column Data Type NULL Description
DEFERRED_TRAN_ID VARCHAR2(30) - The transaction ID that enqueued the calls.
DELIVERY_ORDER NUMBER - An identifier that determines the order of deferred transactions in the queue. The identifier is derived from the system change number (SCN) of the originating transaction.
DESTINATION_LIST VARCHAR2(1) - R indicates that the destinations are determined by the ALL_REPSITES view.

D indicates that the destinations were determined by the DEFDEFAULTDEST view or the NODE_LIST argument to the TRANSACTION or CALL procedures.

START_TIME DATE - The time that the original transaction was enqueued.

DEFTRANDEST

Lists the destinations for each deferred transaction in the deferred transactions queue at the current site.

Column Data Type NULL Description
DEFERRED_TRAN_ID VARCHAR2(30) NOT NULL The transaction ID of the transaction to replicate to the given database link.
DELIVERY_ORDER NUMBER - An identifier that determines the order of deferred transactions in the queue. The identifier is derived from the system change number (SCN) of the originating transaction.
DBLINK VARCHAR2(128) NOT NULL The fully qualified database name of the destination database.