Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
View PDF |
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 processesThis chapter contains the following topic:
Table 20-1 DBMS_CAPTURE_ADM Package Subprograms
Subprogram | Description |
---|---|
Reverses the effects of running the |
|
Reverses the effects of running the |
|
Reverses the effects of running the |
|
Alters a capture process |
|
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 |
|
Creates a capture process |
|
Drops a capture process |
|
Includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process |
|
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 |
|
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 |
|
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 |
|
Sets a capture process parameter to the specified value |
|
Starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue |
|
Stops the capture process from mining redo logs |
Note:
All subprograms commit unless specified otherwise.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();
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 |
---|---|
|
The name of the schema for which to abort the effects of preparing instantiation |
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 |
---|---|
|
The name of the table for which to abort the effects of preparing instantiation, specified as |
This procedure alters a capture process.
See Also:
Oracle Streams Concepts and Administration for more information about altering a capture processSyntax
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 |
---|---|
|
The name of the capture process being altered. You must specify an existing capture process name. Do not specify an owner. |
|
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 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 If you specify See Also: Oracle Streams Concepts and Administration for more information about the changes that can be captured by a capture process |
|
If 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 If the |
|
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. |
|
If If If |
|
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:
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. |
|
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 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 If you specify If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first. |
|
If 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 If the |
|
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 To change the capture user, the user who invokes the 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:
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
Note: If the specified user is dropped using |
|
Either the number of days that a capture process should retain checkpoints before purging them automatically, or 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, When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the 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
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 |
---|---|
|
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.
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 |
---|---|
|
The name of the queue into which the capture process enqueues changes. You must specify an existing queue in the form Note: The |
|
The name of the capture process being created. A Note: The |
|
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 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 If you specify If you specify See Also: Oracle Streams Concepts and Administration for more information about the changes that can be captured by a capture process |
|
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 If If a value is specified for both An error is returned if an invalid SCN is specified. |
|
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 If |
|
If If
|
|
Specifies the lowest SCN in the redo log from which a capture process can capture changes. A non- You can query the |
|
If If If you specify See Also: "Usage Notes" for information about adding redo log files manually |
|
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 If you specify If you specify 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 If you specify both a positive and a negative rule set for a capture process, then the negative rule set is always evaluated first. |
|
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 Only a user who is granted Note: If the specified user is dropped using |
|
Either the number of days that a capture process should retain checkpoints before purging them automatically, or 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, When a checkpoint is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the 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 theALTER
DATABASE
statement and Oracle Data Guard Concepts and Administration for more information registering redo log filesThis 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 |
---|---|
|
The name of the capture process being dropped. Specify an existing capture process name. Do not specify an owner. |
|
If If |
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 viewsThis 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 |
---|---|
|
The name of the capture process. Specify an existing capture process name. Do not specify an owner. |
|
The name of the attribute to be included in or excluded from LCRs captured by the capture process. The following names are valid settings:
|
|
If If |
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.
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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation and supplemental loggingSyntax
DBMS_CAPTURE_ADM.PREPARE_GLOBAL_INSTANTIATION supplemental_logging IN VARCHAR2 DEFAULT 'keys');
Parameter
Table 20-9 PREPARE_GLOBAL_INSTANTIATION Procedure Parameter
Parameter | Description |
---|---|
|
Either If If If |
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.
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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation and supplemental loggingSyntax
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 |
---|---|
|
The name of the schema. For example, |
|
Either If If If |
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.
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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about instantiation and supplemental loggingSyntax
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 |
---|---|
|
The name of the table specified as |
|
Either If If If |
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.
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 |
---|---|
|
The name of the capture process. Do not specify an owner. |
|
The name of the parameter you are setting. See "Capture Process Parameters" for a list of these parameters. |
|
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 |
---|---|---|---|
|
|
If If |
|
|
|
If If This parameter is ignored for local capture processes. |
|
A valid SCN or |
|
The capture process is disabled before capturing a change record with an SCN greater than or equal to the value specified. If |
|
A positive integer or |
|
The capture process stops after capturing the specified number of messages. If |
|
A positive integer |
|
The number of parallel execution servers that can concurrently mine the redo log Setting the Note: When you change the value of this parameter, the capture process is stopped and restarted automatically. |
|
|
|
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 |
|
A positive integer or |
|
The capture process stops as soon as possible after the specified number of seconds since it started. If |
|
|
|
Set this parameter only under the guidance of Oracle Support Services. |
|
|
|
If If 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.
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 |
---|---|
|
The name of the capture process. Do not specify an owner. The capture process uses LogMiner to capture changes in the redo information. A |
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 (c
nnn
) is an Oracle background process.
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 |
---|---|
|
The name of the capture process. A |
|
This parameter is reserved for future use. Currently, valid |
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 (c
nnn
) is an Oracle background process.