20 DBMS_CAPTURE_ADM

The DBMS_CAPTURE_ADM package, one of a set of Streams packages, provides subprograms for starting, stopping, and configuring a capture process. The source of the captured changes is the redo logs, and the repository for the captured changes is a queue.

See Also:

Oracle Streams Concepts and Administration and Oracle Streams Replication Administrator's Guide for more information about this package and capture processes

This chapter contains the following topic:

Summary of DBMS_CAPTURE_ADM Subprograms

Summary of DBMS_CAPTURE_ADM Subprograms

Table 20-1 DBMS_CAPTURE_ADM Package Subprograms

Subprogram	Description
ABORT_GLOBAL_INSTANTIATION Procedure	Reverses the effects of running the `PREPARE_GLOBAL_INSTANTIATION`, `PREPARE_SCHEMA_INSTANTIATION`, and `PREPARE_TABLE_INSTANTIATION` procedures
ABORT_SCHEMA_INSTANTIATION Procedure	Reverses the effects of running the `PREPARE_SCHEMA_INSTANTIATION` and `PREPARE_TABLE_INSTANTIATION` procedures
ABORT_TABLE_INSTANTIATION Procedure	Reverses the effects of running the `PREPARE_TABLE_INSTANTIATION` procedure
ALTER_CAPTURE Procedure	Alters a capture process
BUILD Procedure	Extracts the data dictionary of the current database to the redo logs and automatically specifies database supplemental logging for all primary key and unique key columns
CREATE_CAPTURE Procedure	Creates a capture process
DROP_CAPTURE Procedure	Drops a capture process
INCLUDE_EXTRA_ATTRIBUTE Procedure	Includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process
PREPARE_GLOBAL_INSTANTIATION Procedure	Performs the synchronization necessary for instantiating all the tables in the database at another database and can enable supplemental logging for key columns or all columns in these tables
PREPARE_SCHEMA_INSTANTIATION Procedure	Performs the synchronization necessary for instantiating all tables in the schema at another database and can enable supplemental logging for key columns or all columns in these tables
PREPARE_TABLE_INSTANTIATION Procedure	Performs the synchronization necessary for instantiating the table at another database and can enable supplemental logging for key columns or all columns in the table
SET_PARAMETER Procedure	Sets a capture process parameter to the specified value
START_CAPTURE Procedure	Starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue
STOP_CAPTURE Procedure	Stops the capture process from mining redo logs

Note:

All subprograms commit unless specified otherwise.

ABORT_GLOBAL_INSTANTIATION Procedure

This procedure reverses the effects of running the PREPARE_GLOBAL_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_TABLE_INSTANTIATION procedures.

Specifically, this procedure performs the following actions:

Removes data dictionary information related to the database, schema, and table instantiations
Removes any supplemental logging enabled by the PREPARE_GLOBAL_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_TABLE_INSTANTIATION procedures

Syntax

DBMS_CAPTURE_ADM.ABORT_GLOBAL_INSTANTIATION();

ABORT_SCHEMA_INSTANTIATION Procedure

This procedure reverses the effects of running the PREPARE_SCHEMA_INSTANTIATION procedure. It also reverses the effects of running the PREPARE_TABLE_INSTANTIATION procedure on tables in the specified schema.

Specifically, this procedure performs the following actions:

Removes data dictionary information related to schema instantiations and table instantiations of tables in the schema
Removes any supplemental logging enabled by the PREPARE_SCHEMA_INSTANTIATION procedure
Removes any supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure for tables in the specified schema

Syntax

DBMS_CAPTURE_ADM.ABORT_SCHEMA_INSTANTIATION(
   schema_name  IN  VARCHAR2);

Parameter

Table 20-2 ABORT_SCHEMA_INSTANTIATION Procedure Parameter

Parameter	Description
`schema_name`	The name of the schema for which to abort the effects of preparing instantiation

ABORT_TABLE_INSTANTIATION Procedure

This procedure reverses the effects of running the PREPARE_TABLE_INSTANTIATION procedure.

Specifically, this procedure performs the following actions:

Removes data dictionary information related to the table instantiation
Removes any supplemental logging enabled by the PREPARE_TABLE_INSTANTIATION procedure

Syntax

DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(
   table_name  IN  VARCHAR2);

Parameter

Table 20-3 ABORT_TABLE_INSTANTIATION Procedure Parameter

