Oracle DBMS_CAPTURE_ADM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_CAPTURE_ADM_INTERNAL DBMS_REPCAT_COMMON_UTL DBMS_STREAMS_DECL
DBMS_CAPTURE_ADM_IVK DBMS_REPCAT_DECL DBMS_STREAMS_RPC_INTERNAL
DBMS_CAPTURE_SWITCH_ADM DBMS_STREAMS_ADM DBMS_UTILITY
DBMS_CAPTURE_SWITCH_INTERNAL DBMS_STREAMS_ADM_IVK DBMS_XSTREAM_ADM_UTL
DBMS_LOGREP_IMP DBMS_STREAMS_ADM_UTL DBMS_XSTREAM_GG_ADM
DBMS_LOGREP_UTIL DBMS_STREAMS_ADM_UTL_INVOK DBMS_XSTREAM_UTL_IVK
DBMS_LOGREP_UTIL_INVOK DBMS_STREAMS_AUTO_INT  
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.0.1
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
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);
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 DBMS_UTILITY.UNCL_ARRAY);
TBD
 
ABORT_TABLE_INSTANTIATION
Undoes prepare_table_instantiation dbms_capture_adm.abort_table_instantiation(table_name IN VARCHAR2);
exec dbms_capture_adm.abort_table_instantiation('SERVERS');
 
ALTER_CAPTURE
Alters 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);
TBD
 
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(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;
/
Overload 2 dbms_capture_adm.build;
exec dbms_capture_adm.build;
 
CREATE_CAPTURE (new 12.1 parameters)
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');
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');
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');
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);
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);

Related Topics
ARCHIVELOG Mode
DBMS_APPLY_ADM
DBMS_CDC_PUBLISH
DBMS_CDC_UTILITY
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MC
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved