Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 16.6 Monitoring Conflict Resolution with DBMS_REPCATChapter 17Next: 17.2 DBMS_DEFER_SYS: Managing Deferred Transactions
 

17. Deferred Transactions and Remote Procedure Calls

Contents:
About Deferred Transactions and RPCs
DBMS_DEFER_SYS: Managing Deferred Transactions
DBMS_DEFER: Building Deferred Calls
DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

Oracle's advanced replication option relies primarily on deferred transactions and remote procedure calls (RPCs). When you commit a transaction against a replicated table, for example, the replication support triggers queue a deferred transaction to do your bidding in one or more remote databases. In addition to providing many of the underpinnings for the advanced replication option, the packages used to create and manipulate deferred calls are available for direct use in your applications. You will use the packages described in this chapter for deferred transactions and RPCs.

DBMS_DEFER_SYS

Performs administrative tasks such as scheduling, executing, and deleting queued transactions.

DBMS_DEFER

Builds deferred calls.

DBMS_DEFER_QUERY

Provides access to parameters passed to deferred calls, primarily for diagnostic purposes.

NOTE: Users must have explicit EXECUTE privileges on DBMS_DEFER in order to create deferred calls.

17.1 About Deferred Transactions and RPCs

This section provides some basic information you'll find useful in using the packages described in this chapter.

17.1.1 About Remote Destinations

Every remote procedure call has to be executed somewhere, and Oracle offers several methods of specifying where.

If you are using the advanced replication option, you have implicitly named the destinations for deferred RPCs and transactions by creating master sites. Whenever a user performs DML on a table, the transaction is applied locally and is queued for delivery to all other sites where the table is replicated; these sites are listed in the DBA_REPSITES data dictionary view. Similarly, replicated procedure calls are also queued for all master sites in the replicated environment. Refer to Chapter 15, Advanced Replication, for details on using DBMS_REPCAT, which performs most replicated environment administration operations.

If you are not using the advanced replication option, then the destination site(s) are determined by one of the following means, listed in order of precedence:

  1. The sites specified in the nodes parameter in the call to DBMS_DEFER.CALL, described later in this chapter.

  2. The sites specified in the nodes parameter to DBMS_DEFER.TRANSACTION, described later in this chapter.

  3. The sites specified in the DEFDEFAULTDEST data dictionary view, described later in this chapter (Table 17.4).

If Oracle cannot determine a call's destination by any of these methods, or if you specify differing destinations in the DBMS_DEFER.TRANSACTION and DBMS_DEFER.CALL procedures, the deferred call will raise an exception.

17.1.2 Data Dictionary Views

There are eight data dictionary views (see Table 17.1) that contain data about deferred transactions and RPCs. You can query these views to determine information such as the destination of RPC calls, error messages, and scheduled execution times. Most of the packages associated with deferred calls reference and/or modify the data in these views.

Tables 17-2 through 17-9 provide details about the contents of these views.


Table 17.1: Data Dictionary Views Associated with Deferred Calls

View Name

Description

DEFCALL

Contains information about all deferred RPCs. Queries SYSTEM.DEF$_CALL table.

DEFCALLDEST

Contains the destination database(s) for each deferred RPC in DEFCALL. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_CALLDEST, SYSTEM.DEF$_ERROR, and SYSTEM.REPCAT$_REPPROP.

DEFDEFAULTDEST

Contains the default destinations for deferred RPCs. Queries SYSTEM.DEF$_DEFAULTDEST.

DEFERROR

Contains error information for deferred calls that could not be applied at their destination. Queries SYSTEM.

DEF$_ERROR.

DEFERRORCOUNT

Contains the count of errors for each destination. Queries SYSTEM.DEF$_ERROR.

DEFSCHEDULE

Contains information about the scheduling of deferred jobs. Queries SYSTEM.DEF$_DESTINATION and SYS.JOB$.

DEFTRAN

Contains information about all deferred calls. Queries SYSTEM.DEF$_CALL and SYS.USER$.

DEFTRANDEST