Parameter	Description
`table_name`	The name of the table for which to abort the effects of preparing instantiation, specified as `[schema_name.]object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default.

ALTER_CAPTURE Procedure

This procedure alters a capture process.

See Also:

Oracle Streams Concepts and Administration for more information about altering a capture process

Syntax

DBMS_CAPTURE_ADM.ALTER_CAPTURE(
   capture_name              IN  VARCHAR2,
   rule_set_name             IN  VARCHAR2  DEFAULT NULL,
   remove_rule_set           IN  BOOLEAN   DEFAULT FALSE,
   start_scn                 IN  NUMBER    DEFAULT NULL,
   use_database_link         IN  BOOLEAN   DEFAULT NULL,
   first_scn                 IN  NUMBER    DEFAULT NULL,
   negative_rule_set_name    IN  VARCHAR2  DEFAULT NULL,
   remove_negative_rule_set  IN  BOOLEAN   DEFAULT FALSE,
   capture_user              IN  VARCHAR2  DEFAULT NULL,
   checkpoint_retention_time IN  NUMBER    DEFAULT NULL);

Parameters

Table 20-4 ALTER_CAPTURE Procedure Parameters

Parameter	Description
`capture_name`	The name of the capture process being altered. You must specify an existing capture process name. Do not specify an owner.
`rule_set_name`	The name of the positive rule set for the capture process. The positive rule set contains the rules that instruct the capture process to capture changes. If you want to use a positive rule set for the capture process, then you must specify an existing rule set in the form `[schema_name.]rule_set_name`. For example, to specify a positive rule set in the `HR` schema named `job_capture_rules`, enter `hr.job_capture_rules`. If the schema is not specified, then the current user is the default. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify `NULL` and the `remove_rule_set` parameter is set to `FALSE`, then the procedure retains any existing positive rule set. If you specify `NULL` and the `remove_rule_set` parameter is set to `TRUE`, then the procedure removes any existing positive rule set. See Also: Oracle Streams Concepts and Administration for more information about the changes that can be captured by a capture process
`remove_rule_set`	If `TRUE`, then the procedure removes the positive rule set for the specified capture process. If you remove a positive rule set for a capture process, and the capture process does not have a negative rule set, then the capture process captures all supported changes to all objects in the database, excluding database objects in the `SYS` and `SYSTEM` schemas. If you remove a positive rule set for a capture process, and the capture process has a negative rule set, then the capture process captures all supported changes that are not discarded by the negative rule set. If `FALSE`, then the procedure retains the positive rule set for the specified capture process. If the `rule_set_name` parameter is non-`NULL`, then this parameter should be set to `FALSE`.
`start_scn`	A valid SCN for the database from which the capture process should start capturing changes. The SCN value specified must be greater than or equal to the first SCN for the capture process. An error is returned if an invalid SCN is specified.
`use_database_link`	If `TRUE`, then the capture process at a downstream database uses a database link to the source database for administrative purposes relating to the capture process. If you want a capture process that is not using a database link currently to begin using a database link, then specify `TRUE`. In this case, a database link with the same name as the global name of the source database must exist at the downstream database. If `FALSE`, then either the capture process is running on the source database, or the capture process at a downstream database does not use a database link to the source database. If you want a capture process that is using a database link currently to stop using a database link, then specify `FALSE`. In this case, you must prepare source database objects for instantiation manually when you add or change capture process rules that pertain to these objects. If `NULL`, then the current value of this parameter for the capture process is not changed.
`first_scn`	Specifies the lowest SCN in the redo log from which a capture process can capture changes. If you specify a new first SCN for the capture process, then the specified first SCN must meet the following requirements: It must be greater than the current first SCN for the capture process. It must be less than or equal to the current applied SCN for the capture process. However, this requirement does not apply if the current applied SCN for the capture process is zero. It must be less than or equal to the required checkpoint SCN for the capture process. An error is returned if the specified SCN does not meet the first three requirements. See "Usage Notes" for information about determining an SCN value that meets all of these conditions. When the first SCN is modified, the capture process purges information from its LogMiner data dictionary that is required to restart it at an earlier SCN. Also, if the specified first SCN is higher than the current start SCN for the capture process, then the start SCN is set automatically to the new value of the first SCN.
`negative_rule_set_name`	The name of the negative rule set for the capture process. The negative rule set contains the rules that instruct the capture process to discard changes. If you want to use a negative rule set for the capture process, then you must specify an existing rule set in the form `[schema_name.]rule_set_name`. For example, to specify a negative rule set in the `hr` schema named `neg_capture_rules`, enter `hr.neg_capture_rules`. If the schema is not specified, then the current user is the default. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify `NULL` and the `remove_negative_rule_set` parameter is set to `FALSE`, then the procedure retains any existing negative rule set. If you specify `NULL` and the `remove_negative_rule_set` parameter is set to `TRUE`, then the procedure removes any existing negative rule set. If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first.
`remove_negative_rule_set`	If `TRUE`, then the procedure removes the negative rule set for the specified capture process. If you remove a negative rule set for a capture process, and the capture process does not have a positive rule set, then the capture process captures all supported changes to all objects in the database, excluding database objects in the `SYS` and `SYSTEM` schemas. If you remove a negative rule set for a capture process, and a positive rule set exists for the capture process, then the capture process captures all changes that are not discarded by the positive rule set. If `FALSE`, then the procedure retains the negative rule set for the specified capture process. If the `negative_rule_set_name` parameter is non-`NULL`, then this parameter should be set to `FALSE`.
`capture_user`	The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. If `NULL`, then the capture user is not changed. To change the capture user, the user who invokes the `ALTER_CAPTURE` procedure must be granted `DBA` role. Only the `SYS` user can set the `capture_user` to `SYS`. If you change the capture user, then this procedure grants the new capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue. In addition, make sure the capture user has the following privileges: `EXECUTE` privilege on the rule sets used by the capture process `EXECUTE` privilege on all rule-based transformation functions used in the rule set `EXECUTE` privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the capture process These privileges must be granted directly to the capture user. They cannot be granted through roles. By default, this parameter is set to the user who created the capture process by running either the `CREATE_CAPTURE` procedure in this package or one of the following procedures in the `DBMS_STREAMS_ADM` package with the `streams_type` parameter set to `capture`: `ADD_GLOBAL_RULES` `ADD_SCHEMA_RULES` `ADD_TABLE_RULES` `ADD_SUBSET_RULES` Note: If the specified user is dropped using `DROP` `USER...` `CASCADE`, then the `capture_user` setting for the capture process is set to `NULL` automatically. You must specify a capture user before the capture process can run.
`checkpoint_retention_time`	Either the number of days that a capture process should retain checkpoints before purging them automatically, or `DBMS_CAPTURE_ADM.INFINITE` if checkpoints should not be purged automatically. If `NULL`, then the checkpoint retention time is not changed. If a number is specified, then a capture process purges a checkpoint the specified number of days after the checkpoint was taken. Partial days can be specified using decimal values. For example, `.25` specifies 6 hours. When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the `first_scn` of the capture process is reset to the SCN value corresponding to the first change in the next archived redo log file. See Also: Oracle Streams Concepts and Administration for more information about checkpoint retention time

Usage Notes

If you want to alter the first SCN for a capture process, then the value specified must meet the conditions in the description for the first_scn parameter. The following query determines the current first SCN, applied SCN, and required checkpoint SCN for each capture process in a database:

SELECT CAPTURE_NAME, FIRST_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN
   FROM DBA_CAPTURE;

Also, a capture process is stopped and restarted automatically when you change the value of one or more of the following ALTER_CAPTURE procedure parameters:

start_scn
capture_user

BUILD Procedure

This procedure extracts the data dictionary of the current database to the redo log and automatically specifies database supplemental logging by running the following SQL statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

This procedure is overloaded. One version of this procedure contains the OUT parameter first_scn, and the other does not.

Syntax

DBMS_CAPTURE_ADM.BUILD(
   first_scn  OUT  NUMBER);

DBMS_CAPTURE_ADM.BUILD();

Parameters

Table 20-5 BUILD Procedure Parameter

Parameter	Description
`first_scn`	Contains the lowest SCN value corresponding to the data dictionary extracted to the redo log that can be specified as a first SCN for a capture process

Usage Notes

You can run this procedure multiple times at a source database.

If you plan to capture changes originating at a source database with a capture process, then this procedure must be executed at the source database at least once. When the capture process is started, either at a local source database or at a downstream database, the capture process uses the extracted information in the redo log to create a LogMiner data dictionary.

After executing this procedure, you can query the FIRST_CHANGE# column of the V$ARCHIVED_LOG dynamic performance view where the DICTIONARY_BEGIN column is YES to determine the lowest SCN value for the database that can be specified as a first SCN for a capture process. The first SCN for a capture process is the lowest SCN in the redo log from which the capture process can capture changes.You can specify the first SCN for a capture process when you run the CREATE_CAPTURE or ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

CREATE_CAPTURE Procedure

This procedure creates a capture process.

See Also:

Oracle Streams Concepts and Administration for more information about creating a capture process
Chapter 92, "DBMS_RULE_ADM" for more information about rules and rule sets

Syntax

DBMS_CAPTURE_ADM.CREATE_CAPTURE(
   queue_name                 IN  VARCHAR2,
   capture_name               IN  VARCHAR2,
   rule_set_name              IN  VARCHAR2  DEFAULT NULL,
   start_scn                  IN  NUMBER    DEFAULT NULL,
   source_database            IN  VARCHAR2  DEFAULT NULL,
   use_database_link          IN  BOOLEAN   DEFAULT FALSE,
   first_scn                  IN  NUMBER    DEFAULT NULL,
   logfile_assignment         IN  VARCHAR2  DEFAULT 'implicit',
   negative_rule_set_name     IN  VARCHAR2  DEFAULT NULL,
   capture_user               IN  VARCHAR2  DEFAULT NULL,
   checkpoint_retention_time  IN  NUMBER    DEFAULT 60);

Parameters

Table 20-6 CREATE_CAPTURE Procedure Parameters

Parameter	Description
`queue_name`	The name of the queue into which the capture process enqueues changes. You must specify an existing queue in the form `[schema_name.]queue_name`. For example, to specify a queue in the `hr` schema named `streams_queue`, enter `hr.streams_queue`. If the schema is not specified, then the current user is the default. Note: The `queue_name` setting cannot be altered after the capture process is created.
`capture_name`	The name of the capture process being created. A `NULL` specification is not allowed. Do not specify an owner. Note: The `capture_name` setting cannot be altered after the capture process is created.
`rule_set_name`	The name of the positive rule set for the capture process. The positive rule set contains the rules that instruct the capture process to capture changes. If you want to use a positive rule set for the capture process, then you must specify an existing rule set in the form `[schema_name.]rule_set_name`. For example, to specify a positive rule set in the `hr` schema named `job_capture_rules`, enter `hr.job_capture_rules`. If the schema is not specified, then the current user is the default. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify `NULL`, and no negative rule set is specified, then the capture process captures all supported changes to all objects in the database, excluding database objects in the `SYS` and `SYSTEM` schemas. If you specify `NULL`, and a negative rule set exists for the capture process, then the capture process captures all changes that are not discarded by the negative rule set. See Also: Oracle Streams Concepts and Administration for more information about the changes that can be captured by a capture process
`start_scn`	A valid SCN for the database from which the capture process should start capturing changes. If the specified value is lower than the current SCN of the source database, then either the `first_scn` should be specified, or the SCN value specified for `start_scn` must be greater than or equal to the first SCN of an existing capture process which has taken at least one checkpoint. If `start_scn` is `NULL` and no value is specified for the `first_scn` parameter, then the current SCN of the database is used as start SCN. If `start_scn` is `NULL` and a non-`NULL` value is specified for the `first_scn` parameter, then the `first_scn` value is used. If a value is specified for both `start_scn` and `first_scn`, then the `start_scn` value must be greater than or equal to the `first_scn` value. An error is returned if an invalid SCN is specified.
`source_database`	The global name of the source database. The source database is where the changes to be captured originated. If you do not include the domain name, then the procedure appends it to the database name automatically. For example, if you specify `DBS1` and the domain is `.NET`, then the procedure specifies `DBS1.NET` automatically. If `NULL`, or if the specified name is the same as the global name of the current database, then local capture is assumed and only the default values for `use_database_link` and `first_scn` can be specified.
`use_database_link`	If `TRUE`, then the capture process at a downstream database uses a database link to the source database for administrative purposes relating to the capture process. The capture process uses the database link to prepare database objects for instantiation at the source database and run the `DBMS_CAPTURE_ADM.BUILD` procedure at the source database, if necessary. If `FALSE`, then either the capture process is running on the source database, or the capture process at a downstream database does not use a database link to the source database. In this case, you must perform the following administrative tasks manually: Run the `DBMS_CAPTURE_ADM.BUILD` procedure at the source database to extract the data dictionary at the source database to the redo log when a capture process is created. Obtain the first SCN for the downstream capture process if the first SCN is not specified during capture process creation. The first SCN is needed to create and maintain a capture process. Prepare source database objects for instantiation.
`first_scn`	Specifies the lowest SCN in the redo log from which a capture process can capture changes. A non-`NULL` value for this parameter is valid only if the `DBMS_CAPTURE_ADM.BUILD` procedure has been run at least once at the source database. You can query the `FIRST_CHANGE#` column of the `V$ARCHIVED_LOG` dynamic performance view where the `DICTIONARY_BEGIN` column is `YES` to determine whether the `DBMS_CAPTURE_ADM.BUILD` procedure has been run on a source database. Any of the values returned by such a query can be used as a `first_scn` value if the redo log containing that SCN value is still available.
`logfile_assignment`	If `implicit`, the default, then the capture process at a downstream database scans all redo log files added by redo transport services or manually from the source database to the downstream database. If `explicit`, then a redo log file is scanned by a capture process at a downstream database only if the capture process name is specified in the `FOR` `logminer_session_name` clause when the redo log file is added manually to the downstream database. If `explicit`, then redo transport services cannot be used to add redo log files to the capture process being created. If you specify `explicit` for this parameter for a local capture process, then the local capture process cannot use the online redo log to find changes. In this case, the capture process must use the archived redo log. See Also: "Usage Notes" for information about adding redo log files manually
`negative_rule_set_name`	The name of the negative rule set for the capture process. The negative rule set contains the rules that instruct the capture process to discard changes. If you want to use a negative rule set for the capture process, then you must specify an existing rule set in the form `[schema_name.]rule_set_name`. For example, to specify a negative rule set in the `hr` schema named `neg_capture_rules`, enter `hr.neg_capture_rules`. If the schema is not specified, then the current user is the default. If you specify `NULL`, and no positive rule set is specified, then the capture process captures all supported changes to all objects in the database, excluding database objects in the `SYS` and `SYSTEM` schemas. If you specify `NULL`, and a positive rule set exists for the capture process, then the capture process captures all changes that are not discarded by the positive rule set. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first.
`capture_user`	The user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules. If `NULL`, then the user who runs the `CREATE_CAPTURE` procedure is used. Only a user who is granted `DBA` role can set a capture user. Only the `SYS` user can set the `capture_user` to `SYS`. Note: If the specified user is dropped using `DROP` `USER...` `CASCADE`, then the capture`_user` setting for the capture process is set to `NULL` automatically. You must specify a capture user before the capture process can run.
`checkpoint_retention_time`	Either the number of days that a capture process should retain checkpoints before purging them automatically, or `DBMS_CAPTURE_ADM.INFINITE` if checkpoints should not be purged automatically. If a number is specified, then a capture process purges a checkpoint the specified number of days after the checkpoint was taken. Partial days can be specified using decimal values. For example, `.25` specifies 6 hours. When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the `first_scn` of the capture process is reset to the SCN value corresponding to the first change in the next archived redo log file. See Also: Oracle Streams Concepts and Administration for more information about checkpoint retention time

Usage Notes

The user who invokes this procedure must be granted DBA role.

The capture_user parameter specifies the user who captures changes that satisfy the capture process rule sets. This user must have the necessary privileges to capture changes. This procedure grants the capture user enqueue privilege on the queue used by the capture process and configures the user as a secure queue user of the queue.

In addition, make sure the capture user has the following privileges:

EXECUTE privilege on the rule sets used by the capture process
EXECUTE privilege on all rule-based transformation functions used in the rule set
EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the capture process

These privileges must be granted directly to the capture user. They cannot be granted through roles.

Note:

A capture user does not require privileges on a database object to capture changes to the database object. The capture process can pass these changes to a rule-based transformation function. Therefore, make sure you consider security implications when you configure a capture process.
Creation of the first capture process in a database might take some time because the data dictionary is duplicated during this creation.

If you specify explicit for the logfile_assignment parameter, then you add a redo log file manually to a downstream database using the following statement:

ALTER DATABASE REGISTER LOGICAL LOGFILE 
   file_name FOR capture_process;

Here, file_name is the name of the redo log file being added and capture_process is the name of the capture process that will use the redo log file at the downstream database. The capture_process is equivalent to the logminer_session_name and must be specified. The redo log file must be present at the site running the downstream database. You must transfer this file manually to the site running the downstream database using the DBMS_FILE_TRANSFER package, FTP, or some other transfer method.

See Also:

Oracle Database SQL Reference for more information about the ALTER DATABASE statement and Oracle Data Guard Concepts and Administration for more information registering redo log files

DROP_CAPTURE Procedure

This procedure drops a capture process.

Note:

A capture process must be stopped before it can be dropped.

See Also:

"STOP_CAPTURE Procedure"

Syntax

DBMS_CAPTURE_ADM.DROP_CAPTURE(
   capture_name           IN  VARCHAR2,
   drop_unused_rule_sets  IN  BOOLEAN  DEFAULT FALSE);

Parameters

Table 20-7 DROP_CAPTURE Procedure Parameters

Parameter Description

Parameter	Description
`capture_name`	The name of the capture process being dropped. Specify an existing capture process name. Do not specify an owner.
`drop_unused_rule_sets`	If `TRUE`, then the procedure drops any rule sets, positive and negative, used by the specified capture process if these rule sets are not used by any other Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set. If `FALSE`, then the procedure does not drop the rule sets used by the specified capture process, and the rule sets retain their rules.

capture_name

The name of the capture process being dropped. Specify an existing capture process name. Do not specify an owner.

drop_unused_rule_sets

If TRUE, then the procedure drops any rule sets, positive and negative, used by the specified capture process if these rule sets are not used by any other Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set.

