Oracle | DBMS_CAPTURE

Oracle DBMS_CAPTURE_ADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides programs for starting, stopping, and configuring the Streams capture process. The source of the captured changes is the redo logs, and the repository for the captured changes is a queue.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
infinite	NUMBER	4294967295

Dependencies

DBMS_ASSERT	DBMS_LOGREP_UTIL_INVOK	DBMS_STREAMS_AUTO_INT
DBMS_CAPTURE_ADM_INTERNAL	DBMS_STANDARD	DBMS_STREAMS_RPC_INTERNAL
DBMS_CAPTURE_ADM_IVK	DBMS_STREAMS_ADM	DBMS_UTILITY
DBMS_CAPTURE_SWITCH_ADM	DBMS_STREAMS_ADM_IVK	DBMS_XSTREAM_ADM_UTL
DBMS_CAPTURE_SWITCH_INTERNAL	DBMS_STREAMS_ADM_UTL	DBMS_XSTREAM_GG_ADM
DBMS_LOGREP_IMP	DBMS_STREAMS_ADM_UTL_INVOK	DBMS_XSTREAM_UTL_IVK
DBMS_LOGREP_UTIL

Documented Yes

Exceptions

Error Code	Reason
ORA-25338	inv_sync_capture_proc
ORA-25339	exp_sync_capture
ORA-26678	create_capture_proc

First Available 9.2

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmscap.sql

Subprograms

ABORT_GLOBAL_INSTANTIATION	BUILD	PREPARE_SCHEMA_INSTANTIATION
ABORT_SCHEMA_INSTANTIATION	CREATE_CAPTURE	PREPARE_SYNC_INSTANTIATION
ABORT_SYNC_INSTANTIATION	CREATE_SYNC_CAPTURE	PREPARE_TABLE_INSTANTIATION
ABORT_TABLE_INSTANTIATION	DROP_CAPTURE	SET_PARAMETER
ALTER_CAPTURE	INCLUDE_EXTRA_ATTRIBUTE	START_CAPTURE
ALTER_SYNC_CAPTURE	PREPARE_GLOBAL_INSTANTIATION	STOP_CAPTURE

ABORT_GLOBAL_INSTANTIATION

Reverses the effects of Global, Schema, or Table instantiation dbms_capture_adm.abort_global_instantiation;

exec dbms_capture_adm.abort_global_instantiation;

ABORT_SCHEMA_INSTANTIATION

Reverses the effects of schema or table instantiation dbms_capture_adm.abort_schema_instantiation( schema_name IN VARCHAR2, container IN VARCHAR2 DEFAULT 'CURRENT');

exec dbms_capture_adm.abort_schema_instantiation('UWCLASS');

ABORT_SYNC_INSTANTIATION

Undoes prepare_sync_instantiation
Overload 1 dbms_capture_adm.abort_sync_instantiation(table_names IN VARCHAR2);

exec dbms_capture_adm.abort_sync_instantiation('SERVERS');

Overload 2 dbms_capture_adm.abort_sync_instantiation(table_names IN sys.dbms_utility.unclarray);

TBD

ABORT_TABLE_INSTANTIATION

Undoes prepare_table_instantiation dbms_capture_adm.abort_table_instantiation( table_name IN VARCHAR2, container IN VARCHAR2 DEFAULT 'CURRENT');

exec dbms_capture_adm.abort_table_instantiation('SERVERS');

ALTER_CAPTURE

Alter a capture process 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, start_time IN TIMESTAMP DEFAULT NULL, oldest_scn IN NUMBER DEFAULT NULL);

exec dbms_capture_adm.alter_capture(capture_name=>'UWCAPTURE', first_scn=> 7095460);

ALTER_SYNC_CAPTURE

Alters sync capture process ruleset or capture_user dbms_capture_adm.alter_sync_capture( capture_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, capture_user IN VARCHAR2 DEFAULT NULL);

TBD

BUILD

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
Overload 1 dbms_capture_adm.build;

exec dbms_capture_adm.build;

Overload 2 dbms_capture_adm.build(first_scn OUT NUMBER);

-- database must be in archivelog mode

set serveroutput on

      

      DECLARE

       scnout NUMBER;

      BEGIN

        dbms_capture_adm.build(scnout);

        dbms_output.put_line(scnout);

      END;

      /

CREATE_CAPTURE

Creates a capture process 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, start_time IN TIMESTAMP DEFAULT NULL, source_root_name IN VARCHAR2 DEFAULT NULL, capture_class IN VARCHAR2 DEFAULT 'streams');

TBD

CREATE_SYNC_CAPTURE

Creates sync capture process. If the specified capture_name is already in use an exception is raised. dbms_capture_adm.create_sync_capture( queue_name IN VARCHAR2, capture_name IN VARCHAR2, rule_set_name IN VARCHAR2, capture_user IN VARCHAR2 DEFAULT NULL);

TBD

DROP_CAPTURE

Drops a capture process dbms_capture_adm.drop_capture( capture_name IN VARCHAR2, drop_unused_rule_sets IN BOOLEAN DEFAULT FALSE);

exec dbms_capture_adm.drop_capture('UW_CAPTURE', TRUE);

INCLUDE_EXTRA_ATTRIBUTE

Includes or excludes an extra attribute in logical change records (LCRs) captured by the specified capture process dbms_capture_adm.include_extra_attribute( capture_name IN VARCHAR2, attribute_name IN VARCHAR2, include IN BOOLEAN DEFAULT TRUE);

TBD

PREPARE_GLOBAL_INSTANTIATION

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 dbms_capture_adm.prepare_global_instantiation( supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui

      FROM v$database;

      

      exec dbms_capture_adm.prepare_global_instantiation;

      

      SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui

      FROM v$database;

      

      exec dbms_capture_adm.abort_global_instantiation;

      

      SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui

      FROM  v$database;

PREPARE_SCHEMA_INSTANTIATION

Performs the synchronization necessary for instantiating all tables in the schema at another database and can enable supplemental logging dbms_capture_adm.prepare_schema_instantiation( schema_name IN VARCHAR2, supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');

exec dbms_capture_adm.prepare_schema_instantiation('UWCLASS');

PREPARE_SYNC_INSTANTIATION

Prepares a list of tables for instantiation at the source DB

Overload 1 dbms_capture_adm.prepare_sync_instantiation(table_names IN VARCHAR2) RETURN NUMBER;

set serveroutput on

      

      DECLARE

       n NUMBER;

      BEGIN

        n := dbms_capture_adm.prepare_sync_instantiation('SERVERS');

        dbms_output.put_line(n);

      END;

      /

Overload 2 dbms_capture_adm.prepare_sync_instantiation( table_names IN DBMS_UTILITY.UNCL_ARRAY) RETURN NUMBER;

set serveroutput on

      

      DECLARE

       n        NUMBER;

       tabarray DBMS_UTILITY.UNCL_ARRAY;

      BEGIN

        caparray(1) := 'SERVERS';

        n := dbms_capture_adm.prepare_sync_instantiation(tabarray);

      END;

      /

PREPARE_TABLE_INSTANTIATION

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 dbms_capture_adm.prepare_table_instantiation( table_name IN VARCHAR2, supplemental_logging IN VARCHAR2 DEFAULT 'KEYS', container IN VARCHAR2 DEFAULT 'CURRENT');

See Streams Demo 2

SET_PARAMETER

Sets a capture process parameter to the specified value dbms_capture_adm.set_parameter( capture_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2 DEFAULT NULL);

-- how often by MB of data
exec dbms_capture_adm.set_parameter('UW_CAPTURE', '_checkpoint_frequency', '100');

START_CAPTURE

Starts the capture process, which mines redo logs and enqueues the mined redo information into the associated queue dbms_capture_adm.start_capture(capture_name IN VARCHAR2);

exec dbms_capture_adm.start_capture('UW_CAPTURE');

STOP_CAPTURE

Stops the capture process from mining redo logs dbms_capture_adm.stop_capture( capture_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);

exec dbms_capture_adm.stop_capture('UW_CAPTURE', TRUE);