Oracle8(TM) Server Replication Release 8.0 A54651-01 |
|
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 encapsulate 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:
Oracle's replication management API includes the following packages:
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;
For many procedures and functions in the replication management API, there are important prerequisites to consider. For example:
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.
The DBMS_DEFER package contains the following procedures:
The following pages discuss each procedure.
To build a deferred call to a remote procedure.
The parameters for the CALL procedure are described in Table 10-1, and the exceptions are listed in Table 10-2. The syntax for this procedure is shown below:
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 :=''})
Note: The CALL procedure is overloaded. The NODES and GROUP_NAME parameters are mutually exclusive.
To perform a transaction commit after checking for well-formed deferred remote procedure calls.
The parameter for the COMMIT_WORK procedure is described in Table 10-3, and the exception is listed in Table 10-4. The syntax for this procedure is shown below:
DBMS_DEFER.COMMIT_WORK(commit_work_comment IN VARCHAR2)
Parameter | Description |
---|---|
commit_work_ |
Up to 50 bytes to describe the transaction in the DEF$_CALL table. |
Exception | Description |
---|---|
ORA-23304 (malformedcall) |
The transaction was not correctly formed or terminated. |
To provide 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 need to call one of the following procedures in the DBMS_DEFER package for each argument to the procedure:
DBMS_DEFER.NUMBER_ARG (arg IN NUMBER)
DBMS_DEFER.DATE_ARG (arg IN DATE)
DBMS_DEFER.VARCHAR2_ARG (arg IN VARCHAR2)
DBMS_DEFER.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)
DBMS_DEFER.BFILE_ARG (arg IN BFILE)
DBMS_DEFER.CFILE_ARG (arg IN CFILE)
Parameter | Description |
---|---|
arg |
The value of the parameter that you want to pass to the remote procedure to which you previously deferred a call. |
Exception | Description |
---|---|
ORA-23323 |
The argument value is too long. |
To indicate the start of a new deferred transaction. If you omit this call, Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction.
The parameter for the TRANSACTION procedure is described in Table 10-7, and the exceptions are listed in Table 10-8. The syntax for this procedure is as follows:
DBMS_DEFER.TRANSACTION
DBMS_DEFER.TRANSACTION(nodes IN node_list_t)
Note: 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.
The DBMS_DEFER_QUERY package contains the following procedures and functions:
The following pages discuss each procedure and function.
To determine the form of an argument in a deferred call. For additional information, see page "Determining the Type of an Argument" on page 6-2.
The parameters for the GET_ARG_FORM function are described in Table 10-9, the exception is listed in Table 10-10. The syntax for this procedure is shown below:
DBMS_DEFER_QUERY.GET_ARG_FORM(
callno IN NUMBER,
arg_no IN NUMBER,
deferred_tran_id IN VARCHAR2 )
RETURN NUMBER
Exception | Description |
---|---|
NO_DATA_FOUND |
The input parameters do not correspond to a parameter of a deferred call. |
To determine the type of an argument in a deferred call. For additional information, see page "Determining the Value of an Argument" on page 6-3.
The parameters for the GET_ARG_TYPE function are described in Table 10-11, the exception is listed in Table 10-12, and the possible return values are described in Table 10-13. The syntax for this procedure is shown below:
DBMS_DEFER_QUERY.GET_ARG_TYPE(
callno IN NUMBER,
arg_no IN NUMBER,
deferred_tran_id IN VARCHAR2)
RETURN NUMBER
Exception | Description |
---|---|
NO_DATA_FOUND |
The input parameters do not correspond to a parameter of a deferred call. |
Return Value | Corresponding Datatype |
---|---|
1 |
VARCHAR2 |
2 |
NUMBER |
11 |
ROWID |
12 |
DATE |
23 |
RAW |
96 |
CHAR |
This procedure returns the text version of the various arguments for the given call.
The parameters for the GET_CALL_ARGS procedure are described in Table 10-14 and the exception is listed in Table 10-15. The syntax for this procedure is as follows:
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)
Exception | Description |
---|---|
NO_DATA_FOUND |
The input parameters do not correspond to a parameter of a deferred call. |
To determine the value of an argument in a deferred call. For additional information, see "Determining the Value of an Argument" on page 6-3.
Depending upon the type of the argument value that you want to retrieve, the syntax for the appropriate function is as follows. The parameters for these functions are described in Table 10-16, and the exceptions are listed in Table 10-17. 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 }
Exception | Description |
---|---|
NO_DATA_FOUND |
The input parameters do not correspond to a parameter of a deferred call. |
ORA-26564 |
The argument in this position is not of the specified type. |
The DBMS_DEFER_SYS package contains the following procedures and functions:
The following pages discuss each procedure and function.
To add a destination database to the DEFDEFAULTDEST view.
The parameter for the ADD_DEFAULT_DEST procedure is described in Table 10-18, and the exception is listed in Table 10-19. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.ADD_DEFAULT_DEST(dblink IN VARCHAR2)
Parameter | Description |
---|---|
dblink |
The fully qualified database name of the node that you want to add to the DEFDEFAULTDEST view. |
Exception | Description |
---|---|
ORA-23352 |
The DBLINK that you specified is already in the default list. |
To remove a destination database from the DEFDEFAULTDEST view.
The parameter for the DELETE_DEFAULT_DEST procedure is described in Table 10-20. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.DELETE_DEFAULT_DEST(dblink IN VARCHAR2)
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, no action is taken. |
To remove a destination database from the DEFSCHEDULE view.
The parameters for the DELETE_DEF_DESTINATION procedure is described in Table 10-21. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.DELETE_DEF_DESTINATION(
destination IN VARCHAR2,
force IN BOOLEAN := FALSE)
To delete a transaction from the DEFERROR view.
The parameters for the DELETE_ERROR procedure are described in Table 10-22. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.DELETE_ERROR(
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)
To delete a transaction from the DEFTRANDEST view. If there are no other DEFTRANDEST or DEFERROR entries for the transaction, the transaction is deleted from the DEFTRAN and DEFCALL views as well.
The parameters for the DELETE_TRAN procedure are described in Table 10-23. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.DELETE_TRAN(
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)
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.
The parameter for the DISABLED function is described in Table 10-24, the return values are described in Table 10-25, and the exception is described in Table 10-26. The syntax for this function is shown below:
DBMS_DEFER_SYS.DISABLED(
destination IN VARCHAR2)
RETURN BOOLEAN
Parameter | Description |
---|---|
destination |
The fully qualified database name of the node whose propagation status you want to check. |
Value | Description |
---|---|
TRUE |
Propagation to this site from the current site is disabled. |
FALSE |
Propagation to this site from the current site is enabled. |
Exception | Description |
---|---|
NO_DATA_FOUND |
DESTINATION does not appear in the DEFSCHEDULE view. |
To acquire an exclusive lock that prevents deferred transaction PUSH (either serial or parallel). This function does a commit. The lock is acquired with RELEASE_ON_COMMIT => TRUE, so that pushing of the deferred transaction queue can resume after the next commit.
The parameters and return values are shown below.
DBMS_DEFER_SYS.EXCLUDE_PUSH(
timeout IN INTEGER)
RETURN INTEGER
Table 10-27:
Value | Description |
---|---|
0 |
success, lock acquired |
1 |
timeout, no lock acquired |
2 |
deadlock, no lock acquired |
4 |
already own lock |
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.
The parameters for the EXECUTE_ERROR procedure are described in Table 10-29. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.EXECUTE_ERROR(
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)
Exception | Description |
---|---|
badparam |
Parameter value missing or invalid (for example, if destination is NULL). |
missinguser |
Invalid 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.
The parameters for the EXECUTE_ERROR_AS_USER procedure are described in Table 10-31. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER(
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)
Exception | Description |
---|---|
badparam |
Parameter value missing or invalid (for example, if destination is NULL). |
missinguser |
Invalid user. |
To purge pushed transactions from the deferred transaction queue at your current master or snapshot site.
The parameters for the PURGE function are shown in Table 10-33. The syntax for this function is shown below:
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
Exception | Description |
---|---|
argoutofrange |
A parameter value is out of a valid range. |
executiondisabled |
The execution of deferred RPCs is disabled at the destination. |
defererror |
Internal error. |
To force 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.
The parameters for the PUSH function are shown in Table 10-36. The syntax for this function is shown below:
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
Register 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).
The parameter for the REGISTER_PROPAGATOR procedure is described in Table 10-39. The syntax for this procedure is shown below, and the exceptions are listed in Table 10-40:
DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username IN VARCHAR2)
Parameter | Description |
---|---|
username |
The name of the user. |
Exception | Description |
---|---|
missinguser |
The given user does not exist |
alreadypropagator |
The given user is already the propagator. |
duplicatepropagator |
There is already a different propagator. |
To schedule a job to purge pushed transactions from the deferred transaction queue at your current master or snapshot site. You can schedule only one purge job per site.
The parameters for the SCHEDULE_PURGE procedure are shown in Table 10-41. The syntax for this procedure is shown below:
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)
To schedule a job to push the deferred transaction queue to a remote master destination. This procedure does a commit.
The parameters for the SCHEDULE_PUSH procedure are described in Table 10-42. The syntax for this procedure is shown below:
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)
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, the procedure disables propagation to the given destination and future invocations of DBMS_DEFER_SYS.EXECUTE do not push the deferred remote procedure call (RPC) queue. SET_DISABLED affects neither a session already pushing the queue to the given destination nor sessions appending to the queue with DBMS_DEFER. If the disabled parameter is FALSE, the procedure enables propagation to the given destination and, although this does not push the queue, it permits future invocations to DBMS_DEFER_SYS.EXECUTE 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.
The parameters for the SET_DISABLED procedure are described in Table 10-43 and the exception is listed in Table 10-44. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.SET_DISABLED(
destination IN VARCHAR2,
disabled IN BOOLEAN := TRUE)
Exception | Description |
---|---|
NO_DATA_FOUND |
No entry was found in the DEFSCHEDULE view for the given DESTINATION. |
To unregister a user as the propagator from the local database. This procedure
The parameters for the UNREGISTER_PROPAGATOR procedure are described in Table 10-45. The syntax for this procedure is shown below, and the exceptions are listed in Table 10-46:
DBMS_DEFER_SYS.UNREGISTER_PROPAGATOR(
username IN VARCHAR2
timeout IN INTEGER DEFAULT DBMS_LOCK.MAXWAIT)
Parameter | Description |
---|---|
username |
The name of the propagator user. |
timeout |
Timeout in seconds. If the propagator is in use, the procedure waits until timeout. The default is DBMS_LOCK.MAXWAIT. |
Parameter | Description |
---|---|
missingpropagator |
The given user is not a propagator. |
propagator_inuse |
The propagator is in use, and thus cannot be unregistered. Try later. |
To stop automatic purges of pushed transactions from the deferred transaction queue at a snapshot or master site.
The syntax for this procedure is shown below:
DBMS_DEFER_SYS.UNSCHEDULE_PURGE
To stop automatic pushes of the deferred transaction queue from a snapshot or master site to another master site.
The parameter for the UNSCHEDULE_PUSH procedure is described in Table 10-47, and the exception is described in Table 10-48. The syntax for this procedure is shown below:
DBMS_DEFER_SYS.UNSCHEDULE_PUSH(dblink IN VARCHAR2)
Parameter | Description |
---|---|
dblink |
Fully qualified pathname to master database site at which you want to unschedule periodic execution of deferred remote procedure calls. |
Exception | Description |
---|---|
NO_DATA_FOUND |
No entry was found in the DEFSCHEDULE view for the given DBLINK. |
The DBMS_OFFLINE_OG package contains the following procedures:
The following pages discuss each procedure.
To start offline instantiation of a replicated object group. You must call this procedure from the master definition site.
The parameters for the BEGIN_INSTANTIATION procedure are described in Table 10-49, and the exceptions are listed in Table 10-50. The syntax for this procedure is shown below.
DBMS_OFFLINE_OG.BEGIN_INSTANTIATION(
gname IN VARCHAR2,
new_site IN VARCHAR2)
To disable triggers while data is imported to new master site as part of offline instantiation. You must call this procedure from the new master site. See "Snapshot Cloning and Offline Instantiation" on page 7-15
The parameters for the BEGIN_LOAD procedure are described in Table 10-51, and the exceptions are listed in Table 10-52. The syntax for this procedure is shown below.
DBMS_OFFLINE_OG.BEGIN_LOAD(
gname IN VARCHAR2,
new_site IN VARCHAR2)
To complete offline instantiation of a replicated object group. You must call this procedure from the master definition site. See "Snapshot Cloning and Offline Instantiation" on page 7-15.
The parameters for the END_INSTANTIATION procedure are described in Table 10-53, and the exceptions are listed in Table 10-54. The syntax for this procedure is shown below.
DBMS_OFFLINE_OG.END_INSTANTIATION(
gname IN VARCHAR2,
new_site IN VARCHAR2)
To reenable triggers after importing data to new master site as part of offline instantiation. You must call this procedure from the new master site. For additional information, see "Snapshot Cloning and Offline Instantiation" on page 7-15.
The parameters for the END_LOAD procedure are described in Table 10-55, and the exceptions are listed in Table 10-56. The syntax for this procedure is shown below.
DBMS_OFFLINE_OG.END_LOAD(
gname IN VARCHAR2,
new_site IN VARCHAR2)
To resume replication activity at all existing sites except the new site during offline instantiation of a replicated object group. You must call this procedure from the master definition site. For additional information, see "Snapshot Cloning and Offline Instantiation" on page 7-15.
The parameters for the RESUME_SUBSET_OF_MASTERS procedure are described in Table 10-57, and the exceptions are listed in Table 10-58. The syntax for this procedure is shown below.
DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS(
gname IN VARCHAR2,
new_site IN VARCHAR2)
The DBMS_OFFLINE_SNAPSHOT package contains the following procedures:
The following pages discuss each procedure.
To prepare 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. For additional information, see "Snapshot Cloning and Offline Instantiation" on page 7-15.
The parameters for the BEGIN_LOAD procedure are described in Table 10-59, and the exceptions are listed in Table 10-60. The syntax for this procedure is shown below.
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)
To complete offline instantiation of a snapshot. You must call this procedure from the snapshot site for the new snapshot. For additional information, see "Snapshot Cloning and Offline Instantiation" on page 7-15.
The parameters for the END_LOAD procedure are described in Table 10-61, and the exceptions are listed in Table 10-62. The syntax for this procedure is shown below.
DBMS_OFFLINE_SNAPSHOT.END_LOAD(
gname IN VARCHAR2,
sname IN VARCHAR2,
snapshot_oname IN VARCHAR2)
The DBMS_RECTIFIER_DIFF package contains the following procedures:
The following pages discuss each procedure.
To determine the differences between two tables.
The parameters for the DIFFERENCES procedure are described in Table 10-63, and the exceptions are listed in Table 10-64. The syntax for this procedure is shown below.
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.
The error ORA-00001 (Unique constraint violated) is issued when there are any unique or primary key constraints on the MISSING_ROWS_DATA table.
To resolve the differences between two tables.
The parameters for the RECTIFY procedure are described in Table 10-65, and the exceptions are listed in Table 10-66. The syntax for this procedure is shown below.
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.
The DBMS_REFRESH package contains the following procedures:
The following pages discuss each procedure.
To add snapshots to a refresh group. For additional information, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameters for the ADD procedure are described in Table 10-67. The syntax for this procedure is shown below.
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.
To change the refresh interval for a snapshot group. For additional information, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameters for the CHANGE procedure are described in Table 10-68. The syntax for this procedure is shown below:
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)
To remove all of the snapshots from a refresh group and delete the refresh group. For additional information, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameter for the DESTROY procedure is described in Table 10-69. The syntax for this procedure is shown below:
DBMS_REFRESH.DESTROY(name IN VARCHAR2)
Parameter | Description |
---|---|
name |
Name of the refresh group that you want to destroy. |
To specify 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 "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameters for the MAKE procedure are described in Table 10-70. The syntax for this procedure is shown below:
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.
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, 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 your NEXT_DATE evaluates to Monday, Oracle will refresh 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. See "Example Date Expressions" on page 2-26. |
implicit_destroy |
Set this argument 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, 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 MAKE generates an error message. |
job |
This parameter is 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 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. |
purge_option |
If you are using the parallel propagation mechanism (i.e., parallelism is set to 1 or greater), 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, 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. |
To manually refresh a refresh group. For additional information, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameter for the REFRESH procedure is described in Table 10-71. The syntax for this procedure is shown below:
DBMS_REFRESH.REFRESH(name IN VARCHAR2)
Parameter | Description |
---|---|
name |
Name of the refresh group that you want to refresh manually. |
To remove snapshots from a refresh group. For additional information, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-38.
The parameters for the SUBTRACT procedure are described in Table 10-72. The syntax for this procedure is shown below:
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.
The DBMS_REPCAT package includes the following procedures and functions:
The following pages discuss each procedure and function.
To add members to an existing column group. You must call this procedure from the master definition site.
The parameters for the ADD_GROUPED_COLUMN procedure are described in Table 10-73, and the exceptions are listed in Table 10-74. The syntax for this procedure is shown below:
DBMS_REPCAT.ADD_GROUPED_COLUMN(
sname, IN VARCHAR2,
oname, IN VARCHAR2,
column_group IN VARCHAR2,
list_of_column_names IN VARCHAR2 | DBMS_REPCAT.VARCHAR2S)
To add 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.
The parameters for the ADD_MASTER_DATABASE procedure are described in Table 10-75, and the exceptions are listed in Table 10-76. The syntax for this procedure is shown below:
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')
Parameter | Description |
---|---|
gname |
The name of the object group being replicated. This object group must already exist at the master definition site. |
master |
The 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 "Replicating Object Definitions to Master Sites" on page 3-22 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 comment is added to the MASTER_COMMENT field of the RepSite view. |
propagation_mode |
Method of forwarding changes to and receiving changes from new master database. Accepted values are SYNCHRONOUS and ASYNCHRONOUS. |
To add 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 "Priority Group and Site Priority Update" on page 5-18.
The parameters for the ADD_PRIORITY_datatype procedure are described in Table 10-77, and the exceptions are listed in Table 10-78. The syntax for the ADD_PRIORITY_datatype procedure is shown below.
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 }
To add a new site to a site priority group. You must call this procedure from the master definition site. For additional information, see "Adding a Site to the Group" on page 5-44.
The parameters for the ADD_SITE_PRIORITY_SITE procedure are described in Table 10-79, and the exceptions are listed in Table 10-80. The syntax for this procedure is shown below:
DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
gname IN VARCHAR2,
name IN VARCHAR2
site IN VARCHAR2,
priority IN NUMBER)
To designate 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 additional information, see "Designating a Conflict Resolution Method" on page 5-7.
The parameters for the ADD_conflicttype_RESOLUTION procedure are described in Table 10-81, and the exceptions are listed in Table 10-82. The syntax for the ADD_UPDATE_RESOLUTION procedure is shown below:
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)
The syntax for the ADD_DELETE_RESOLUTION procedure is shown below:
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)
The syntax for the ADD_UNIQUE_RESOLUTION procedure is shown below:
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)
Parameter | Description |
---|---|
sname |
The name of the schema containing the table to be replicated. |
oname |
The name of the table for which you are adding a conflict resolution routine. |
column_group |
The 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 |
The 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 |
The order in which the designated conflict resolution methods should be applied. |
method |
The 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, so this argument is not used. |
parameter_column_ |
The 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, you should pass the name of the column containing the timestamp value as this argument. If your are using a USER FUNCTION, 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 `*', the columns will be passed to your function in alphabetical order. |
priority_group |
If you are using the PRIORITY GROUP or SITE PRIORITY update conflict resolution method, you must supply the name of the priority group that you have created. See "Priority Group and Site Priority Update" on page 5-18. 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, you must supply the name of the conflict resolution routine that you have written. If you are using one of the standard methods, you can use the default value for this argument, NULL. |
comment |
This user comment is added to the RepResolution view. |
To alter 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, ALTER_MASTER_PROPAGATION ignores that database link. You cannot change the propagation mode from a master to itself.
The parameters for the ALTER_MASTER_PROPAGATION procedure are described in Table 10-83, and the exceptions are listed in Table 10-84. The syntax for this procedure is shown below:
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.
To alter an object in your replicated environment. You must call this procedure from the master definition site.
The parameters for the ALTER_MASTER_REPOBJECT procedure are described in Table 10-85, and the exceptions are listed in Table 10-86. The syntax for this procedure is shown below:
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)
Note: If the DDL is supplied without specifying a schema, 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.
To alter the priority level associated with a given priority group member. You must call this procedure from the master definition site. See "Altering the Priority of a Member" on page 5-40.
The parameters for the ALTER_PRIORITY procedure are described in Table 10-87, and the exceptions are listed in Table 10-88. The syntax for this procedure is shown below:
DBMS_REPCAT.ALTER_PRIORITY(
gname IN VARCHAR2,
pgroup IN VARCHAR2,
old_priority IN NUMBER,
new_priority IN NUMBER)
To alter 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 "Altering the Value of a Member" on page 5-39.
The parameters for the ALTER_PRIORITY_datatype procedure are described in Table 10-89, and the exceptions are listed in Table 10-90. The syntax for the ALTER_PRIORITY_datatype procedure is shown below.
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 }
To alter the priority level associated with a given site. You must call this procedure from the master definition site. See "Altering the Priority Level of a Site" on page 5-44.
The parameters for the ALTER_SITE_PRIORITY procedure are described in Table 10-91, and the exceptions are listed in Table 10-92. The syntax for this procedure is shown below:
DBMS_REPCAT.ALTER_SITE_PRIORITY(
gname IN VARCHAR2,
name IN VARCHAR2
old_priority IN NUMBER,
new_priority IN NUMBER)
To alter the site associated with a given priority level. You must call this procedure from the master definition site. See "Altering the Site Associated with a Priority Level" on page 5-45.
The parameters for the ALTER_SITE_PRIORITY_SITE procedure are described in Table 10-93, and the exceptions are listed in Table 10-94. The syntax for this procedure is shown below:
DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE(
gname IN VARCHAR2,
name IN VARCHAR2
old_site IN VARCHAR2,
new_site IN VARCHAR2)
To alter 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.
The parameters for the ALTER_SNAPSHOT_PROPAGATION procedure are described in Table 10-95, and the exceptions are listed in Table 10-96. The syntax for this procedure is shown below:
DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION(
gname IN VARCHAR2,
propagation_mode IN VARCHAR2,
comment IN VARCHAR2 := '')
Table 10-95: Parameters for ALTER_SNAPSHOT_PROPAGATION
To stop collecting statistics about the successful resolution of update, uniqueness, and delete conflicts for a table.
The parameters for the CANCEL_STATISTICS procedure are described in Table 10-97, and the exceptions are listed in Table 10-98. The syntax for this procedure is shown below:
DBMS_REPCAT.CANCEL_STATISTICS(
sname IN VARCHAR2,
oname IN VARCHAR2)
Parameter | Description |
---|---|
sname |
The name of the schema in which the table is located. |
oname |
The name of the table for which you do not want to gather conflict resolution statistics. |
Exception | Description |
---|---|
missingschema |
The given schema does not exist. |
missingobject |
The given table does not exist. |
statnotreg |
The given table is not currently registered to collect statistics. |
To update 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.
The parameters for the COMMENT_ON_COLUMN_GROUP procedure are described in Table 10-99, and the exceptions are listed in Table 10-100. The syntax for this procedure is shown below:
DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
comment IN VARCHAR2)
Exception | Description |
---|---|
nonmasterdef |
The invocation site is not the master definition site. |
missinggroup |
The given column group does not exist. |
missingobj |
The object is missing. |
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 DBMS_REPCAT.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.
The parameters for the COMMENT_ON_PRIORITY_GROUP and COMMENT_ON_SITE_PRIORITY procedures are described in Table 10-101, and the exceptions are listed in Table 10-102.
The syntax for the COMMENT_ON_PRIORITY_GROUP procedure is shown below:
DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP(
gname IN VARCHAR2,
pgroup IN VARCHAR2,
comment IN VARCHAR2)
The syntax for the COMMENT_ON_SITE_PRIORITY procedure is shown below:
DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY(
gname IN VARCHAR2,
name IN VARCHAR2,
comment IN VARCHAR2)
To update the comment field in the REPGROUP view for a replicated object group. This procedure must be issued at the master definition site.
The parameters for the COMMENT_ON_REPGROUP procedure are described in Table 10-103, and the exceptions are listed in Table 10-104. The syntax for this procedure is shown below:
DBMS_REPCAT.COMMENT_ON_REPGROUP(
gname IN VARCHAR2,
comment IN VARCHAR2)
Parameter | Description |
---|---|
gname |
The name of the object group that you want to comment on. |
comment |
The updated comment to include in the SCHEMA_COMMENT field of the RepGroup view. |
Exception | Description |
---|---|
nonmasterdef |
The invocation site is not the master definition site. |
commfailure |
At least one master site is not accessible. |
To update the comment field in the RepSite view for a replicated site. This procedure must be issued at the master definition site.
The parameters for the COMMENT_ON_REPSITES procedure are described in Table 10-105, and the exceptions are listed in Table 10-106. The syntax for this procedure is shown below:
DBMS_REPCAT.COMMENT_ON_REPSITES(
gname IN VARCHAR2,
[ master IN VARCHAR,]
comment IN VARCHAR2)
Exception | Description |
---|---|
nonmasterdef |
The invocation site is not the master definition site. |
nonmaster |
The invocation site is not a master site. |
commfailure |
At least one master site is not accessible. |
To update the comment field in the RepObject view for a replicated object. This procedure must be issued at the master definition site.
The parameters for the COMMENT_ON_REPOBJECT procedure are described in Table 10-107, and the exceptions are listed in Table 10-108. The syntax for this procedure is shown below:
DBMS_REPCAT.COMMENT_ON_REPOBJECT(
sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
comment IN VARCHAR2)
To update 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 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.
The parameters for the COMMENT_ON_conflicttype_RESOLUTION procedures are described in Table 10-109, and the exceptions are listed in Table 10-110.
The syntax for the COMMENT_ON_UPDATE_RESOLUTION procedure is shown below:
DBMS_REPCAT.COMMENT_ON_UPDATE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
sequence_no IN NUMBER,
comment IN VARCHAR2)
The syntax for the COMMENT_ON_UNIQUE_RESOLUTION procedure is shown below:
DBMS_REPCAT.COMMENT_ON_UNIQUE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
constraint_name IN VARCHAR2,
sequence_no IN NUMBER,
comment IN VARCHAR2)
The syntax for the COMMENT_ON_DELETE_RESOLUTION procedure is shown below:
DBMS_REPCAT.COMMENT_ON_DELETE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
sequence_no IN NUMBER,
comment IN VARCHAR2)
To create a new, empty, quiesced master replication object group.
The parameters for the CREATE_MASTER_REPGROUP procedure are described in Table 10-111, and the exceptions are listed in Table 10-112. The syntax for this procedure is shown below:
DBMS_REPCAT.CREATE_MASTER_REPGROUP(
gname IN VARCHAR2,
group_comment IN VARCHAR2 := '',
master_comment IN VARCHAR2 := ''),
qualifier IN VARCHAR2 := '')
Parameter | Description |
---|---|
gname |
The name of the object group that you want to create. |
group_comment |
This comment is added to the RepCat view. |
master_comment |
This comment is added to the RepGroup view. |
qualifier |
Connection qualifier for object group. Be sure to use the @ sign, as shown in the example: See "Using Connection Qualifiers for a Master Group" on page 3-17. |
To indicate that an object is a replicated object.
The parameters for the CREATE_MASTER_REPOBJECT procedure are shown in Table 10-113, and the exceptions are listed in Table 10-114. The syntax for this procedure is shown below:
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 := '')
Note: If the DDL is supplied without specifying a schema, 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.
.
Parameters | Description |
---|---|
sname |
The name of the schema in which the object that you want to replicate is located. |
oname |
The name of the object you are replicating. If DDL_TEXT is NULL, this object must already exist in the given schema. To ensure uniqueness, table names should be a maximum of 27 bytes long, and packages should be no more than 24 bytes. |
type |
The 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 10-116 for more information on how these changes are applied. |
ddl_text |
If the object does not already exist at the master definition site, 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 will be 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 RETRY if the error was transient or has since been rectified; for example, if you previously had insufficient resources. If RETRY is TRUE, 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 10-116 for more information. |
gname |
The 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. |
To create a new, empty snapshot replication object group in your local database.
The parameters for the CREATE_SNAPSHOT_REPGROUP procedure are described in Table 10-116, and the procedures are listed in Table 10-117. The syntax for this procedure is shown below:
DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP(
gname IN VARCHAR2,
master IN VARCHAR2,
comment IN VARCHAR2 := '',
propagation_mode IN VARCHAR2 := 'ASYNCHRONOUS')
Note: CREATE_SNAPSHOT_REPGROUP automatically calls DBMS_REPCAT.REGISTER_SNAPSHOT_REPGROUP, but ignores any errors that may have happened during registration.
Table 10-116: Parameters for CREATE_SNAPSHOT_REPGROUP
To add a replicated object to your snapshot site.
The parameters for the CREATE_SNAPSHOT_REPOBJECT procedure are shown in Table 10-118, and the exceptions are listed in Table 10-119. The syntax for this procedure is shown below:
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 )
Note: If the DDL is supplied without specifying a schema, 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.
Parameter | Description |
---|---|
sname |
The name of the schema in which the object is located. |
oname |
The name of the object that you want to add to the replicated snapshot object group. ONAME must exist at the associated master site. |
type |
The type of the object that you are replicating. The types supported for snapshot sites are: PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, SNAPSHOT, SYNONYM, 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, 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 object group of the master site designated for this schema, Oracle raises a missingobject error. |
comment |
This comment is added to the OBJECT_COMMENT field of the RepObject view. |
gname |
The name of the replicated object 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 |
The 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 when you want propagation of new and old values to be minimized. The default is TRUE. See "Minimizing Data Propagation" on page 7-30. |
To create an empty column group. You must call this procedure from the master definition site. See "Understanding Column Groups" on page 5-3.
The parameters for the DEFINE_COLUMN_GROUP procedure are described in Table 10-120, and the exceptions are listed in Table 10-121. The syntax for this procedure is shown below:
DBMS_REPCAT.DEFINE_COLUMN_GROUP(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
comment IN VARCHAR2 := NULL)
To create a new priority group for a replicated object group. You must call this procedure from the master definition site. See "Priority Group and Site Priority Update" on page 5-18.
The parameters for the DEFINE_PRIORITY_GROUP procedure are described in Table 10-122, and the exceptions are listed in Table 10-123. The syntax for this procedure is shown below:
DBMS_REPCAT.DEFINE_PRIORITY_GROUP(
gname IN VARCHAR2,
pgroup IN VARCHAR2,
datatype IN VARCHAR2,
fixed_length IN INTEGER := NULL,
comment IN VARCHAR2 := NULL)
To create a new site priority group for a replicated object group. You must call this procedure from the master definition site. See "Priority Group and Site Priority Update" on page 5-18.
The parameters for the DEFINE_SITE_PRIORITY procedure are described in Table 10-124, and the exceptions are listed in Table 10-125. The syntax for this procedure is shown below:
DBMS_REPCAT.DEFINE_SITE_PRIORITY(
gname IN VARCHAR2,
name IN VARCHAR2,
comment IN VARCHAR2 := NULL)
To execute the local outstanding deferred administrative procedures for the given replicated object group at the current master site, or (with assistance from job queues) for all master sites.
Note: 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.
The parameters for the DO_DEFERRED_REPCAT_ADMIN procedure are described in Table 10-126, and the exceptions are listed in Table 10-127. The syntax for this procedure is shown below:
DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN(
gname IN VARCHAR2,
all_sites IN BOOLEAN := FALSE)
Parameter | Description |
---|---|
gname |
The name of the replicated object group. |
all_sites |
If ALL_SITES is TRUE, use a job to execute the local administrative procedures at each master. |
Exception | Description |
---|---|
nonmaster |
The invocation site is not a master site. |
commfailure |
At least one master site is not accessible and all_sites is TRUE. |
To drop a column group. You must call this procedure from the master definition site. See "Dropping a Column Group" on page 5-30.
The parameters for the DROP_COLUMN_GROUP procedure are described in Table 10-128, and the exceptions are listed in Table 10-129. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_COLUMN_GROUP(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2)
To remove members from a column group. You must call this procedure from the master definition site. See "Removing Members from a Column Group" on page 5-30.
The parameters for the DROP_GROUPED_COLUMN procedure are described in Table 10-130, and the exceptions are listed in Table 10-131. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_GROUPED_COLUMN(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
list_of_column_names IN VARCHAR2 | DBMS_REPCAT.VARCHAR2S)
To drop a replicated object group from your current site. To drop the replicated object 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.
The parameters for the DROP_MASTER_REPGROUP procedure are described in Table 10-132, and the exceptions are listed in Table 10-133. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_MASTER_REPGROUP(
gname IN VARCHAR2,
drop_contents IN BOOLEAN := FALSE,
all_sites IN BOOLEAN := FALSE)
To drop a replicated object from a replicated object group. You must call this procedure from the master definition site.
The parameters for the DROP_MASTER_REPOBJECT procedure are described in Table 10-134, and the exceptions are listed in Table 10-135. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_MASTER_REPOBJECT(
sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
drop_objects IN BOOLEAN := FALSE)
To drop a member of a priority group by priority level. You must call this procedure from the master definition site. See "Dropping a Member by Priority" on page 5-41.
The parameters for the DROP_PRIORITY procedure are described in Table 10-136, and the exceptions are listed in Table 10-137. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_PRIORITY(
gname IN VARCHAR2,
pgroup IN VARCHAR2,
priority_num IN NUMBER)
To drop a priority group for a given replicated object group. You must call this procedure from the master definition site. See "Dropping a Priority Group" on page 5-41.
The parameters for the DROP_PRIORITY_GROUP procedure are described in Table 10-138, and the exceptions are listed in Table 10-139. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_PRIORITY_GROUP(
gname IN VARCHAR2,
pgroup IN VARCHAR2)
Parameter | Description |
---|---|
gname |
The replicated object group with which the priority group is associated. |
pgroup |
The name of the priority group that you want to drop. |
To drop 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 "Dropping a Member by Value" on page 5-40.
The parameters for the DROP_PRIORITY_datatype procedure are described in Table 10-140, and the exceptions are listed in Table 10-141. The syntax for the DROP_PRIORITY_datatype procedure is shown below.
DBMS_REPCAT.DROP_PRIORITY_datatype(
gname IN VARCHAR2,
pgroup IN VARCHAR2,
value IN datatype)
where datatype:
{ NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| NCHAR
| NVARCHAR2 }
To drop a site priority group for a given replicated object group. You must call this procedure from the master definition site. See "Dropping a Site Priority Group" on page 5-46.
The parameters for the DROP_SITE_PRIORITY procedure are described in Table 10-142, and the exceptions are listed in Table 10-143. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_SITE_PRIORITY(
gname IN VARCHAR2,
name IN VARCHAR2)
Parameter | Description |
---|---|
gname |
The replicated object group with which the site priority group is associated. |
name |
The name of the site priority group that you want to drop. |
To drop a given site, by name, from a site priority group. You must call this procedure from the master definition site. See "Dropping a Site by Site Name" on page 5-45.
The parameters for the DROP_SITE_PRIORITY_SITE procedure are described in Table 10-144, and the exceptions are listed in Table 10-145. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_SITE_PRIORITY_SITE(
gname IN VARCHAR2,
name IN VARCHAR2,
site IN VARCHAR2)
To drop a snapshot site from your replicated environment.
The parameters for the DROP_SNAPSHOT_REPGROUP procedure are described in Table 10-146, and the exceptions are listed in Table 10-147. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP(
gname IN VARCHAR2,
drop_contents IN BOOLEAN := FALSE)
Note: DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP automatically calls DBMS_REPCAT.REGISTER_SNAPSHOT_ REPGROUP to unregister the snapshot, but ignores any errors that may have occurred during unregistration.
Exception | Description |
---|---|
nonsnapshot |
The invocation site is not a snapshot site. |
missrepgrp |
The specified object group does not exist. |
To drop a replicated object from a snapshot site.
The parameters for the DROP_SNAPSHOT_REPOBJECT procedure are described in Table 10-148, and the exceptions are listed in Table 10-149. The syntax for this procedure is shown below:
DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT(
sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
drop_objects IN BOOLEAN := FALSE)
Exception | Description |
---|---|
nonsnapshot |
The invocation site is not a snapshot site. |
missingobject |
The given object does not exist. |
typefailure |
The given type parameter is not supported. |
To drop 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 |
The parameters for the DROP_conflicttype_RESOLUTION procedure are described in Table 10-150, and the exceptions are listed in Table 10-151. The syntax for the DROP_UPDATE_RESOLUTION procedure is shown below:
DBMS_REPCAT.DROP_UPDATE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
sequence_no IN NUMBER)
The syntax for the DROP_DELETE_RESOLUTION procedure is shown below:
DBMS_REPCAT.DROP_DELETE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
sequence_no IN NUMBER)
The syntax for the DROP_UNIQUE_RESOLUTION procedure is shown below:
DBMS_REPCAT.DROP_UNIQUE_RESOLUTION(
sname IN VARCHAR2,
oname IN VARCHAR2,
constraint_name IN VARCHAR2,
sequence_no IN NUMBER)
To supply DDL that you want to have executed at each master site. You can call this procedure only from the master definition site.
The parameters for the EXECUTE_DDL procedure are described in Table 10-152, and the exceptions are listed in Table 10-153. The syntax for this procedure is shown below:
DBMS_REPCAT.EXECUTE_DDL(
gname IN VARCHAR2,
{ master_list IN VARCHAR2 := NULL,
| master_table IN DBMS_UTILITY.DBLINK_ARRAY,}
DDL_TEXT IN VARCHAR2)
Note: If the DDL is supplied without specifying a schema, 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.
To provide more fine-grained control of replication support generation. Primarily used for environments that include Oracle7 Release 7.3 sites. Generates the packages needed to support replication for a given table at all master sites. You must call this procedure from the master definition site.
The parameters for the GENERATE_REPLICATION_PACKAGE procedure are described in Table 10-154, and the exceptions are listed in Table 10-155. The syntax for this procedure is shown below:
DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE(
sname IN VARCHAR2,
oname IN VARCHAR2)
Parameter | Description |
---|---|
sname |
The schema in which the table is located. |
oname |
The name of the table for which you are generating replication support. |
To generate the triggers, packages, and procedures needed to support replication. You must call this procedure from the master definition site.
The parameters for the GENERATE_REPLICATION_SUPPORT procedure are described in Table 10-156, and the exceptions are listed in Table 10-157. The syntax for this procedure is shown below:
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 )
Parameter | Description |
---|---|
sname |
The schema in which the object is located. |
oname |
The name of the object for which you are generating replication support. |
type |
The 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 PROCEDURE, PACKAGE or PACKAGE BODY, this value is prepended to the generated wrapper procedure names. By default, no prefix is assigned. The default is DEFER_. |
distributed |
This parameter must be set to TRUE if your COMPATIBLE parameter is set to 7.3.0 or greater. |
gen_objs_owner |
The name of the user you want to use as owner of the transaction. |
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. See "Minimizing Data Propagation" on page 7-30. |
To provide more fine-grained control of replication support generation. Primarily used for environments that include Oracle7 Release 7.3 sites. Generates the triggers and their associated packages needed to support replication for a given object at all master sites, or to generate the triggers and their associated packages needed to support replication for all of the objects in a given object group at a list of master sites. You must call this procedure from the master definition site. The associated object group must be quiesced.
The parameters for the GENERATE_REPLICATION_TRIGGER procedure are described in Table 10-158, and the exceptions are listed in Table 10-159. This syntax for this procedure is shown below:
DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
sname IN VARCHAR2,
oname IN VARCHAR2,
gen_objs_owner IN VARCHAR2 := NULL
min_communication IN BOOLEAN := TRUE) DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
gname IN VARCHAR2,
gen_objs_owner IN VARCHAR2 := NULL
min_communication IN BOOLEAN := NULL)
Attention: The GENERATE_REPLICATION_TRIGGER procedure is overloaded to allow you to generate support for a single object at all master sites or for an object group at a list of sites. Because the parameter types are the same for both calls, you may need to use named notation to indicate whether you are calling the procedure for a single object or for an object group.
Attention: If you want to generate support for a list of master sites (that is, if you will not be using the default, NULL), you must use either an array or named notation.
Parameter | Description |
---|---|
sname |
The schema in which the object is located. |
oname |
The name of the object for which you are generating replication support. |
gname |
The name of the object group for which you want to generate support. |
gen_objs_owner |
This parameter is provided for compatibility with previous releases. If you have any pre-release 7.3 snapshot sites, you must set this parameter to TRUE. |
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 varies. See "Minimizing Data Propagation" on page 7-30. |
After altering the propagation mode of an object group, you need to regenerate the supporting PL/SQL triggers for these objects at each Oracle7 Release 7.3 site in the replicated environment. Internal triggers automatically change propagation mode as directed by ALTER_MASTER_PROPAGATION and are not affected by attempts to regenerate triggers.
To generate the supporting PL/SQL triggers and their associated packages for all Oracle7 release 7.3 members of an object group for a given set of master sites, call the DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER procedure, as shown in the following example:
DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER( gname => 'acct');
Because no list of master sites is specified in this example, Oracle regenerates the supporting triggers and their associated packages for the objects in the GNAME object group at all Oracle7 release 7.3 master sites. You must call this procedure from the master definition site for the given replicated object group. Oracle must successfully create the necessary triggers at the master definition site for this procedure to complete successfully. These objects are asynchronously created at the other master sites as described
To activate triggers and generate packages needed to support the replication of updatable snapshots or procedural replication.You must call this procedure from the snapshot site.
The parameters for the GENERATE_SNAPSHOT_SUPPORT procedure are described in Table 10-160, and the exceptions are listed in Table 10-161. The syntax for this procedure is shown below:
DBMS_REPCAT.GENERATE_SNAPSHOT_SUPPORT
sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
gen_objs_owner IN VARCHAR2 := '',
min_communication IN BOOLEAN := TRUE)
Note: CREATE_SNAPSHOT_REPOBJECT automatically generates snapshot support for updatable snapshots.
To create a new column group with one or more members. You must call this procedure from the master definition site. See "Creating a Column Group with Members" on page 5-29.
The parameters for the MAKE_COLUMN_GROUP procedure are described in Table 10-162, and the exceptions are listed in Table 10-163. The syntax for this procedure is shown below:
DBMS_REPCAT.MAKE_COLUMN_GROUP(
sname IN VARCHAR2,
oname IN VARCHAR2,
column_group IN VARCHAR2,
list_of_column_names IN VARCHAR2 | DBMS_REPCAT.VARCHAR2S)
To remove local messages in the RepCatLog associated with a given identification number, source, or replicated object group.
The parameters for the PURGE_MASTER_LOG procedure are described in Table 10-164, and the exception is listed in Table 10-165. If any parameter is NULL, Oracle treats it as a wildcard. The syntax for this procedure is shown below:
DBMS_REPCAT.PURGE_MASTER_LOG(
id IN NATURAL,
source IN VARCHAR2,
gname IN VARCHAR2)
Exception | Description |
---|---|
nonmaster |
GNAME is not NULL and the invocation site is not a master site. |
To remove information from the RepResolution_Statistics view.
The parameters for the PURGE_STATISTICS procedure are described in Table 10-166, and the exceptions are listed in Table 10-167. The syntax for this procedure is shown below:
DBMS_REPCAT.PURGE_STATISTICS(
sname IN VARCHAR2,
oname IN VARCHAR2,
start_date IN DATE,
end_date IN DATE)
Exception | Description |
---|---|
missingschema |
The given schema does not exist. |
missingobject |
The given table does not exist. |
statnotreg |
Table not registered to collect statistics. |
To refresh a snapshot site object group with the most recent data from its associated master site.
The parameters for the REFRESH_SNAPSHOT_REPGROUP procedure are described in Table 10-168, and the exceptions are listed in Table 10-169. The syntax for this procedure is shown below:
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)
To facilitate the administration of snapshots at their respective master sites by inserting/modifying/deleting from repcat_repsite.
The parameters for REGISTER_SNAPSHOT_REPGROUP are described in Table 10-170. The syntax for the REGISTER_SNAPSHOT_REPGROUP procedure is shown below.
DBMS_REPCAT.REGISTER_SNAPGROUP_REPGROUP(
gname IN VARCHAR2,
snapsite IN VARCHAR2,
comment IN VARCHAR2 := NULL,
rep_type IN NUMBER := reg_unknown)
Table 10-171: Exceptions for REGISTER_SNAPSHOT_REPGROUP
To collect information about the successful resolution of update, delete and uniqueness conflicts for a table.
The parameters for the REGISTER_STATISTICS procedure are described in Table 10-172, and the exceptions are listed in Table 10-173. The syntax for this procedure is shown below:
DBMS_REPCAT.REGISTER_STATISTICS(
sname IN VARCHAR2,
oname IN VARCHAR2)
Parameter | Description |
---|---|
sname |
The name of the schema in which the table is located. |
oname |
The name of the table for which you want to gather conflict resolution statistics. |
Exception | Description |
---|---|
missingschema |
The given schema does not exist. |
missingobject |
The given table does not exist. |
To change your master definition site to another master site in your replicated environment.
The parameters for the RELOCATE_MASTERDEF procedure are described in Table 10-174, and the exceptions are listed in Table 10-175. The syntax for this procedure is shown below:
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)
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, you should invoke RELOCATE_MASTERDEF with NOTIFY_MASTERS TRUE and INCLUDE_OLD_MASTERDEF FALSE. If several master sites and the master definition site fail, the administrator should invoke RELOCATE_MASTERDEF at each operational master with NOTIFY_MASTERS FALSE.
To remove 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.
The parameters for the REMOVE_MASTER_DATABASES procedure are described in Table 10-176, and the exceptions are listed in Table 10-177. The syntax for this procedure is shown below:
DBMS_REPCAT.REMOVE_MASTER_DATABASES(
gname IN VARCHAR2,
master_list IN VARCHAR2 |
master_table IN DBMS_UTILITY.DBLINK_ARRAY)
To ensure that the objects in the replicated object 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.
The parameters for the REPCAT_IMPORT_CHECK procedure are described in Table 10-178, and the exceptions are listed in Table 10-179. The syntax for this procedure is shown below:
DBMS_REPCAT.REPCAT_IMPORT_CHECK(
gname IN VARCHAR2,
master IN BOOLEAN)
To resume normal replication activity after quiescing a replicated environment.
The parameters for the RESUME_MASTER_ACTIVITY procedure are described in Table 10-180, and the exceptions are listed in Table 10-181. The syntax for this procedure is shown below:
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname IN VARCHAR2,
override IN BOOLEAN := FALSE)
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_AND_COMPARE_OLD_VALUES at the master definition site.
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, 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.
Caution: Read "Minimizing Data Propagation" on page 7-30 before changing the default behavior of Oracle.
The parameters for the SEND_AND_COMPARE_OLD_VALUES procedure are described in Table 10-182, and the exceptions are listed in Table 10-183. The syntax for this procedure is shown below:
DBMS_REPCAT.SEND_AND_COMPARE_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.
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 "Designating an Alternate Key for a Replicated Table" on page 3-21
The parameters for the SET_COLUMNS procedure are described in Table 10-184, and the exceptions are listed in Table 10-185. The syntax for this procedure is shown below:
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.
To suspend replication activity for an object group. You must call this procedure from the master definition site.
Note: The current implementation of SUSPEND_MASTER_ACTIVITY quiesces all replicated object groups at each master site.
The parameter for the SUSPEND_MASTER_ACTIVITY procedure is described in Table 10-186, and the exceptions are listed in Table 10-187. The syntax for this procedure is shown below:
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(gname IN VARCHAR2)
Parameter | Description |
---|---|
gname |
The name of the object group for which you want to suspend activity. |
To change the master database of a snapshot replicated object 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.
The parameters for the SWITCH_SNAPSHOT_MASTER procedure are described in Table 10-188, and the exceptions are listed in Table 10-189. The syntax for this procedure is shown below:
DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER(
gname IN VARCHAR2,
master IN VARCHAR2)
Exception | Description |
---|---|
nonsnapshot |
The invocation site is not a snapshot site. |
nonmaster |
The given database is not a master site. |
commfailure |
The given database is not accessible. |
To facilitate the administration of snapshots at their respective master sites by inserting/modifying/deleting from repcat$_repsite.
The parameters for UNREGISTER_SNAPSHOT_REPGROUP are described in Table 10-190. The syntax for the UNREGISTER_SNAPSHOT_REPGROUP procedure is shown below.
DBMS_REPCAT.UNREGISTER_SNAPGROUP_REPGROUP(
gname IN VARCHAR2,
snapsite IN VARCHAR2)
Parameter | Description |
---|---|
gname |
The name of the snapshot object group to be unregistered. |
snapsite |
Global name of the snapshot site. |
To validate the correctness of key conditions of a multiple master replication environment, you can execute the VALIDATE procedure, which is overloaded.
The parameters for the VALIDATE function are described in Table 10-191 and the exceptions are described in Table 10-192. The syntax for this procedure is shown below:
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
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.
To determine whether changes that were asynchronously propagated to a master site have been applied.
The parameters for the WAIT_MASTER_LOG procedure are described in Table 10-193, and the exception is listed in Table 10-194. The syntax for this procedure is shown below:
DBMS_REPCAT.WAIT_MASTER_LOG(
gname IN VARCHAR2,
record_count IN NATURAL,
timeout IN NATURAL,
true_count OUT NATURAL)
Exception | Description |
---|---|
nonmaster |
The invocation site is not a master site. |
The DBMS_REPCAT_ADMIN package contains the following procedures:
The following pages discuss each procedure.
To grant the necessary privileges to the replication administrator to administer any replicated object group at the current site.
The parameter for the GRANT_ADMIN_ANY_SCHEMA procedure is described in Table 10-195, and the exception is described in Table 10-196. The syntax for this procedure is shown below:
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username IN VARCHAR2)
Parameter | Description |
---|---|
username |
The name of the replication administrator to whom you want to grant the necessary privileges and roles to administer any replicated object groups at the current site. |
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
To grant 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.
The parameter for the GRANT_ADMIN_REPSCHEMA procedure is described in Table 10-197, and the exception is described in Table 10-198. The syntax for this procedure is shown below:
DBMS_REPCAT_ADMIN.GRANT_ADMIN_SCHEMA(username IN VARCHAR2)
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
To revoke the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_ANY_SCHEMA.
Attention: Identical privileges and roles that were granted independently of GRANT_ADMIN_ANY_SCHEMA are also revoked.
The parameter for the REVOKE_ADMIN_ANY_SCHEMA procedure is described in Table 10-199, and the exception is described in Table 10-200. The syntax for this procedure is shown below:
DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_SCHEMA(username IN VARCHAR2)
Parameter | Description |
---|---|
username |
The name of the replication administrator whose privileges you want to revoke. |
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
To revoke the privileges and roles from the replication administrator that would be granted by GRANT_ADMIN_SCHEMA.
Attention: Identical privileges and roles that were granted independently of GRANT_ADMIN_SCHEMA are also revoked.
The parameter for the REVOKE_ADMIN_SCHEMA procedure is described in Table 10-201, and the exception is described in Table 10-202. The syntax for this procedure is shown below:
DBMS_REPCAT_ADMIN.REVOKE_ADMIN_SCHEMA(username IN VARCHAR2)
Parameter | Description |
---|---|
username |
The name of the replication administrator whose privileges you want to revoke. |
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
The DBMS_REPCAT_AUTH package contains the following procedures:
The following pages discuss each procedure.
To grant the privileges needed by the advanced replication facility to a user.
The parameter for the GRANT_SURROGATE_REPCAT procedure is described in Table 10-203, and the exception is described in Table 10-204. The syntax for this procedure is shown below:
DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid IN VARCHAR2)
Parameter | Description |
---|---|
userid |
The name of the user to whom you wish to grant the necessary privileges. |
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
To revoke the privileges granted to the surrogate repcat user.
The parameters for the REVOKE_SURROGATE_REPCAT procedure is described in Table 10-205, and the exception is described in Table 10-206. The syntax for this procedure is shown below:
DBMS_REPCAT_AUTH.REVOKE_SURROGATE_REPCAT(userid IN VARCHAR2)
Parameter | Description |
---|---|
userid |
The name of the user from whom you wish to revoke the necessary privileges. |
Exception | Description |
---|---|
ORA-01917 |
The user does not exist. |
The DBMS_REPUTIL package contains the following procedures:
The following pages discuss each procedure.
To modify tables without replicating the modifications to any other sites in the replicated environment, or to disable 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.
The syntax for the REPLICATION_OFF procedure is shown below. This procedure takes no arguments.
DBMS_REPUTIL.REPLICATION_OFF
To reenable replication of changes after replication has been temporarily suspended by calling REPLICATION_OFF.
The syntax for the REPLICATION_ON procedure is shown below. This procedure takes no arguments.
DBMS_REPUTIL.REPLICATION_ON
The DBMS_SNAPSHOT package contains the following procedures and one function:
This procedure must be called before a master table is reorganized. It performs process to preserve snapshot data needed for refresh.
Additional Information: See "Reorganizing Master Tables that Have Snapshot Logs" on page 2-31.
The parameters for the BEGIN_TABLE_REORGANIZATION procedure are described in Table 10-207. The syntax for BEGIN_TABLE_REORGANIZATION is shown below.
DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION(
tabowner IN VARCHAR2
tabname IN VARCHAR2)
Parameter | Description |
---|---|
tabowner |
The owner of the table being reorganized. |
tabname |
The name of the table being reorganized. |
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 "Reorganizing Master Tables that Have Snapshot Logs" on page 2-31.
The parameters for END_TABLE_REORGANIZATION are described in Table 10-208. The syntax for END_TABLE_REORGANIZATION is shown below.
DBMS_SNAPSHOT.END_TABLE_REORGANIZATION(
tabowner IN VARCHAR2
tabname IN VARCHAR2)
Parameter | Description |
---|---|
tabowner |
The owner of the table being reorganized. |
tabname |
The name of the table being reorganized. |
To return the value of the I_AM_REFRESH package state.
The I_AM_A_REFRESH function takes no arguments. A return value of TRUE indicates that all local replication triggers for snapshots will be effectively disabled in this session because each replication trigger first checks this state. A return value of FALSE indicates that these triggers are enabled. The syntax for this procedure is shown below.
DBMS_SNAPSHOT.I_AM_A_REFRESH RETURN BOOLEAN
To purge rows from the snapshot log.
The parameters for the PURGE_LOG procedure are described in Table 10-209. The syntax for this procedure is shown below:
DBMS_SNAPSHOT.PURGE_LOG(
master IN VARCHAR2,
num IN BINARY_INTEGER := 1,
flag IN VARCHAR2 := 'NOP')
To consistently refresh one or more snapshots that are not members of the same refresh group. For additional information, see page Table 10-210.
The parameters for the REFRESH procedure are described in Table 10-210. The syntax for this procedure is shown below:
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)
To enable the administration of individual snapshots.
The parameters for the REGISTER_SNAPSHOT procedure are described in Table 10-211. The syntax for the REGISTER_SNAPSHOT procedure is shown below.
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.
Note: 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, the most recent values for SNAPSHOT_ID, FLAG, and QUERY_TXT are stored. If a query exceeds the maximum VARCHAR2 size, 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.
Note: If you do NOT want the snapshot query registered at the master site, 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.
To set the I_AM_REFRESH package state to the appropriate value.
The parameter for the SET_I_AM_A_REFRESH procedure is described in Table 10-212. The syntax for this procedure is shown below.
DBMS_SNAPSHOT.SET_I_AM_A_REFRESH(value IN BOOLEAN)
To enable the administration of individual snapshots. Invoked at a master site to unregister a snapshot.
The parameters for the UNREGISTER_SNAPSHOT procedure are described in Table 10-213. The syntax for the UNREGISTER_SNAPSHOT procedure is shown below.
DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT(
snapowner IN VARCHAR2,
snapname IN VARCHAR2,
snapsite IN VARCHAR2)
Parameters | Description |
---|---|
snapowner |
The owner of the snapshot. |
snapname |
The name of the snapshot. |
snapsite |
The name of the snapshot site. |
Table 10-214 describes the package variables that are used by the advanced replication facility. You may need to check the value of one or more of these variables in your own packages or triggers.