Oracle8i Replication API Reference
Release 8.1.5

A67793-01

Library

Product

Contents

Index

Prev Next

8
Replication Management API Reference

All installations of Oracle advanced replication include the replication management application programming interface (API). A server's replication management API is a set of PL/SQL packages that encapsulates procedures and functions that administrators can use to configure Oracle's advanced replication features. Oracle Replication Manager also uses the procedures and functions of each site's replication management API to perform work. This chapter describes that packages that constitute Oracle replication API, including:

Packages

Oracle's replication management API includes the following packages:

Examples of Using Oracle's Replication Management API

To use Oracle's replication management API, you issue procedure or function calls using an ad-hoc query tool such as an Enterprise Manager SQL Worksheet, Server Manager's command prompt, or SQL*Plus. For example, the following call to the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure creates a new replicated table SALES.EMP in the ACCT replication group.

DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
     sname                => 'sales', 
     oname                => 'emp', 
     type                 => 'table', 
     use_existing_object  => TRUE, 
     ddl_text             => 'CREATE TABLE acct_rec.emp AS . . .', 
     comment              => 'created by . . .',
     retry                => FALSE,
     copy_rows            => TRUE,
     gname                => 'acct');

To call a replication management API function, you must provide an environment to receive the return value of the function. For example, the following anonymous PL/SQL block calls the DBMS_DEFER_SYS.DISABLED function in an IF statement.

BEGIN
 IF DBMS_DEFER_SYS.DISABLED('inst2') THEN
  DBMS_OUTPUT.PUT_LINE('Propagation to INST2 is disabled.');
 ELSE
  DBMS_OUTPUT.PUT_LINE('Propagation to INST2 is enabled.');
 END IF;
END;

Prerequisites to Consider

For many procedures and functions in the replication management API, there are important prerequisites to consider. For example:

Replication Manager and Oracle Replication Management API

Oracle's Replication Manager uses the replication management API to perform most of its functions. Using Replication Manager is much more convenient than issuing replication management API calls individually because the utility:

An easy way to learn how to use Oracle's replication management API is to use Replication Manager scripting feature. When you start an administrative session with Replication Manager, turn scripting on. When you are finished, turn scripting off and then review the script file. The script file contains all replication management API calls that were made during the session. See the Replication Manager help documentation for more information about its scripting feature.

DBMS_DEFER Package

Summary of Subprograms

Table 8-1
Subprogram  Description 
CALL procedure
 

Builds a deferred call to a remote procedure  

COMMIT_WORK procedure
 

Performs a transaction commit after checking for well-formed deferred remote procedure calls  

datatype_ARG procedure
 

Provides the data that is to be passed to a deferred remote procedure call  

TRANSACTION procedure
 

Indicates the start of a new deferred transaction  

CALL procedure

This procedure builds a deferred call to a remote procedure.

Syntax

DBMS_DEFER.CALL (
   schema_name       IN   VARCHAR2,
   package_name      IN   VARCHAR2,
   proc_name         IN   VARCHAR2,
   arg_count         IN   NATURAL,
   { nodes           IN   node_list_t 
   | group_name      IN   VARCHAR2 :=''});

Parameters

Table 8-2 CALL Procedure Parameters
Parameter  Description 
schema_name
 

Name of the schema in which the stored procedure is located.  

package_name
 

Name of the package containing the stored procedure. The stored procedure must be part of a package. Deferred calls to standalone procedures are not supported.  

proc_name
 

Name of the remote procedure to which you want to defer a call.  

arg_count
 

Number of parameters for the procedure. You must have one call to DBMS_DEFER.datatype_ARG for each of these parameters.  

nodes
 

A PL/SQL table of fully qualified database names to which you want to propagate the deferred call. The table is indexed starting at position 1 and ending when a NULL entry is found, or the NO_DATA_FOUND exception is raised. The data in the table is case insensitive. This argument is optional.  

group_name
 

Reserved for internal use.  


Note:

The CALL procedure is overloaded. The nodes and group_name parameters are mutually exclusive.  


Exceptions

Table 8-3 CALL Procedure Exceptions
Exception  Description 
ORA-23304 
(malformedcall)
 

Previous call was not correctly formed.  

ORA-23319
 

Parameter value is not appropriate.  

ORA-23352
 

Destination list (specified by nodes or by a previous DBMS_DEFER.TRANSACTION call) contains duplicates.  

COMMIT_WORK procedure

This procedure performs a transaction commit after checking for well-formed deferred remote procedure calls.

Syntax

DBMS_DEFER.COMMIT_WORK (
   commit_work_comment IN VARCHAR2);

Parameters

Table 8-4 COMMIT_WORK Procedure Parameters
Parameter  Description 
commit_work_
comment
 

Equivalent to SQL "COMMIT COMMENT" statement.  

Exceptions

Table 8-5 COMMIT_WORK Procedure Exceptions
Exception  Description 
ORA-23304 
(malformedcall)
 

Transaction was not correctly formed or terminated.  

datatype_ARG procedure

This procedure provides the data that is to be passed to a deferred remote procedure call. Depending upon the type of the data that you need to pass to a procedure, you must call one of the following procedures for each argument to the procedure.

Syntax

DBMS_DEFER.NUMBER_ARG       (arg  IN NUMBER);
DBMS_DEFER.DATE_ARG         (arg  IN DATE);
DBMS_DEFER.VARCHAR2_ARG     (arg  IN VARCHAR2);
DBMS_DEFER.CHAR_ARG         (arg  IN CHAR);
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);
DBMS_DEFER.NCHAR_ARG        (arg  IN NCHAR);
DBMS_DEFER.NVARCHAR2_ARG    (arg  IN NVARCHAR2);
DBMS_DEFER.ANY_CLOB_ARG     (arg  IN CLOB);
DBMS_DEFER.ANY_VARCHAR2_ARG (arg  IN VARCHAR2);
DBMS_DEFER.ANY_CHAR_ARG     (arg  IN CHAR);

Parameters

Table 8-6 datatype_ARG Procedure Parameters
Parameter  Description 
arg
 

Value of the parameter that you want to pass to the remote procedure to which you previously deferred a call.  

Exceptions

Table 8-7 datatype_ARG Procedure Exceptions
Exception  Description 
ORA-23323
 

Argument value is too long.  

TRANSACTION procedure

This procedure indicates the start of a new deferred transaction. If you omit this call, then Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction.

Syntax

DBMS_DEFER.TRANSACTION (
   nodes  IN   node_list_t);

Parameters

Table 8-8 TRANSACTION Procedure Parameters
Parameter  Description 
nodes
 

A PL/SQL table of fully qualified database names to which you want to propagate the deferred calls of the transaction. The table is indexed starting at position 1 until a NULL entry is found, or the NO_DATA_FOUND exception is raised. The data in the table is case insensitive.  

Exceptions

Table 8-9 TRANSACTION Procedure Exceptions
Exception  Description 
ORA-23304 
(malformedcall)
 

Previous transaction was not correctly formed or terminated.  

ORA-23319
 

Parameter value is not appropriate.  

ORA-23352
 

Raised by DBMS_DEFER.CALL if the node list contains duplicates.  

Usage Notes

The TRANSACTION procedure is overloaded. The behavior of the version without an input parameter is similar to that of the version with an input parameter, except that the former uses the nodes in the DEFDEFAULTDEST view instead of using the nodes in the nodes parameter.

DBMS_DEFER_QUERY Package

Summary of Subprograms

Table 8-10 DBMS_DEFER_QUERY Package Subprograms
Subprogram  Description 
GET_ARG_FORM function
 

Determines the form of an argument in a deferred call.  

GET_ARG_TYPE function
 

Determines the type of an argument in a deferred call.  

GET_CALL_ARGS 
procedure
 

Returns the text version of the various arguments for the given call.  

GET_datatype_ARG 
function
 

Determines the value of an argument in a deferred call.  

GET_ARG_FORM function

This function determines the form of an argument in a deferred call. This function will return the character set ID of a deferred call parameter.

For more about displaying deferred transactions, see "Displaying Deferred Transactions" in the Oracle8i Replication manual. For more information about displaying error transactions, see "Displaying Error Transactions" in the Oracle8i Replication manual.

Syntax

DBMS_DEFER_QUERY.GET_ARG_FORM (
   callno                IN   NUMBER,
   arg_no                IN   NUMBER,
   deferred_tran_id      IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 8-11 GET_ARG_FORM Function Parameters
Parameter  Description 
callno
 

Call identifier from the DEFCALL view.  

arg_no
 

Position of desired parameter in calls argument list. Parameter positions are 1..number of parameters in call.  

deferred_tran_id
 

Deferred transaction ID.  

Exceptions

Table 8-12 GET_ARG_FORM Function Exceptions
Exception  Description 
NO_DATA_FOUND
 

Input parameters do not correspond to a parameter of a deferred call.  

Returns

Table 8-13 GET_ARG_Form Function Returns
Return Value  Corresponding Datatype 
1
 
CHAR, VARCHAR2, CLOB
 
2
 
NCHAR, NVARCHAR2, NCLOB
 

GET_ARG_TYPE function

This function determines the type of an argument in a deferred call. The type of the deferred RPC parameter will be returned.

For more about displaying deferred transactions, see "Displaying Deferred Transactions" in the Oracle8i Replication manual. For more information about displaying error transactions, see "Displaying Error Transactions" in the Oracle8i Replication manual.

Syntax

DBMS_DEFER_QUERY.GET_ARG_TYPE (
   callno            IN   NUMBER,
   arg_no            IN   NUMBER,
   deferred_tran_id  IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 8-14 GET_ARG_TYPE Function Parameters
Parameter  Description 
callno
 

ID number from the DEFCALL view of the deferred remote procedure call.  

arg_no
 

Numerical position of the argument to the call whose type you want to determine. The first argument to a procedure is in position 1.  

deferred_tran_id
 

Identifier of the deferred transaction.  

Exceptions

Table 8-15 GET_ARG_TYPE Function Exceptions
Exception  Description 
NO_DATA_FOUND
 

Input parameters do not correspond to a parameter of a deferred call.  

Returns

Table 8-16 GET_ARG_TYPE Function Returns
Return Value  Corresponding Datatype 
1
 
VARCHAR2
 
2
 
NUMBER
 
11
 
ROWID
 
12
 
DATE
 
23
 
RAW
 
96
 
CHAR
 
112
 
CLOB
 
113
 
BLOB
 

GET_CALL_ARGS procedure

This procedure returns the text version of the various arguments for the given call. The text version is limited to the first 2000 bytes.

Syntax

DBMS_DEFER_QUERY.GET_CALL_ARGS (
   callno    IN  NUMBER,
   startarg  IN  NUMBER := 1,
   argcnt    IN  NUMBER,       
   argsize   IN  NUMBER,     
   tran_id   IN  VARCHAR2, 
   date_fmt  IN  VARCHAR2, 
   types     OUT TYPE_ARY,  
   forms     OUT TYPE_ARY,
   vals      OUT VAL_ARY);

Parameters

Table 8-17 GET_CALL_ARGS Procedure Parameters
Parameter  Description 
callno
 

ID number from the DEFCALL view of the deferred RPC.  

startarg
 

Numerical position of the first argument you want described.  

argcnt
 

Number of arguments in the call.  

argsize
 

Maximum size of returned argument.  

tran_id
 

Identifier of the deferred transaction.  

date_fmt
 

Format in which the date should be returned.  

types
 

Array containing the types of arguments.  

forms
 

Array containing the character set forms of arguments.  

vals
 

Array containing the values of the arguments in a textual form.  

Exceptions

Table 8-18 GET_CALL_ARGS Procedure Exceptions
Exception  Description 
NO_DATA_FOUND
 

Input parameters do not correspond to a parameter of a deferred call.  

GET_datatype_ARG function

This function determines the value of an argument in a deferred call.

For more about displaying deferred transactions, see "Displaying Deferred Transactions" in the Oracle8i Replication manual. For more information about displaying error transactions, see "Displaying Error Transactions" in the Oracle8i Replication manual.

Syntax

Depending upon the type of the argument value that you want to retrieve, the syntax for the appropriate function is as follows. Each of these functions returns the value of the specified argument.

DBMS_DEFER_QUERY.GET_datatype_ARG (
   callno             IN   NUMBER,
   arg_no             IN   NUMBER,
   deferred_tran_id   IN   VARCHAR2 DEFAULT NULL)
  RETURN datatype;

where datatype:

{ NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| ROWID
| BLOB
| CLOB
| NCLOB
| NCHAR
| NVARCHAR2 }

Parameters

Table 8-19 GET_datatype_ARG Function Parameters
Parameter  Description 
callno
 

ID number from the DEFCALL view of the deferred remote procedure call.  

arg_no
 

Numerical position of the argument to the call whose value you want to determine. The first argument to a procedure is in position one.  

deferred_tran_id
 

Identifier of the deferred transaction. Defaults to the last transaction identifier passed to GET_ARG_TYPE. The default is NULL.  

Exceptions

Table 8-20 GET_datatype_ARG Function Exceptions
Exception  Description 
NO_DATA_FOUND
 

Input parameters do not correspond to a parameter of a deferred call.  

ORA-26564
 

Argument in this position is not of the specified type.  

DBMS_DEFER_SYS Package

Summary of Subprograms

Table 8-21 DBMS_DEFER_SYS Package Subprograms
Subprogram  Description 
ADD_DEFAULT_DEST 
procedure
 

Adds a destination database to the DEFDEFAULTDEST view.  

DELETE_DEFAULT_DEST 
procedure
 

Removes a destination database from the DEFDEFAULTDEST view.  

DELETE_DEF_DESTINATION 
procedure
 

Removes a destination database from the DEFSCHEDULE view.  

DELETE_ERROR
 

Deletes a transaction from the DEFERROR view.  

DELETE_TRAN
 

Deletes a transaction from the DEFTRANDEST view.  

DISABLED
 

Determines whether propagation of the deferred transaction queue from the current site to a given site is enabled.  

EXCLUDE_PUSH
 

Acquires an exclusive lock that prevents deferred transaction PUSH.  

EXECUTE_ERROR
 

Re-executes a deferred transaction that did not initially complete successfully.  

EXECUTE_ERROR_AS_USER
 

Re-executes a deferred transaction that did not initially complete successfully.  

PURGE
 

Purges pushed transactions from the deferred transaction queue at your current master or snapshot site.  

PUSH function
 

Forces a deferred remote procedure call queue at your current master or snapshot site to be pushed to another master site.  

REGISTER_PROPAGATOR 
procedure
 

Registers the given user as the propagator for the local database.  

SCHEDULE_PURGE 
procedure
 

Schedules a job to purge pushed transactions from the deferred transaction queue at your current master or snapshot site.  

SCHEDULE_PUSH procedure
 

Schedules a job to push the deferred transaction queue to a remote master destination.  

SET_DISABLED procedure
 

Disables or enables propagation of the deferred transaction queue from the current site to a given destination site.  

UNREGISTER_PROPAGATOR 
procedure
 

Unregister a user as the propagator from the local database.  

UNSCHEDULE_PURGE 
procedure
 

Stops automatic purges of pushed transactions from the deferred transaction queue at a snapshot or master site.  

UNSCHEDULE_PUSH 
procedure
 

Stops automatic pushes of the deferred transaction queue from a snapshot or master site to another master site.  

ADD_DEFAULT_DEST procedure

This procedure adds a destination database to the DEFDEFAULTDEST view.

Syntax

DBMS_DEFER_SYS.ADD_DEFAULT_DEST (
   dblink   IN   VARCHAR2);

Parameters

Table 8-22 ADD_DEFAULT_DEST Procedure Parameters
Parameter  Description 
dblink
 

The fully qualified database name of the node that you want to add to the DEFDEFAULTDEST view.  

Exceptions

Table 8-23 ADD_DEFAULT_DEST Procedure Exceptions
Exception  Description 
ORA-23352
 

The dblink that you specified is already in the default list.  

DELETE_DEFAULT_DEST procedure

This procedure removes a destination database from the DEFDEFAULTDEST view.

Syntax

DBMS_DEFER_SYS.DELETE_DEFAULT_DEST (
   dblink   IN   VARCHAR2);

Parameters

Table 8-24 DELETE_DEFAULT_DEST Procedure Parameters
Parameter  Description 
dblink
 

The fully qualified database name of the node that you want to delete from the DEFDEFAULTDEST view. If Oracle does not find this dblink in the view, then no action is taken.  

DELETE_DEF_DESTINATION procedure

This procedure removes a destination database from the DEFSCHEDULE view.

Syntax

DBMS_DEFER_SYS.DELETE_DEF_DESTINATION (
   destination   IN   VARCHAR2,
   force         IN   BOOLEAN := FALSE);

Parameters

Table 8-25 DELETE_DEF_DESTINATION Procedure Parameters
Parameter  Description 
destination
 

The fully qualified database name of the destination that you want to delete from the DefSchedule view. If Oracle does not find this destination in the view, then no action is taken.  

force
 

When set to TRUE, Oracle ignores all safety checks and deletes the destination.  

DELETE_ERROR

To delete a transaction from the DEFERROR view.

Syntax

DBMS_DEFER_SYS.DELETE_ERROR(
   deferred_tran_id     IN   VARCHAR2,
   destination          IN   VARCHAR2);

Parameters

Table 8-26 Parameters for DELETE_ERROR
Parameter  Description 
deferred_tran_id
 

ID number from the DEFERROR view of the deferred transaction that you want to remove from the DEFERROR view. If this parameter is NULL, then all transactions meeting the requirements of the other parameter are removed.  

destination
 

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. If this parameter is NULL, then all transactions meeting the requirements of the other parameter are removed from the DEFERROR view.  

DELETE_TRAN

To delete a transaction from the DEFTRANDEST view. If there are no other DEFTRANDEST or DEFERROR entries for the transaction, then the transaction is deleted from the DEFTRAN and DEFCALL views as well.

Syntax

DBMS_DEFER_SYS.DELETE_TRAN (
   deferred_tran_id     IN   VARCHAR2,
   destination          IN   VARCHAR2);

Parameters

Table 8-27 Parameters for DELETE_TRAN
Parameter  Description 
deferred_tran_id
 

ID number from the DEFTRAN view of the deferred transaction that you want to delete. If this is NULL, then all transactions meeting the requirements of the other parameter are deleted.  

destination
 

The fully qualified database name from the DEFTRANDEST view of the database to which the transaction was originally queued. If this is NULL, then all transactions meeting the requirements of the other parameter are deleted.  

DISABLED

To determine whether propagation of the deferred transaction queue from the current site to a given site is enabled. The DISABLED function returns TRUE if the deferred remote procedure call (RPC) queue is disabled for the given destination.

Syntax

DBMS_DEFER_SYS.DISABLED (
   destination  IN   VARCHAR2)
  RETURN BOOLEAN;

Parameters

Table 8-28 Parameter for DISABLED
Parameter  Description 
destination
 

The fully qualified database name of the node whose propagation status you want to check.  

Returns

Table 8-29 Return Values for DISABLED
Value  Description 
TRUE
 

Propagation to this site from the current site is disabled.  

FALSE
 

Propagation to this site from the current site is enabled.  

Exceptions

Table 8-30 Exception for DISABLED
Exception  Description 
NO_DATA_FOUND
 

DESTINATION does not appear in the DEFSCHEDULE view.  

EXCLUDE_PUSH

To acquire an exclusive lock that prevents deferred transaction PUSH (either serial or parallel). This function does a commit when acquiring the lock. The lock is acquired with RELEASE_ON_COMMIT => TRUE, so that pushing of the deferred transaction queue can resume after the next commit.

Syntax

DBMS_DEFER_SYS.EXCLUDE_PUSH (
   timeout   IN   INTEGER)
  RETURN INTEGER;

Parameters

Table 8-31 Parameter for EXCLUDE_PUSH
Parameter  Description 
timeout
 

Timeout in seconds. If the lock cannot be acquired within this time period (either because of an error or because a PUSH is currently under way), then the call returns a value of 1. A timeout value of DBMS_LOCK.MAXWAIT waits indefinitely.  

Returns

E_PUSH
Table 8-32 Return Values for EXCLUDE_PUSH
Value  Description  
0
 

Success, lock acquired.  

1
 

Timeout, no lock acquired.  

2
 

Deadlock, no lock acquired.  

4
 

Already own lock.  

EXECUTE_ERROR

To reexecute a deferred transaction that did not initially complete successfully. This procedure raises an ORA-24275 error when illegal combinations of NULL and non-NULL parameters are used.

Syntax

DBMS_DEFER_SYS.EXECUTE_ERROR ( 
   deferred_tran_id IN   VARCHAR2,
   destination      IN   VARCHAR2);

Parameters

Table 8-33 Parameters for EXECUTE_ERROR
Parameter  Description 
deferred_tran_id
 

ID number from the DEFERROR view of the deferred transaction that you want to re-execute. If this is NULL, then all transactions queued for destination are re-executed.  

destination
 

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. This must not be NULL.  

Exceptions

Table 8-34 Exceptions for EXECUTE_ERROR
Exception  Description 
badparam
 

Parameter value missing or invalid (for example, if destination is NULL).  

missinguser
 

Invalid user.  

EXECUTE_ERROR_AS_USER

To reexecute a deferred transaction that did not initially complete successfully. Each transaction is executed in the security context of the connected user. This procedure raises an ORA-24275 error when illegal combinations of NULL and non-NULL parameters are used.

Syntax

DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER ( 
   deferred_tran_id IN   VARCHAR2,
   destination      IN   VARCHAR2);

Parameters

Table 8-35 Parameters for EXECUTE_ERROR_AS_USER
Parameter  Description 
deferred_tran_id
 

ID number from the DEFERROR view of the deferred transaction that you want to re-execute. If this is NULL, then all transactions queued for destination are re-executed.  

destination
 

The fully qualified database name from the DEFERROR view of the database to which the transaction was originally queued. This must not be NULL.  

Exceptions

Table 8-36 Exceptions for EXECUTE_ERROR_AS_USER
Exception  Description 
badparam
 

Parameter value missing or invalid (for example, if destination is NULL).  

missinguser
 

Invalid user.  

PURGE

To purge pushed transactions from the deferred transaction queue at your current master or snapshot site.

Syntax

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        := NULL);
  RETURN BINARY_INTEGER;

Parameters

Table 8-37 Parameters for PURGE
Parameter  Description 
purge_method
 

Controls how to purge the deferred transaction queue; purge_method_quick costs less, while purge_method_precise offers better precision.  

rollback_segment
 

Name of rollback segment to use for the purge, or NULL for default.  

startup_seconds
 

Maximum number of seconds to wait for a previous purge of the same deferred transaction queue.  

execution_seconds
 

If >0, then stop purge cleanly after the specified number of seconds of real time.  

delay_seconds
 

Stop purge cleanly after the deferred transaction queue has no transactions to purge for delay_seconds.  

transaction_count
 

If > 0, then shutdown cleanly after purging transaction_count number of transactions.  

write_trace
 

When set to TRUE, Oracle records the result value returned by the PURGE function in the server's trace file.  

Returns

Table 8-38 Return Values for Purge
Value  Description  
0
 

OK, terminated after delay_seconds expired.  

1
 

Terminated by lock timeout while starting.  

2
 

Terminated by exceeding execution_seconds.  

3
 

Terminated by exceeding transaction_count.  

5
 

Terminated after errors.  

Exceptions

Table 8-39 Exceptions for PURGE
Exception  Description 
argoutofrange
 

Parameter value is out of a valid range.  

executiondisabled
 

Execution of purging is disabled.  

defererror
 

Internal error.  

PUSH function

This function forces a deferred remote procedure call queue at your current master or snapshot site to be pushed (executed, propagated) to another master site using either serial or parallel propagation.

Syntax

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;

Parameters

Table 8-40 PUSH Function Parameters
Parameter  Description 
destination
 

The fully qualified database name of the master to which you are forwarding changes.  

parallelism
 

0 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set the parameter unless so directed by Oracle Worldwide Support.  

stop_on_error
 

The default, FALSE, indicates that the executor should continue even if errors, such as conflicts, are encountered. If TRUE, then shutdown (cleanly if possible) at the first indication that a transaction encountered an error at the destination site.  

write_trace
 

When set to TRUE, Oracle records the result value returned by the function in the server's trace file.  

startup_seconds
 

Maximum number of seconds to wait for a previous push to the same destination.  

execution_seconds
 

If >0, then stop push cleanly after the specified number of seconds of real time. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue.  

delay_seconds
 

Do not return before the specified number of seconds have elapsed, even if the queue is empty. Useful for reducing execution overhead if PUSH is called from a tight loop.  

transaction_count
 

If > 0, then the maximum number of transactions to be pushed before stopping. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue that need to be pushed.  

delivery_order_
limit
 

Stop execution cleanly before pushing a transaction where delivery_order >= delivery_order_limit  

Returns

Table 8-41 PUSH Function Returns
Value  Description  
0
 

OK, terminated after delay_seconds expired.  

1
 

Terminated by lock timeout while starting.  

2
 

Terminated by exceeding execution_seconds.  

3
 

Terminated by exceeding transaction_count.  

4
 

Terminated by exceeding delivery_order_limit.  

5
 

Terminated after errors.  

PUSH

Exceptions

Table 8-42 Exceptions for PUSH
Exception  Description 
deferror 
incompleteparallelpush
 

Serial propagation requires that parallel propagation shuts down cleanly.  

executiondisabled
 

Execution of deferred RPCs is disabled at the destination.  

crt_err_err
 

Error while creating entry in DEFERROR.  

deferred_rpc_qiesce
 

Replication activity for object group is suspended.  

commfailure
 

Communication failure during deferred RPC.  

missingpropator
 

A propagator does not exist.  

REGISTER_PROPAGATOR procedure

This procedure registers the given user as the propagator for the local database. It also grants to the given user CREATE SESSION, CREATE PROCEDURE, CREATE DATABASE LINK, and EXECUTE ANY PROCEDURE privileges (so that the user can create wrappers).

Syntax

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
   username  IN  VARCHAR2);

Parameters

Table 8-43 REGISTER_PROPAGATOR Procedure Parameters
Parameter  Description 
username
 

Name of the user.  

Exceptions

Table 8-44 REGISTER_PROPAGATOR Procedure Exceptions
Exception  Description 
missinguser
 

Given user does not exist.  

alreadypropagator
 

Given user is already the propagator.  

duplicatepropagator
 

There is already a different propagator.  

SCHEDULE_PURGE procedure

This procedure schedules a job to purge pushed transactions from the deferred transaction queue at your current master or snapshot site. You should schedule one purge job.

Syntax

DBMS_DEFER_SYS.SCHEDULE_PURGE (
   interval             IN  VARCHAR2,
   next_date            IN  DATE,
   reset                IN  BOOLEAN        := NULL,
   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);

Parameters

Table 8-45 SCHEDULE_PURGE Procedure Parameters
Parameter  Description 
interval
 

Allows you to provide a function to calculate the next time to purge. This value is stored in the interval field of the DEFSCHEDULE view and calculates the next_date field of this view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If the field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, you must supply a value for next_date.  

next_date
 

Allows you to specify a given time to purge pushed transactions from the site's queue. This value is stored in the next_date field of the DEFSCHEDULE view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If this field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for interval.  

reset
 

Set to TRUE to reset LAST_TXN_COUNT, LAST_ERROR, and LAST_MSG to NULL.  

purge_method
 

Controls how to purge the deferred transaction queue; purge_method_quick costs less, while purge_method_precise offers better precision.  

rollback_segment
 

Name of rollback segment to use for the purge, or NULL for default.  

startup_seconds
 

Maximum number of seconds to wait for a previous purge of the same deferred transaction queue.  

execution_seconds
 

If >0, then stop purge cleanly after the specified number of seconds of real time.  

delay_seconds
 

Stop purge cleanly after the deferred transaction queue has no transactions to purge for delay_seconds.  

transaction_count
 

If > 0, then shutdown cleanly after purging transaction_count number of transactions.  

write_trace
 

When set to TRUE, Oracle records the result value returned by the PURGE function in the server's trace file.  

SCHEDULE_PUSH procedure

This procedure schedules a job to push the deferred transaction queue to a remote master destination. This procedure does a COMMIT.