If FALSE, then the procedure does not drop the rule sets used by the specified capture process, and the rule sets retain their rules.

Usage Notes

When you use this procedure to drop a capture process, rules-related information for the capture process created by the DBMS_STREAMS_ADM package is removed from the data dictionary views for Streams rules. Information about such a rule is removed even if the rule is not in either rule set for the capture process.

The following are the data dictionary views for Streams rules:

ALL_STREAMS_GLOBAL_RULES
DBA_STREAMS_GLOBAL_RULES
ALL_STREAMS_MESSAGE_RULES
DBA_STREAMS_MESSAGE_RULES
ALL_STREAMS_SCHEMA_RULES
DBA_STREAMS_SCHEMA_RULES
ALL_STREAMS_TABLE_RULES
DBA_STREAMS_TABLE_RULES
ALL_STREAMS_RULES
DBA_STREAMS_RULES

See Also:

Oracle Streams Concepts and Administration for more information about Streams data dictionary views

INCLUDE_EXTRA_ATTRIBUTE Procedure

This procedure includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process.

Syntax

DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
   capture_name    IN  VARCHAR2,
   attribute_name  IN  VARCHAR2,
   include         IN  BOOLEAN   DEFAULT TRUE);

Parameters

Table 20-8 INCLUDE_EXTRA_ATTRIBUTE Procedure Parameters

Parameter Description

Parameter	Description
`capture_name`	The name of the capture process. Specify an existing capture process name. Do not specify an owner.
`attribute_name`	The name of the attribute to be included in or excluded from LCRs captured by the capture process. The following names are valid settings: `row_id` The rowid of the row changed in a row LCR. This attribute is not included in DDL LCRs, or in row LCRs for index-organized tables. The type is `VARCHAR2`. `serial#` The serial number of the session that performed the change captured in the LCR. The type is `NUMBER`. `session#` The identifier of the session that performed the change captured in the LCR. The type is `NUMBER`. `thread#` The thread number of the instance in which the change captured in the LCR was performed. Typically, the thread number is relevant only in a Real Application Clusters environment. The type is `NUMBER`. `tx_name` The name of the transaction that includes the LCR. The type is `VARCHAR2`. `username` The name of the user who performed the change captured in the LCR. The type is `VARCHAR2`.
`include`	If `TRUE`, then the specified attribute is included in LCRs captured by the capture process If `FALSE`, then the specified attribute is excluded from LCRs captured by the capture process

capture_name

The name of the capture process. Specify an existing capture process name. Do not specify an owner.

attribute_name

The name of the attribute to be included in or excluded from LCRs captured by the capture process. The following names are valid settings:

row_id

The rowid of the row changed in a row LCR. This attribute is not included in DDL LCRs, or in row LCRs for index-organized tables. The type is VARCHAR2.
serial#

The serial number of the session that performed the change captured in the LCR. The type is NUMBER.
session#

The identifier of the session that performed the change captured in the LCR. The type is NUMBER.
thread#

The thread number of the instance in which the change captured in the LCR was performed. Typically, the thread number is relevant only in a Real Application Clusters environment. The type is NUMBER.
tx_name

The name of the transaction that includes the LCR. The type is VARCHAR2.
username

The name of the user who performed the change captured in the LCR. The type is VARCHAR2.

include

If TRUE, then the specified attribute is included in LCRs captured by the capture process

If FALSE, then the specified attribute is excluded from LCRs captured by the capture process

Usage Notes

The redo log contains information about each change made to a database, and some of this information is not captured by a capture process unless you use this procedure to instruct a capture process to capture it. This procedure enables you to specify extra information in the redo log that a capture process should capture. If you want to exclude an extra attribute that is being captured by a capture process, then specify the attribute and specify FALSE for the include parameter.

PREPARE_GLOBAL_INSTANTIATION Procedure

This procedure performs the synchronization necessary for instantiating all the tables in the database at another database and can enable supplemental logging for key columns or all columns in these tables.

This procedure records the lowest SCN of each object in the database for instantiation. SCNs subsequent to the lowest SCN for an object can be used for instantiating the object. Running this procedure prepares all current and future objects in the database for instantiation.

Syntax

DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION
   supplemental_logging  IN  VARCHAR2  DEFAULT 'keys');

Parameter

Table 20-9 PREPARE_GLOBAL_INSTANTIATION Procedure Parameter

Parameter Description

Parameter	Description
`supplemental_logging`	Either `none`, `keys`, or `all`. If `none` is specified, then this procedure does not enable supplemental logging for any columns in the tables in the database. This procedure does not remove existing supplemental logging specifications for these tables. If `keys` is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the database and for any table added to the database in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally. Specifying `keys` does not enable supplemental logging of bitmap join index columns. If `all` is specified, then this procedure enables supplemental logging for all columns in the tables in the database and for any table added to the database in the future. The columns are logged unconditionally. Supplemental logging is not enabled for columns of the following types: LOB, `LONG`, `LONG` `RAW`, and user-defined type.

supplemental_logging

Either none, keys, or all.

If none is specified, then this procedure does not enable supplemental logging for any columns in the tables in the database. This procedure does not remove existing supplemental logging specifications for these tables.

If keys is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the database and for any table added to the database in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally. Specifying keys does not enable supplemental logging of bitmap join index columns.

If all is specified, then this procedure enables supplemental logging for all columns in the tables in the database and for any table added to the database in the future. The columns are logged unconditionally. Supplemental logging is not enabled for columns of the following types: LOB, LONG, LONG RAW, and user-defined type.

Usage Notes

Run this procedure at the source database.

If you use a capture process to capture all of the changes to a database, then use this procedure to prepare the tables in the database for instantiation after the capture process has been configured.

PREPARE_SCHEMA_INSTANTIATION Procedure

This procedure performs the synchronization necessary for instantiating all tables in the schema at another database and can enable supplemental logging for key columns or all columns in these tables.

This procedure records the lowest SCN of each object in the schema for instantiation. SCNs subsequent to the lowest SCN for an object can be used for instantiating the object. Running this procedure prepares all current and future objects in the schema for instantiation.

Syntax

DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
   schema_name           IN  VARCHAR2,
   supplemental_logging  IN  VARCHAR2  DEFAULT 'keys');

Parameters

Table 20-10 PREPARE_SCHEMA_INSTANTIATION Procedure Parameters

Parameter Description

Parameter	Description
`schema_name`	The name of the schema. For example, `hr`.
`supplemental_logging`	Either `none`, `keys`, or `all`. If `none` is specified, then this procedure does not enable supplemental logging for any columns in the tables in the schema. This procedure does not remove existing supplemental logging specifications for these tables. If `keys` is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the schema and for any table added to this schema in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally. Specifying `keys` does not enable supplemental logging of bitmap join index columns. If `all` is specified, then this procedure enables supplemental logging for all columns in the tables in the schema and for any table added to this schema in the future. The columns are logged unconditionally. Supplemental logging is not enabled for columns of the following types: LOB, `LONG`, `LONG` `RAW`, and user-defined type.

schema_name

The name of the schema. For example, hr.

supplemental_logging

Either none, keys, or all.

If none is specified, then this procedure does not enable supplemental logging for any columns in the tables in the schema. This procedure does not remove existing supplemental logging specifications for these tables.

If keys is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the tables in the schema and for any table added to this schema in the future. Primary key columns are logged unconditionally. Unique key, bitmap index, and foreign key columns are logged conditionally. Specifying keys does not enable supplemental logging of bitmap join index columns.

If all is specified, then this procedure enables supplemental logging for all columns in the tables in the schema and for any table added to this schema in the future. The columns are logged unconditionally. Supplemental logging is not enabled for columns of the following types: LOB, LONG, LONG RAW, and user-defined type.

Usage Notes

Run this procedure at the source database. If you use a capture process to capture all of the changes to a schema, then use this procedure to prepare the tables in the schema for instantiation after the capture process has been configured.

PREPARE_TABLE_INSTANTIATION Procedure

This procedure performs the synchronization necessary for instantiating the table at another database and can enable supplemental logging for key columns or all columns in the table.

This procedure records the lowest SCN of the table for instantiation. SCNs subsequent to the lowest SCN for an object can be used for instantiating the object.

Syntax

DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
   table_name            IN  VARCHAR2,
   supplemental_logging  IN  VARCHAR2  DEFAULT 'keys');

Parameters

Table 20-11 PREPARE_TABLE_INSTANTIATION Procedure Parameters

Parameter Description

Parameter	Description
`table_name`	The name of the table specified as `[schema_name.]object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default.
`supplemental_logging`	Either `none`, `keys`, or `all`. If `none` is specified, then this procedure does not enable supplemental logging for any columns in the table. This procedure does not remove existing supplemental logging specifications for the table. If `keys` is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the table. The procedure places the key columns for the table in three separate log groups: the primary key columns in an unconditional log group, the unique key columns and bitmap index columns in a conditional log group, and the foreign key columns in a conditional log group. Specifying `keys` does not enable supplemental logging of bitmap join index columns. If `all` is specified, then this procedure enables supplemental logging for all columns in the table. The procedure places all of the columns for the table in an unconditional log group. Supplemental logging is not enabled for columns of the following types: LOB, `LONG`, `LONG` `RAW`, and user-defined type.

table_name

The name of the table specified as [schema_name.]object_name. For example, hr.employees. If the schema is not specified, then the current user is the default.

supplemental_logging

Either none, keys, or all.

If none is specified, then this procedure does not enable supplemental logging for any columns in the table. This procedure does not remove existing supplemental logging specifications for the table.

If keys is specified, then this procedure enables supplemental logging for primary key, unique key, bitmap index, and foreign key columns in the table. The procedure places the key columns for the table in three separate log groups: the primary key columns in an unconditional log group, the unique key columns and bitmap index columns in a conditional log group, and the foreign key columns in a conditional log group. Specifying keys does not enable supplemental logging of bitmap join index columns.

If all is specified, then this procedure enables supplemental logging for all columns in the table. The procedure places all of the columns for the table in an unconditional log group. Supplemental logging is not enabled for columns of the following types: LOB, LONG, LONG RAW, and user-defined type.

Usage Notes

Run this procedure at the source database. If you use a capture process to capture all of the changes to a table, then use this procedure to prepare the table for instantiation after the capture process has been configured.

SET_PARAMETER Procedure

This procedure sets a capture process parameter to the specified value.

Syntax

DBMS_CAPTURE_ADM.SET_PARAMETER(
   capture_name  IN  VARCHAR2,
   parameter     IN  VARCHAR2,
   value         IN  VARCHAR2);

Parameters

Table 20-12 SET_PARAMETER Procedure Parameters

Parameter	Description
`capture_name`	The name of the capture process. Do not specify an owner.
`parameter`	The name of the parameter you are setting. See "Capture Process Parameters" for a list of these parameters.
`value`	The value to which the parameter is set

Usage Notes

When you alter a parameter value, a short amount of time might pass before the new value for the parameter takes effect.

Capture Process Parameters

The following table lists the parameters for the capture process.

Table 20-13 Capture Process Parameters

Parameter Name	Possible Values	Default	Description
`disable_on_limit`	`Y` or `N`	`N`	If `Y`, then the capture process is disabled because it reached a value specified by the `time_limit` parameter or `message_limit` parameter. If `N`, then the capture process is restarted immediately after stopping because it reached a limit.
`downstream_real_time_mine`	`Y` or `N`	`Y` for local capture processes `N` for downstream capture processes	If `Y`, then the capture process is a real-time downstream capture process. After setting this parameter to `y`, switch the redo log file at the source database using the SQL statement `ALTER` `SYSTEM` `ARCHIVE` `LOG` `CURRENT` to begin real-time downstream capture. If this parameter is set to `Y`, then redo data from the source database must be sent to the standby redo log at the downstream database. See Oracle Streams Concepts and Administration for information about creating a real-time downstream capture process. If `N`, then the capture process is an archived-log downstream capture process. This parameter is ignored for local capture processes.
`maximum_scn`	A valid SCN or `infinite`	`infinite`	The capture process is disabled before capturing a change record with an SCN greater than or equal to the value specified. If `infinite`, then the capture process runs regardless of the SCN value.
`message_limit`	A positive integer or `infinite`	`infinite`	The capture process stops after capturing the specified number of messages. If `infinite`, then the capture process continues to run regardless of the number of messages captured.
`parallelism`	A positive integer	`1`	The number of parallel execution servers that can concurrently mine the redo log Setting the `parallelism` parameter to a number higher than the number of available parallel execution servers might disable the capture process. Make sure the `PROCESSES` and `PARALLEL_MAX_SERVERS` initialization parameters are set appropriately when you set the `parallelism` capture process parameter. Note: When you change the value of this parameter, the capture process is stopped and restarted automatically.
`startup_seconds`	`0`, a positive integer, or `infinite`	`0`	The maximum number of seconds to wait for another instantiation of the same capture process to finish. If the other instantiation of the same capture process does not finish within this time, then the capture process does not start. This parameter is useful only if you are starting the capture process manually. If `infinite`, then the capture process does not start until another instantiation of the same capture process finishes.
`time_limit`	A positive integer or `infinite`	`infinite`	The capture process stops as soon as possible after the specified number of seconds since it started. If `infinite`, then the capture process continues to run until it is stopped explicitly.
`trace_level`	`0` or a positive integer	`0`	Set this parameter only under the guidance of Oracle Support Services.
`write_alert_log`	`Y` or `N`	`Y`	If `Y`, then the capture process writes a message to the alert log on exit. If `N`, then the capture process does not write a message to the alert log on exit. The message specifies the reason the capture process stopped.

Note:

For all parameters that are interpreted as positive integers, the maximum possible value is 4,294,967,295. Where applicable, specify infinite for larger values.
For parameters that require an SCN setting, any valid SCN value can be specified.

START_CAPTURE Procedure

This procedure starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue.

The start status is persistently recorded. Hence, if the status is ENABLED, then the capture process is started upon database instance startup.

The capture process is a background Oracle process and is prefixed by c.

The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the start status of a capture process.

See Also:

Chapter 106, "DBMS_STREAMS_ADM"

Syntax

DBMS_CAPTURE_ADM.START_CAPTURE(
   capture_name  IN  VARCHAR2);

Parameters

Table 20-14 START_CAPTURE Procedure Parameter

Parameter Description

Parameter	Description
`capture_name`	The name of the capture process. Do not specify an owner. The capture process uses LogMiner to capture changes in the redo information. A `NULL` setting is not allowed.

capture_name

The name of the capture process. Do not specify an owner.

The capture process uses LogMiner to capture changes in the redo information. A NULL setting is not allowed.

Usage Notes

The capture process status is persistently recorded. Hence, if the status is ENABLED, then the capture process is started upon database instance startup. A capture process (cnnn) is an Oracle background process.

STOP_CAPTURE Procedure

This procedure stops the capture process from mining redo logs.

The stop status is persistently recorded. Hence, if the status is DISABLED, then the capture process is not started upon database instance startup.

The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the stop status of a capture process.

Syntax

DBMS_CAPTURE_ADM.STOP_CAPTURE(
   capture_name  IN  VARCHAR2,
   force         IN  BOOLEAN  DEFAULT FALSE);

Parameters

Table 20-15 STOP_CAPTURE Procedure Parameters

Parameter	Description
`capture_name`	The name of the capture process. A `NULL` setting is not allowed. Do not specify an owner.
`force`	This parameter is reserved for future use. Currently, valid `BOOLEAN` settings are ignored.

Usage Notes

The capture process status is persistently recorded. Hence, if the status is DISABLED or ABORTED, then the capture process is not started upon database instance startup. A capture process (cnnn) is an Oracle background process.