Contains the destination database(s) for each deferred transaction. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_ERROR, SYSTEM.DEF$_CALLDEST, and SYSTEM.REPCAT$_REPPROP.


Table 17.2: Columns in DEFCALL Data Dictionary View

Column Name

Description

CALLNO

Unique ID of call at deferred_tran_db

DEFERRED_TRAN_DB

Global name of database that originated the call

DEFERRED_TRAN_ID

Unique ID of the transaction

SCHEMANAME

Schema that owns the package

PACKAGENAME

Name of the package

PROCNAME

Name of the procedure within the package

ARGCOUNT

Number of arguments passed to the procedure


Table 17.3: Columns in DEFCALLDEST Data Dictionary View

Column Name

Description

CALLNO

Unique ID of call at deferred_tran_db.

DEFERRED_TRAN_ID

Unique ID of the transaction. Note that each deferred_tran_id has one or more calls.

DEFERRED_TRAN_DB

Global name of database that originated the call.

DBLINK

Global name of the destination database.


Table 17.4: Columns in DEFDEFAULTDEST Data Dictionary View

Column Name

Description

DBLINK

Global name of the destination database


Table 17.5: Columns in DEFERROR Data Dictionary View

Column Name

Description

DEFERRED_TRAN_DB

Global name of the database that originated the RPC

DEFERRED_TRAN_ID

ID of the transaction originating or copying the deferred RPC

CALLNO

Unique ID of call at deferred_tran_db

DESTINATION

Database link used to specify the destination database

ERROR_TIME

Time the error occurred

ERROR_NUMBER

Oracle error number

ERROR_MSG

Error message text


Table 17.6: Columns in DEFERRCOUNT Data Dictionary View

Column Name

Description

ERRCOUNT

Number of errors in deferred RPC calls to destination

DESTINATION

Global name of destination database


Table 17.7: Columns in DEFSCHEDULE Data Dictionary View

Column Name

Description

DBLINK

Global name of the database for which pushes of deferred RPC calls is scheduled

JOB

Number of the job (job column in DBA_JOBS)

INTERVAL

Date expression that determines how often the job runs

NEXT_DATE

Next time the job is scheduled to run

LAST_DATE

Last time the job ran

DISABLED

Y if propagation to destination is disabled, otherwise N

LAST_TXN_COUNT

Number of transactions pushed last time job ran

LAST_ERROR

Oracle error number from the most recent push

LAST_MSG

Error message text from the most recent push


Table 17.8: Columns in DEFTRAN Data Dictionary View

Column Name

Description

DEFERRED_TRAN_ID

ID of the transaction that originated or copied the deferred RPCs.

DEFERRED_TRAN_DB

Global name of the database that originated or copied the deferred RPCs.

ORIGIN_TRAN_ID

ID of the transaction that originated the deferred RPCs.

ORIGIN_TRAN_DB

Global name of the database that originated the deferred RPCs.

ORIGIN_USER

USERID of user originating deferred RPC calls.

DELIVERY_ORDER

SCN of the deferred transaction in the queue.

DESTINATION_LIST

R if destinations are determined by the DBA_REPSCHEMA data dictionary view. D if destinations were specified in DEFDEFAULTDEST or the NODE_LIST parameter to the DBMS_DEFER.TRANSACTION, DBMS_DEFER.CALL, or DBMS_DEFER.COPY procedures.

START_TIME

Start time of the origination transaction.

COMMIT_COMMENT

User-supplied comments.


Table 17.9: Columns in DEFTRANDEST Data Dictionary View

Column Name

Description

DEFERRED_TRAN_ID

ID of the transaction to propagate to database specified by dblink

DEFERRED_TRAN_DB

Global name of the database that originated the deferred transaction

DBLINK

Global name of the destination database


Previous: 16.6 Monitoring Conflict Resolution with DBMS_REPCATOracle Built-in PackagesNext: 17.2 DBMS_DEFER_SYS: Managing Deferred Transactions
16.6 Monitoring Conflict Resolution with DBMS_REPCATBook Index17.2 DBMS_DEFER_SYS: Managing Deferred Transactions

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference