25 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 make up the transactions, and any errors encountered during attempted execution of the transaction.

This chapter contains these topics:

DEFCALL
DEFCALLDEST
DEFDEFAULTDEST
DEFERRCOUNT
DEFERROR
DEFLOB
DEFPROPAGATOR
DEFSCHEDULE
DEFTRAN
DEFTRANDEST

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.

See Also:

Oracle Database Advanced Replication for information about parallel propagation
"SCHEDULE_PUSH Procedure"
"CLEAR_PROP_STATISTICS Procedure"

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.