Release 8.0
A58245-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Replication Release 8.0 A58245-01 |
|
This chapter describes the following topics:
Oracle provides several tables and views for you to use in administering deferred transactions. These views provide information about each deferred transaction, such as the transaction destinations, the deferred calls that make up the transaction, and any errors encountered during attempted execution of the transaction.
Attention: You should not modify these tables directly; use the procedures provided in the DBMS_DEFER and DBMS_DEFER_SYS packages.
These views are briefly described below. For more information, see Chapter 10, "Data Dictionary Views".
| Data Dictionary View | Description |
|---|---|
| DEFCALL | Records all deferred remote procedure calls (RPCs). |
| DEFCALLDEST | Lists the destinations for each deferred remote procedure call. |
| DEFDEFAULT DEST | Lists the default destination for deferred remote procedure calls. |
| DEFERROR | Provides information about transactions that could not be applied. |
| DEFLOB | Storage for LOB parameters to deferred RPCs. |
| DEFSCHEDULE | Displays information about when a job is next scheduled to be executed. |
| DEFTRAN | Records all deferred transactions. |
| DEFTRANDEST | Lists the destinations for a deferred transaction. |
Every well formed deferred transaction must consist of zero or one DBMS_DEFER.TRANSACTION calls followed by zero or more well formed deferred remote procedure calls, followed by a SQL COMMIT statement.
Attention: The procedures for which you are building deferred calls must be part of a package. Deferred calls to standalone procedures are not supported.
Every well formed deferred remote procedure call must consist of one DBMS_DEFER.CALL call, followed by zero or more DBMS_DEFER.datatype_ARG calls. The number of calls to the appropriate datatype_ARG procedures is determined by the value of the ARG_COUNT parameter passed to the CALL procedure.
If you do not call DBMS_DEFER.TRANSACTION to indicate the start of a transaction, Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction.
To create your own deferred transactions, you must have the EXECUTE privilege on the DBMS_DEFER package. This package is owned by SYS. Because deferred transactions are executed in the privilege domain of the replication propagator, EXECUTE privileges on the DBMS_DEFER package should not be widely granted.
Suggestion: To control access to these procedures, you should create a cover package in the replication propagator's schema, and grant EXECUTE on this cover package.
In addition to building the calls that make up a deferred transaction, you must also specify the destination for this transaction. Transactions placed into the deferred transaction queue by the advanced replication facility are queued to all of the asynchronous locations (dblinks) for the replicated object, as listed in the DBA_REPPROP view. When you use the procedures in the DBMS_DEFER package to add a deferred transaction to the queue, you must specify a destination using one of the following methods. These methods are listed in order of precedence:
Note: This method cannot be combined with any of the following methods.
Indicate the start of a new deferred transaction by calling the TRANSACTION procedure in the DBMS_DEFER package, as shown in the following example:
nodes dbms_defer.node_list_t;
node(1) := 'acct_hq.hq.com';
node(2) := 'acct_ny.ny.com';
DBMS_DEFER.TRANSACTION(nodes);
In this example, any calls that make up the deferred transaction for which you do not specify a destination when you call DBMS_DEFER.CALL, will be queued for the ACCT_HQ and ACCT_NY databases.
The call to TRANSACTION is optional. If you do not call TRANSACTION, Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction. Calling TRANSACTION is useful if you want to specify a list of nodes to which to forward the deferred calls, and the list is the same for all calls in the deferred transaction.
All deferred transactions are recorded in the DEFTRAN view. Each destination of the transaction is noted in the DEFTRANDEST view.
Additional Information: The parameters for the TRANSACTION procedure are described in Table 9-7 on page 9 - 9, and the exceptions are listed in Table 9-8 on page 9 - 9.
To build a deferred call to a remote procedure, call the CALL procedure in the DBMS_DEFER package, as shown in the following example:
DBMS_DEFER.CALL( schema_name => 'accts_rec', package_name => 'hr', proc_name => 'hire_emp', arg_count => 3);
This example builds a deferred call to the HR.HIRE_EMP procedure in the ACCTS_REC schema. This HIRE_EMP procedure takes three arguments. No destination is specified for the deferred call, so the destination must have been specified using one of the other methods outlined on page 8 - 3.
All deferred remote procedure calls are recorded in the DEFCALL view. Each destination for the call is noted in the DEFCALLDEST view.
Additional Information: The parameters for the CALL procedure are described in Table 9-1 on page 9 - 6, and the exceptions are listed in Table 9-2 on page 9 - 6.
After deferring a call to a remote procedure, you must provide the data that is passed to this procedure (only IN parameters are supported). There must be one call for each of the arguments that is passed to the remote procedure, and these calls must be made in the order that the arguments must be passed. The type of the data determines which procedure in the DBMS_DEFER package you must call. For example, suppose you deferred a call to the HIRE_EMP procedure, and it took three arguments, as shown below:
HIRE_EMP(ename IN VARCHAR2, empno IN NUMBER, salary IN NUMBER)
After building the deferred call to HIRE_EMP, you could pass the necessary data to this procedure by making the following three calls:
DBMS_DEFER.VARCHAR2_ARG('scott'); DBMS_DEFER.NUMBER_ARG(12345); DBMS_DEFER.NUMBER_ARG(30000);
Depending upon the type of the data that you need to pass to the procedure, you need to call one of the following procedures in the DBMS_DEFER package for each argument to the procedure:
DBMS_DEFER.NUMBER_ARG(arg IN NUMBER); DBMS_DEFER.DATE_ARG(arg IN DATE); DBMS_DEFER.VARCHAR2_ARG(arg IN VARCHAR2); DBMS_DEFER.NVARCHAR2_ARG(arg IN NVARCHAR2); DBMS_DEFER.CHAR_ARG(arg IN CHAR); DBMS_DEFER.NCHAR_ARG(arg IN NCHAR); DBMS_DEFER.ROWID_ARG(arg IN ROWID); DBMS_DEFER.RAW_ARG(arg IN RAW); DBMS_DEFER.BLOB_ARG(arg IN BLOB); DBMS_DEFER.CLOB_ARG(arg IN CLOB); DBMS_DEFER.NCLOB_ARG(arg IN NCLOB);
Note: The RAW_ARG, CHAR_ARG, NCHAR_ARG, VARCHAR2_ARG, and NVARCHAR2_ARG procedures can raise an ORA-23323 exception if the argument that you pass to the procedure is too long.
If you use the DBMS_DEFER package to build a deferred transaction, and you do not supply a destination for a deferred transaction or the calls within that transaction, Oracle uses the DEFDEFAULTDEST view to determine the destination databases to which you want to defer a remote procedure call.
To add a destination database to this view call the ADD_DEFAULT_DEST procedure in the DBMS_DEFER_SYS package as shown in the following example:
DBMS_DEFER_SYS.ADD_DEFAULT_DEST( dblink => 'acct_ny.ny.com');
In this example, any future deferred transactions for which no destination has been specified will be queued for the ACCT_NY database.
Additional Information: The parameter for the ADD_DEFAULT_DEST procedure is described in Table 9-18 on page 9 - 19, and the exception is listed in Table 9-19 on page 9 - 19.
To remove a destination database from the DEFDEFAULTDEST view, call the DELETE_DEFAULT_DEST procedure in the DBMS_DEFER_SYS package, as shown in the following example:
DBMS_DEFER_SYS.DELETE_DEFAULT_DEST( dblink => 'acct_ny.ny.com');
In this example, any future deferred transactions that you create will no longer be queued for the ACCT_NY database as the default.
To delete a transaction from the deferred transaction queue, you can use Replication Manager. For more information, see "Purging a Site's Deferred Transaction Queue" on page 3-12.
Additional Information: The parameter for the DELETE_DEFAULT_DEST procedure is described in Table 9-20 on page 9 - 20.
When you build a deferred transaction, the transaction is added to the deferred transaction queue at your local site. The remote procedures are not executed until this queue is pushed. You can either schedule this queue to be pushed at a periodic interval by creating a scheduled link or by calling DBMS_DEFER_SYS.SCHEDULE_PUSH, or you can force the queue to be pushed immediately with Replication Manager or by calling DBMS_DEFER_SYS.PUSH. These transactions are propagated in the same manner as your DML changes are propagated by the advanced replication facility.
Oracle supports large internal objects (LOBs): binary LOBs (BLOBs); character LOBs (CLOBs); and national character LOBs (NCLOBs).
Note: For data manipulation language (DML) or for piecewise update, the larger the size of the LOB (update), the more the propagation time will increase.
Note: All sites must be Oracle8 sites to support deferred RPCs of above named objects.
Attention: For security, note that a LOB parameter to a (deferred) RPC is visible in the transaction only while the RPC is being executed.
Oracle stores internal LOB parameters to deferred RPCs in a side table that is referenced only by way of a synonym. This gives the you flexibility for storage parameters and the containing schema. The following shows the default storage table for LOB parameters.
CREATE TABLE system.def$_lob( id RAW(16) CONSTRAINT def$_lob_primary PRIMARY KEY, deferred_tran_db VARCHAR2(128), -- origin db deferred_tran_id VARCHAR2(22), -- transaction id blob_col BLOB, clob_col CLOB nclob_col NCLOB) / -- make deletes fast CREATE INDEX system.def$_lob_n1 ON system.def$_lob( deferred_tran_db, deferred_tran_id) / -- use a synonym in case underlying table is moved CREATE SYNONYM sys.def$_lob FOR system.def$_lob / CREATE OR REPLACE VIEW DefLOB AS SELECT * FROM sys.def$_lob / CREATE PUBLIC SYNONYM DefLOB FOR DefLOB /