Syntax

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);

Parameters

Table 8-46 SCHEDULE_PUSH Procedure Parameters
Parameter  Description 
destination
 

The fully qualified database name of the master to which you are forwarding changes.  

interval
 

Allows you to provide a function to calculate the next time to push. This value is stored in the interval field of the DEFSCHEDULE view and calculates the next_date field of this view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If the field had no previous value, it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for next_date.  

next_date
 

Allows you to specify a given time to push deferred transactions to the master site destination. This value is stored in the next_date field of the DEFSCHEDULE view. If you use the default value for this parameter, NULL, then the value of this field remains unchanged. If this field had no previous value, then it is created with a value of NULL. If you do not supply a value for this field, then you must supply a value for interval.  

reset
 

Set to TRUE to reset LAST_TXN_COUNT, LST_ERROR, and LAST_MSG to NULL.  

parallelism
 

0 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set the parameter unless so directed by Oracle Worldwide Support.  

stop_on_error
 

The default, FALSE, indicates that the executor should continue even if errors, such as conflicts, are encountered. If TRUE, then shutdown (cleanly if possible) at the first indication that a transaction encountered an error at the destination site.  

write_trace
 

When set to TRUE, Oracle records the result value returned by the function in the server's trace file.  

startup_seconds
 

Maximum number of seconds to wait for a previous push to the same destination.  

execution_seconds
 

If >0, then stop execution cleanly after the specified number of seconds of real time. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue.  

delay_seconds
 

Do not return before the specified number of seconds have elapsed, even if the queue is empty. Useful for reducing execution overhead if PUSH is called from a tight loop.  

transaction_count
 

If > 0, then the maximum number of transactions to be pushed before stopping. If transaction_count and execution_seconds are zero (the default), then transactions are executed until there are no more in the queue that need to be pushed.  

SET_DISABLED procedure

To disable or enable propagation of the deferred transaction queue from the current site to a given destination site. If the disabled parameter is TRUE, then the procedure disables propagation to the given destination and future invocations of PUSH do not push the deferred remote procedure call (RPC) queue. SET_DISABLED eventually affects a session already pushing the queue to the given destination, but does not affect sessions appending to the queue with DBMS_DEFER.

If the disabled parameter is FALSE, then the procedure enables propagation to the given destination and, although this does not push the queue, it permits future invocations to PUSH to push the queue to the given destination. Whether the disabled parameter is TRUE or FALSE, a COMMIT is required for the setting to take effect in other sessions.

Syntax

DBMS_DEFER_SYS.SET_DISABLED (
   destination   IN   VARCHAR2,
   disabled      IN   BOOLEAN := TRUE);

Parameters

Table 8-47 SET_DISABLED Procedure Parameters
Parameter  Description 
destination
 

The fully qualified database name of the node whose propagation status you want to change.  

disabled
 

By default, this parameter disables propagation of the deferred transaction queue from your current site to the given destination. Set this to FALSE to enable propagation.  

Exceptions

Table 8-48 SET_DISABLED Procedure Exceptions
Exception  Description 
NO_DATA_FOUND
 

No entry was found in the DEFSCHEDULE view for the given destination.  

UNREGISTER_PROPAGATOR procedure

To unregister a user as the propagator from the local database. This procedure

Syntax

DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR (
   username  IN  VARCHAR2
   timeout   IN  INTEGER DEFAULT DBMS_LOCK.MAXWAIT);

Parameters

Table 8-49 UNREGISTER_PROPAGATOR Procedure Parameters
Parameter  Description 
username
 

Name of the propagator user.  

timeout
 

Timeout in seconds. If the propagator is in use, then the procedure waits until timeout. The default is DBMS_LOCK.MAXWAIT.  

Exceptions

Table 8-50 UNREGISTER_PROPAGATOR Procedure Exceptions
Parameter  Description 
missingpropagator
 

Given user is not a propagator.  

propagator_inuse
 

Propagator is in use, and thus cannot be unregistered. Try later.  

UNSCHEDULE_PURGE procedure

This procedure stops automatic purges of pushed transactions from the deferred transaction queue at a snapshot or master site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PURGE;

Parameters

None

UNSCHEDULE_PUSH procedure

This procedure stops automatic pushes of the deferred transaction queue from a snapshot or master site to another master site.

Syntax

DBMS_DEFER_SYS.UNSCHEDULE_PUSH (
   dblink   IN   VARCHAR2);

Parameters

Table 8-51 UNSCHEDULE_PUSH Procedure Parameters
Parameter  Description 
dblink
 

Fully qualified pathname to master database site at which you want to unschedule periodic execution of deferred remote procedure calls.  

Table 8-52 UNSCHEDULE_PUSH Procedure Exceptions
Exception  Description 
NO_DATA_FOUND
 

No entry was found in the DEFSCHEDULE view for the given dblink.  

DBMS_OFFLINE_OG Package

Summary of Subprograms

Table 8-53 DBMS_OFFLINE_OG Package Subprograms
Subprogram  Description 
BEGIN_INSTANTIATION 
procedure
 

Starts offline instantiation of a replicated master group.  

BEGIN_LOAD procedure
 

Disables triggers while data is imported to new master site as part of offline instantiation.  

END_INSTANTIATION 
procedure
 

Completes offline instantiation of a replicated master group.  

END_LOAD procedure
 

Re-enables triggers after importing data to new master site as part of offline instantiation.  

RESUME_SUBSET_OF_
MASTERS procedure
 

Resumes replication activity at all existing sites except the new site during offline instantiation of a replicated master group.  

BEGIN_INSTANTIATION procedure

This procedure starts offline instantiation of a replicated master group. You must call this procedure from the master definition site.



Note:

This procedure is used in performing an offline instantiation of a master table in a multimaster replication environment.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_OG.BEGIN_INSTANTIATION (
   gname     IN   VARCHAR2,
   new_site  IN   VARCHAR2
   fname     IN   VARCHAR2);

Parameters

Table 8-54 BEGIN_INSTANTIATION Procedure Parameters
Parameter  Description 
gname
 

Name of the object group that you want to replicate to the new site.  

new_site
 

The fully qualified database name of the new site to which you want to replicate the object group.  

fname
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-55 BEGIN_INSTANTIATION Procedure Exceptions
Exception  Description 
badargument
 

NULL or empty string for object group or new master site name.  

dbms_repcat. 
nonmasterdef
 

This procedure must be called from the master definition site.  

sitealreadyexists
 

Given site is already a master site for this object group.  

wrongstate
 

Status of master definition site must be QUIESCED.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

dbms_repcat.missing_
flavor
 

If you receive this exception, contact Oracle Worldwide Support.  

BEGIN_LOAD procedure

This procedure disables triggers while data is imported to new master site as part of offline instantiation. You must call this procedure from the new master site.



Note:

This procedure is used in performing an offline instantiation of a master table in a multimaster replication environment.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_OG.BEGIN_LOAD (
   gname     IN   VARCHAR2,
   new_site  IN   VARCHAR2);

Parameters

Table 8-56 BEGIN_LOAD Procedure Parameters
Parameter  Description 
gname
 

Name of the object group whose members you are importing.  

new_site
 

The fully qualified database name of the new site at which you will be importing the object group members.  

Exceptions

Table 8-57 BEGIN_LOAD Procedure Exceptions
Exception  Description 
badargument
 

Null or empty string for object group or new master site name.  

wrongsite
 

This procedure must be called from the new master site.  

unknownsite
 

Given site is not recognized by object group.  

wrongstate
 

Status of the new master site must be QUIESCED.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

END_INSTANTIATION procedure

This procedure completes offline instantiation of a replicated master group. You must call this procedure from the master definition site.



Note:

This procedure is used in performing an offline instantiation of a master table in a multimaster replication environment.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_OG.END_INSTANTIATION (
  gname     IN  VARCHAR2,
  new_site  IN  VARCHAR2);

Parameters

Table 8-58 END_INSTANTIATION Procedure Parameters
Parameter  Description 
gname
 

Name of the object group that you are replicating to the new site.  

new_site
 

The fully qualified database name of the new site to which you are replicating the object group.  

Exceptions

Table 8-59 END_INSTANTIATION Procedure Exceptions
Exception  Description 
badargument
 

Null or empty string for object group or new master site name.  

dbms_repcat. 
nonmasterdef
 

This procedure must be called from the master definition site.  

unknownsite
 

Given site is not recognized by object group.  

wrongstate
 

Status of master definition site must be QUIESCED.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

END_LOAD procedure

This procedure re-enables triggers after importing data to new master site as part of offline instantiation. You must call this procedure from the new master site.



Note:

This procedure is used in performing an offline instantiation of a master table in a multimaster replication environment.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_OG.END_LOAD (
   gname     IN   VARCHAR2,
   new_site  IN   VARCHAR2
   fname     IN   VARCHAR2);

Parameters

Table 8-60 END_LOAD Procedure Parameters
Parameter  Description 
gname
 

Name of the object group whose members you have finished importing.  

new_site
 

The fully qualified database name of the new site at which you have imported the object group members.  

fname
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-61 END_LOAD Procedure Exceptions
Exception  Description 
badargument
 

NULL or empty string for object group or new master site name.  

wrongsite
 

This procedure must be called from the new master site.  

unknownsite
 

Given site is not recognized by object group.  

wrongstate
 

Status of the new master site must be QUIESCED.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

dbms_repcat.flavor_
noobject
 

If you receive this exception, contact Oracle Worldwide Support.  

dbms_repcat.flavor_
contains
 

If you receive this exception, contact Oracle Worldwide Support.  

RESUME_SUBSET_OF_MASTERS procedure

This procedure resumes replication activity at all existing sites except the new site during offline instantiation of a replicated master group. You must call this procedure from the master definition site.



Note:

This procedure is used in performing an offline instantiation of a master table in a multimaster replication environment.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (
   gname     IN  VARCHAR2,
   new_site  IN  VARCHAR2
   override  IN  BOOLEAN := FALSE);

Parameters

Table 8-62 RESUME_SUBSET_OF_MASTERS Procedure Parameters
Parameter  Description 
gname
 

Name of the object group that you are replicating to the new site.  

new_site
 

The fully qualified database name of the new site to which you are replicating the object group.  

override
 

If this is TRUE, then it ignores any pending RepCat administration requests and restores normal replication activity at each master as quickly as possible. This should be considered only in emergency situations.

If this is FALSE, then it restores normal replication activity at each master only when there is no pending RepCat administration request for gname at that master.  

Exceptions

Table 8-63 RESUME_SUBSET_OF_MASTERS Procedure Exceptions
Exception  Description 
badargument
 

NULL or empty string for object group or new master site name.  

dbms_repcat. 
nonmasterdef
 

This procedure must be called from the master definition site.  

unknownsite
 

Given site is not recognized by object group.  

wrongstate
 

Status of master definition site must be QUIESCED.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

DBMS_OFFLINE_SNAPSHOT Package

Summary of Subprograms

Table 8-64 DBMS_OFFLINE_SNAPSHOT Package Subprograms
Subprogram  Description 
BEGIN_LOAD procedure
 

Prepares a snapshot site for import of a new snapshot as part of offline instantiation.  

END_LOAD procedure
 

Completes offline instantiation of a snapshot.  

BEGIN_LOAD procedure

This procedure prepares a snapshot site for import of a new snapshot as part of offline instantiation. You must call this procedure from the snapshot site for the new snapshot.



Note:

This procedure is used in performing an offline instantiation of a snapshot.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (
   gname               IN   VARCHAR2,
   sname               IN   VARCHAR2,
   master_site         IN   VARCHAR2,
   snapshot_oname      IN   VARCHAR2,
   storage_c           IN   VARCHAR2 := '',
   comment             IN   VARCHAR2 := '',
   min_communication   IN   BOOLEAN  := TRUE);

Parameters

Table 8-65 BEGIN_LOAD Procedure Parameters
Parameter  Description 
gname
 

Name of the object group for the snapshot that you are creating using offline instantiation.  

sname
 

Name of the schema for the new snapshot.  

master_site
 

Fully qualified database name of the snapshot's master site.  

snapshot_oname
 

Name of the temporary snapshot created at the master site.  

storage_c
 

Storage options to use when creating the new snapshot at the snapshot site.  

comment
 

User comment.  

min_communication
 

If TRUE, then the update trigger sends the new value of a column only if the update statement modifies the column. The update trigger sends the old value of the column only if it is a key column or a column in a modified column group.  

Exceptions

Table 8-66 BEGIN_LOAD Procedure Exceptions
Exception  Description 
badargument
 

Null or empty string for object group, schema, master site, or snapshot name.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

missingremotesnap
 

Could not locate given snapshot at given master site.  

dbms_repcat. 
missingschema
 

Given schema does not exist.  

snaptabmismatch
 

Base table name of the snapshot at the master and snapshot do not match.  

END_LOAD procedure

This procedure completes offline instantiation of a snapshot. You must call this procedure from the snapshot site for the new snapshot.



Note:

This procedure is used in performing an offline instantiation of a snapshot.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table) or with the procedures in the DBMS_REPCAT_INSTANTIATE package (used for instantiating a deployment template). See these respective packages for more information on their usage.  


Syntax

DBMS_OFFLINE_SNAPSHOT.END_LOAD (
   gname           IN  VARCHAR2,
   sname           IN  VARCHAR2,
   snapshot_oname  IN  VARCHAR2);

Parameters

Table 8-67 END_LOAD Procedure Parameters
Parameter  Description 
gname
 

Name of the object group for the snapshot that you are creating using offline instantiation.  

sname
 

Name of the schema for the new snapshot.  

snapshot_oname
 

Name of the snapshot.  

Exceptions

Table 8-68 END_LOAD Procedure Exceptions
Exception  Description 
badargument
 

NULL or empty string for object group, schema, or snapshot name.  

dbms_repcat. 
missingrepgroup
 

gname does not exist as a replicated master group.  

dbms_repcat. 
nonsnapshot
 

This procedure must be called from the snapshot site.  

DBMS_RECTIFIER_DIFF Package

Summary of Subprograms

Table 8-69 DBMS_RECTIFIER_DIFF Package Subprograms
Subprogram  Description 
DIFFERENCES 
procedure
 

Determines the differences between two tables.  

RECTIFY procedure
 

Resolves the differences between two tables.  

DIFFERENCES procedure

This procedure determines the differences between two tables.

Syntax

DBMS_RECTIFIER_DIFF.DIFFERENCES (
   sname1               IN  VARCHAR2,
   oname1               IN  VARCHAR2,
   reference_site       IN  VARCHAR2 := '',
   sname2               IN  VARCHAR2,
   oname2               IN  VARCHAR2,
   comparison_site      IN  VARCHAR2 := '',
   where_clause         IN  VARCHAR2 := '',
   { column_list        IN  VARCHAR2 := '', 
   | array_columns      IN  dbms_utility.name_array, }
   missing_rows_sname   IN  VARCHAR2,
   missing_rows_oname1  IN  VARCHAR2,
   missing_rows_oname2  IN  VARCHAR2,
   missing_rows_site    IN  VARCHAR2 := '',
   max_missing          IN  INTEGER,
   commit_rows          IN  INTEGER := 500);


Note:

This procedure is overloaded. The column_list and array_columns parameters are mutually exclusive.  


Parameters

Table 8-70 DIFFERENCES Procedure Parameters
Parameter  Description 
sname1
 

Name of the schema at REFERENCE_SITE.  

oname1
 

Name of the table at REFERENCE_SITE.  

reference_site
 

Name of the reference database site. The default, NULL, indicates the current site.  

sname2
 

Name of the schema at COMPARISON_SITE.  

oname2
 

Name of the table at COMPARISON_SITE.  

comparison_site
 

Name of the comparison database site. The default, NULL, indicates the current site.  

where_clause
 

Only rows satisfying this restriction are selected for comparison. The default, NULL, indicates the current site.  

column_list
 

A comma-separated list of one or more column names being compared for the two tables. You must not have any white space before or after the comma. The default, NULL, indicates that all columns be compared.  

array_columns
 

A PL/SQL table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be NULL. If position 1 is NULL, then all columns are used.  

missing_rows_sname
 

Name of the schema containing the tables with the missing rows.  

missing_rows_oname1
 

Name of the table at MISSING_ROWS_SITE that stores information about the rows in the table at REFERENCE site missing from the table at COMPARISON site and the rows at COMPARISON site missing from the table at REFERENCE site.  

missing_rows_oname2
 

Name of the table at MISSING_ROWS_SITE that stores about the missing rows. This table has three columns: the rowid of the row in the MISSING_ROWS_ONAME1 table, the name of the site at which the row is present, and the name of the site from which the row is absent.  

missing_rows_site
 

Name of the site where the MISSING_ROWS_ONAME1 and MISSING_ROWS_ONAME2 tables are located. The default, NULL, indicates that the tables are located at the current site.  

max_missing
 

Integer that refers to the maximum number of rows that should be inserted into the missing_rows_oname table. If more than max_missing number of rows is missing, then that many rows are inserted into missing_rows_oname, and the routine then returns normally without determining whether more rows are missing; this argument is useful in the cases that the fragments are so different that the missing rows table will have too many entries and there's no point in continuing. Raises exception badnumber if max_missing is less than 1 or NULL.  

commit_rows
 

Maximum number of rows to insert to or delete from the reference or comparison table before a COMMIT occurs. By default, a COMMIT occurs after 500 inserts or 500 deletes. An empty string (' ') or NULL indicates that a COMMIT should only be issued after all rows for a single table have been inserted or deleted.  

Exceptions

Table 8-71 DIFFERENCES Procedure Exceptions
Exception  Description 
nosuchsite
 

Database site could not be found.  

badnumber
 

COMMIT_ROWS parameter less than 1.  

missingprimarykey
 

Column list must include primary key (or SET_COLUMNS equivalent).  

badname
 

NULL or empty string for table or schema name.  

cannotbenull
 

Parameter cannot be NULL.  

notshapeequivalent
 

Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes.  

unknowncolumn
 

Column does not exist.  

unsupportedtype
 

Type not supported.  

dbms_repcat. 
commfailure
 

Remote site is inaccessible.  

dbms_repcat. 
missingobject
 

Table does not exist.  

Restrictions

The error ORA-00001 (Unique constraint violated) is issued when there are any unique or primary key constraints on the MISSING_ROWS_DATA table.

RECTIFY procedure

This procedure resolves the differences between two tables.

Syntax

DBMS_RECTIFIER_DIFF.RECTIFY (
   sname1               IN  VARCHAR2,
   oname1               IN  VARCHAR2,
   reference_site       IN  VARCHAR2 := '',
   sname2               IN  VARCHAR2,
   oname2               IN  VARCHAR2,
   comparison_site      IN  VARCHAR2 := '',
   { column_list        IN  VARCHAR2 := '', 
   | array_columns      IN  dbms_utility.name_array, }
   missing_rows_sname   IN  VARCHAR2,
   missing_rows_oname1  IN  VARCHAR2,
   missing_rows_oname2  IN  VARCHAR2,
   missing_rows_site    IN  VARCHAR2 := '',
   commit_rows          IN  INTEGER := 500);


Note:

This procedure is overloaded. The column_list and array_columns parameters are mutually exclusive.  


Parameters

Table 8-72 RECTIFY Procedure Parameters
Parameter  Description 
sname1
 

Name of the schema at REFERENCE_SITE.  

oname1
 

Name of the table at REFERENCE_SITE.  

reference_site
 

Name of the reference database site. The default, NULL, indicates the current site.  

sname2
 

Name of the schema at COMPARISON_SITE.  

oname2
 

Name of the table at COMPARISON_SITE.  

comparison_site
 

Name of the comparison database site. The default, NULL, indicates the current site.  

column_list
 

A comma-separated list of one or more column names being compared for the two tables. You must not have any white space before or after the comma. The default, NULL, indicates that all columns be compared.  

array_columns
 

A PL/SQL table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be NULL. If position 1 is NULL, then all columns are used.  

missing_rows_sname
 

Name of the schema containing the tables with the missing rows.  

missing_rows_oname1
 

Name of the table at MISSING_ROWS_SITE that stores information about the rows in the table at REFERENCE site missing from the table at COMPARISON site and the rows at COMPARISON site missing from the table at REFERENCE site.  

missing_rows_oname2
 

Name of the table at MISSING_ROWS_SITE that stores about the missing rows. This table has three columns: the rowid of the row in the MISSING_ROWS_ONAME1 table, the name of the site at which the row is present, and the name of the site from which the row is absent.  

missing_rows_site
 

Name of the site where the MISSING_ROWS_ONAME1 and MISSING_ROWS_ONAME2 tables are located. The default, NULL, indicates that the tables are located at the current site.  

commit_rows
 

Maximum number of rows to insert to or delete from the reference or comparison table before a COMMIT occurs. By default, a COMMIT occurs after 500 inserts or 500 deletes. An empty string (' ') or NULL indicates that a COMMIT should only be issued after all rows for a single table have been inserted or deleted.  

Exceptions

Table 8-73 RECTIFY Procedure Exceptions
Exception  Description 
nosuchsite
 

Database site could not be found.  

badnumber
 

COMMIT_ROWS parameter less than 1.  

badname
 

NULL or empty string for table or schema name.  

dbms_repcat. 
commfailure
 

Remote site is inaccessible.  

dbms_repcat. 
missingobject
 

Table does not exist.  

DBMS_REFRESH Package

Summary of Subprograms

Table 8-74 DBMS_REFRESH Package Subprograms
Subprogram  Description 
ADD procedure
 

Adds snapshots to a refresh group.  

CHANGE procedure
 

Changes the refresh interval for a snapshot group.  

DESTROY procedure
 

Removes all of the snapshots from a refresh group and deletes the refresh group.  

MAKE procedure
 

Specifies the members of a refresh group and the time interval used to determine when the members of this group should be refreshed.  

REFRESH procedure
 

Manually refreshes a refresh group.  

SUBTRACT procedure
 

Removes snapshots from a refresh group.  

ADD procedure

This procedure adds snapshots to a refresh group.

For additional information, see "ADD OBJECTS TO REFRESH GROUP". Also see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.ADD (
   name     IN VARCHAR2,
   { list   IN VARCHAR2, 
   | tab    IN DBMS_UTILITY.UNCL_ARRAY, }
   lax      IN BOOLEAN := FALSE);


Note:

This procedure is overloaded. The list and tab parameters are mutually exclusive.  


Parameters

Table 8-75 ADD Procedures Parameters
Parameter  Description 
name
 

Name of the refresh group to which you want to add members.  

list
 

Comma-separated list of snapshots that you want to add to the refresh group. (Synonyms are not supported.)  

tab
 

Instead of a comma-separated list, you can supply a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a snapshot. The first snapshot should be in position 1. The last position must be NULL.  

lax
 

A snapshot can belong to only one refresh group at a time. If you are moving a snapshot from one group to another, then you must set the lax flag to TRUE to succeed. Oracle then automatically removes the snapshot from the other refresh group and updates its refresh interval to be that of its new group. Otherwise, the call to ADD generates an error message.  

CHANGE procedure

This procedure changes the refresh interval for a snapshot group.

For additional information, see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.CHANGE (
   name                  IN VARCHAR2,
   next_date             IN DATE           := NULL,
   interval              IN VARCHAR2       := NULL,
   implicit_destroy      IN BOOLEAN        := NULL,
   rollback_seg          IN VARCHAR2       := NULL,
   push_deferred_rpc     IN BOOLEAN        := NULL,
   refresh_after_errors  IN BOOLEAN        := NULL,
   purge_option          IN BINARY_INTEGER := NULL,
   parallelism           IN BINARY_INTEGER := NULL,
   heap_size             IN BINARY_INTEGER := NULL);

Parameters

Table 8-76 CHANGE Procedures Parameters
Parameter  Description 
name
 

Name of the refresh group for which you want to alter the refresh interval.  

next_date
 

Next date that you want a refresh to occur. By default, this date remains unchanged.  

interval
 

Function used to calculate the next time to refresh the snapshots in the group. This interval is evaluated immediately before the refresh. Thus, you should select an interval that is greater than the time it takes to perform a refresh. By default, the interval remains unchanged.  

implicit_destroy
 

Allows you to reset the value of the implicit_destroy flag. If this flag is set, then Oracle automatically deletes the group if it no longer contains any members. By default, this flag remains unchanged.  

rollback_seg
 

Allows you to change the rollback segment used. By default, the rollback segment remains unchanged. To reset this parameter to use the default rollback segment, specify NULL, including the quotes. Specifying NULL without quotes indicates that you do not want to change the rollback segment currently being used.  

push_deferred_rpc
 

Used by updatable snapshots only. Set this parameter to TRUE if you want to push changes from the snapshot to its associated master before refreshing the snapshot. Otherwise, these changes may appear to be temporarily lost. By default, this flag remains unchanged  

refresh_after_
errors
 

Used by updatable snapshots only. Set this parameter to TRUE if you want the refresh to proceed even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master. By default, this flag remains unchanged.  

purge_option
 

If you are using the parallel propagation mechanism (in other words, parallelism is set to 1 or greater), then 0 = don't purge; 1 = lazy (default); 2 = aggressive. In most cases, lazy purge is the optimal setting. Set purge to aggressive to trim back the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, then set purge to don't purge and occasionally execute PUSH with purge set to aggressive to reduce the queue.  

parallelism
 

0 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set the parameter unless so directed by Oracle Worldwide Support.  

DESTROY procedure

This procedure removes all of the snapshots from a refresh group and delete the refresh group.

For additional information, see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.DESTROY (
   name   IN   VARCHAR2);

Parameters

Table 8-77 DESTROY Procedure Parameters
Parameter  Description 
name
 

Name of the refresh group that you want to destroy.  

MAKE procedure

This procedure specifies the members of a refresh group and the time interval used to determine when the members of this group should be refreshed.

For additional information, see "CREATE REFRESH GROUP". Also see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.MAKE (
   name                   IN    VARCHAR2
   { list                 IN    VARCHAR2,
   | tab                IN     DBMS_UTILITY.UNCL_ARRAY,}
   next_date              IN     DATE,
   interval               IN     VARCHAR2,
   implicit_destroy     IN     BOOLEAN         := FALSE,
   lax                  IN     BOOLEAN         := FALSE,
   job                  IN     BINARY INTEGER  := 0,
   rollback_seg         IN     VARCHAR2        := NULL,
   push_deferred_rpc    IN     BOOLEAN         := TRUE,
   refresh_after_errors IN     BOOLEAN         := FALSE)
   purge_option            IN     BINARY_INTEGER := NULL,
   parallelism             IN     BINARY_INTEGER := NULL,
   heap_size               IN     BINARY_INTEGER := NULL);


Note:

This procedure is overloaded. The list and tab parameters are mutually exclusive.  


Table 8-78 MAKE Procedure Parameters
Parameter  Description 
name
 

Unique name used to identify the refresh group. Refresh groups must follow the same naming conventions as tables.  

list
 

Comma-separated list of snapshots that you want to refresh. (Synonyms are not supported.) These snapshots can be located in different schemas and have different master tables; however, all of the listed snapshots must be in your current database.  

tab
 

Instead of a comma separated list, you can supply a PL/SQL table of names of snapshots that you want to refresh using the datatype DBMS_UTILITY.UNCL_ARRAY. If the table contains the names of N snapshots, then the first snapshot should be in position 1 and the N + 1 position should be set to NULL.  

next_date
 

Next date that you want a refresh to occur.  

interval
 

Function used to calculate the next time to refresh the snapshots in the group. This field is used with the NEXT_DATE value.

For example, if you specify NEXT_DAY(SYSDATE+1, "MONDAY") as your interval, and if your NEXT_DATE evaluates to Monday, then Oracle refreshes the snapshots every Monday. This interval is evaluated immediately before the refresh. Thus, you should select an interval that is greater than the time it takes to perform a refresh.  

implicit_destroy
 

Set this to TRUE if you want to delete the refresh group automatically when it no longer contains any members. Oracle checks this flag only when you call the SUBTRACT procedure. That is, setting this flag still allows you to create an empty refresh group.  

lax
 

A snapshot can belong to only one refresh group at a time. If you are moving a snapshot from an existing group to a new refresh group, then you must set this to TRUE to succeed. Oracle then automatically removes the snapshot from the other refresh group and updates its refresh interval to be that of its new group. Otherwise, the call to MAKE generates an error message.  

job
 

Needed by the Import utility. Use the default value, 0.  

rollback_seg
 

Name of the rollback segment to use while refreshing snapshots. The default, NULL, uses the default rollback segment.  

push_deferred_rpc
 

Used by updatable snapshots only. Use the default value, TRUE, if you want to push changes from the snapshot to its associated master before refreshing the snapshot. Otherwise, these changes may appear to be temporarily lost.  

refresh_after_errors
 

Used by updatable snapshots only. Set this to 0 if you want the refresh to proceed even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master.  

purge_option
 

If you are using the parallel propagation mechanism (in other words, parallelism is set to 1 or greater), then 0 = don't purge; 1 = lazy (default); 2 = aggressive. In most cases, lazy purge is the optimal setting.

Set purge to aggressive to trim back the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, then set purge to don't purge and occasionally execute PUSH with purge set to aggressive to reduce the queue.  

parallelism
 

0 = serial propagation; n > 0 = parallel propagation with n parallel server processes; 1 = parallel propagation using only one parallel server process.  

heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set this unless so directed by Oracle Worldwide Support.  

REFRESH procedure

This procedure manually refreshes a refresh group.

For additional information, see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.REFRESH (
   name   IN    VARCHAR2);
Table 8-79 REFRESH Procedure Parameters
Parameter  Description 
name
 

Name of the refresh group that you want to refresh manually.  

SUBTRACT procedure

This procedure removes snapshots from a refresh group.

For additional information, see "Snapshot Concepts & Architecture" in the Oracle8i Replication manual.

Syntax

DBMS_REFRESH.SUBTRACT (
   name       IN    VARCHAR2,
   { list     IN    VARCHAR2,
   | tab     IN    DBMS_UTILITY.UNCL_ARRAY, }
   lax       IN    BOOLEAN := FALSE);


Note:

This procedure is overloaded. The list and tab parameters are mutually exclusive.  


Parameters

Table 8-80 SUBTRACT Procedure Parameters
Parameter  Description 
name
 

Name of the refresh group from which you want to remove members.  

list
 

Comma-separated list of snapshots that you want to remove from the refresh group. (Synonyms are not supported.) These snapshots can be located in different schemas and have different master tables; however, all of the listed snapshots must be in your current database.  

tab
 

Instead of a comma-separated list, you can supply a PL/SQL table of names of snapshots that you want to refresh using the datatype DBMS_UTILITY.UNCL_ARRAY. If the table contains the names of N snapshots, then the first snapshot should be in position 1 and the N+1 position should be set to NULL.  

lax
 

Set this to FALSE if you want Oracle to generate an error message if the snapshot you are attempting to remove is not a member of the refresh group.  

DBMS_REPCAT Package

Summary of Subprograms

Table 8-81 DBMS_REPCAT Package Subprograms
Subprogram  Description 
ADD_GROUPED_COLUMN 
procedure
 

Adds members to an existing column group.  

ADD_MASTER_DATABASE 
procedure
 

Adds another master site to your replicated environment.  

ADD_PRIORITY_datatype 
procedure
 

Adds a member to a priority group.  

ADD_SITE_PRIORITY_SITE 
procedure
 

Adds a new site to a site priority group.  

ADD_conflicttype_RESOLUTION 
procedure
 

Designates a method for resolving an update, delete, or uniqueness conflict.  

ALTER_MASTER_PROPAGATION 
procedure
 

Alters the propagation method for a given object group at a given master site.  

ALTER_MASTER_REPOBJECT 
procedure
 

Alters an object in your replicated environment.  

ALTER_PRIORITY procedure
 

Alters the priority level associated with a given priority group member.  

ALTER_PRIORITY_datatype 
procedure
 

Alters the value of a member in a priority group.  

ALTER_SITE_PRIORITY 
procedure
 

Alters the priority level associated with a given site.  

ALTER_SITE_PRIORITY_SITE 
procedure
 

Alters the site associated with a given priority level.  

ALTER_SNAPSHOT_PROPAGATION 
procedure
 

Alters the propagation method for a given object group at the current snapshot site.  

CANCEL_STATISTICS procedure
 

Stops collecting statistics about the successful resolution of update, uniqueness, and delete conflicts for a table.  

COMMENT_ON_COLUMN_GROUP 
procedure
 

Updates the comment field in the RepColumn_Group view for a column group.  

COMMENT_ON_PRIORITY_
GROUP/COMMENT_ON_SITE_
PRIORITY procedure
 

Updates the comment field in the REPPRIORITY_GROUP view for a (site) priority group.  

COMMENT_ON_REPGROUP 
procedure
 

Updates the comment field in the REPGROUP view for a replicated master group.  

COMMENT_ON_REPSITES 
procedure
 

Updates the comment field in the RepSite view for a replicated site.  

COMMENT_ON_REPOBJECT 
procedure
 

Updates the comment field in the RepObject view for a replicated object.  

COMMENT_ON_conflicttype_
RESOLUTION procedure
 

Updates the comment field in the RepResolution view for a conflict resolution routine.  

CREATE_MASTER_REPGROUP 
procedure
 

Creates a new, empty, quiesced master replication object group.  

CREATE_MASTER_REPOBJECT 
procedure
 

Indicates that an object is a replicated object.  

CREATE_SNAPSHOT_REPGROUP 
procedure
 

Creates a new, empty snapshot replication object group in your local database.  

CREATE_SNAPSHOT_REPOBJECT 
procedure
 

Adds a replicated object to your snapshot site.  

DEFINE_COLUMN_GROUP 
procedure
 

Creates an empty column group  

DEFINE_PRIORITY_GROUP 
procedure
 

Creates a new priority group for a replicated master group.  

DEFINE_SITE_PRIORITY 
procedure
 

Creates a new site priority group for a replicated master group.  

DO_DEFERRED_REPCAT_ADMIN 
procedure
 

Executes the local outstanding deferred administrative procedures for the given replicated master group at the current master site, or for all master sites.  

DROP_COLUMN_GROUP procedure
 

Drops a column group.  

DROP_GROUPED_COLUMN 
procedure
 

Removes members from a column group.  

DROP_MASTER_REPGROUP 
procedure
 

Drops a replicated master group from your current site.  

DROP_MASTER_REPOBJECT 
procedure
 

Drops a replicated object from a replicated master group.  

DROP_PRIORITY procedure
 

Drops a member of a priority group by priority level.  

DROP_PRIORITY_GROUP 
procedure
 

Drops a priority group for a given replicated master group.  

DROP_PRIORITY_datatype 
procedure
 

Drops a member of a priority group by value.  

DROP_SITE_PRIORITY 
procedure
 

Drops a site priority group for a given replicated master group.  

DROP_SITE_PRIORITY_SITE 
procedure
 

Drops a given site, by name, from a site priority group.  

DROP_SNAPSHOT_REPGROUP 
procedure
 

Drops a snapshot site from your replicated environment.  

DROP_SNAPSHOT_REPOBJECT 
procedure
 

Drops a replicated object from a snapshot site.  

DROP_conflicttype_
RESOLUTION procedure
 

Drops an update, delete, or uniqueness conflict resolution routine.  

EXECUTE_DDL procedure
 

Supplies DDL that you want to have executed at each master site.  

GENERATE_REPLICATION_
SUPPORT procedure
 

Generates the triggers, packages, and procedures needed to support replication.  

GENERATE_SNAPSHOT_SUPPORT 
procedure
 

Activates triggers and generate packages needed to support the replication of updatable snapshots or procedural replication.  

MAKE_COLUMN_GROUP procedure
 

Creates a new column group with one or more members.  

PURGE_MASTER_LOG procedure
 

Removes local messages in the RepCatLog associated with a given identification number, source, or replicated master group.  

PURGE_STATISTICS procedure
 

Removes information from the RepResolution_Statistics view.  

REFRESH_SNAPSHOT_REPGROUP 
procedure
 

Refreshes a snapshot site object group with the most recent data from its associated master site.  

REGISTER_SNAPSHOT_REPGROUP 
procedure
 

Facilitates the administration of snapshots at their respective master sites by inserting/modifying/deleting from repcat_repsite.  

REGISTER_STATISTICS 
procedure
 

Collects information about the successful resolution of update, delete and uniqueness conflicts for a table.  

RELOCATE_MASTERDEF 
procedure
 

Changes your master definition site to another master site in your replicated environment.  

REMOVE_MASTER_DATABASES 
procedure
 

Removes one or more master databases from a replicated environment.  

REPCAT_IMPORT_CHECK 
procedure
 

Ensures that the objects in the replicated master group have the appropriate object identifiers and status values after you perform an export/import of a replicated object or an object used by the advanced replication facility.  

RESUME_MASTER_ACTIVITY 
procedure
 

Resumes normal replication activity after quiescing a replicated environment.  

SUSPEND_MASTER_ACTIVITY 
procedure
 

Suspends replication activity for an object group  

SWITCH_SNAPSHOT_MASTER 
procedure
 

Changes the master database of a snapshot replicated master group to another master site.  

UNREGISTER_SNAPSHOT_
REPGROUP procedure
 

Facilitates the administration of snapshots at their respective master sites by inserting/modifying/deleting from repcat$_repsite.  

VALIDATE function
 

Validates the correctness of key conditions of a multiple master replication environment.  

WAIT_MASTER_LOG procedure
 

Determines whether changes that were asynchronously propagated to a master site have been applied.  

ADD_GROUPED_COLUMN procedure

This procedure adds members to an existing column group. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.ADD_GROUPED_COLUMN ( 
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   column_group          IN   VARCHAR2,
   list_of_column_names  IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S);

Parameters

Table 8-82 ADD_GROUPED_COLUMN Procedure Parameters
Parameter  Description 
sname
 

Schema in which the replicated table is located.  

oname
 

Name of the replicated table with which the column group is associated.  

column_group
 

Name of the column group to which you are adding members.  

list_of_column_names
 

Names of the columns that you are adding to the designated column group. This can either be a comma-separated list or a PL/SQL table of column names. The PL/SQL table must be of type dbms_repcat.varchar2s. Use the single value '*' to create a column group that contains all of the columns in your table.  

Table 8-83 ADD_GROUPED_COLUMN Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingobject
 

Given table does not exist.  

missinggroup
 

Given column group does not exist.  

missingcolumn
 

Given column does not exist in the designated table.  

duplicatecolumn
 

Given column is already a member of another column group.  

missingschema
 

Given schema does not exist.  

notquiesced
 

Object group that the given table belongs to is not quiesced.  

ADD_MASTER_DATABASE procedure

This procedure adds another master site to your replicated environment. This procedure regenerates all the triggers and their associated packages at existing master sites. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.ADD_MASTER_DATABASE (
   gname                 IN   VARCHAR2,
   master                IN   VARCHAR2, 
   use_existing_objects  IN   BOOLEAN  := TRUE, 
   copy_rows             IN   BOOLEAN  := TRUE,
   comment               IN   VARCHAR2 := '',
   propagation_mode      IN   VARCHAR2 := 'ASYNCHRONOUS',
   fname                 IN   VARCHAR2 := NULL);

Parameters

Table 8-84 ADD_MASTER_DATABASE Procedure Parameters
Parameter  Description 
gname
 

Name of the object group being replicated. This object group must already exist at the master definition site.  

master
 

Fully qualified database name of the new master database.  

use_existing_objects
 

Indicate TRUE if you want to reuse any objects of the same type and shape that already exist in the schema at the new master site. See "Using Multimaster Replication" in the Oracle8i Replication manual for more information on how these changes are applied.  

copy_rows
 

Indicate TRUE if you want the initial contents of a table at the new master site to match the contents of the table at the master definition site.  

comment
 

This is added to the MASTER_COMMENT field of the RepSites view.  

propagation_mode
 

Method of forwarding changes to and receiving changes from new master database. Accepted values are SYNCHRONOUS and ASYNCHRONOUS.  

fname
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-85 ADD_MASTER_DATABASE Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

notquiesced
 

replicated master group has not been suspended.  

missingrepgroup
 

Object group does not exist at the given database site.  

commfailure
 

New master is not accessible.  

typefailure
 

An incorrect propagation mode was specified.  

notcompat
 

Compatibility mode must be 7.3.0.0 or greater.  

duplrepgrp
 

Master site already exists.  

ADD_PRIORITY_datatype procedure

This procedure adds a member to a priority group. You must call this procedure from the master definition site. The procedure that you must call is determined by the datatype of your priority column. You must call this procedure once for each of the possible values of the priority column.

For additional information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ADD_PRIORITY_datatype (
   gname              IN   VARCHAR2, 
   pgroup             IN   VARCHAR2, 
   value              IN   datatype, 
   priority           IN   NUMBER);

where datatype:

{ NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| NCHAR
| NVARCHAR2 }

Parameters

Table 8-86 ADD_PRIORITY_datatype Procedure Parameters
Parameter  Description 
gname
 

replicated master group for which you are creating a priority group.  

pgroup
 

Name of the priority group.  

value
 

Value of the priority group member. This would be one of the possible values of the associated priority column of a table using this priority group.  

priority
 

Priority of this value. The higher the number, the higher the priority.  

Exceptions

Table 8-87 ADD_PRIORITY_datatype Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

duplicatevalue
 

Given value already exists in the priority group.  

duplicatepriority
 

Given priority already exists in the priority group.  

missingrepgroup
 

Given replicated master group does not exist.  

missingprioritygroup
 

Given priority group does not exist.  

typefailure
 

Given value has the incorrect datatype for the priority group.  

notquiesced
 

Given replicated master group is not quiesced.  

ADD_SITE_PRIORITY_SITE procedure

This procedure adds a new site to a site priority group. You must call this procedure from the master definition site.

For additional information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
   gname          IN   VARCHAR2, 
   name           IN   VARCHAR2
   site           IN   VARCHAR2,
   priority       IN   NUMBER);

Parameters

Table 8-88 ADD_SITE_PRIORITY_SITE Procedure Parameters
Parameter  Description 
gname
 

replicated master group for which you are adding a site to a group.  

name
 

Name of the site priority group to which you are adding a member.  

site
 

Global database name of the site that you are adding.  

priority
 

Priority level of the site that you are adding. A higher number indicates a higher priority level.  

Exceptions

Table 8-89 ADD_SITE_PRIORITY_SITE Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingpriority
 

Given site priority group does not exist.  

duplicatepriority
 

Given priority level already exists for another site in the group.  

duplicatevalue
 

Given site already exists in the site priority group.  

notquiesced
 

replicated master group is not quiesced.  

ADD_conflicttype_RESOLUTION procedure

This procedure designates a method for resolving an update, delete, or uniqueness conflict. You must call these procedures from the master definition site. The procedure that you need to call is determined by the type of conflict that the routine resolves.

Conflict Type  Procedure Name 
update
 
ADD_UPDATE_RESOLUTION
 
uniqueness
 
ADD_UNIQUE_RESOLUTION
 
delete
 
ADD_DELETE_RESOLUTION
 

For more information about designating methods to resolve update conflicts, selecting uniqueness conflict resolution methods, and, assigning delete conflict resolution methods see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
   sname                  IN   VARCHAR2, 
   oname                  IN   VARCHAR2, 
   column_group           IN   VARCHAR2,
   sequence_no            IN   NUMBER,
   method                 IN   VARCHAR2, 
   parameter_column_name  IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S, 
   priority_group         IN   VARCHAR2     := NULL,
   function_name          IN   VARCHAR2     := NULL,
   comment                IN   VARCHAR2     := NULL);

DBMS_REPCAT.ADD_DELETE_RESOLUTION (
   sname                  IN   VARCHAR2, 
   oname                  IN   VARCHAR2, 
   sequence_no            IN   NUMBER,
   parameter_column_name  IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S, 
   function_name          IN   VARCHAR2,
   comment                IN   VARCHAR2     := NULL
   method                 IN   VARCHAR2     := 'USER FUNCTION');

DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
   sname                 IN   VARCHAR2, 
   oname                 IN   VARCHAR2, 
   constraint_name       IN   VARCHAR2,
   sequence_no           IN   NUMBER,
   method                IN   VARCHAR2, 
   parameter_column_name IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S, 
   function_name         IN   VARCHAR2     := NULL,
   comment               IN   VARCHAR2     := NULL);

Parameters

Table 8-90 ADD_conflicttype_RESOLUTION Procedure Parameters
Parameter  Description 
sname
 

Name of the schema containing the table to be replicated.  

oname
 

Name of the table for which you are adding a conflict resolution routine.  

column_group
 

Name of the column group for which you are adding a conflict resolution routine. Column groups are required for update conflict resolution routines only.  

constraint_name
 

Name of the unique constraint or unique index for which you are adding a conflict resolution routine. Use the name of the unique index if it differs from the name of the associated unique constraint. Constraint names are required for uniqueness conflict resolution routines only.  

sequence_no
 

Order in which the designated conflict resolution methods should be applied.  

method
 

Type of conflict resolution routine that you want to create. This can be the name of one of the standard routines provided with advanced replication, or, if you have written your own routine, you should choose USER FUNCTION, and provide the name of your routine as the FUNCTION_NAME argument. The methods supported in this release are: MINIMUM, MAXIMUM, LATEST TIMESTAMP, EARLIEST TIMESTAMP, ADDITIVE, AVERAGE, PRIORITY GROUP, SITE PRIORITY, OVERWRITE, and DISCARD (for update conflicts) and APPEND SITE NAME, APPEND SEQUENCE NUMBER, and DISCARD (for uniqueness conflicts). There are no standard methods for delete conflicts.  

parameter_column_
name
 

Name of the columns used to resolve the conflict. The standard methods operate on a single column. For example, if you are using the LATEST TIMESTAMP method for a column group, then you should pass the name of the column containing the timestamp value as this argument. If your are using a USER FUNCTION, then you can resolve the conflict using any number of columns.

This argument accepts either a comma separated list of column names, or a PL/SQL table of type dbms_repcat.varchar2s. The single value '*' indicates that you want to use all of the columns in the table (or column group, for update conflicts) to resolve the conflict. If you specify '*', then the columns are passed to your function in alphabetical order.  

priority_group
 

If you are using the PRIORITY GROUP or SITE PRIORITY update conflict resolution method, then you must supply the name of the priority group that you have created.

See "Conflict Resolution" in the Oracle8i Replication manual. If you are using a different method, you can use the default value for this argument, NULL. This argument is applicable to update conflicts only.  

function_name
 

If you selected the USER FUNCTION method, or if you are adding a delete conflict resolution routine, then you must supply the name of the conflict resolution routine that you have written. If you are using one of the standard methods, then you can use the default value for this argument, NULL.  

comment
 

This user comment is added to the RepResolution view.  

Exceptions

Table 8-91 ADD_conflicttype_RESOLUTION Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingobject
 

Given object does not exist as a table in the given schema using row-level replication.  

missingschema
 

Given schema does not exist.  

missingcolumn
 

Column that you specified as part of the PARAMETER_COLUMN_NAME argument does not exist.  

missinggroup
 

Given column group does not exist.  

missingprioritygroup
 

priority group that you specified does not exist for the table.  

invalidmethod
 

Resolution method that you specified is not recognized.  

invalidparameter
 

Number of columns that you specified for the PARAMETER_COLUMN_NAME argument is invalid. (The standard routines take only one column name.)  

missingfunction
 

User function that you specified does not exist.  

missingconstraint
 

Constraint that you specified for a uniqueness conflict does not exist.  

notquiesced
 

Object group that the given table belongs to is not quiesced.  

duplicateresolution
 

Given conflict resolution method is already registered.  

paramtype
 

Type is different from the type assigned to the priority group.  

ALTER_MASTER_PROPAGATION procedure

This procedure alters the propagation method for a given object group at a given master site. This object group must be quiesced. You must call this procedure from the master definition site. If the master appears in the dblink_list or dblink_table, then ALTER_MASTER_PROPAGATION ignores that database link. You cannot change the propagation mode from a master to itself.

Syntax

DBMS_REPCAT.ALTER_MASTER_PROPAGATION (
   gname               IN   VARCHAR2, 
   master              IN   VARCHAR2,
   { dblink_list       IN   VARCHAR2, 
   | dblink_table      IN   dbms_utility.dblink_array,}
   propagation_mode    IN   VARCHAR2 : ='asynchronous',
   comment             IN   VARCHAR2 := '');


Note:

This procedure is overloaded. The dblink_list and dblink_table parameters are mutually exclusive.  


Parameters

Table 8-92 ALTER_MASTER_PROPAGATION Procedure Parameters
Parameter  Description 
gname
 

Name of the object group to which to alter the propagation mode.  

master
 

Name of the master site at which to alter the propagation mode.  

dblink_list
 

A comma-separated list of database links for which to alter propagation. If NULL, then all masters except the master site being altered are used by default.  

dblink_table
 

A PL/SQL table, indexed from position 1, of database links for which to alter propagation.  

propagation_mode
 

Determines the manner in which changes from the given master site are propagated to the sites identified by the list of database links. Appropriate values are SYNCHRONOUS and ASYNCHRONOUS.  

comment
 

This comment is added to the RepProp view.  

Exceptions

Table 8-93 ALTER_MASTER_PROPAGATION Procedure Exceptions
Exception  Description 
nonmasterdef
 

Local site is not the master definition site.  

notquiesced
 

Local site is not quiesced.  

typefailure
 

Propagation mode specified was not recognized.  

nonmaster
 

List of database links includes a site that is not a master site.  

ALTER_MASTER_REPOBJECT procedure

This procedure alters an object in your replicated environment. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
   sname        IN   VARCHAR2, 
   oname        IN   VARCHAR2, 
   type         IN   VARCHAR2, 
   ddl_text     IN   VARCHAR2, 
   comment      IN   VARCHAR2     := '', 
   retry        IN   BOOLEAN      := FALSE);

Parameters

Table 8-94 ALTER_MASTER_REPOBJECT Procedure Parameters
Parameter  Description 
sname
 

Schema containing the object that you want to alter.  

oname
 

Name of the object that you want to alter.  

type
 

Type of the object that you are altering. The types supported are: TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY.  

ddl_text
 

The DDL text that you want used to alter the object. Oracle does not parse this DDL before applying it; therefore, you must ensure that your DDL text provides the appropriate schema and object name for the object being altered.  

comment
 

If not NULL, then this comment is added to the COMMENT field of the RepObject view.  

retry
 

If retry is TRUE, then ALTER_MASTER_REPOBJECT alters the object only at masters whose object status is not VALID.  

Exceptions

Table 8-95 ALTER_MASTER_REPOBJECT Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

notquiesced
 

Associated object group has not been suspended.  

missingobject
 

Object identified by SNAME and ONAME does not exist.  

typefailure
 

Given type parameter is not supported.  

ddlfailure
 

DDL at the master definition site did not succeed.  

commfailure
 

At least one master site is not accessible.  

Usage Notes

If the DDL is supplied without specifying a schema, then the default schema is the replication administrator's schema. Be sure to specify the schema if it is other than the replication administrator's schema.

ALTER_PRIORITY procedure

This procedure alters the priority level associated with a given priority group member. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ALTER_PRIORITY (
   gname            IN   VARCHAR2, 
   pgroup           IN   VARCHAR2, 
   old_priority     IN   NUMBER, 
   new_priority     IN   NUMBER);

Parameters

Table 8-96 ALTER_PRIORITY Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the priority group is associated.  

pgroup
 

Name of the priority group containing the priority that you want to alter.  

old_priority
 

Current priority level of the priority group member.  

new_priority
 

New priority level that you want assigned to the priority group member.  

Exceptions

Table 8-97 ALTER_PRIORITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

duplicatepriority
 

New priority level already exists in the priority group.  

missingrepgroup
 

Given replicated master group does not exist.  

missingvalue
 

Value was not registered by a call to DBMS_REPCAT.ADD_PRIORITY_datatype.  

missingprioritygroup
 

Given priority group does not exist.  

notquiesced
 

Given replicated master group is not quiesced.  

ALTER_PRIORITY_datatype procedure

This procedure alters the value of a member in a priority group. You must call this procedure from the master definition site. The procedure that you must call is determined by the datatype of your priority column.

For additional information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ALTER_PRIORITY_datatype (
   gname        IN   VARCHAR2, 
   pgroup       IN   VARCHAR2, 
   old_value    IN   datatype, 
   new_value    IN   datatype);

where datatype:

{ NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| NCHAR
| NVARCHAR2 }

Parameters

Table 8-98 ALTER_PRIORITY_datatype Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the priority group is associated.  

pgroup
 

Name of the priority group containing the value that you want to alter.  

old_value
 

Current value of the priority group member.  

new_value
 

New value that you want assigned to the priority group member.  

Exceptions

Table 8-99 ALTER_PRIORITY_datatype Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

duplicatevalue
 

New value already exists in the priority group.  

missingrepgroup
 

Given replicated master group does not exist.  

missingprioritygroup
 

Given priority group does not exist.  

missingvalue
 

Old value does not exist.  

paramtype
 

New value has the incorrect datatype for the priority group.  

typefailure
 

Given value has the incorrect datatype for the priority group.  

notquiesced
 

Given replicated master group is not quiesced.  

ALTER_SITE_PRIORITY procedure

This procedure alters the priority level associated with a given site. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ALTER_SITE_PRIORITY (
   gname         IN   VARCHAR2,
   name          IN   VARCHAR2,
   old_priority  IN   NUMBER,
   new_priority  IN   NUMBER);

Parameters

Table 8-100 ALTER_SITE_PRIORITY Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the site priority group is associated.  

name
 

Name of the site priority group whose member you are altering.  

old_priority
 

Current priority level of the site whose priority level you want to change.  

new_priority
 

New priority level for the site. A higher number indicates a higher priority level.  

Exceptions

Table 8-101 ALTER_SITE_PRIORITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingpriority
 

Old priority level is not associated with any group members.  

duplicatepriority
 

New priority level already exists for another site in the group.  

missingvalue
 

Old value does not already exist.  

paramtype
 

New value has the incorrect datatype for the priority group.  

notquiesced
 

replicated master group is not quiesced.  

ALTER_SITE_PRIORITY_SITE procedure

This procedure alters the site associated with a given priority level. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE (
   gname     IN   VARCHAR2,
   name      IN   VARCHAR2,
   old_site  IN   VARCHAR2,
   new_site  IN   VARCHAR2);

Parameters

Table 8-102 ALTER_SITE_PRIORITY_SITE Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the site priority group is associated.  

name
 

Name of the site priority group whose member you are altering.  

old_site
 

Current global database name of the site to dissociate from the priority level.  

new_site
 

New global database name that you want to associate with the current priority level.  

Exceptions

Table 8-103 ALTER_SITE_PRIORITY_SITE Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingpriority
 

Given site priority group does not exist.  

missingvalue
 

Old site is not a group member.  

notquiesced
 

replicated master group is not quiesced  

ALTER_SNAPSHOT_PROPAGATION procedure

This procedure alters the propagation method for a given object group at the current snapshot site. This procedure pushes the deferred transaction queue at the snapshot site, locks the snapshot base tables, and regenerates any triggers and their associated packages. You must call this procedure from the snapshot site.

Syntax

DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION (
   gname                IN  VARCHAR2, 
   propagation_mode     IN  VARCHAR2,
   comment              IN  VARCHAR2   := '');

Parameters

Table 8-104 ALTER_SNAPSHOT_PROPAGATION Procedure Parameters
Parameter  Description 
gname
 

Name of the object group for which to alter propagation mode.  

propagation_mode
 

Manner in which changes from the current snapshot site are propagated to its associated master site. Appropriate values are SYNCHRONOUS and ASYNCHRONOUS.  

comment
 

This comment is added to the RepProp view.  

Exceptions

Table 8-105 ALTER_SNAPSHOT_PROPAGATION Procedure Exceptions
Exception  Description 
missingrepgroup
 

Given replicated master group does not exist.  

typefailure
 

Propagation mode was specified incorrectly.  

nonsnapshot
 

Current site is not a snapshot site for the given object group.  

commfailure
 

Cannot contact master.  

CANCEL_STATISTICS procedure

This procedure stops collecting statistics about the successful resolution of update, uniqueness, and delete conflicts for a table.

Syntax

DBMS_REPCAT.CANCEL_STATISTICS (
   sname    IN   VARCHAR2,
   oname    IN   VARCHAR2);

Parameters

Table 8-106 CANCEL_STATISTICS Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the table is located.  

oname
 

Name of the table for which you do not want to gather conflict resolution statistics.  

Exceptions

Table 8-107 CANCEL_STATISTICS Procedure Exceptions
Exception  Description 
missingschema
 

Given schema does not exist.  

missingobject
 

Given table does not exist.  

statnotreg
 

Given table is not currently registered to collect statistics.  

COMMENT_ON_COLUMN_GROUP procedure

This procedure updates the comment field in the RepColumn_Group view for a column group. This comment is not added at all master sites until the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.

Syntax

DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP (
   sname           IN   VARCHAR2, 
   oname           IN   VARCHAR2, 
   column_group    IN   VARCHAR2, 
   comment         IN   VARCHAR2);

Parameters

Table 8-108 COMMENT_ON_COLUMN_GROUP Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the object is located.  

oname
 

Name of the replicated table with which the column group is associated.  

column_group
 

Name of the column group.  

comment
 

Text of the updated comment that you want included in the GROUP_COMMENT field of the RepColumn_Group view.  

Exceptions

Table 8-109 COMMENT_ON_COLUMN_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missinggroup
 

Given column group does not exist.  

missingobj
 

Object is missing.  

COMMENT_ON_PRIORITY_GROUP/COMMENT_ON_SITE_PRIORITY procedure

COMMENT_ON_PRIORITY_GROUP updates the comment field in the REPPRIORITY_GROUP view for a priority group. This comment is not added at all master sites until the next call to GENERATE_REPLICATION_SUPPORT.

COMMENT_ON_SITE_PRIORITY updates the comment field in the REPPRIORITY_GROUP view for a site priority group. This procedure is a wrapper for the COMMENT_ON_COLUMN_GROUP procedure and is provided as a convenience only. This procedure must be issued at the master definition site.

Syntax

DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP (
   gname       IN   VARCHAR2, 
   pgroup      IN   VARCHAR2, 
   comment     IN   VARCHAR2);

DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY (
   gname       IN   VARCHAR2, 
   name        IN   VARCHAR2, 
   comment     IN   VARCHAR2);

Parameters

Table 8-110 COMMENT_ON_PRIORITY_GROUP/COMMENT_ON_SITE_PRIORITY Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

pgroup/name
 

Name of the priority or site priority group.  

comment
 

Text of the updated comment that you want included in the PRIORITY_COMMENT field of the RepPriority_Group view.  

Exceptions

Table 8-111 COMMENT_ON_PRIORITY_GROUP/COMMENT_ON_SITE_PRIORITY Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingprioritygroup
 

Given priority group does not exist.  

COMMENT_ON_REPGROUP procedure

This procedure updates the comment field in the REPGROUP view for a replicated master group. This procedure must be issued at the master definition site.

Syntax

DBMS_REPCAT.COMMENT_ON_REPGROUP (
   gname     IN   VARCHAR2,
   comment   IN   VARCHAR2);

Parameters

Table 8-112 COMMENT_ON_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the object group that you want to comment on.  

comment
 

Updated comment to include in the SCHEMA_COMMENT field of the RepGroup view.  

Exceptions

Table 8-113 COMMENT_ON_REPGROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

commfailure
 

At least one master site is not accessible.  

COMMENT_ON_REPSITES procedure

This procedure updates the comment field in the RepSite view for a replicated site. This procedure must be issued at the master definition site.

Syntax

DBMS_REPCAT.COMMENT_ON_REPSITES ( 
   gname       IN   VARCHAR2, 
   [ master    IN   VARCHAR,] 
   comment     IN   VARCHAR2);

Parameters

Table 8-114 COMMENT_ON_REPSITES Procedure Parameters
Parameter  Description 
gname
 

Name of the object group. This avoids confusion if a database is a master site in more than one replicated environment.  

master
 

(Optional) The fully qualified database name of the master site that you want to comment on. To update comments at a snapshot site, omit this parameter.  

comment
 

Text of the updated comment that you want to include in the MASTER_COMMENT field of the RepSites view.  

Exceptions

Table 8-115 COMMENT_ON_REPSITES Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

nonmaster
 

Invocation site is not a master site.  

commfailure
 

At least one master site is not accessible.  

COMMENT_ON_REPOBJECT procedure

This procedure updates the comment field in the RepObject view for a replicated object. This procedure must be issued at the master definition site.

Syntax

DBMS_REPCAT.COMMENT_ON_REPOBJECT (
   sname    IN   VARCHAR2, 
   oname    IN   VARCHAR2, 
   type     IN   VARCHAR2, 
   comment  IN   VARCHAR2);

Parameters

Table 8-116 COMMENT_ON_REPOBJECT Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the object is located.  

oname
 

Name of the object that you want to comment on.  

type
 

Type of the object.  

comment
 

Text of the updated comment that you want to include in the OBJECT_COMMENT field of the RepObject view.  

Exceptions

Table 8-117 COMMENT_ON_REPOBJECT Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist.  

typefailure
 

Given type parameter is not supported.  

commfailure
 

At least one master site is not accessible.  

COMMENT_ON_conflicttype_RESOLUTION procedure

This procedure updates the comment field in the RepResolution view for a conflict resolution routine. The procedure that you need to call is determined by the type of conflict that the routine resolves. These procedures must be issued at the master definition site.

Conflict Type  Procedure Name 

update  

COMMENT_ON_UPDATE_RESOLUTION
 

uniqueness  

COMMENT_ON_UNIQUE_RESOLUTION
 

delete  

COMMENT_ON_DELETE_RESOLUTION
 

The comment is not added at all master sites until the next call to GENERATE_REPLICATION_SUPPORT.

Syntax

DBMS_REPCAT.COMMENT_ON_UPDATE_RESOLUTION (
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   column_group          IN   VARCHAR2,
   sequence_no           IN   NUMBER, 
   comment               IN   VARCHAR2);

DBMS_REPCAT.COMMENT_ON_UNIQUE_RESOLUTION (
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   constraint_name       IN   VARCHAR2,
   sequence_no           IN   NUMBER, 
   comment               IN   VARCHAR2);

DBMS_REPCAT.COMMENT_ON_DELETE_RESOLUTION (
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   sequence_no           IN   NUMBER, 
   comment               IN   VARCHAR2);

Parameters

Table 8-118 COMMENT_ON_conflicttype_RESOLUTION Procedure Parameters
Parameter  Description 
sname
 

Name of the schema.  

oname
 

Name of the replicated table with which the conflict resolution routine is associated.  

column_group
 

Name of the column group with which the update conflict resolution routine is associated.  

constraint_name
 

Name of the unique constraint with which the uniqueness conflict resolution routine is associated.  

sequence_no
 

Sequence number of the conflict resolution procedure.  

comment
 

The text of the updated comment that you want included in the RESOLUTION_COMMENT field of the RepResolution view.  

Exceptions

Table 8-119 COMMENT_ON_conflicttype_RESOLUTION Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist.  

missingresolution
 

Specified conflict resolution routine is not registered.  

COMPARE_OLD_VALUES procedure

You have the option of comparing old column values for each non-key column of a replicated table for updates and deletes. The default is to compare old values for all columns. You can change this behavior at all master and snapshot sites by invoking DBMS_REPCAT.COMPARE_OLD_VALUES at the master definition site.

Syntax

DBMS_REPCAT.COMPARE_OLD_VALUES(
   sname           IN  VARCHAR2,
   oname           IN  VARCHAR2,
   { column_list   IN  VARCHAR2,
   | column_table  IN  DBMS_REPCAT.VARCHAR2s,}
   operation       IN  VARCHAR2 := `UPDATE',
   compare         IN  BOOLEAN := TRUE );


Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.  


Parameters

Table 8-120 COMPARE_OLD_VALUES Procedure Parameters
Parameter  Description 
sname
 

Schema in which the table is located.  

oname
 

Name of the replicated table.  

column_list
 

A comma-separated list of the columns in the table. There must be no white space between entries.  

column_table
 

Instead of a list, you can use a PL/SQL table of type DBMS_REPCAT.VARCHAR2S to contain the column names. The first column name should be at offset 1, the second at offset 2, and so on.  

operation
 

Possible values are: UPDATE, DELETE, or the asterisk wildcard '*', which means update and delete.  

compare
 

If compare is TRUE, the old values of the specified columns are compared when sent. If compare is FALSE, the old values of the specified columns are not compared when sent. Unspecified columns and unspecified operations are not affected. The specified change takes effect at the master definition site as soon as min_communication is TRUE for the table. The change takes effect at a master site or at a snapshot site the next time replication support is generated at that site with min_communication TRUE.  


Note:

The operation parameter allows you to decide whether or not to transmit old values for non-key columns when rows are deleted or when non-key columns are updated. If you do not send the old value, then Oracle sends a NULL in place of the old value and assumes the old value is equal to the current value of the column at the target side when the update or delete is applied.

Read "Minimizing Data Propagation for Update Conflict Resolution" in the Oracle8i Replication manual before changing the default behavior of Oracle.  


Exceptions

Table 8-121 COMPARE_OLD_VALUES Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist as a table in the given schema awaiting row-level replication information.  

missingcolumn
 

At least one column is not in the table.  

notquiesced
 

replicated master group has not been suspended.  

typefailure
 

An illegal operation is given.  

CREATE_MASTER_REPGROUP procedure

This procedure creates a new, empty, quiesced master replication object group.

Syntax

DBMS_REPCAT.CREATE_MASTER_REPGROUP (
   gname            IN   VARCHAR2, 
   group_comment    IN   VARCHAR2     := '', 
   master_comment   IN   VARCHAR2     := ''),
   qualifier        IN   VARCHAR2     := '');

Parameters

Table 8-122 CREATE_MASTER_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the object group that you want to create.  

group_comment
 

This comment is added to the RepGroup view.  

master_comment
 

This comment is added to the RepSites view.  

qualifier
 

Connection qualifier for object group. Be sure to use the @ sign, as shown in the example: See "Managing Master Groups" in the Oracle8i Replication manual.  

Exceptions

Table 8-123 CREATE_MASTER_REPGROUP Procedure Exceptions
Exception  Description 
duplicaterepgroup
 

Object group already exists.  

norepopt
 

Advanced replication option is not installed.  

missingrepgroup
 

Object group name was not specified.  

qualifiertoolong
 

Connection qualifier is too long.  

CREATE_MASTER_REPOBJECT procedure

This procedure indicates that an object is a replicated object.

Syntax

DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
   sname                IN   VARCHAR2,
   oname                IN   VARCHAR2, 
   type                 IN   VARCHAR2,
   use_existing_object  IN   BOOLEAN     := TRUE, 
   ddl_text             IN   VARCHAR2    := NULL, 
   comment              IN   VARCHAR2    := '', 
   retry                IN   BOOLEAN     := FALSE
   copy_rows            IN   BOOLEAN     := TRUE,
   gname                IN   VARCHAR2    := '');

Parameters

Table 8-124 CREATE_MASTER_REPOBJECT Procedure Parameters
Parameters  Description 
sname
 

Name of the schema in which the object that you want to replicate is located.  

oname
 

Name of the object you are replicating. If DDL_TEXT is NULL, then this object must already exist in the given schema. To ensure uniqueness, table names should be a maximum of 27 bytes long, and package names should be no more than 24 bytes.  

type
 

Type of the object that you are replicating. The types supported are: TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY.  

use_existing_object
 

Indicate TRUE if you want to reuse any objects of the same type and shape at the current master sites. See Table 8-126 for more information.  

ddl_text
 

If the object does not already exist at the master definition site, then you must supply the DDL text necessary to create this object. PL/SQL packages, package bodies, procedures, and functions must have a trailing semicolon. SQL statements do not end with trailing semicolon. Oracle does not parse this DDL before applying it; therefore, you must ensure that your DDL text provides the appropriate schema and object name for the object being created.  

comment
 

This comment is added to the OBJECT_COMMENT field of the RepObject view.  

retry
 

Indicate TRUE if you want Oracle to reattempt to create an object that it was previously unable to create. Use this if the error was transient or has since been rectified; for example, if you previously had insufficient resources. If this is TRUE, then Oracle creates the object only at master sites whose object status is not VALID.  

copy_rows
 

Indicate TRUE if you want the initial contents of a newly replicated object to match the contents of the object at the master definition site. See Table 8-126 for more information.  

gname
 

Name of the object group in which you want to create the replicated object. The schema name is used as the default object group name if none is specified.  

Table 8-125 CREATE_MASTER_REPOBJECT Procedure Exceptions
Exceptions  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

notquiesced
 

replicated master group has not been suspended.  

duplicateobject
 

Given object already exists in the replicated master group and retry is FALSE, or if a name conflict occurs.  

missingobject
 

Object identified by SNAME and ONAME does not exist and appropriate DDL has not been provided.  

typefailure
 

Objects of the given type cannot be replicated.  

ddlfailure
 

DDL at the master definition site did not succeed.  

commfailure
 

At least one master site is not accessible.  

notcompat
 

Not all remote masters in 7.3 compatibility mode.  

Object Creations

Table 8-126 Object Creation at Master Sites
Object Already Exists?  COPY_ROWS  USE_EXISTING_
OBJECTS
 
Result 

yes  

TRUE
 
TRUE
 

duplicatedobject message if objects do not match. For tables, use data from master definition site.  

yes  

FALSE
 
TRUE
 

duplicatedobject message if objects do not match. For tables, DBA must ensure contents are identical.  

yes  

TRUE/FALSE
 
FALSE
 

duplicatedobject message.  

no  

TRUE
 
TRUE/FALSE
 

Object is created. Tables populated using data from master definition site.  

no  

FALSE
 
TRUE/FALSE
 

Object is created. DBA must populate tables and ensure consistency of tables at all sites.  

Usage Notes

If the DDL is supplied without specifying a schema, then the default schema is the replication administrator's schema. Be sure to specify the schema if it is other than the replication administrator's schema.

CREATE_SNAPSHOT_REPGROUP procedure

This procedure creates a new, empty snapshot group in your local database.

Syntax

DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP (
   gname              IN   VARCHAR2, 
   master             IN   VARCHAR2, 
   comment            IN   VARCHAR2     := '',
   propagation_mode   IN   VARCHAR2     := 'ASYNCHRONOUS',
   fname              IN   VARCHAR2     := NULL);

Parameters

Table 8-127 CREATE_SNAPSHOT_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group. This object group must exist at the given master site.  

master
 

Fully qualified database name of the database in the replicated environment to use as the master.  

comment
 

This comment is added to the RepGroup view.  

propagation_mode
 

Method of propagation for all updatable snapshots in the object group. Acceptable values are SYNCHRONOUS and ASYNCHRONOUS.  

fname
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-128 CREATE_SNAPSHOT_REPGROUP Procedure Exceptions
Exception  Description 
duplicaterepgroup
 

Object group already exists at the invocation site.  

nonmaster
 

Given database is not a master site.  

commfailure
 

Given database is not accessible.  

norepopt
 

Advanced replication option is not installed.  

typefailure
 

Propagation mode was specified incorrectly.  

missingrepgroup
 

If replicated master group not at master site.  

Usage Notes

CREATE_SNAPSHOT_REPGROUP automatically calls REGISTER_SNAPSHOT_REPGROUP, but ignores any errors that may have happened during registration.

CREATE_SNAPSHOT_REPOBJECT procedure

This procedure adds a replicated object to your snapshot site.

Syntax

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
   sname                   IN   VARCHAR2,
   oname                   IN   VARCHAR2, 
   type                    IN   VARCHAR2, 
   ddl_text                IN   VARCHAR2  := '',
   comment                 IN   VARCHAR2  := '',
   gname                   IN   VARCHAR2  := '',
   gen_objs_owner          IN   VARCHAR2 := '',
   min_communication       IN   BOOLEAN  := TRUE ,
   generate_80_compatible  IN   BOOLEAN  := TRUE);

Parameters

Table 8-129 CREATE_SNAPSHOT_REPOBJECT Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the object is located.  

oname
 

Name of the object that you want to add to the replicated snapshot object group. ONAME must exist at the associated master site.  

type
 

Type of the object that you are replicating. The types supported for snapshot sites are: PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, SNAPSHOT, SYNONYM, TRIGGER, and VIEW.  

ddl_text
 

For objects of type SNAPSHOT, the DDL text needed to create the object; for other types, use the default, ('' (an empty string). If a snapshot with the same name already exists, then Oracle ignores the DDL and registers the existing snapshot as a replicated object. If the master table for a snapshot does not exist in the replicated master group of the master site designated for this schema, then Oracle raises a missingobject error.  

comment
 

This comment is added to the OBJECT_COMMENT field of the RepObject view.  

gname
 

Name of the replicated master group to which you are adding an object. The schema name is used as the default group name if none is specified.  

gen_objs_owner
 

Name of the user you want to assign as owner of the transaction.  

min_communication
 

Set to FALSE if any master site is running Oracle7 release 7.3. Set to TRUE to minimize new and old values of propagation. The default is TRUE. For more information, see "Conflict Resolution" in the Oracle8i Replication manual.  

generate_80_
compatible
 

Set to TRUE if any master site is running a version of Oracle Server prior to Oracle8i release 8.1.5. Set to FALSE if replicated environment is a pure Oracle8i release 8.1.5 or greater environment.  

Exceptions

Table 8-130 CREATE_SNAPSHOT_REPOBJECT Procedure Exceptions
Exception  Description 
nonsnapshot
 

Invocation site is not a snapshot site.  

nonmaster
 

Master is no longer a master site.  

missingobject
 

Given object does not exist in the master's replicated master group.  

duplicateobject
 

Given object already exists with a different shape.  

typefailure
 

Type is not an allowable type.  

ddlfailure
 

DDL did not succeed.  

commfailure
 

Master site is not accessible.  

missingschema
 

Schema does not exist as a database schema.  

badsnapddl
 

DDL was executed but snapshot does not exist.  

onlyonesnap
 

Only one snapshot for master table can be created.  

badsnapname
 

Snapshot base table differs from master table.  

missingrepgroup
 

replicated master group does not exist.  

Usage Notes

If the DDL is supplied without specifying a schema, then the default schema is the replication administrator's schema. Be sure to specify the schema if it is other than the replication administrator's schema.

DEFINE_COLUMN_GROUP procedure

This procedure creates an empty column group. You must call this procedure from the master definition site.

For more information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DEFINE_COLUMN_GROUP ( 
   sname            IN   VARCHAR2,
   oname            IN   VARCHAR2,
   column_group     IN   VARCHAR2,
   comment          IN   VARCHAR2 := NULL);

Parameters

Table 8-131 DEFINE_COLUMN_GROUP Procedure Parameters
Parameter  Description 
sname
 

Schema in which the replicated table is located.  

oname
 

Name of the replicated table for which you are creating a column group.  

column_group
 

Name of the column group that you want to create.  

comment
 

This user text is displayed in the RepColumn_Group view.  

Exceptions

Table 8-132 DEFINE_COLUMN_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingobject
 

Given table does not exist.  

duplicategroup
 

Given column group already exists for the table.  

notquiesced
 

Object group that the given table belongs to is not quiesced.  

DEFINE_PRIORITY_GROUP procedure

This procedure creates a new priority group for a replicated master group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DEFINE_PRIORITY_GROUP (
   gname         IN   VARCHAR2, 
   pgroup        IN   VARCHAR2, 
   datatype      IN   VARCHAR2, 
   fixed_length  IN   INTEGER := NULL,
   comment       IN   VARCHAR2 := NULL);

Parameters

Table 8-133 DEFINE_PRIORITY_GROUP Procedure Parameters
Parameter  Description 
gname
 

replicated master group for which you are creating a priority group.  

pgroup
 

Name of the priority group that you are creating.  

datatype
 

Datatype of the priority group members. The datatypes supported are: CHAR, VARCHAR2, NUMBER, DATE, RAW, NCHAR, and NVARCHAR2.  

fixed_length
 

You must provide a column length for the CHAR datatype. All other types can use the default, NULL.  

comment
 

This user comment is added to the RepPriority view.  

Exceptions

Table 8-134 DEFINE_PRIORITY_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

duplicatepriority 
group
 

Given priority group already exists in the replicated master group.  

typefailure
 

Given datatype is not supported.  

notquiesced
 

replicated master group is not quiesced.  

DEFINE_SITE_PRIORITY procedure

This procedure creates a new site priority group for a replicated master group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DEFINE_SITE_PRIORITY (
   gname        IN   VARCHAR2, 
   name         IN   VARCHAR2,
   comment      IN   VARCHAR2 := NULL);

Parameters

Table 8-135 DEFINE_SITE_PRIORITY Procedure Parameters
Parameter  Description 
gname
 

The replicated master group for which you are creating a site priority group.  

name
 

Name of the site priority group that you are creating.  

comment
 

This user comment is added to the RepPriority view.  

Exceptions

Table 8-136 DEFINE_SITE_PRIORITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

duplicate 
prioritygroup
 

Given site priority group already exists in the replicated master group.  

notquiesced
 

replicated master group is not quiesced.  

DO_DEFERRED_REPCAT_ADMIN procedure

This procedure executes the local outstanding deferred administrative procedures for the given replicated master group at the current master site, or (with assistance from job queues) for all master sites.

Syntax

DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN (
   gname          IN   VARCHAR2,
   all_sites      IN   BOOLEAN := FALSE);

Parameters

Table 8-137 DO_DEFERRED_REPCAT_ADMIN Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

all_sites
 

If this is TRUE, then use a job to execute the local administrative procedures at each master.  

Exceptions

Table 8-138 DO_DEFERRED_REPCAT_ADMIN Procedure Exceptions
Exception  Description 
nonmaster
 

Invocation site is not a master site.  

commfailure
 

At least one master site is not accessible and all_sites is TRUE.  

Usage Notes

DO_DEFERRED_REPCAT_ADMIN executes only those administrative requests submitted by the connected user that called DO_DEFERRED_REPCAT_ADMIN. Requests submitted by other users are ignored.

DROP_COLUMN_GROUP procedure

This procedure drops a column group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_COLUMN_GROUP (
   sname        IN   VARCHAR2,
   oname        IN   VARCHAR2,
   column_group IN   VARCHAR2);

Parameters

Table 8-139 DROP_COLUMN_GROUP Procedure Parameters
Parameter  Description 
sname
 

Schema in which the replicated table is located.  

oname
 

Name of the replicated table whose column group you are dropping.  

column_group
 

Name of the column group that you want to drop.  

Exceptions

Table 8-140 DROP_COLUMN_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

referenced
 

Given column group is being used in conflict detection and resolution.  

missingobject
 

Given table does not exist.  

missinggroup
 

Given column group does not exist.  

notquiesced
 

replicated master group that the table belongs to is not quiesced.  

DROP_GROUPED_COLUMN procedure

This procedure removes members from a column group. You must call this procedure from the master definition site.

For more information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_GROUPED_COLUMN (
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   column_group          IN   VARCHAR2,
   list_of_column_names  IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S);

Parameters

Table 8-141 DROP_GROUPED_COLUMN Procedure Parameters
Parameter  Description 
sname
 

Schema in which the replicated table is located.  

oname
 

Name of the replicated table in which the column group is located.  

column_group
 

Name of the column group from which you are removing members.  

list_of_column_names
 

Names of the columns that you are removing from the designated column group. This can either be a comma-separated list or a PL/SQL table of column names. The PL/SQL table must be of type dbms_repcat.varchar2s.  

Exceptions

Table 8-142 DROP_GROUPED_COLUMN Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingobject
 

Given table does not exist.  

notquiesced
 

replicated master group that the table belongs to is not quiesced.  

DROP_MASTER_REPGROUP procedure

This procedure drops a replicated master group from your current site. To drop the replicated master group from all master sites, including the master definition site, you can call this procedure at the master definition site, and set the final argument to TRUE.

Syntax

DBMS_REPCAT.DROP_MASTER_REPGROUP (
   gname             IN VARCHAR2,
   drop_contents     IN BOOLEAN    := FALSE,
   all_sites         IN BOOLEAN     := FALSE);

Parameters

Table 8-143 DROP_MASTER_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group that you want to drop from the current master site.  

drop_contents
 

By default, when you drop the object group at a master site, all of the objects remain in the database. They simply are no longer replicated; that is, the replicated objects in the object group no longer send changes to, or receive changes from, other master sites. If you set this to TRUE, then any replicated objects in the replicated master group are dropped from their associated schemas.  

all_sites
 

If this is TRUE and if the invocation site is the master definition site, then the procedure synchronously multicasts the request to all masters. In this case, execution is immediate at the master definition site and may be deferred at all other master sites.  

Exceptions

Table 8-144 DROP_MASTER_REPGROUP Procedure Exceptions
Exception  Description 
nonmaster
 

Invocation site is not a master site.  

nonmasterdef
 

Invocation site is not the master definition site and ALL_SITES is TRUE.  

commfailure
 

At least one master site is not accessible and ALL_SITES is TRUE.  

fullqueue
 

Deferred RPC queue has entries for the replicated master group.  

masternotremoved
 

Master does not recognize the masterdef and ALL_SITES is TRUE.  

DROP_MASTER_REPOBJECT procedure

This procedure drops a replicated object from a replicated master group. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.DROP_MASTER_REPOBJECT (
   sname          IN   VARCHAR2, 
   oname          IN   VARCHAR2, 
   type           IN   VARCHAR2, 
   drop_objects   IN   BOOLEAN     := FALSE);

Parameters

Table 8-145 DROP_MASTER_REPOBJECT Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the object is located.  

oname
 

Name of the object that you want to remove from the replicated master group.  

type
 

Type of object that you want to drop.  

drop_objects
 

By default, the object remains in the schema, but is dropped from the replicated master group; that is, any changes to the object are no longer replicated to other master and snapshot sites. To completely remove the object from the replicated environment, set this argument to TRUE.  

Exceptions

Table 8-146 DROP_MASTER_REPOBJECT Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist.  

typefailure
 

Given type parameter is not supported.  

commfailure
 

At least one master site is not accessible.  

DROP_PRIORITY procedure

This procedure drops a member of a priority group by priority level. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_PRIORITY(
      gname          IN   VARCHAR2,
      pgroup         IN   VARCHAR2, 
      priority_num   IN   NUMBER);

Parameters

Table 8-147 DROP_PRIORITY Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the priority group is associated.  

pgroup
 

Name of the priority group containing the member that you want to drop.  

priority_num
 

Priority level of the priority group member that you want to remove from the group.  

Exceptions

Table 8-148 DROP_PRIORITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingprioritygroup
 

Given priority group does not exist.  

notquiesced  

replicated master group is not quiesced.  

DROP_PRIORITY_GROUP procedure

This procedure drops a priority group for a given replicated master group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_PRIORITY_GROUP (
   gname     IN   VARCHAR2,
   pgroup    IN   VARCHAR2);

Parameters

Table 8-149 DROP_PRIORITY_GROUP Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the priority group is associated.  

pgroup
 

Name of the priority group that you want to drop.  

Exceptions

Table 8-150 DROP_PRIORITY_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

referenced
 

Given priority group is being used in conflict resolution.  

notquiesced
 

Given replicated master group is not quiesced.  

DROP_PRIORITY_datatype procedure

This procedure drops a member of a priority group by value. You must call this procedure from the master definition site. The procedure that you must call is determined by the datatype of your priority column.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_PRIORITY_datatype (
   gname    IN   VARCHAR2,
   pgroup   IN   VARCHAR2, 
   value    IN   datatype);

where datatype:

{ NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| NCHAR
| NVARCHAR2 }

Parameters

Table 8-151 DROP_PRIORITY_datatype Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the priority group is associated.  

pgroup
 

Name of the priority group containing the member that you want to drop.  

value
 

Value of the priority group member that you want to remove from the group.  

Exceptions

Table 8-152 DROP_PRIORITY_datatype Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingprioritygroup
 

Given priority group does not exist.  

paramtype, 
typefailure
 

Value has the incorrect datatype for the priority group.  

notquiesced
 

Given replicated master group is not quiesced  

DROP_SITE_PRIORITY procedure

This procedure drops a site priority group for a given replicated master group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_SITE_PRIORITY (
   gname      IN   VARCHAR2,
   name       IN   VARCHAR2);

Parameters

Table 8-153 DROP_SITE_PRIORITY Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the site priority group is associated.  

name
 

Name of the site priority group that you want to drop.  

Exceptions

Table 8-154 DROP_SITE_PRIORITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

referenced
 

Given site priority group is being used in conflict resolution.  

notquiesced
 

Given replicated master group is not quiesced  

DROP_SITE_PRIORITY_SITE procedure

This procedure drops a given site, by name, from a site priority group. You must call this procedure from the master definition site.

See "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.DROP_SITE_PRIORITY_SITE (
   gname      IN   VARCHAR2, 
   name       IN   VARCHAR2,
   site       IN   VARCHAR2);

Parameters

Table 8-155 DROP_SITE_PRIORITY_SITE Procedure Parameters
Parameter  Description 
gname
 

replicated master group with which the site priority group is associated.  

name
 

Name of the site priority group whose member you are dropping.  

site
 

Global database name of the site you are removing from the group.  

Exceptions

Table 8-156 DROP_SITE_PRIORITY_SITE Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingrepgroup
 

Given replicated master group does not exist.  

missingpriority
 

Given site priority group does not exist.  

notquiesced
 

Given replicated master group is not quiesced.  

DROP_SNAPSHOT_REPGROUP procedure

This procedure drops a snapshot site from your replicated environment.

Syntax

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP (
   gname                 IN   VARCHAR2, 
   drop_contents         IN   BOOLEAN   := FALSE);

Parameters

Table 8-157 DROP_SNAPSHOT_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group that you want to drop from the current snapshot site. All objects generated to support replication, such as triggers and packages, are dropped.  

drop_contents
 

By default, when you drop the replicated master group at a snapshot site, all of the objects remain in their associated schemas; they simply are no longer replicated. If you set this to TRUE, then any replicated objects in the replicated master group are dropped from their schemas.  

Exceptions

Table 8-158 DROP_SNAPSHOT_REPGROUP Procedure Exceptions
Exception  Description 
nonsnapshot
 

Invocation site is not a snapshot site.  

missingrepgroup
 

Specified object group does not exist.  

Usage Notes

DROP_SNAPSHOT_REPGROUP automatically calls UNREGISTER_SNAPSHOT_REPGROUP to unregister the snapshot, but ignores any errors that may have occurred during unregistration.

DROP_SNAPSHOT_REPOBJECT procedure

This procedure drops a replicated object from a snapshot site.

Syntax

DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT ( 
   sname          IN   VARCHAR2, 
   oname          IN   VARCHAR2, 
   type           IN   VARCHAR2, 
   drop_objects   IN   BOOLEAN  := FALSE);

Parameters

Table 8-159 DROP_SNAPSHOT_REPOBJECT Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the object is located.  

oname
 

Name of the object that you want to drop from the replicated master group.  

type
 

Type of the object that you want to drop.  

drop_objects
 

By default, the object remains in its associated schema, but is dropped from its associated object group. To completely remove the object from its schema at the current snapshot site, set this argument to TRUE.  

Exceptions

Table 8-160 DROP_SNAPSHOT_REPOBJECT Procedure Exceptions
Exception  Description 
nonsnapshot
 

Invocation site is not a snapshot site.  

missingobject
 

Given object does not exist.  

typefailure
 

Given type parameter is not supported.  

DROP_conflicttype_RESOLUTION procedure

This procedure drops an update, delete, or uniqueness conflict resolution routine. You must call these procedures from the master definition site. The procedure that you must call is determined by the type of conflict that the routine resolves.

Conflict Type  Procedure Name 

update  

DROP_UPDATE_RESOLUTION
 

uniqueness  

DROP_UNIQUE_RESOLUTION
 

delete  

DROP_DELETE_RESOLUTION
 

Syntax

DBMS_REPCAT.DROP_UPDATE_RESOLUTION (
   sname              IN   VARCHAR2, 
   oname              IN   VARCHAR2, 
   column_group       IN   VARCHAR2, 
   sequence_no        IN   NUMBER);

DBMS_REPCAT.DROP_DELETE_RESOLUTION (
   sname              IN   VARCHAR2, 
   oname              IN   VARCHAR2, 
   sequence_no        IN   NUMBER);

DBMS_REPCAT.DROP_UNIQUE_RESOLUTION (
   sname              IN   VARCHAR2, 
   oname              IN   VARCHAR2, 
   constraint_name    IN   VARCHAR2, 
   sequence_no        IN   NUMBER);

Parameters

Table 8-161 DROP_conflicttype_RESOLUTION Procedure Parameters
Parameter  Description 
sname
 

Schema in which the table is located.  

oname
 

Name of the table for which you want to drop a conflict resolution routine.  

column_group
 

Name of the column group for which you want to drop an update conflict resolution routine.  

constraint_name
 

Name of the Unique constraint for which you want to drop a unique conflict resolution routine.  

sequence_no
 

Sequence number assigned to the conflict resolution method that you want to drop. This number uniquely identifies the routine.  

Exceptions

Table 8-162 DROP_conflicttype_RESOLUTION Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

missingobject
 

Given object does not exist as a table in the given schema, or a conflict resolution routine with the given sequence number is not registered.  

notquiesced
 

replicated master group is not quiesced.  

EXECUTE_DDL procedure

This procedure supplies DDL that you want to have executed at some or all master sites. You can call this procedure only from the master definition site.

Syntax

DBMS_REPCAT.EXECUTE_DDL (
   gname           IN   VARCHAR2,
   { master_list   IN   VARCHAR2     := NULL, 
   | master_table  IN   DBMS_UTILITY.DBLINK_ARRAY,}
   DDL_TEXT        IN   VARCHAR2);

Parameters

Table 8-163 EXECUTE_DDL Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

master_list
 

A comma-separated list of master sites at which you want to execute the supplied DDL. There must be no extra white space between site names. The default value, NULL, indicates that the DDL should be executed at all sites, including the master definition site.  

master_table
 

A table of master sites at which you want to execute the supplied DDL. The first master should be at offset 1, the second at offset 2, and so on.  

ddl_text
 

The DDL that you want to have executed at each of the given master sites.  

Exceptions

Table 8-164 EXECUTE_DDL Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

nonmaster
 

At least one site is not a master site.  

ddlfailure
 

DDL at the master definition site did not succeed.  

commfailure
 

At least one master site is not accessible.  

Usage Notes

If the DDL is supplied without specifying a schema, then the default schema is the replication administrator's schema. Be sure to specify the schema if it is other than the replication administrator's schema. This procedure is overloaded. The MASTER_LIST and MASTER_TABLE parameters are mutually exclusive.

GENERATE_REPLICATION_SUPPORT procedure

This procedure generates the triggers and packages needed to support replication. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
  sname                     IN    VARCHAR2, 
  oname                     IN    VARCHAR2, 
  type                      IN    VARCHAR2, 
  package_prefix            IN    VARCHAR2   := NULL, 
  procedure_prefix          IN    VARCHAR2   := NULL, 
  distributed               IN    BOOLEAN    := TRUE,
  gen_objs_owner            IN    VARCHAR2   := NULL,
  min_communication         IN    BOOLEAN    := TRUE,
  generate_80_compatible    IN    BOOLEAN    := TRUE);

Parameters

Table 8-165 GENERATE_REPLICATION_SUPPORT Procedure Parameters
Parameter  Description 
sname
 

Schema in which the object is located.  

oname
 

Name of the object for which you are generating replication support.  

type
 

Type of the object. The types supported are: TABLE, PACKAGE, and PACKAGE BODY.  

package_prefix
 

For objects of type PACKAGE or PACKAGE BODY this value is prepended to the generated wrapper package name. The default is DEFER_.  

procedure_prefix
 

For objects of type PACKAGE or PACKAGE BODY, this value is prepended to the generated wrapper procedure names. By default, no prefix is assigned.  

distributed
 

This must be set to TRUE.  

gen_objs_owner
 

For objects of type PACKAGE or PACKAGE BODY, the schema in which the generated object should be created. If NULL, the objects will be created in SNAME.  

min_communication
 

Set to FALSE if any master site is running Oracle7 release 7.3. Set to TRUE when you want propagation of new and old values to be minimized. The default is TRUE. For more information, see "Conflict Resolution' in the Oracle8i Replication manual.  

generate_80_
compatible
 

Set to TRUE if any master site is running a version of Oracle Server prior to Oracle8i release 8.1.5. Set to FALSE if replicated environment is a pure Oracle8i release 8.1.5 or greater environment.  

Exceptions

Table 8-166 GENERATE_REPLICATION_SUPPORT Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist as a table in the given schema awaiting row-level replication information or as a package (body) awaiting wrapper generation.  

typefailure
 

Given type parameter is not supported.  

notquiesced
 

replicated master group has not been suspended.  

commfailure
 

At least one master site is not accessible.  

missingschema
 

Schema does not exist.  

dbnotcompatible
 

One of the masters is not 7.3 compatible.  

duplicateobject
 

Object already exists.  

GENERATE_SNAPSHOT_SUPPORT procedure

This procedure activates triggers and generate packages needed to support the replication of updatable snapshots or procedural replication.You must call this procedure from the snapshot site.

Syntax

DBMS_REPCAT.GENERATE_SNAPSHOT_SUPPORT (
   sname                    IN VARCHAR2,
   oname                    IN VARCHAR2,
   type                     IN VARCHAR2,
   gen_objs_owner           IN VARCHAR2 := '',
   min_communication        IN BOOLEAN  := TRUE,
   generate_80_compatible   IN BOOLEAN := TRUE);

Parameters

Table 8-167 GENERATE_SNAPSHOT_SUPPORT Procedure Parameters
Parameter  Description 
sname
 

Schema in which the object is located.  

oname
 

The name of the object that you are generating support for.  

type
 

Type of the object. The types supported are SNAPSHOT, PACKAGE, and PACKAGE BODY.  

gen_objs_owner
 

For objects of type PACKAGE or PACKAGE BODY, the schema in which the generated object should be created. If NULL, the objects will be created in SNAME.  

min_communication
 

If TRUE, then the update trigger sends the new value of a column only if the update statement modifies the column. The update trigger sends the old value of the column only if it is a key column or a column in a modified column group.  

generate_80_
compatible
 

Set to TRUE if any master site is running a version of Oracle Server prior to Oracle8i release 8.1.5. Set to FALSE if replicated environment is a pure Oracle8i release 8.1.5 or greater environment.  

Exceptions

Table 8-168 GENERATE_SNAPSHOT_SUPPORT Procedure Exceptions
Exceptions  Descriptions 
nonsnapshot
 

Invocation site is not a snapshot site.  

missingobject
 

Given object does not exist as a snapshot in the replicated schema awaiting row/column-level replication information or as a package (body) awaiting wrapper generation.  

typefailure
 

Given type parameter is not supported.  

missingschema
 

Specified owner of generated objects does not exist.  

missingremoteobject
 

Master object has not yet generated replication support.  

commfailure
 

Master is not accessible.  

Usage Notes

CREATE_SNAPSHOT_REPOBJECT automatically generates snapshot support for updatable snapshots.

MAKE_COLUMN_GROUP procedure

This procedure creates a new column group with one or more members. You must call this procedure from the master definition site.

For more information, see "Conflict Resolution" in the Oracle8i Replication manual.

Syntax

DBMS_REPCAT.MAKE_COLUMN_GROUP ( 
   sname                 IN   VARCHAR2,
   oname                 IN   VARCHAR2,
   column_group          IN   VARCHAR2,
   list_of_column_names  IN   VARCHAR2 | DBMS_REPCAT.VARCHAR2S);

Parameters

Table 8-169 MAKE_COLUMN_GROUP Procedure Parameters
Parameter  Description 
sname
 

Schema in which the replicated table is located.  

oname
 

Name of the replicated table for which you are creating a new column group.  

column_group
 

Name that you want assigned to the column group that you are creating.  

list_of_column_names
 

Names of the columns that you are grouping. This can either be a comma-separated list or a PL/SQL table of column names. The PL/SQL table must be of type dbms_repcat.varchar2s. Use the single value '*' to create a column group that contains all of the columns in your table.  

Exceptions

Table 8-170 MAKE_COLUMN_GROUP Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the masterdef site.  

duplicategroup
 

Given column group already exists for the table.  

missingobject
 

Given table does not exist.  

missingcolumn
 

Given column does not exist in the designated table.  

duplicatecolumn
 

Given column is already a member of another column group.  

notquiesced
 

replicated master group is not quiesced.  

PURGE_MASTER_LOG procedure

This procedure removes local messages in the RepCatLog associated with a given identification number, source, or replicated master group.

Syntax

DBMS_REPCAT.PURGE_MASTER_LOG (
   id      IN   NATURAL, 
   source  IN   VARCHAR2, 
   gname   IN   VARCHAR2);

Parameters

Table 8-171 PURGE_MASTER_LOG Procedure Parameters
Parameter  Description 
id
 

Identification number of the request, as it appears in the RepCatLog view.  

source
 

Master site from which the request originated.  

gname
 

Name of the replicated master group for which the request was made.  

Exceptions

Table 8-172 PURGE_MASTER_LOG Procedure Exceptions
Exception  Description 
nonmaster
 

gname is not NULL, and the invocation site is not a master site.  

PURGE_STATISTICS procedure

This procedure removes information from the RepResolution_Statistics view.

Syntax

DBMS_REPCAT.PURGE_STATISTICS (
   sname      IN   VARCHAR2, 
   oname      IN   VARCHAR2, 
   start_date IN   DATE,
   end_date   IN   DATE);

Parameters

Table 8-173 PURGE_STATISTICS Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the replicated table is located.  

oname
 

Name of the table whose conflict resolution statistics you want to purge.  

start_date/end_date
 

Range of dates for which you want to purge statistics. If START_DATE is NULL, then purge all statistics up to the END_DATE. If END_DATE is NULL, then purge all statistics after the START_DATE.  

Exceptions

Table 8-174 PURGE_STATISTICS Procedure Exceptions
Exception  Description 
missingschema
 

Given schema does not exist.  

missingobject
 

Given table does not exist.  

statnotreg
 

Table not registered to collect statistics.  

REFRESH_SNAPSHOT_REPGROUP procedure

This procedure refreshes a snapshot site object group with the most recent data from its associated master site.

Syntax

DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP (
   gname                  IN   VARCHAR2,
   drop_missing_contents  IN   BOOLEAN    := FALSE,
   refresh_snapshots      IN   BOOLEAN    := FALSE,
   refresh_other_objects  IN   BOOLEAN    := FALSE);

Parameters

Table 8-175 REFRESH_SNAPSHOT_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

drop_missing_
contents
 

If an object was dropped from the replicated master group, then it is not automatically dropped from the schema at the snapshot site. It is simply no longer replicated; that is, changes to this object are no longer sent to its associated master site. Snapshots can continue to be refreshed from their associated master tables; however, any changes to an updatable snapshot are lost. When an object is dropped from the object group, you can choose to have it dropped from the schema entirely by setting this argument to TRUE.  

refresh_snapshots
 

Set this to TRUE to refresh the contents of the snapshots in the replicated master group.  

refresh_other_
objects
 

Set this to TRUE to refresh the contents of the non-snapshot objects in the replicated master group.  

Exceptions

Table 8-176 REFRESH_SNAPSHOT_REPGROUP Procedure Exceptions
Exception  Description 
nonsnapshot
 

Invocation site is not a snapshot site.  

nonmaster
 

Master is no longer a master site.  

commfailure
 

Master is not accessible.  

missingrepgroup
 

Object group name not specified.  

REGISTER_SNAPSHOT_REPGROUP procedure

This procedure facilitates the administration of snapshots at their respective master sites by inserting/modifying/deleting from registered_snapshot_groups.

Syntax

DBMS_REPCAT.REGISTER_SNAPSHOT_REPGROUP (
   gname            IN   VARCHAR2,
   snapsite         IN   VARCHAR2,
   comment          IN   VARCHAR2  := NULL,
   rep_type         IN   NUMBER    := reg_unknown,
   fname            IN   VARCHAR2  := NULL);

Parameters

Table 8-177 REGISTER_SNAPSHOT_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the snapshot object group to be registered.  

snapsite
 

Global name of the snapshot site.  

comment
 

Comment for the snapshot site or update for an existing comment.  

rep_type
 

Version of the snapshot group. Valid constants that can be assigned include reg_uknown (the default), reg_v7_group, reg_v8_group, and reg_repapi_group.  

fname
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-178 REGISTER_SNAPSHOT_REPGROUP Procedure Exceptions
Exception  Description 
missingrepgroup
 

Object group name not specified.  

nullsitename
 

A snapshot site was not specified.  

nonmaster
 

Procedure must be executed at the snapshot's master site.  

duplicaterepgroup
 

Object already exists.  

REGISTER_STATISTICS procedure

This procedure collects information about the successful resolution of update, delete, and uniqueness conflicts for a table.

Syntax

DBMS_REPCAT.REGISTER_STATISTICS (
   sname IN   VARCHAR2,
   oname IN   VARCHAR2);

Parameters

Table 8-179 REGISTER_STATISTICS Procedure Parameters
Parameter  Description 
sname
 

Name of the schema in which the table is located.  

oname
 

Name of the table for which you want to gather conflict resolution statistics.  

Exceptions

Table 8-180 REGISTER_STATISTICS Procedure Exceptions
Exception  Description 
missingschema
 

Given schema does not exist.  

missingobject
 

Given table does not exist.  

RELOCATE_MASTERDEF procedure

This procedure changes your master definition site to another master site in your replicated environment.

Syntax

DBMS_REPCAT.RELOCATE_MASTERDEF (
   gname                  IN   VARCHAR2, 
   old_masterdef          IN   VARCHAR2, 
   new_masterdef          IN   VARCHAR2, 
   notify_masters         IN   BOOLEAN    := TRUE,
   include_old_masterdef  IN   BOOLEAN    := TRUE,
   require_flavor_change  IN   BOOLEAN    := FALSE);

Parameters

Table 8-181 RELOCATE_MASTERDEF Procedure Parameters
Parameter  Description 
gname
 

Name of the object group whose master definition you want to relocate.  

old_masterdef
 

Fully qualified database name of the current master definition site.  

new_masterdef
 

Fully qualified database name of the existing master site that you want to make the new master definition site.  

notify_masters
 

If this is TRUE, then the procedure synchronously multicasts the change to all masters (including OLD_MASTERDEF only if INCLUDE_OLD_MASTERDEF is TRUE). If any master does not make the change, then roll back the changes at all masters.  

include_old_
masterdef
 

If NOTIFY_MASTERS is TRUE and if INCLUDE_OLD_MASTERDEF is also TRUE, then the old master definition site is also notified of the change.  

require_flavor_
change
 

This system parameter is for internal use only. Do not set the parameter unless so directed by Oracle Worldwide Support.  

Exceptions

Table 8-182 RELOCATE_MASTERDEF Procedure Exceptions
Exception  Description 
nonmaster
 

NEW_MASTERDEF is not a master site or the invocation site is not a master site.  

nonmasterdef
 

OLD_MASTERDEF is not the master definition site.  

commfailure
 

At least one master site is not accessible and NOTIFY_MASTERS is TRUE.  

Usage Notes

It is not necessary for either the old or new master definition site to be available when you call RELOCATE_MASTERDEF. In a planned reconfiguration, you should invoke RELOCATE_MASTERDEF with NOTIFY_MASTERS TRUE and INCLUDE_OLD_MASTERDEF TRUE.

If just the master definition site fails, then you should invoke RELOCATE_MASTERDEF with NOTIFY_MASTERS TRUE and INCLUDE_OLD_MASTERDEF FALSE. If several master sites and the master definition site fail, then the administrator should invoke RELOCATE_MASTERDEF at each operational master with NOTIFY_MASTERS FALSE.

REMOVE_MASTER_DATABASES procedure

This procedure removes one or more master databases from a replicated environment. This procedure regenerates the triggers and their associated packages at the remaining master sites. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.REMOVE_MASTER_DATABASES (
   gname           IN   VARCHAR2,
   master_list     IN   VARCHAR2 |
   master_table    IN   DBMS_UTILITY.DBLINK_ARRAY);

Parameters

Table 8-183 REMOVE_MASTER_DATABASES Procedure Parameters
Parameter  Description 
gname
 

Name of the object group associated with the replicated environment. This prevents confusion if a master database is involved in more than one replicated environment.  

master_list
 

A comma-separated list of fully qualified master database names that you want to remove from the replicated environment. There must be no white space between names in the list.  

master_table
 

In place of a list, you may also specify the database names in a PL/SQL table of type DBMS_UTILITY.DBLINK_ARRAY.  

Exceptions

Table 8-184 REMOVE_MASTER_DATABASES Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

nonmaster
 

At least one of the given databases is not a master site.  

reconfigerror
 

One of the given databases is the master definition site.  

commfailure
 

At least one remaining master site is not accessible.  

REPCAT_IMPORT_CHECK procedure

This procedure ensures that the objects in the replicated master group have the appropriate object identifiers and status values after you perform an export/import of a replicated object or an object used by the advanced replication facility.

Syntax

DBMS_REPCAT.REPCAT_IMPORT_CHECK (
   gname      IN   VARCHAR2,
   master     IN   BOOLEAN);

Parameters

Table 8-185 REPCAT_IMPORT_CHECK Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group. If you omit both parameters, then the procedure checks all replicated master groups at your current site.  

master
 

Set this to TRUE if you are checking a master site or FALSE if you are checking a snapshot site.  

Exceptions

Table 8-186 REPCAT_IMPORT_CHECK Procedure Exceptions
Exception  Description 
nonmaster
 

MASTER is TRUE and either the database is not a master site for the object group or the database is not the expected database.  

nonsnapshot
 

MASTER is FALSE and the database is not a snapshot site for the object group.  

missingobject
 

A valid replicated object in the object group does not exist.  

missingrepgroup
 

The given group name does not exist.  

RESUME_MASTER_ACTIVITY procedure

This procedure resumes normal replication activity after quiescing a replicated environment.

Syntax

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
   gname       IN  VARCHAR2, 
   override    IN  BOOLEAN := FALSE);

Parameters

Table 8-187 RESUME_MASTER_ACTIVITY Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

override
 

If this is TRUE, then it ignores any pending RepCat administration requests and restores normal replication activity at each master as quickly as possible. This should be considered only in emergency situations.

If this is FALSE, then it restores normal replication activity at each master only when there is no pending RepCat administration request for gname at that master.  

Exceptions

Table 8-188 RESUME_MASTER_ACTIVITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

notquiesced
 

replicated master group is not quiescing or quiesced.  

commfailure
 

At least one master site is not accessible.  

SEND_OLD_VALUES procedure

You have the option of sending old column values for each non-key column of a replicated table for updates and deletes. The default is to send old values for all columns. You can change this behavior at all master and snapshot sites by invoking DBMS_REPCAT.SEND_OLD_VALUES at the master definition site.

Syntax

DBMS_REPCAT.SEND_OLD_VALUES(
   sname           IN  VARCHAR2,
   oname           IN  VARCHAR2,
   { column_list   IN  VARCHAR2,
   | column_table  IN  DBMS_REPCAT.VARCHAR2s,}
   operation       IN  VARCHAR2 := `UPDATE',
   send            IN  BOOLEAN := TRUE );


Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.  


Parameters

Table 8-189 SEND_OLD_VALUES Procedure Parameters
Parameter  Description 
sname
 

Schema in which the table is located.  

oname
 

Name of the replicated table.  

column_list
 

A comma-separated list of the columns in the table. There must be no white space between entries.  

column_table
 

Instead of a list, you can use a PL/SQL table of type DBMS_REPCAT.VARCHAR2S to contain the column names. The first column name should be at offset 1, the second at offset 2, and so on.  

operation
 

Possible values are: UPDATE, DELETE, or the asterisk wildcard '*', which means update and delete.  

send
 

If TRUE, then the old values of the specified columns are sent. If FALSE, then the old values of the specified columns are not sent. Unspecified columns and unspecified operations are not affected. The specified change takes effect at the master definition site as soon as min_communication is TRUE for the table. The change takes effect at a master site or at a snapshot site the next time replication support is generated at that site with min_communication TRUE.  


Note:

The operation parameter allows you to decide whether or not to transmit old values for non-key columns when rows are deleted or when non-key columns are updated. If you do not send the old value, then Oracle sends a NULL in place of the old value and assumes the old value is equal to the current value of the column at the target side when the update or delete is applied.

Read "Minimizing Data Propagation for Update Conflict Resolution" in the Oracle8i Replication manual before changing the default behavior of Oracle.  


Exceptions

Table 8-190 SEND_OLD_VALUES Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist as a table in the given schema awaiting row-level replication information.  

missingcolumn
 

At least one column is not in the table.  

notquiesced
 

replicated master group has not been suspended.  

typefailure
 

An illegal operation is given.  

SET_COLUMNS procedure

To use an alternate column or group of columns, instead of the primary key, to determine which columns of a table to compare when using row-level replication. You must call this procedure from the master definition site.

See "Using Multimaster Replication" in the Oracle8i Replication manual

Syntax

DBMS_REPCAT.SET_COLUMNS (
   sname           IN    VARCHAR2,
   oname           IN    VARCHAR2,
   { column_list   IN    VARCHAR2
   | column_table  IN    DBMS_UTILITY.NAME_ARRAY } );


Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.  


Parameters

Table 8-191 SET_COLUMNS Procedure Parameters
Parameter  Description 
sname
 

Schema in which the table is located.  

oname
 

Name of the table.  

column_list
 

A comma-separated list of the columns in the table that you want to use as a primary key. There must be no white space between entries.  

column_table
 

Instead of a list, you can use a PL/SQL table of type DBMS_UTILITY.NAME_ARRAY to contain the column names. The first column name should be at offset 1, the second at offset 2, and so on.  

Exceptions

Table 8-192 SET_COLUMNS Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

missingobject
 

Given object does not exist as a table in the given schema awaiting row-level replication information.  

missingcolumn
 

At least one column is not in the table.  

SUSPEND_MASTER_ACTIVITY procedure

This procedure suspends replication activity for an object group. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
   gname   IN   VARCHAR2);

Parameters

Table 8-193 SUSPEND_MASTER_ACTIVITY Procedure Parameters
Parameter  Description 
gname
 

Name of the object group for which you want to suspend activity.  

Exceptions

Table 8-194 SUSPEND_MASTER_ACTIVITY Procedure Exceptions
Exception  Description 
nonmasterdef
 

Invocation site is not the master definition site.  

notnormal
 

replicated master group is not in normal operation.  

commfailure
 

At least one master site is not accessible.  

Usage Notes

The current implementation of SUSPEND_MASTER_ACTIVITY quiesces all replicated master groups at each master site.

SWITCH_SNAPSHOT_MASTER procedure

This procedure changes the master database of a snapshot replicated master group to another master site. This procedure does a full refresh of the affected snapshots and regenerates the triggers and their associated packages as needed. This procedure does not push the queue to the old master site before changing masters.

Syntax

DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER (
   gname              IN   VARCHAR2, 
   master             IN   VARCHAR2);

Parameters

Table 8-195 SWITCH_SNAPSHOT_MASTER Procedure Parameters
Parameter  Description 
gname
 

Name of the snapshot object group for which you want to change master sites.  

master
 

Fully qualified database name of the new master database to use for the snapshot site.  

Exceptions

Table 8-196 SWITCH_SNAPSHOT_MASTER Procedure Exceptions
Exception  Description 
nonsnapshot
 

Invocation site is not a snapshot site.  

nonmaster
 

Given database is not a master site.  

commfailure
 

Given database is not accessible.  

UNREGISTER_SNAPSHOT_REPGROUP procedure

This procedure facilitates the administration of snapshots at their respective master sites by inserting/modifying/deleting from registered_snapshot_groups.

Syntax

DBMS_REPCAT.UNREGISTER_SNAPSHOT_REPGROUP (
   gname     IN   VARCHAR2,
   snapsite  IN   VARCHAR2);

Parameters

Table 8-197 UNREGISTER_SNAPSHOT_REPGROUP Procedure Parameters
Parameter  Description 
gname
 

Name of the snapshot object group to be unregistered.  

snapsite
 

Global name of the snapshot site.  

VALIDATE function

This function validates the correctness of key conditions of a multiple master replication environment. This is overloaded.

Syntax

DBMS_REPCAT.VALIDATE (
   gname               IN  VARCHAR2, 
   check_genflags      IN  BOOLEAN := FALSE,
   check_valid_objs    IN  BOOLEAN := FALSE,
   check_links_sched   IN  BOOLEAN := FALSE,
   check_links         IN  BOOLEAN := FALSE,
   error_table         OUT dbms_repcat.validate_err_table )
  RETURN BINARY_INTEGER;

DBMS_REPCAT.VALIDATE (
   gname               IN  VARCHAR2, 
   check_genflags      IN  BOOLEAN := FALSE,
   check_valid_objs    IN  BOOLEAN := FALSE,
   check_links_sched   IN  BOOLEAN := FALSE,
   check_links         IN  BOOLEAN := FALSE,
   error_msg_table     OUT DBMS_UTILITY.UNCL_ARRAY,
   error_num_table     OUT DBMS_UTILITY.NUMBER_ARRAY )
  RETURN BINARY_INTEGER;

Parameters

Table 8-198 VALIDATE Function Parameters
Parameter  Description 
gname
 

Name of the master group to validate.  

check_genflags
 

Check whether all the objects in the group are generated. This must be done at the masterdef site only.  

check_valid_objs
 

Check that the underlying objects for objects in the group valid. This must be done at the masterdef site only. The masterdef site goes to all other sites and checks that the underlying objects are valid. The validity of the objects is checked within the schema of the connected user.  

check_links_sched
 

Check whether the links are scheduled for execution. This should be invoked at each master site.  

check_links
 

Check whether the connected user (repadmin), as well as the propagator, have correct links for replication to work properly. Checks that the links exist in the database and are accessible. This should be invoked at each master site.  

error_table
 

Returns the message and numbers of all errors that are found.  

error_msg_table
 

Returns the messages of all errors that are found.  

error_num_table
 

Returns the numbers of all errors that are found.  

Exceptions

Table 8-199 VALIDATE Function Exceptions
Exception  Description 
missingdblink
 

Database link does not exist in the schema of the replication propagator or has not been scheduled. Ensure that the database link exists in the database, is accessible, and is scheduled for execution.  

dblinkmismatch
 

Database link name at the local node does not match the global name of the database that the link accesses. Ensure that global names is set to true and the link name matches the global name.  

dblinkuidmismatch
 

User name of the replication administration user at the local node and the user name at the node corresponding to the database link are not the same. Advanced replication expects the two users to be the same. Ensure that the user ID of the replication administration user at the local node and the user ID at the node corresponding to the database link are the same.  

objectnotgenerated
 

Object has not been generated at other master sites or is still being generated. Ensure that the object is generated by calling generate_replication_support and do_deferred_repcat_admin for the object at the masterdef site.  

opnotsupported
 

Operation is not supported if the object group is replicated at a pre-V8 node. Ensure that all nodes of the replicated master group are V8.  

Usage Notes

The return value of VALIDATE is the number of errors found. The function's OUT parameter(s) returns any errors that are found. In the first interface function, the ERROR_TABLE consists of an array of records. Each record has a VARCHAR2 and a NUMBER in it. The string field contains the error message and the number field contains the Oracle error number.

The second interface is similar except that there are two OUT arrays. A VARCHAR2 array with the error messages and a NUMBER array with the error numbers.

WAIT_MASTER_LOG procedure

This procedure determines whether changes that were asynchronously propagated to a master site have been applied.

Syntax

DBMS_REPCAT.WAIT_MASTER_LOG (
   gname          IN    VARCHAR2, 
   record_count   IN    NATURAL, 
   timeout        IN    NATURAL, 
   true_count     OUT   NATURAL);

Parameters

Table 8-200 WAIT_MASTER_LOG Procedure Parameters
Parameter  Description 
gname
 

Name of the replicated master group.  

record_count
 

Procedure returns whenever the number of incomplete activities is at or below this threshold.  

timeout
 

Maximum number of seconds to wait before the procedure returns.  

true_count
(out parameter)
 

Returns the number of incomplete activities.  

Exceptions

Table 8-201 WAIT_MASTER_LOG Procedure Exceptions
Exception  Description 
nonmaster
 

Invocation site is not a master site.  

DBMS_REPCAT_ADMIN Package

Summary of Subprograms

Table 8-202 DBMS_REPCAT_ADMIN Package Subprograms
Subprogram  Description 
GRANT_ADMIN_ANY_
SCHEMA procedure
 

Grants the necessary privileges to the replication administrator to administer any replicated master group at the current site.  

GRANT_ADMIN_SCHEMA 
procedure
 

Grants the necessary privileges to the replication administrator to administer a schema at the current site.  

REGISTER_USER_
REPGROUP procedure
 

Assigns proxy snapshot administrator or receiver privileges at the master site for use with remote sites.  

REVOKE_ADMIN_ANY_
SCHEMA procedure
 

Revokes the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_ANY_SCHEMA.  

REVOKE_ADMIN_SCHEMA 
procedure
 

Revokes the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_SCHEMA.  

UNREGISTER_USER_
REPGROUP procedure
 

Revokes the privileges and roles from the proxy snapshot administrator or receiver that would be granted by the REGISTER_USER_REPGROUP procedure.  

GRANT_ADMIN_ANY_SCHEMA procedure

This procedure grants the necessary privileges to the replication administrator to administer any replicated master group at the current site.

Syntax

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
   username  IN   VARCHAR2);

Parameters

Table 8-203 GRANT_ADMIN_ANY_SCHEMA Procedure Parameters
Parameter  Description 
username
 

Name of the replication administrator to whom you want to grant the necessary privileges and roles to administer any replicated master groups at the current site.  

Exceptions

Table 8-204 GRANT_ADMIN_ANY_REPGROUP Procedure Exceptions
Exception  Description 
ORA-01917
 

User does not exist.  

GRANT_ADMIN_SCHEMA procedure

This procedure grants the necessary privileges to the replication administrator to administer a schema at the current site. This procedure is most useful if your object group does not span schemas.

Syntax

DBMS_REPCAT_ADMIN.GRANT_ADMIN_SCHEMA (
   username IN VARCHAR2);

Parameters

Table 8-205 GRANT_ADMIN_REPSCHEMA Procedure Parameters
Parameter  Description 
username
 

Name of the replication administrator. This user is then granted the necessary privileges and roles to administer the schema of the same name within a replicated master group at the current site.  

Exceptions

Table 8-206 GRANT_ADMIN_REPSCHEMA Procedure Exceptions
Exception  Description 
ORA-01917
 

User does not exist.  

REGISTER_USER_REPGROUP procedure

This procedure assigns proxy snapshot administrator or receiver privileges at the master site for use with remote sites. This procedure grants only the necessary privileges to the proxy snapshot administrator or receiver, avoiding having to grant the powerful privileges granted by the GRANT_ADMIN_SCHEMA or GRANT_ADMIN_ANY_SCHEMA procedures.

See "Advanced Techniques" in the Oracle8i Replication manual for more information on trusted versus untrusted security models.

Syntax

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
   username         IN   VARCHAR2,
   privilege_type   IN   VARCHAR2,
   list_of_gnames   IN   VARCHAR2 |
   table_of_gnames  IN   dbms_utility.name_array);

Parameters

Table 8-207 REGISTER_USER_REPGROUP Procedure Parameters
Parameter  Description 
username
 

Name of the user you are giving either proxy snapshot administrator or receiver privileges to.  

privilege_type
 
Specifies the privilege type you are assigning. Use the following values for to define your privilege_type:

RECEIVER for receiver privileges

PROXY_SNAPADMIN for proxy snapadmin privileges.  

list_of_gnames
 

Comma-separated list of object groups you want a user registered for receiver privileges. There must be no whitespace between entries in the list. If you set list_of_gnames to NULL, then the user is registered for all object groups, even object groups that are not yet known when this procedure is called. You must use named notation in order to set list_of_gnames to NULL. An invalid object group in the list causes registration to fail for the entire list.

If you specify a value for list_of_gnames, then do not specify a value for table_of_gnames.  

table_of_gnames
 

PL/SQL table of object groups you want a user registered for receiver privileges. The PL/SQL table must be of type DBMS_UTILITY.NAME_ARRAY. This table is 1-based. Use the single value NULL to register the user for all object groups. An invalid object group in the table causes registration to fail for the entire table.

If you specify a value for table_of_gnames, then do not specify a value for list_of_gnames.  

Exceptions

Table 8-208 REGISTER_USER_REPGROUP Procedure Exceptions
Exception  Description 
nonmaster
 

Specified object group does not exist or the invocation database is not a master.  

ORA-01917
 

User does not exist.  

typefailure
 

Incorrect privilege type was specified.  

REVOKE_ADMIN_ANY_SCHEMA procedure

This procedure revokes the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_ANY_SCHEMA.


Note:

Identical privileges and roles that were granted independently of GRANT_ADMIN_ANY_SCHEMA are also revoked.  


Syntax

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_SCHEMA (
   username IN VARCHAR2);

Parameters

Table 8-209 REVOKE_ADMIN_ANY_SCHEMA Procedure Parameters
Parameter  Description 
username
 

Name of the replication administrator whose privileges you want to revoke.  

Exceptions

Table 8-210 REVOKE_ADMIN_ANY_SCHEMA Procedure Exceptions
Exception  Description 
ORA-01917
 

User does not exist.  

REVOKE_ADMIN_SCHEMA procedure

This procedure revokes the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_SCHEMA.


Note:

Identical privileges and roles that were granted independently of GRANT_ADMIN_SCHEMA are also revoked.  


Syntax

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_SCHEMA (
   username IN VARCHAR2);

Parameters

Table 8-211 REVOKE_ADMIN_SCHEMA Procedure Parameters
Parameter  Description 
username
 

Name of the replication administrator whose privileges you want to revoke.  

Exceptions

Table 8-212 REVOKE_ADMIN_SCHEMA Procedure Exceptions
Exception  Description 
ORA-01917
 

User does not exist.  

UNREGISTER_USER_REPGROUP procedure

This procedure revokes the privileges and roles from the proxy snapshot administrator or receiver that would be granted by the REGISTER_USER_REPGROUP procedure.

Syntax

DBMS_REPCAT_ADMIN.UNREGISTER_USER_REPGROUP (
   username         IN   VARCHAR2,
   privilege_type   IN   VARCHAR2,
   list_of_gnames   IN   VARCHAR2 |
   table_of_gnames  IN   dbms_utility.name_array);

Parameters

Table 8-213 UNREGISTER_USER_REPGROUP Procedure Parameters
Parameter  Description 
username
 

Name of the user you are unregistering.  

privilege_type
 

Specifies the privilege type you are revoking. Use the following values for to define your privilege_type:

RECEIVER for receiver privileges

PROXY_SNAPADMIN for proxy snapadmin privileges.  

list_of_gnames
 

Comma-separated list of object groups you want a user unregistered for receiver privileges. There must be no whitespace between entries in the list. If you set list_of_gnames to NULL, then the user is unregistered for all object groups registered. You must use named notation in order to set list_of_gnames to NULL. An invalid object group in the list causes unregistration to fail for the entire list.

If you specify a value for list_of_gnames, then do not specify a value for table_of_gnames.  

table_of_gnames
 

PL/SQL table of object groups you want a user unregistered for receiver privileges. The PL/SQL table must be of type DBMS_UTILITY.NAME_ARRAY. This table is 1-based. Use the single value NULL to unregister the user for all object groups registered. An invalid object group in the table causes unregistration to fail for the entire table.

If you specify a value for table_of_gnames, then do not specify a value for list_of_gnames.  

Exceptions

Table 8-214 UNREGISTER_USER_REPGROUP Procedure Exceptions
Exception  Description 
nonmaster
 

Specified object group does not exist or the invocation database is not a master.  

ORA-01917
 

User does not exist.  

typefailure
 

Incorrect privilege type was specified.  

DBMS_REPCAT_INSTANTIATE Package

Summary of Subprograms

Table 8-215 DBMS_REPCAT_INSTANTIATE Package Subprograms
Subprogram  Description 
DROP_SITE_INSTANTIATION 
procedure
 

Public procedure that removes the target site from the DBA_REPCAT_TEMPLATE_SITES view.  

INSTANTIATE_OFFLINE 
procedure
 

Public procedure that generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline.  

INSTANTIATE_ONLINE 
procedure
 

Public procedure that generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online.  

DROP_SITE_INSTANTIATION procedure

This procedure drops a template instantiation at a target site. This procedure removes all related meta data at the master site and disables the specified site from refreshing their snapshots. You need to execute this procedure as the user that originally instantiated the template (to see who instantiated the template, query the REPCAT_TEMPLATE_SITES View, which is described.

Syntax

The parameter for the DROP_SITE_INSTANTIATION procedure is described in Table 8-216:

DBMS_REPCAT_INSTANTIATE.DROP_SITE_INSTANTIATION(
     refresh_template_name  IN   VARCHAR2,
     site_name              IN   VARCHAR2,
     repapi_site_id         IN   NUMBER := -1e-130)
Table 8-216 Parameter for DROP_SITE_INSTANTIATION
Parameter  Description 
refresh_template_name
 

The name of the deployment template to be dropped.  

site_name
 

Identifies the Oracle server site where you want to drop the specified template instantiation (if you specify a SITE_NAME, do not specify a REPAPI_SITE_ID).  

repapi_site_id
 

Identifies the REPAPI location where you want to drop the specified template instantiation (if you specify a REPAPI_SITE_ID, do not specify a SITE_NAME).  

INSTANTIATE_OFFLINE procedure

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline. This generated script should be used at remote snapshot sites that are NOT able to remain connected to the master site for an extended amount of time. This is an ideal solution where the remote snapshot site is a laptop. Use the packaging tool in Replication Manager to package the generated script and data into a single file, that can be posted on an FTP site or loaded to a CD-ROM, floppy disk, etc. See "Deploying Template" in the Oracle8i Replication manual for more information.

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, including Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.

The user that executes this public procedure will become the "registered" user of the instantiated template at the specified site.



Note:

This procedure is used in performing an offline instantiation of a deployment template.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table) or with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot). See these respective packages for more information on their usage.  


Syntax

The parameter for the INSTANTIATE_OFFLINE function is described in Table 8-217, and the exception is described in Table 8-218. The syntax for this function is shown below:

DBMS_REPCAT_INSTANTIATE.INSTANTIATE_OFFLINE(
     refresh_template_name   IN   VARCHAR2,
     site_name               IN   VARCHAR2, 
     runtime_parm_id         IN   NUMBER := -1e-130,
     next_date               IN   DATE := SYSDATE,
     interval                IN   VARCHAR2 :'SYSDATe + 1')
     return NUMBER
Table 8-217 Parameter for INSTANTIATE_OFFLINE
Parameter  Description 
refresh_template_name
 

The name of the deployment template to be instantiated.  

site_name
 

The name of the remote site that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Table 8-218 Exception for INSTANTIATE_OFFLINE
Exception  Description 

miss_refresh_template  

The deployment template name specified is invalid or does not exist.  

miss_user  

The name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, than the specified user is not authorized to instantiate the target deployment template.  

bad_parms  

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e. type mismatch).  

Returns

Table 8-219 INSTANTIATE_OFFLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

INSTANTIATE_ONLINE procedure

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online. This generated script should be used at remote snapshot sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote snapshot site may be lengthy (depending on the amount of data that is populated to the new snapshots).

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.

The user that executes this public procedure will become the "registered" user of the instantiated template at the specified site.

Syntax

The parameter for the INSTANTIATE_ONLINE function is described in Table 8-220, and the exception is described in Table 8-221. The syntax for this function is shown below:

DBMS_REPCAT_INSTANTIATE.INSTANTIATE_ONLINE(
     refresh_template_name   IN   VARCHAR2,
     site_name               IN   VARCHAR2, 
     runtime_parm_id         IN   NUMBER := -1e-130,
     next_date               IN   DATE := SYSDATE,
     interval                IN   VARCHAR2 :'SYSDATe + 1')
     return NUMBER
Table 8-220 Parameter for INSTANTIATE_ONLINE
Parameter  Description 
refresh_template_name
 

The name of the deployment template to be instantiated.  

site_name
 

The name of the remote site that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Table 8-221 Exception for INSTANTIATE_ONLINE
Exception  Description 

miss_refresh_template  

The deployment template name specified is invalid or does not exist.  

miss_user  

The name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, than the specified user is not authorized to instantiate the target deployment template.  

bad_parms  

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e. type mismatch).  

Returns

Table 8-222 INSTANTIATE_ONLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

DBMS_REPCAT_RGT Package

Summary of Subprograms

Table 8-223 DBMS_REPCAT_RGT Package Subprograms
Subprogram  Description 
ALTER_REFRESH_TEMPLATE 
procedure
 

Allows the DBA to alter existing deployment templates.  

ALTER_TEMPLATE_OBJECT 
procedure
 

Alters objects that have been added to a specified deployment template.  

ALTER_TEMPLATE_PARM 
procedure
 

Allows the DBA to alter the parameters for a specific deployment template.  

ALTER_USER_AUTHORIZATION 
procedure
 

Alters the contents of the DBA_REPCAT_TEMPLATE_AUTH view.  

ALTER_USER_PARM_VALUE 
procedure
 

Changes existing parameter values that have been defined for a specific user.  

COMPARE_TEMPLATES 
function
 

Allows a DBA to compare the contents of two deployment templates.  

COPY_TEMPLATE function
 

Allows the DBA to copy a deployment template.  

CREATE_OBJECT_FROM_
EXISTING function
 

Creates a template object definition from existing database objects and adds it to a target deployment template.  

CREATE_REFRESH_TEMPLATE 
function
 

Creates the deployment template, which allows you to define the template name, private/public status, and target refresh group.  

CREATE_TEMPLATE_OBJECT 
function
 

Adds object definitions to a target deployment template container.  

CREATE_TEMPLATE_PARM 
function
 

Creates parameters for a specific deployment template to allow custom data sets to be created at the remote snapshot site.  

CREATE_USER_AUTHORIZATION 
function
 

Authorizes specific users to instantiate private deployment templates.  

CREATE_USER_PARM_VALUE 
function
 

Pre-defines deployment template parameter values for specific users.  

DELETE_RUNTIME_PARMS 
procedure
 

Deletes a runtime parameter value that you defined using the INSERT_RUNTIME_PARMS procedure.  

DROP_ALL_OBJECTS 
procedure
 

Allows the DBA to drop all objects or specific object types from a deployment template.  

DROP_ALL_TEMPLATE_PARMS 
procedure
 

Allows the DBA to drop template parameters for a specified deployment template.  

DROP_ALL_TEMPLATE_SITES 
procedure
 

Removes all entries from the DBA_REPCAT_TEMPLATE_SITES view.  

DROP_ALL_TEMPLATES 
procedure
 

Removes all deployment templates at the site where the procedure is called.  

DROP_ALL_USER_
AUTHORIZATIONS procedure
 

Allows the DBA to drop all user authorizations for a specified deployment template.  

DROP_ALL_USER_PARM_VALUES 
procedure
 

Drops user parameter values for a specific deployment template.  

DROP_REFRESH_TEMPLATE 
procedure
 

Drops a deployment template.  

DROP_SITE_INSTANTIATION 
procedure
 

Removes the target site from the DBA_REPCAT_TEMPLATE_SITES view.  

DROP_TEMPLATE_OBJECT 
procedure
 

Removes a template object from a specific deployment template.  

DROP_TEMPLATE_PARM 
procedure
 

Removes an existing template parameter from the DBA_REPCAT_TEMPLATE_PARMS view.  

DROP_USER_AUTHORIZATION 
procedure
 

Removes a user authorization entry from the DBA_REPCAT_TEMPLATE_AUTH view.  

DROP_USER_PARM_VALUE 
procedure
 

Removes a pre-defined user parameter value for a specific deployment template.  

GET_RUNTIME_PARM_ID 
function
 

Retrieves an ID to be used when defining a runtime parameter value.  

INSERT_RUNTIME_PARMS 
procedure
 

Defines runtime parameter values prior to instantiating a template.  

INSTANTIATE_OFFLINE 
function
 

Generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline.  

INSTANTIATE_ONLINE 
function
 

Generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online.  

LOCK_TEMPLATE_EXCLUSIVE 
procedure
 

Prevents users from reading or instantiating the template when a deployment template is being updated or modified.  

LOCK_TEMPLATE_SHARED 
procedure
 

Makes a specified deployment template read-only.  

ALTER_REFRESH_TEMPLATE procedure

This procedure allows the DBA to alter existing deployment templates. Alterations may include defining a new deployment template name, a new refresh group, or a new owner and changing the public/private status.

Syntax

DBMS_REPCAT_RGT.ALTER_REFRESH_TEMPLATE (
   refresh_template_name      IN   VARCHAR2,
   new_owner                  IN   VARCHAR2 := '-',
   new_refresh_group_name     IN   VARCHAR2 := '-',
   new_refresh_template_name  IN   VARCHAR2 := '-',
   new_template_comment       IN   VARCHAR2 := '-',
   new_public_template        IN   VARCHAR2 := '-',
   new_last_modified          IN   DATE := to_date('1', 'J'),
   new_modified_by            IN   NUMBER := -1e-130);

Parameters

Table 8-224 ALTER_REFRESH_TEMPLATE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

The name of the deployment template that you want to alter.  

new_owner
 

The name of the new deployment template owner. Do not specify a value to keep the current owner.  

new_refresh_group_
name
 

If necessary, use this parameter to specify a new refresh group name that the template objects will be added to. Do not specify a value to keep the current refresh group.  

new_refresh_
template_name
 

Use this parameter to specify a new deployment template name. Do not specify a value to keep the current deployment template name.  

new_template_comment
 

New deployment template comments. Do not specify a value to keep the current template comment.  

new_public_template
 

Determines whether the deployment template is public or private. Only acceptable values are 'Y' and 'N' ('Y' = public and 'N' = private). Do not specify a value to keep the current value.  

new_last_modified
 

Contains the date of the last modification made to this deployment template. If a value is not specified, then the current date is automatically used.  

new_modified_by
 

Contains the name of the user who last modified this deployment template. If a value is not specified, then the current user is automatically used.  

Exceptions

Table 8-225 ALTER_REFRESH_TEMPLATE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

bad_public_template
 

The public_template parameter is specified incorrectly. The public_template parameter must be specified as a 'Y' for a public template or an 'N' for a private template.  

dupl_refresh_
template
 

A template with the specified name already exists. See the DBA_REPCAT_REFRESH_TEMPLATES view.  

ALTER_TEMPLATE_OBJECT procedure

This procedure alters objects that have been added to a specified deployment template. The most common changes may include altering the object DDL and/or assigning the object to a different deployment template.

Changes made to the template is reflected only at new sites instantiating the deployment template. Remote sites that have already instantiated the template need to re-instantiate the deployment template to apply the changes.

Syntax

DBMS_REPCAT_RGT.ALTER_TEMPLATE_OBJECT (
   refresh_template_name       IN   VARCHAR2, 
   object_name                 IN   VARCHAR2,
   object_type                 IN   VARCHAR2,
   new_refresh_template_name   IN   VARCHAR2 := '-',
   new_object_name             IN   VARCHAR2 := '-',
   new_object_type             IN   VARCHAR2 := '-',
   new_ddl_text                IN   CLOB  := '-',
   new_master_rollback_seg     IN   VARCHAR2 := '-',
   new_flavor_id               IN   NUMBER := -1e-130);

Parameters

Table 8-226 ALTER_TEMPLATE_OBJECT Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Deployment template name that contains the object that you want to alter.  

object_name
 

Name of the template object that you want to alter.  

object_type
 

Type of object that you want to alter.  

new_refresh_
template_name
 

Name of the new deployment template that you want to re-assign this object to. Do not specify a value to keep the object assigned to the current deployment template.  

new_object_name
 

New name of the template object. Do not specify a value to keep the current object name.  

new_object_type
 

If specified, then the new object type. Objects of the following type may be specified:

SNAPSHOT PROCEDURE

INDEX FUNCTION

TABLE PACKAGE

VIEW PACKAGE BODY

SYNONYM TRIGGER

SEQUENCE DATABASE LINK  

new_ddl_text
 

New object DDL for specified object. Do not specify any new DDL text to keep the current object DDL.  

new_master_rollback_
seg
 

New master rollback segment for specified object. Do not specify a value to keep the current rollback segment.  

new_flavor_id
 

New flavor ID for the specified object. Do not specify a value to keep the current flavor ID.  

Exceptions

Table 8-227 ALTER_TEMPLATE_OBJECT Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_flavor_id
 

Flavor ID specified is invalid or does not exist.  

bad_object_type
 

Object type is specified incorrectly. See Table 8-226 for a list of valid object types.  

miss_template_object
 

Template object name specified is invalid or does not exist.  

dupl_template_object
 

New template name specified in the new_refresh_template_name parameter already exists.  

Usage Notes

Because the ALTER_TEMPLATE_OBJECT procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_TEMPLATE_OBJECT procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_TEMPLATE_OBJECT procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'CREATE SNAPSHOT snap_sales AS SELECT *
      FROM sales WHERE salesperson = :salesid and region_id = :region';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_OBJECT(
      refresh_template_name => 'rgt_personnel',
      object_name => 'SNAP_SALES',
      object_type => 'SNAPSHOT',
      new_ddl_text => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

ALTER_TEMPLATE_PARM procedure

This procedure allows the DBA to alter the parameters for a specific deployment template. Alterations may include renaming the parameter or redefining the default value and prompt string.

Syntax

DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
   refresh_template_name       IN   VARCHAR2,
   parameter_name              IN   VARCHAR2,
   new_refresh_template_name   IN   VARCHAR2 := '-',
   new_parameter_name          IN   VARCHAR2 := '-',
   new_default_parm_value      IN   CLOB := NULL,
   new_prompt_string           IN   VARCHAR2 := '-',
   new_user_override           IN   VARCHAR2 := '-');

Parameters

Table 8-228 ALTER_TEMPLATE_PARM Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameter that you want to alter.  

parameter_name
 

Name of the parameter that you want to alter.  

new_refresh_
template_name
 

Name of the deployment template that the specified parameter should be re-assigned to (useful when you want to move a parameter from one template to another). Do not specify a value to keep the parameter assigned to the current template.  

new_parameter_name
 

New name of the template parameter. Do not specify a value to keep the current parameter name.  

new_default_parm_
value
 

New default value for the specified parameter. Do not specify a value to keep the current default value.  

new_prompt_string
 

New prompt text for the specified parameter. Do not specify a value to keep the current prompt string.  

new_user_override
 

Determines if the user can override the default value if prompted during the instantiation process (the user is prompted if no user parameter value has been defined for this parameter). Set this parameter to 'Y' to allow a user to override the default value or set this parameter to 'N' to not allow an override.  

Exceptions

Table 8-229 ALTER_TEMPLATE_PARM Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_template_parm
 

Template parameter specified is invalid or does not exist.  

dupl_template_parm
 

Combination of new_refresh_template_name and new_parameter_name already exists.  

Usage Notes

Because the ALTER_TEMPLATE_PARM procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_TEMPLATE_PARM procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_TEMPLATE_PARM procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM(
      refresh_template_name => 'rgt_personnel',
      parameter_name => 'region',
      new_default_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

ALTER_USER_AUTHORIZATION procedure

This procedure alters the contents of the DBA_REPCAT_TEMPLATE_AUTH view. Specifically, you can change user/deployment template authorization assignments. This procedure is helpful, for example, if an employee moves positions and requires the snapshot environment of another deployment template; the DBA simply assigns the employee the new deployment template and the user is authorized to instantiate the target template.

Syntax

DBMS_REPCAT_RGT.ALTER_USER_AUTHORIZATION (
   user_name                   IN   VARCHAR2,
   refresh_template_name       IN   VARCHAR2,
   new_user_name               IN   VARCHAR2 := '-',
   new_refresh_template_name   IN   VARCHAR2 := '-');

Parameters

Table 8-230 ALTER_USER_AUTHORIZATION Procedure Parameters
Parameter  Description 
user_name
 

Name of the user whose authorization you want to alter.  

refresh_template_
name
 

Name of the deployment template that is currently assigned to the specified user that you want to alter.  

new_user_name
 

Use this parameter to define a new user for this template authorization. Do not specify a value to keep the current user  

new_refresh_
template_name
 

The deployment template that the specified user (either the existing or, if specified, the new user) is authorized to instantiate. Do not specify a value to keep the current deployment template.  

Exceptions

Table 8-231 ALTER_USER_AUTHORIZATION Procedure Exceptions
Exception  Description 
miss_user_
authorization
 

The combination of user_name and refresh_template_name values specified does not exist in the DBA_REPCAT_TEMPLATE_AUTH view.  

miss_user
 

The user name specified for the new_user_name or user_name parameter is invalid or does not exist.  

miss_refresh_
template
 

The deployment template specified for the new_refresh_template parameter is invalid or does not exist.  

dupl_user_
authorization
 

A row already exists for the specified user name and deployment template name. See the DBA_REPCAT_AUTH_TEMPLATES view.  

ALTER_USER_PARM_VALUE procedure

This procedure changes existing parameter values that have been defined for a specific user. This procedure is especially helpful if your snapshot environment uses assignment tables; simply change a user parameter value to quickly and securely change the data set of a remote snapshot site.

See "Deployment Template Design" in the Oracle8i Replication manual for more information on using assignment tables.

Syntax

DBMS_REPCAT_RGT.ALTER_USER_PARM_VALUE(
   refresh_template_name         IN   VARCHAR2, 
   parameter_name                IN   VARCHAR2,
   user_name                     IN   VARCHAR2,
   new_refresh_template_name     IN   VARCHAR2 := '-',
   new_parameter_name            IN   VARCHAR2 := '-',
   new_user_name                 IN   VARCHAR2 := '-',
   new_parm_value                IN   CLOB := NULL);

Parameters

Table 8-232 ALTER_USER_PARM_VALUE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the user parameter value that you want to alter.  

parameter_name
 

Name of the parameter that you want to alter.  

user_name
 

Name of the user whose parameter value you want to alter.  

new_refresh_
template_name
 

Name of the deployment template that the specified user parameter value should be re-assigned to (useful when you are authorizing a user for a different template). Do not specify a value to keep the parameter assigned to the current template.  

new_parameter_name
 

The new template parameter name. Do not specify a value to keep the user value defined for the existing parameter.  

new_user_name
 

The new user name that this parameter value is for. Do not specify a value to keep the parameter value assigned to the current user.  

new_parm_value
 

The new parameter value for the specified user parameter. Do not specify a value to keep the current parameter value.  

Exceptions

Table 8-233 ALTER_USER_PARM_VALUE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_template_parm
 

Template parameter specified is invalid or does not exist.  

miss_user
 

User name specified for the user_name or new_user_name parameters is invalid or does not exist.  

miss_user_parm_
values
 

User parameter value specified does not exist.  

dupl_user_parm_
values
 

New user parameter specified already exists.  

Usage Notes

Because the ALTER_USER_PARM_VALUE procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_USER_PARM_VALUE procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_USER_PARM_VALUE procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_USER_PARM_VALUE(
      refresh_template_name => 'rgt_personnel',
      parameter_name => 'region',
      user_name => 'BOB',
      new_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

COMPARE_TEMPLATES function

This function allows a DBA to compare the contents of two deployment templates. Any discrepancies between the two deployment templates is stored in the USER_REPCAT_TEMP_OUTPUT table.

The COMPARE_TEMPLATES function returns a number that you specify in the WHERE clause when querying the USER_REPCAT_TEMP_OUTPUT table. For example, if the COMPARE_TEMPLATES procedure returns the number 10, you would execute the following SELECT statement to view all discrepancies between two specified templates (your SELECT statement returns no rows if the templates are identical):

SELECT text FROM USER_REPCAT_TEMP_OUTPUT
   WHERE output_id =  10 ORDER BY LINE;

The contents of the USER_REPCAT_TEMP_OUTPUT are lost after you disconnect or a ROLLBACK has been performed.

Syntax

DBMS_REPCAT_RGT.COMPARE_TEMPLATES (
   source_template_name    IN   VARCHAR2,
   compare_template_name   IN   VARCHAR2)
  return NUMBER;

Parameters

Table 8-234 COMPARE_TEMPLATES Function Parameters
Parameter  Description 
source_template_name
 

Name of the first deployment template to be compared.  

compare_template_
name
 

Name of the second deployment template to be compared.  

Exceptions

Table 8-235 COMPARE_TEMPLATES Function Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name to be compared is invalid or does not exist.  

Returns

Table 8-236 COMPARE_TEMPLATES Function Returns
Return Value  Description 

<system generated number>  

Specify the number returned for the output_id value when you select from the USER_REPCAT_TEMP_OUTPUT view to view the discrepancies between the compared templates.  

COPY_TEMPLATE function

This function allows the DBA to copy a deployment template. COPY_TEMPLATE is helpful when a new deployment template will use many of the object contained in an existing deployment template. This function copies the deployment template, template objects, template parameters, and user parameter values. The DBA can optionally have the function copy the user authorizations for this template. The number returned by this function is used internally by Oracle to manage deployment templates.


Note:

The values in the DBA_REPCAT_TEMPLATE_SITES view are not copied.  


This function also allows the DBA to copy a deployment template to another master site, which is helpful for deployment template distribution and to split network loads between multiple sites.

Syntax

DBMS_REPCAT_RGT.COPY_TEMPLATE (
   old_refresh_template_name     IN   VARCHAR2, 
   new_refresh_template_name     IN   VARCHAR2,
   copy_user_authorizations      IN   VARCHAR2,
   dblink                        IN   VARCHAR2 := NULL)
   return NUMBER;

Parameters

Table 8-237 COPY_TEMPLATE Function Parameters
Parameter  Description 
old_refresh_
template_name
 

Name of the deployment template to be copied.  

new_refresh_
template_name
 

Name of the new deployment template.  

copy_user_
authorizations
 

Specifies whether the template authorizations for the original template should be copied for the new deployment template. Valid values for this parameter are 'Y', 'N' and NULL.

Note: All users must exist at the target database.  

dblink
 

Optionally defines where the deployment template should be copied from (this is helpful to distribute deployment templates to other master sites). If none is specified, then the deployment template is copied from the local master site.  

Exceptions

Table 8-238 COPY_TEMPLATE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name to be copied is invalid or does not exist.  

dupl_refresh_
template
 

Name of the new refresh template specified already exists.  

bad_copy_auth
 

Value specified for the copy_user_authorization parameter is invalid. Valid values are 'Y', 'N', and NULL.  

Returns

Table 8-239 COPY_TEMPLATES Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_OBJECT_FROM_EXISTING function

This function creates a template object definition from existing database objects and adds it to a target deployment template. The object DDL that created the original database object is executed when the target deployment template is instantiated at the remote snapshot site. This is ideal for adding existing triggers and procedures to your template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_OBJECT_FROM_EXISTING(
     refresh_template_name  IN   VARCHAR2,
     object_name            IN   VARCHAR2,
     sname                  IN   VARCHAR2,
     oname                  IN   VARCHAR2,
     otype                  IN   VARCHAR2)
     return NUMBER

Parameters

Table 8-240 CREATE_OBJECT_FROM_EXISTING Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to add this object to.  

object_name
 

If necessary, the new name of the existing object that you are adding to your deployment template (allows you to define a new name for an existing object).  

sname
 

The schema that contains the object that you are creating your template object from.  

oname
 

Name of the object that you are creating your template object from.  

otype
 

The type of database object that you are adding to the template (i.e., PROCEDURE, TRIGGER, etc.). The object type must be specified using the following numerical identifiers (DATABASE LINK or SNAPSHOT are not a valid object types for this function):

SEQUENCE PROCEDURE

INDEX FUNCTION

TABLE PACKAGE

VIEW PACKAGE BODY

SYNONYM TRIGGER  

Exceptions

Table 8-241 CREATE_OBJECT_FROM_EXISTING Function Exceptions
Exception  Description 
miss_refresh_
template
 

The specified refresh template name is invalid or missing. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of existing deployment templates.  

bad_object_type
 

The object type is specified incorrectly (see Table 8-246 for more information).  

dupl_template_object
 

An object of the same name and type has already been added to the specified deployment template.  

objectmissing
 

Existing object specified does not exist.  

Returns

Table 8-242 CREATE_OBJECT_FROM_EXISTING Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_REFRESH_TEMPLATE function

This function creates the deployment template, which allows you to define the template name, private/public status, and target refresh group. Each time that you create a template object, user authorization, or template parameter, you reference the deployment template created with this function. This function adds a row to the DBA_REPCAT_REFRESH_TEMPLATES view. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
   owner                  IN   VARCHAR2, 
   refresh_group_name     IN   VARCHAR2, 
   refresh_template_name  IN   VARCHAR2, 
   template_comment       IN   VARCHAR2 := NULL,
   public_template        IN   VARCHAR2 := NULL,
   last_modified          IN   DATE := SYSDATE,
   modified_by            IN   VARCHAR2 := USER,
   creation_date          IN   DATE := SYSDATE,
   created_by             IN   VARCHAR2 := USER)
  return NUMBER;

Parameters

Table 8-243 CREATE_REFRESH_TEMPLATE Function Parameters
Parameter  Description 
owner
 

User name of the deployment template owner is specified with this parameter. If an owner is not specified, then the name of the user creating the template is automatically used.  

refresh_group_name
 

Name of the refresh group that is created when this template is instantiated. All objects created by this template are assigned to the specified refresh group.  

refresh_template_
name
 

Name of the deployment template that you are creating. This name is referenced in all activities that involve this deployment template.  

template_comment
 

User comments defined with this are listed in the DBA_REPCAT_REFRESH_TEMPLATES view.  

public_template
 

Specifies whether the deployment template is public or private. Only acceptable values are 'Y' and 'N' ('Y' = public and 'N' = private).  

last_modified
 

The date of the last modification made to this deployment template. If a value is not specified, then the current date is automatically used.  

modified_by
 

Name of the user who last modified this deployment template. If a value is not specified, then the current user is automatically used.  

creation_date
 

The date that this deployment template was created. If a value is not specified, then the current date is automatically used.  

created_by
 

Name of the user who created this deployment template. If a value is not specified, then the current user is automatically used.  

Exceptions

Table 8-244 CREATE_REFRESH_TEMPLATE Function Exceptions
Exception  Description 
dupl_refresh_
template
 

A template with the specified name already exists. See the DBA_REPCAT_REFRESH_TEMPLATES view to see a list of existing templates.  

bad_public_template
 

The public_template parameter is specified incorrectly. The public_template parameter must be specified as a 'Y' for a public template or an 'N' for a private template.  

Returns

Table 8-245 CREATE_REFRESH_TEMPLATE Function Returns
Return Value  Description 
<system generated 
number>
 

System generated number is used internally by Oracle.  

CREATE_TEMPLATE_OBJECT function

This function adds object definitions to a target deployment template container. The specified object DDL is executed when the target deployment template is instantiated at the remote snapshot site. In addition to adding snapshots, this function can add tables, procedures, and other objects to your template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
   refresh_template_name  IN   VARCHAR2, 
   object_name            IN   VARCHAR2, 
   object_type            IN   VARCHAR2,
   ddl_text               IN   CLOB,
   master_rollback_seg    IN   VARCHAR2 := NULL,
   flavor_id              IN   NUMBER := -1e-130)
  return NUMBER;

Parameters

Table 8-246 CREATE_TEMPLATE_OBJECT Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to add this object to.  

object_name
 

Name of the template object that you are creating.  

object_type
 

The type of database object that you are adding to the template (i.e., SNAPSHOT, TRIGGER, PROCEDURE, etc.). Objects of the following type may be specified:

SNAPSHOT PROCEDURE

INDEX FUNCTION

TABLE PACKAGE

VIEW PACKAGE BODY

SYNONYM MATERIALIZED VIEW

SEQUENCE DATABASE LINK

TRIGGER  

ddl_text
 

Contains the DDL that creates the object that you are adding to the template. Be sure to end your DDL with a semi-colon. (Remember, you can use a colon (:) to create a template parameter for your template object; see "Creating Snapshots with Deployment Templates" in the Oracle8i Replication book for more information.  

master_rollback_seg
 

Specifies the name of the rollback segment to use when executing the defined object DDL at the remote snapshot site.  

flavor_id
 

Defines the flavor ID for this template object.  

Exceptions

Table 8-247 CREATE_TEMPLATE_OBJECT Function Exceptions
Exception  Description 
miss_refresh_
template
 

Specified refresh template name is invalid or missing. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of existing deployment templates.  

bad_object_type
 

Object type is specified incorrectly. See Table 8-246 for a list of valid object types.  

dupl_template_object
 

An object of the same name and type has already been added to the specified deployment template.  

Returns

Table 8-248 CREATE_TEMPLATE_OBJECT Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because CREATE_TEMPLATE_OBJECT utilizes a CLOB, you need to utilize the DBMS_LOB package when using the CREATE_TEMPLATE_OBJECT function. The following example illustrates how to use the DBMS_LOB package with the CREATE_TEMPLATE_OBJECT function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'CREATE SNAPSHOT snap_sales AS SELECT *
        FROM sales WHERE salesperson = :salesid';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT(
        refresh_template_name => 'rgt_personnel',
        object_name => 'snap_sales',
        object_type => 'SNAPSHOT',
        ddl_text => templob,
        master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

CREATE_TEMPLATE_PARM function

This function creates parameters for a specific deployment template to allow custom data sets to be created at the remote snapshot site. This function is only required when the DBA wants to define a set of template variables before adding any template objects (when objects are added to the template using the CREATE_TEMPLATE_OBJECT function, any variables in the object DDL are automatically added to the DBA_REPCAT_TEMPLATE_PARMS view).

The DBA typically uses the ALTER_TEMPLATE_PARM function to modify the default parameter values and/or prompt strings (see ALTER_TEMPLATE_PARM procedure on page for more information). The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_TEMPLATE_PARM (
   refresh_template_name  IN   VARCHAR2, 
   parameter_name         IN   VARCHAR2,
   default_parm_value     IN   CLOB := NULL,
   prompt_string          IN   VARCHAR2 := NULL,
   user_override          IN   VARCHAR2 := NULL)
   return NUMBER;

Parameters

Table 8-249 CREATE_TEMPLATE_PARM Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to create the parameter for.  

parameter_name
 

Name of the parameter you are creating.  

default_parm_value
 

Default values for this parameter are defined using this parameter. If a user parameter value or runtime parameter value is not present, then this default value is used during the instantiation process.  

prompt_string
 

The descriptive prompt text that is displayed for this template parameter during the instantiation process.  

user_override
 

Determines if the user can override the default value if prompted during the instantiation process (the user is prompted if no user parameter value has been defined for this parameter). Set this parameter to 'Y' to allow a user to override the default value or set this parameter to 'N' to not allow an override.  

Exceptions

Table 8-250 CREATE_TEMPLATE_PARM Function Exceptions
Exception  Description 
miss_refresh_
template
 

The specified refresh template name is invalid or missing.  

dupl_template_parm
 

A parameter of the same name has already been defined for the specified deployment template.  

Returns

Table 8-251 CREATE_TEMPLATE_PARM Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because the CREATE_TEMPLATE_PARM function utilizes a CLOB, you need to utilize the DBMS_LOB package when using the CREATE_TEMPLATE_PARM function. The following example illustrates how to use the DBMS_LOB package with the CREATE_TEMPLATE_PARM function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_PARM(
        refresh_template_name => 'rgt_personnel',
        parameter_name => 'region',
        default_parm_value => templob,
        prompt_string => 'Enter your region ID:',
        user_override => 'Y');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

CREATE_USER_AUTHORIZATION function

This function authorizes specific users to instantiate private deployment templates. Users not authorized for a private deployment template are not able to instantiate the private template. This function adds a row to the DBA_REPCAT_AUTH_TEMPLATES view.

Before you authorize a user, verify that the user exists at the master site where the user will instantiate the deployment template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION (
   user_name               IN   VARCHAR2, 
   refresh_template_name   IN   VARCHAR2)
   return NUMBER;

Parameters

Table 8-252 CREATE_USER_AUTHORIZATION Function Parameters
Parameter  Description 
user_name
 

Name of the user that you want to authorize to instantiate the specified template. Specify multiple users by separating user names with a comma (i.e., 'john, mike, bob')  

refresh_template_
name
 

Name of the template that you want to authorize the specified user to instantiate.  

Exceptions

Table 8-253 CREATE_USER_AUTHORIZATION Function Exceptions
Exception  Description 
miss_user
 

User name supplied is invalid or does not exist.  

miss_refresh_
template
 

Refresh template name supplied is invalid or does not exist.  

dupl_user_
authorization
 

An authorization has already been created for the specified user and deployment template. See the DBA_REPCAT_AUTH_TEMPLATES view for a listing of template authorizations.  

Returns

Table 8-254 CREATE_USER_AUTHORIZATION Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_USER_PARM_VALUE function

This function is used to pre-define deployment template parameter values for specific users. For example, if you want to pre-defined the region parameter as WEST for user 33456, then you would use the this function.

Any values specified with this function take precedence over default values specified for the template parameter. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
   refresh_template_name    IN   VARCHAR2, 
   parameter_name           IN   VARCHAR2,
   user_name                IN   VARCHAR2,
   parm_value               IN   CLOB := NULL)
  return NUMBER;

Parameters

Table 8-255 CREATE_USER_PARM_VALUE Function Parameters
Parameter  Description 
refresh_template_
name
 

Specifies the name of the deployment template that contains the parameter you are creating a user value for.  

parameter_name
 

Name of the template parameter that you are defining a user parameter value for.  

user_name
 

Specifies the name of the user that you are pre-defining a parameter value for.  

parm_value
 

The pre-defined parameter value that will be used during the instantiation process initiated by the specified user.  

Exceptions

Table 8-256 CREATE_USER_PARM_VALUE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or missing.  

dupl_user_parm_
values
 

A parameter value for the specified user, parameter, and deployment template has already been defined. Query the DBA_REPCAT_USER_PARMS view for a listing of existing user parameter values.  

miss_template_parm
 

Specified deployment template parameter name is invalid or missing.  

miss_user
 

Specified user name is invalid or missing.  

Returns

Table 8-257 CREATE_USER_PARM_VALUE Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because the CREATE_USER_PARM_VALUE function utilizes a CLOB, you need to utilize the DBMS_LOB package when using the this function. The following example illustrates how to use the DBMS_LOB package with the CREATE_USER_PARM_VALUE function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE(
        refresh_template_name => 'rgt_personnel',
        parameter_name => 'region',
        user_name => 'BOB',
        user_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

DELETE_RUNTIME_PARMS procedure

Use this procedure before instantiating a deployment template to delete a runtime parameter value that you defined using the INSERT_RUNTIME_PARMS procedure.

Syntax

DBMS_REPCAT_RGT.DELETE_RUNTIME_PARMS(
   runtime_parm_id    IN   NUMBER, 
   parameter_name     IN   VARCHAR2);

Parameters

Table 8-258 DELETE_RUNTIME_PARMS Procedure Parameters
Parameter  Description 
runtime_parm_id
 

Specifies the ID that you previously assigned the runtime parameter value to (this value was retrieved using the GET_RUNTIME_PARM_ID function).  

parameter_name
 

Specifies the name of the parameter value that you want to drop (query the DBA_REPCAT_TEMPLATE_PARMS for a list of deployment template parameters).  

Exceptions

Table 8-259 DELETE_RUNTIME_PARMS Procedure Exceptions
Exception  Description 
miss_template_parm
 

The specified deployment template parameter name is invalid or missing.  

DROP_ALL_OBJECTS procedure

This procedure allows the DBA to drop all objects or specific object types from a deployment template.



Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_OBJECTS (
   refresh_template_name   IN   VARCHAR2,
   object_type             IN   VARCHAR2 := NULL);

Parameters

Table 8-260 DROP_ALL_OBJECTS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the objects that you want to drop.  

object_type
 

If NULL, then all objects in the template are dropped. If an object type is specified, then only objects of that type are dropped. Objects of the following type may be specified:

SNAPSHOT PROCEDURE

INDEX FUNCTION

TABLE PACKAGE

VIEW PACKAGE BODY

SYNONYM MATERIALIZED VIEW

SEQUENCE DATABASE LINK

TRIGGER  

Exceptions

Table 8-261 DROP_ALL_OBJECTS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

bad_object_type
 

Object type is specified incorrectly. See Table 8-260 for a list of valid object types.  

DROP_ALL_TEMPLATE_PARMS procedure

This procedure allows the DBA to drop template parameters for a specified deployment template. The DBA can use this procedure to drop all parameters that are not referenced by a template objects or drop all objects that reference a parameter and the parameters themselves.



Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATE_PARMS (
   refresh_template_name   IN   VARCHAR2,
   drop_objects            IN   VARCHAR2 := N);

Parameters

Table 8-262 DROP_ALL_TEMPLATE_PARMS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameters that you want to drop.  

drop_objects
 

If no value is specified, then this defaults to N, which drops all parameters not referenced by a template object.

If Y is specified, then all objects that reference a template parameter and the template parameters themselves are dropped.  

Exceptions

Table 8-263 DROP_ALL_TEMPLATE_PARMS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_TEMPLATE_SITES procedure

This procedure removes all entries from the DBA_REPCAT_TEMPLATE_SITES view, which keeps a record of sites that have instantiated a particular deployment template.



Caution:

This is a dangerous procedure that cannot be undone. Additionally, Oracle Lite sites that have instantiated the dropped template will no longer be able to refresh their snapshots.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATE_SITES (
   refresh_template_name   IN   VARCHAR2);

Parameters

Table 8-264 DROP_ALL_TEMPLATE_SITES Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the sites that you want to drop.  

Exceptions

Table 8-265 DROP_ALL_TEMPLATE_SITES Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_TEMPLATES procedure

This procedure removes all deployment templates at the site where the procedure is called.


Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATES;

Parameters

None

DROP_ALL_USER_AUTHORIZATIONS procedure

This procedure allows the DBA to drop all user authorizations for a specified deployment template. Executing this procedure removes rows from the DBA_REPCAT_AUTH_TEMPLATES view.

This procedure might be implemented after converting a private template to a public template and the user authorizations are no longer required.

Syntax

DBMS_REPCAT_RGT.DROP_ALL_USER_AUTHORIZATIONS (
   refresh_template_name   IN   VARCHAR2);

Parameters

Table 8-266 DROP_ALL_USER_AUTHORIZATIONS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the objects that you want to drop.  

Exceptions

Table 8-267 DROP_ALL_USER_AUTHORIZATIONS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_USER_PARM_VALUES procedure

This procedure drops user parameter values for a specific deployment template. This procedure is very flexible in allowing the DBA to define a set of user parameter values to be deleted. For example, defining the following parameters have the effect:

refresh_template_name: drops all user parameters for the specified deployment template.

refresh_template_name, user_name: drops all of the specified user parameters for the specified deployment template.

refresh_template_name, parameter_name: drops all user parameter values for the specified deployment template parameter.

refresh_template_name, parameter_name, user_name: drops the specified user's value for the specified deployment template parameter (equivalent to DROP_USER_PARM).

Syntax

DBMS_REPCAT_RGT.DROP_ALL_USER_PARMS (
   refresh_template_name   IN   VARCHAR2,
   user_name               IN   VARCHAR2,
   parameter_name          IN   VARCHAR2);

Parameters

Table 8-268 DROP_ALL_USER_PARMS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameter values that you want to drop.  

user_name
 

Name of the user whose parameter values you want to drop.  

parameter_name
 

Template parameter that contains the values that you want to drop.  

Exceptions

Table 8-269 DROP_ALL_USER_PARMS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

User name specified is invalid or does not exist.  

miss_user_parm_
values
 

Deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

DROP_REFRESH_TEMPLATE procedure

This procedure drops a deployment template. Dropping a deployment template has a cascading effect, removing all related template parameters, user authorizations, template objects, and user parameters (this procedure does not drop template sites).

Syntax

DBMS_REPCAT_RGT.DROP_REFRESH_TEMPLATE (
   refresh_template_name  IN   VARCHAR2);

Parameters

Table 8-270 DROP_REFRESH_TEMPLATE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be dropped.  

Exceptions

Table 8-271 DROP_REFRESH_TEMPLATE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of deployment templates.  

DROP_SITE_INSTANTIATION procedure

Purpose

This procedure drops a template instantiation at a target site. This procedure removes all related meta data at the master site and disables the specified site from refreshing their snapshots.

Syntax

The parameter for the DROP_SITE_INSTANTIATION procedure is described in Table 8-270, and the exception is described in Table 8-271. The syntax for this procedure is shown below:

DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION (
     refresh_template_name  IN   VARCHAR2,
     user_name              IN   VARCHAR2,
     site_name              IN   VARCHAR2,
     repapi_site_id         IN   NUMBER := -1e-130)
Table 8-272 Parameter for DROP_SITE_INSTANTIATION
Parameter  Description 
refresh_template_name
 

The name of the deployment template to be dropped.  

user_name
 

Enter the name of the user that originally instantiated the template at the remote snapshot site. Query the REPCAT_TEMPLATE_SITES view to see the users that instantiated templates (see the "REPCAT_TEMPLATE_SITES View" section for more information).  

site_name
 

Identifies the Oracle server site where you want to drop the specified template instantiation (if you specify a SITE_NAME, do not specify a REPAPI_SITE_ID).  

repapi_site_id
 

Identifies the REPAPI location where you want to drop the specified template instantiation (if you specify a REPAPI_SITE_ID, do not specify a SITE_NAME).  

DROP_TEMPLATE_OBJECT procedure

This procedure removes a template object from a specific deployment template. For example, a DBA would use this procedure to remove an outdated snapshot from a deployment template. Changes made to the template are reflected at new sites instantiating the deployment template. Remote sites that have already instantiated the template need to re-instantiate the deployment template to apply the changes.

Syntax

DBMS_REPCAT_RGT.DROP_TEMPLATE_OBJECT (
   refresh_template_name  IN   VARCHAR2, 
   object_name            IN   VARCHAR2,
   object_type            IN   VARCHAR2);

Parameters

Table 8-273 DROP_TEMPLATE_OBJECT Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you are dropping the object from.  

object_name
 

Name of the template object to be dropped.  

object_type
 

The type of object that is to be dropped. Objects of the following type may be specified:

SNAPSHOT PROCEDURE

INDEX FUNCTION

TABLE PACKAGE

VIEW PACKAGE BODY

SYNONYM MATERIALIZED VIEW

SEQUENCE DATABASE LINK

TRIGGER  

Exceptions

Table 8-274 DROP_TEMPLATE_OBJECT Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist.  

miss_template_object
 

The template object specified is invalid or does not exist. Query the DBA_REPCAT_TEMPLATE_OBJECT view to see a list of deployment template objects.  

DROP_TEMPLATE_PARM procedure

This procedure removes an existing template parameter from the DBA_REPCAT_TEMPLATE_PARMS view. This procedure is helpful when you have dropped a template object and a particular parameter is no longer needed.

Syntax

DBMS_REPCAT_RGT.DROP_TEMPLATE_PARM (
   refresh_template_name  IN   VARCHAR2, 
   parameter_name         IN   VARCHAR2);

Parameters

Table 8-275 DROP_TEMPLATE_PARM Procedure Parameters
Parameter  Description 
refresh_template_
name
 

The deployment template name that has the parameter that you want to drop  

parameter_name
 

Name of the parameter that you want to drop.  

Exceptions

Table 8-276 DROP_TEMPLATE_PARM Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist.  

miss_template_parm
 

The parameter name specified is invalid or does not exist. Query the DBA_REPCAT_TEMPLATE_PARMS view to see a list of template parameters.  

DROP_USER_AUTHORIZATION procedure

This procedure removes a user authorization entry from the DBA_REPCAT_TEMPLATE_AUTH view. This procedure is used when removing a user's template authorization. If a user's authorization is removed, then the user is no longer able to instantiate the target deployment template.

See also DROP_ALL_USER_AUTHORIZATIONS procedure for additional information.

Syntax

DBMS_REPCAT_RGT.DROP_USER_AUTHORIZATION (
   refresh_template_name   IN   VARCHAR2,
   user_name               IN   VARCHAR2);

Parameters

Table 8-277 DROP_USER_AUTHORIZATION Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that the user's authorization is being removed from.  

user_name
 

Name of the user whose authorization is being removed.  

Exceptions

Table 8-278 DROP_USER_AUTHORIZATION Procedure Exceptions
Exception  Description 
miss_user
 

Specified user name is invalid or does not exist.  

miss_user_
authorization
 

Specified user and deployment template combination does not exist. Query the DBA_REPCAT_TEMPLATE_AUTH view to see a list of user/deployment template authorizations.  

miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_USER_PARM_VALUE procedure

This procedure removes a pre-defined user parameter value for a specific deployment template. This procedure is often executed after a user's template authorization has been removed.

Syntax

DBMS_REPCAT_RGT.DROP_USER_PARM_VALUE (
   refresh_template_name    IN   VARCHAR2, 
   parameter_name           IN   VARCHAR2,
   user_name                IN   VARCHAR2);

Parameters

Table 8-279 DROP_USER_PARM_VALUE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Deployment template name that contains the parameter value that you want to drop.  

parameter_name
 

Parameter name that contains the pre-defined value that you want to drop.  

user_name
 

Name of the user whose parameter value you want to drop.  

Exceptions

Table 8-280 DROP_USER_PARM_VALUE Procedure Exceptions
Exception  Description 

miss_refresh_template  

Deployment template name specified is invalid or does not exist.  

miss_user  

User name specified is invalid or does not exist.  

miss_user_parm_values  

Deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

GET_RUNTIME_PARM_ID function

This function retrieves an ID to be used when defining a runtime parameter value. All runtime parameter values are assigned to this ID and are also used during the instantiation process.

Syntax

DBMS_REPCAT_RGT.GET_RUNTIME_PARM_ID
  RETURN NUMBER;

Parameters

None

Returns

Table 8-281 GET_RUNTIME_PARM_ID Function Returns
Return Value  Corresponding Datatype 

<system generated number>  

Runtime parameter values are assigned to the system generated number and is also used during the instantiation process.  

INSERT_RUNTIME_PARMS procedure

This procedure defines runtime parameter values prior to instantiating a template. This procedure should be used to define parameter values when no user parameter values have been defined and you do not want to accept the default parameter values.

Before using the this procedure, be sure to execute the GET_RUNTIME_PARM_ID function to retrieve a parameter ID to be used when inserting a runtime parameter. This ID is used for defining runtime parameter values and instantiating deployment template.

Syntax

DBMS_REPCAT_RGT.INSERT_RUNTIME_PARMS (
   runtime_parm_id    IN   NUMBER, 
   parameter_name     IN   VARCHAR2,
   parameter_value    IN   CLOB);

Parameters

Table 8-282 INSERT_RUNTIME_PARMS Procedure Parameters
Parameter  Description 
runtime_parm_id
 

The ID retrieved by the GET_RUNTIME_PARM_ID function. This ID is also used when instantiating the deployment template (be sure to use the same ID for all parameter values for a deployment template).  

parameter_name
 

Name of the template parameter that you are defining a runtime parameter value for (query the DBA_REPCAT_TEMPLATE_PARMS view for a list of template parameters).  

parameter_value
 

The runtime parameter value that you want to use during the deployment template instantiation process.  

Exceptions

Table 8-283 INSERT_RUNTIME_PARMS Procedure Exceptions
Exception  Description 

miss_refresh_template  

The deployment template name specified is invalid or does not exist.  

miss_user  

The user name specified is invalid or does not exist.  

miss_user_parm_values  

The deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

Usage Notes

Because the this procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the INSERT_RUNTIME_PARMS procedure. The following example illustrates how to use the DBMS_LOB package with the INSERT_RUNTIME_PARMS procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.INSERT_RUNTIME_PARMS(
      runtime_parm_id => 20,
      parameter_name => 'region',
      parameter_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

INSTANTIATE_OFFLINE function

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline. This generated script should be used at remote snapshot sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote snapshot site may be lengthy (depending on the amount of data that is populated to the new snapshots). This procedure needs to be executed separately for each user instantiation.

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, including Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.


Note:

This procedure is used in performing an offline instantiation of a deployment template. Additionally, this procedure is for replication administrators that are instantiating for another user. Users wanting to perform their own instantiation should use the public version of the "INSTANTIATE_OFFLINE procedure" function, described.

This procedure should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table) or with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot). See these respective packages for more information on their usage.  


Syntax

DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE(
     refresh_template_name   IN   VARCHAR2,
     site_name               IN   VARCHAR2, 
     user_name               IN   VARCHAR2 := NULL,
     runtime_parm_id         IN   NUMBER := -1e-130,
     next_date               IN   DATE := SYSDATE,
     interval                IN   VARCHAR2 :'SYSDATE + 1')
     return NUMBER

Parameters

Table 8-284 INSTANTIATE_OFFLINE Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be instantiated.  

site_name
 

Name of the remote site that is instantiating the deployment template.  

user_name
 

Name of the authorized user that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Exceptions

Table 8-285 INSTANTIATE_OFFLINE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

Name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, then the specified user is not authorized to instantiate the target deployment template.  

bad_parms
 

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e., type mismatch).  

Returns

Table 8-286 INSTANTIATE_OFFLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

INSTANTIATE_ONLINE function

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online. This generated script should be used at remote snapshot sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote snapshot site may be lengthy (depending on the amount of data that is populated to the new snapshots). This procedure needs to be executed separately for each user instantiation.

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, including Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.


Note:

This procedure is for replication administrators that are instantiating for another user. Users wanting to perform their own instantiation should use the public version of the "INSTANTIATE_ONLINE procedure" function, described.  


Syntax

DBMS_REPCAT_RGT.INSTANTIATE_ONLINE(
   refresh_template_name   IN   VARCHAR2,
   site_name               IN   VARCHAR2 := NULL,
   user_name               IN   VARCHAR2 := NULL, 
   runtime_parm_id         IN   NUMBER := -1e-130,
   next_date               IN   DATE := SYSDATE,
   interval                IN   VARCHAR2 :'SYSDATe + 1')
  return NUMBER;

Parameters

Table 8-287 INSTANTIATE_ONLINE Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be instantiated.  

site_name
 

Name of the remote site that is instantiating the deployment template.  

user_name
 

Name of the authorized user that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Exceptions

Table 8-288 INSTANTIATE_ONLINE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

Name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, then the specified user is not authorized to instantiate the target deployment template.  

bad_parms
 

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e., type mismatch).  

Returns

Table 8-289 INSTANTIATE_ONLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

LOCK_TEMPLATE_EXCLUSIVE procedure

When a deployment template is being updated or modified, you should use the LOCK_TEMPLATE_EXCLUSIVE procedure to prevent users from reading or instantiating the template.

The lock will be released when a ROLLBACK or COMMIT is performed.


Note:

This procedure should be executed before you make any modifications to your deployment template.  


Syntax

The syntax for this procedure is shown below:

DBMS_REPCAT_RGT.LOCK_TEMPLATE_EXCLUSIVE()

LOCK_TEMPLATE_SHARED procedure

The LOCK_TEMPLATE_SHARED procedure is used to make a specified deployment template "read-only." This procedure should be called before instantiating a template, as this will ensure that nobody can change the deployment template while it is being instantiated.

The lock will be released when a ROLLBACK or COMMIT is performed.

Syntax

The syntax for this procedure is shown below:

DBMS_REPCAT_RGT.LOCK_TEMPLATE_SHARED()

DBMS_REPUTIL Package

Summary of Subprograms

Table 8-290 DBMS_REPUTIL Package Subprograms
Subprogram  Description 
REPLICATION_OFF 
procedure
 

modifies tables without replicating the modifications to any other sites in the replicated environment, or disables row-level replication when using procedural replication.  

REPLICATION_ON 
procedure
 

Re-enables replication of changes after replication has been temporarily suspended.  

REPLICATION_IS_ON 
function
 

Determines whether or not replication is running.  

FROM_REMOTE function
 

Returns TRUE at the beginning of procedures in the internal replication packages, and returns FALSE at the end of these procedures.  

GLOBAL_NAME function
 

Determines the global database name of the local database (the global name is the returned value).  

MAKE_INTERNAL_PKG 
procedure
 

Synchronizes internal packages and tables in the replication catalog. This procedure is executed under the direction of Oracle Worldwide Support only.  

SYNC_UP_REP 
procedure
 

Synchronizes internal triggers and tables/snapshots in the replication catalog. This procedure is executed under the direction of Oracle Worldwide Support only.  

REPLICATION_OFF procedure

This procedure modifies tables without replicating the modifications to any other sites in the replicated environment, or disables row-level replication when using procedural replication. In general, you should suspend replication activity for all master groups in your replicated environment before setting this flag.

Syntax

DBMS_REPUTIL.REPLICATION_OFF;

Parameters

None

REPLICATION_ON procedure

This procedure re-enables replication of changes after replication has been temporarily suspended.

Syntax

DBMS_REPUTIL.REPLICATION_ON;

Parameters

None

REPLICATION_IS_ON function

This function determines whether or not replication is running. A returned value of TRUE indicates that the generated replication triggers are enabled. FALSE indicates that replication is disabled at the current site for the replicated master group.

The returning value of this function is set by calling the REPLICATION_ON or REPLICATION_OFF procedures in the DBMS_REPUTIL package.

Syntax

DBMS_REPUTIL.REPLICATION_IS_ON
  return BOOLEAN;

Parameters

None

FROM_REMOTE function

This function returns TRUE at the beginning of procedures in the internal replication packages, and returns FALSE at the end of these procedures. You may need to check this function if you have any triggers that could be fired as the result of an update by an internal package.

Syntax

DBMS_REPUTIL.FROM_REMOTE
   return BOOLEAN;

Parameters

None

GLOBAL_NAME function

This function determines the global database name of the local database (the global name is the returned value).

Syntax

DBMS_REPUTIL.GLOBAL_NAME
   return VARCHAR2;

Parameters

None

MAKE_INTERNAL_PKG procedure

This procedure synchronizes the existence of an internal package with a table in the replication catalog. If the table has replication support, execute this procedure to create the internal package. If replication support does not exist, destroy any related internal package.


Warning:

This procedure should only be executed under the guidance of Oracle Worldwide Support.  


Syntax

DBMS_REPUTIL.MAKE_INTERNAL_PKG (
   canon_sname    IN   VARCHAR2
   canon_oname    IN   VARCHAR2);

Parameters

Table 8-291 MAKE_INTERNAL_PKG Procedure Parameters
Parameter  Description 
canon_sname
 

Schema containing the table to be synchronized.

This parameter value must be cannonically definined (capitalization must match object and must not be enclosed in double quotes).  

canon_oname
 

Name of the table to be synchronized.

This parameter value must be cannonically definined (capitalization must match object and must not be enclosed in double quotes).  

SYNC_UP_REP procedure

This procedure synchronizes the existence of an internal trigger with a table or snapshot in the replication catalog. If the table or snapshot has replication support, execute this procedure to create the internal replication trigger. If replication support does not exist, destroy any related internal trigger.


Warning:

This procedure should only be executed under the guidance of Oracle Worldwide Support.  


Syntax

DBMS_REPUTIL.SYNC_UP_REP (
   canon_sname    IN   VARCHAR2
   canon_oname    IN   VARCHAR2);

Parameters

Table 8-292 SYNC_UP_REP Procedure Parameters
Parameter  Description 
canon_sname
 

Schema containing the table or snapshot to be synchronized.

This parameter value must be cannonically definined (capitalization must match object and must not be enclosed in double quotes).  

canon_oname
 

Name of the table or snapshot to be synchronized.

This parameter value must be cannonically definined (capitalization must match object and must not be enclosed in double quotes).  

DBMS_SNAPSHOT Package

Summary of Subprograms

Table 8-293 DBMS_SNAPSHOT Package Subprograms
Subprogram  Description 
BEGIN_TABLE_REORGANIZATION 
procedure
 

Performs a process to preserve snapshot data needed for refresh.  

END_TABLE_REORGANIZATION
 

Ensures that the snapshot data for the master table is valid and that the master table is in the proper state.  

I_AM_A_REFRESH function
 

Returns the value of the I_AM_REFRESH package state.  

PURGE_DIRECT_LOAD_LOG 
procedure
 

Purges rows from the direct loader log after they are no longer needed by any snapshots (used with data warehousing).  

PURGE_LOG procedure
 

Purges rows from the snapshot log.  

PURGE_SNAPSHOT_FROM_LOG 
procedure
 

Purges rows from the snapshot log.  

REFRESH procedure
 

Consistently refreshes one or more snapshots that are not members of the same refresh group.  

REFRESH_ALL_MVIEWS 
procedure
 

Refreshes all snapshots that have not been refreshed because the most recent bulk load to a dependent detail table.  

REFRESH_DEPENDENT procedure
 

Refreshes all table-based snapshots that depend on a specified detail table or list of detail tables.  

REGISTER_SNAPSHOT procedure
 

Enables the administration of individual snapshots.  

UNREGISTER_SNAPSHOT 
procedure
 

Enables the administration of individual snapshots. Invoked at a master site to unregister a snapshot.  

BEGIN_TABLE_REORGANIZATION procedure

This procedure performs a process to preserve snapshot data needed for refresh. It must be called before a master table is reorganized.

Additional Information: See "Administering a Replicated Environment" in the Oracle8i Replication manual.

Syntax

DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (
   tabowner    IN   VARCHAR2
   tabname     IN   VARCHAR2);

Parameters

Table 8-294 BEGIN_TABLE_REORGANIZATION Procedure Parameters
Parameter  Description 
tabowner
 

Owner of the table being reorganized.  

tabname
 

Name of the table being reorganized.  

END_TABLE_REORGANIZATION

This procedure must be called after a master table is reorganized. It ensures that the snapshot data for the master table is valid and that the master table is in the proper state.

Additional Information: See "Administering a Replicated Environment" in the Oracle8i Replication manual.

Syntax

DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (
   tabowner    IN   VARCHAR2
   tabname     IN   VARCHAR2);

Parameters

Table 8-295 END_TABLE_REORGANIZATION Procedure Parameters
Parameter  Description 
tabowner
 

Owner of the table being reorganized.  

tabname
 

Name of the table being reorganized.  

I_AM_A_REFRESH function

This function returns the value of the I_AM_REFRESH package state.

Syntax

DBMS_SNAPSHOT.I_AM_A_REFRESH 
  RETURN BOOLEAN;

Parameters

None

Returns

A return value of TRUE indicates that all local replication triggers for snapshots are effectively disabled in this session because each replication trigger first checks this state. A return value of FALSE indicates that these triggers are enabled.

PURGE_DIRECT_LOAD_LOG procedure

This procedure remove entries from the direct loader log after they are no longer needed for any known snapshot (materialized view). This procedure will usually be used in environments using Oracle's Data Warehousing technology. For more information, see Oracle8i Tuning.

Syntax

DBMS_SNAPSHOT.PURGE_DIRECT_LOAD_LOG ();

PURGE_LOG procedure

This procedure purges rows from the snapshot log.

Syntax

DBMS_SNAPSHOT.PURGE_LOG (
   master        IN   VARCHAR2,
   num           IN   BINARY_INTEGER := 1,
   flag          IN   VARCHAR2       := 'NOP');

Parameters

Table 8-296 PURGE_LOG Procedure Parameters
Parameter  Description 
master
 

Name of the master table.  

num
 

Number of least recently refreshed snapshots whose rows you want to remove from snapshot log. For example, the following statement deletes rows needed to refresh the two least recently refreshed snapshots:

dbms_snapshot.purge_log('master_table', 2);

To delete all rows in the snapshot log, indicate a high number of snapshots to disregard, as in this example:

dbms_snapshot.purge_log('master_table',9999);

This statement completely purges the snapshot log that corresponds to MASTER_TABLE if fewer than 9999 snapshots are based on MASTER_TABLE. A simple snapshot whose rows have been purged from the snapshot log must be completely refreshed the next time it is refreshed.  

flag
 

Specify DELETE to guarantee that rows are deleted from the snapshot log for at least one snapshot. This argument can override the setting for the argument num. For example, the following statement deletes rows from the least recently refreshed snapshot that actually has dependent rows in the snapshot log:

dbms_snapshot.purge_log('master_table',1,'DELETE');  

PURGE_SNAPSHOT_FROM_LOG procedure

This procedure is called on the master site to delete the rows in snapshot refresh related data dictionary tables maintained at the master site for the specified snapshot identified by its snapshot_id or the combination of the snapowner, snapname, and the snapsite. If the snapshot specified is the oldest snapshot to have refreshed from any of the master tables, then the snapshot log is also purged. This procedure does not unregister the snapshot.

In case there is an error while purging one of the snapshot logs, the successful purge operations of the previous snapshot logs are not rolled back. This is to minimize the size of the snapshot logs. In case of an error, this procedure can be invoked again until all the snapshot logs are purged.

Syntax

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   snapshot_id   IN   BINARY_INTEGER  |
   snapowner     IN   VARCHAR2,
   snapname      IN   VARCHAR2, 
   snapsite      IN   VARCHAR2);

Parameters

Table 8-297 PURGE_SNAPSHOT_FROM_LOG Procedure Parameters
Parameter  Description 
snapshot_id
 

If you want to execute this procedure based on the ID of the target snapshot, specify the snapshot ID using the snapshot_id parameter. Query the DBA_SNAPSHOT_LOGS view at the snapshot log site for a listing of snapshot IDs.

Executing this procedure based on the snapshot ID is useful if the target snapshot is not listed in the list of registered snapshots (DBA_REGISTERED_SNAPSHOTS).

If you specify a snapshot ID, do not specify values for the snapowner, snapname, or snapsite parameters.  

snapowner
 

If do not specify a snapshot_id, enter the owner of the target snapshot using the snapowner parameter. Query the DBA_REGISTERED_SNAPSHOTS view at the snapshot log site to view the snapshot owners.  

snapname
 

If do not specify a snapshot_id, enter the name of the target snapshot using the snapname parameter. Query the DBA_REGISTERED_SNAPSHOTS view at the snapshot log site to view the snapshot names.  

snapsite
 

If do not specify a snapshot_id, enter the site of the target snapshot using the snapsite parameter. Query the DBA_REGISTERED_SNAPSHOTS view at the snapshot log site to view the snapshot sites.  

REFRESH procedure

This procedure refreshes a list of snapshots.

Syntax

DBMS_SNAPSHOT.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := TRUE,
   refresh_after_errors   IN     BOOLEAN        := FALSE,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0
   atomic_refresh         IN     BOOLEAN        := TRUE);

Parameters

Table 8-298 REFRESH Procedure Parameters
Parameter  Description 
list
tab
 

Comma-separated list of snapshots that you want to refresh. (Synonyms are not supported.) These snapshots can be located in different schemas and have different master tables; however, all of the listed snapshots must be in your local database. Alternatively, you may pass in a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a snapshot.  

method
 

A string of refresh methods indicating how to refresh the listed snapshots. `F' or `f' indicates fast refresh, `?' indicates force refresh, `C' or `c' indicates complete refresh, and `A' or `a' indicates always refresh, If a snapshot does not have a corresponding refresh method (that is, if more snapshots are specified than refresh methods), then that snapshot is refreshed according to its default refresh method. For example, the following EXECUTE statement within SQL*Plus:

dbms_snapshot.refresh
   ('s_emp,s_dept,scott.s_salary','CF');

performs a complete refresh of the S_EMP snapshot, a fast refresh of the S_DEPT snapshot, and a default refresh of the
SCOTT.S_SALARY snapshot.  

rollback_seg
 

Name of the snapshot site rollback segment to use while refreshing snapshots.  

push_deferred_rpc
 

Used by updatable snapshots only. Set this parameter to TRUE if you want to push changes from the snapshot to its associated master before refreshing the snapshot. Otherwise, these changes may appear to be temporarily lost.  

refresh_after_errors
 

If this parameter is TRUE, an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this parameter is TRUE and atomic_refresh is FALSE, this procedure will continue to refresh other snapshots if it fails while refreshing a snapshot.  

purge_option
 

If you are using the parallel propagation mechanism (in other words, parallelism is set to 1 or greater), 0 means do not purge, 1 means lazy purge, and 2 means aggressive purge. In most cases, lazy purge is the optimal setting. Set purge to aggressive to trim the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, set purge to do not purge and occasionally execute PUSH with purge set to aggressive to reduce the queue.  

parallelism
 

0 means serial propagation, n > 0 means parallel propagation with n parallel server processes, and 1 means parallel propagation using only one parallel server process.  

heap_size
 

Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Do not set this parameter unless directed to do so by Oracle Worldwide Support.  

atomic_refresh
 

If this parameter is set to TRUE, then the list of snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, none of the snapshots will be updated.

If this parameter is set to FALSE, then each of the snapshots will be refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is FALSE.

If FALSE and the Summary Management option is not purchased, then an error is raised.  

REFRESH_ALL_MVIEWS procedure

This procedure refreshes all snapshots (materialized views) with the following properties:

This procedure is intended for use with data warehouses.

Syntax

DBMS_SNAPSHOT.REFRESH_ALL_MVIEWS (
   number_of_failures     OUT   BINARY_INTEGER,
   method                 IN    VARCHAR2         := NULL,
   rollback_seg           IN    VARCHAR2         := NULL,
   refresh_after_errors   IN    BOOLEAN          := FALSE,
   atomic_refresh         IN    BOOLEAN          := TRUE);

Parameters

Table 8-299 REFRESH_ALL_MVIEWS Procedure Parameters
Parameter  Description 
number_of_failures
 

Returns the number of failures that occurred during processing.  

method
 

A single refresh method indicating the type of refresh to perform for each snapshot that is refreshed. `F' or `f' indicates fast refresh, `?' indicates force refresh, `C' or `c' indicates complete refresh, and `A' or `a' indicates always refresh. If no method is specified, a snapshot is refreshed according to its default refresh method.  

rollback_seg
 

Name of the snapshot site rollback segment to use while refreshing snapshots.  

refresh_after_errors
 

If this parameter is TRUE, an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this parameter is TRUE and atomic_refresh is FALSE, this procedure will continue to refresh other snapshots if it fails while refreshing a snapshot.  

atomic_refresh
 

If this parameter is set to TRUE, then the refreshed snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, none of the snapshots will be updated.

If this parameter is set to FALSE, then each of the refreshed snapshots will be refreshed in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is FALSE.

If FALSE and the Summary Management option is not purchased, then an error is raised.  

REFRESH_DEPENDENT procedure

This procedure refreshes all snapshots (materialized views) with the following properties:

This procedure is intended for use with data warehouses.

Syntax

DBMS_SNAPSHOT.REFRESH_DEPENDENT (
   number_of_failures     OUT    BINARY_INTEGER,
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2    := NULL,
   rollback_seg           IN     VARCHAR2    := NULL,
   refresh_after_errors   IN     BOOLEAN     := FALSE,
   atomic_refresh         IN     BOOLEAN     := TRUE);

Parameters

Table 8-300 REFRESH_DEPENDENT Procedure Parameters
Parameter  Description 
number_of_failures
 

Returns the number of failures that occurred during processing.  

list
tab
 

Comma-separated list of detail tables on which snapshots can depend. (Synonyms are not supported.) These tables and the snapshots that depend on them can be located in different schemas. However, all of the tables and snapshots must be in your local database. Alternatively, you may pass in a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a table.  

method
 

A string of refresh methods indicating how to refresh the dependent snapshots. All of the snapshots that depend on a particular table are refreshed according to the refresh method associated with that table. `F' or `f' indicates fast refresh, `?' indicates force refresh, `C' or `c' indicates complete refresh, and `A' or `a' indicates always refresh. If a table does not have a corresponding refresh method (that is, if more tables are specified than refresh methods), then any snapshot that depends on that table is refreshed according to its default refresh method. For example, the following EXECUTE statement within SQL*Plus:

dbms_snapshot.refresh_dependent
   ('emp,dept,scott.salary','CF');

performs a complete refresh of the snapshots that depend on the EMP table, a fast refresh of the snapshots that depend on the DEPT table, and a default refresh of the snapshots that depend on the SCOTT.SALARY table.  

rollback_seg
 

Name of the snapshot site rollback segment to use while refreshing snapshots.  

refresh_after_errors
 

If this parameter is TRUE, an updatable snapshot will continue to refresh even if there are outstanding conflicts logged in the DEFERROR view for the snapshot's master table. If this parameter is TRUE and atomic_refresh is FALSE, this procedure will continue to refresh other snapshots if it fails while refreshing a snapshot.  

atomic_refresh
 

If this parameter is set to TRUE, then the refreshed snapshots will be refreshed in a single transaction. All of the refreshed snapshots will be updated to a single point in time. If the refresh fails for any of the snapshots, none of the snapshots will be updated.

If this parameter is set to FALSE, then each of the refreshed snapshots will be refreshed in separate transactions. The number of job queue processes must be set to 1 or greater if this parameter is FALSE.

If FALSE and the Summary Management option is not purchased, then an error is raised.  

REGISTER_SNAPSHOT procedure

This procedure enables the administration of individual snapshots.

Syntax

DBMS_SNAPSHOT.REGISTER_SNAPSHOT (
   snapowner   IN   VARCHAR2,
   snapname    IN   VARCHAR2,
   snapsite    IN   VARCHAR2,
   snapshot_id IN   DATE | BINARY_INTEGER,
   flag        IN   BINARY_INTEGER,
   qry_txt     IN   VARCHAR2,
   rep_type    IN   BINARY_INTEGER := DBMS_SNAPSHOT.REG_UNKNOWN);


Note:

This procedure is overloaded. The snapshot_id and flag parameters are mutually exclusive.  


Parameters

Table 8-301 REGISTER_SNAPSHOT Procedure Parameters
Parameter  Description 
sowner
 

Owner of the snapshot.  

snapname
 

Name of the snapshot.  

snapsite
 

Name of the snapshot site for a snapshot registering at an Oracle8 or greater master. This should not contain any double quotes.  

snapshot_id
 

The identification number of the snapshot. Specify an Oracle8 snapshot as a BINARY_INTEGER; specify an Oracle7 snapshot registering at an Oracle8 or greater master sites as a DATE.  

flag
 

PL/SQL package variable indicating whether subsequent create or move commands are registered in the query text.  

query_txt
 

The first 32,000 bytes of the query.  

rep_type
 

Version of the snapshot. Valid constants that can be assigned include reg_uknown (the default), reg_v7_group, reg_v8_group, and reg_repapi_group.  

Usage Notes

This procedure is executed at the master site, and can be done by a remote procedure call. If REGISTER_SNAPSHOT is called multiple times with the same SNAPOWNER, SNAPNAME, and SNAPSITE, then the most recent values for SNAPSHOT_ID, FLAG, and QUERY_TXT are stored. If a query exceeds the maximum VARCHAR2 size, then QUERY_TXT contains the first 32000 characters of the query and the remainder is truncated. When invoked manually, the values of SNAPSHOT_ID and FLAG have to be looked up in the snapshot views by the person who calls the procedure.

If you do not want the snapshot query registered at the master site, then call the SET_REGISTER_QUERY_TEXT procedure with the option set to FALSE. To see the most recent setting of the option, call the GET_REG_QUERY_TEXT_FLAG function at the snapshot site before issuing the DDL.

UNREGISTER_SNAPSHOT procedure

This procedure enables the administration of individual snapshots. Invoked at a master site to unregister a snapshot.

Syntax

DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (
   snapowner      IN   VARCHAR2,
   snapname       IN   VARCHAR2,
   snapsite       IN   VARCHAR2);

Parameters

Table 8-302 UNREGISTER_SNAPSHOT Procedure Parameters
Parameters  Description 
snapowner
 

Owner of the snapshot.  

snapname
 

Name of the snapshot.  

snapsite
 

Name of the snapshot site.  




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index