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
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);

Related Topics
ARCHIVELOG Mode
Built-in Functions
Built-in Packages
Database Security
DBMS_APPLY_ADM
DBMS_CAPTURE_ADM_INTERNAL
DBMS_CAPTURE_ADM_IVK
DBMS_CAPTURE_PROCESS
DBMS_CAPTURE_SWITCH_ADM
DBMS_CAPTURE_SWITCH_INTERNAL
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx