CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_CAPTURE_ADM

Version 11.1
 
General Information
Purpose Provides subprograms for starting, stopping, and configuring a capture process
Source {ORACLE_HOME}/rdbms/admin/dbmscap.sql
First Available 9.2
Dependencies
DBMS_CAPTURE_ADM DBMS_REPCAT_DECL
DBMS_CAPTURE_ADM_INTERNAL DBMS_STREAMS_ADM
DBMS_LOGREP_IMP DBMS_STREAMS_ADM_UTL
DBMS_CAPTURE_SWITCH_ADM DBMS_STREAMS_ADM_UTL_INVOK
DBMS_CAPTURE_SWITCH_INTERNAL DBMS_STREAMS_DECL
DBMS_LOGREP_IMP DBMS_STREAMS_RPC
DBMS_LOGREP_UTIL DBMS_STREAMS_RPC_INTERNAL
DBMS_LOGREP_UTIL_INVOK DBMS_UTILITY
DBMS_REPCAT_COMMON_UTL  
Exceptions
Exception Description
-26678 create_capture_proc
-25338 inv_sync_capture_proc
-25339 exp_sync_capture
Object Privileges GRANT execute ON dbms_capture_adm TO <schema_name>;
GRANT execute ON dbms_capture_adm TO uwclass;
 
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 (new 11g)
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);
TBD
 
ALTER_SYNC_CAPTURE (new 11g)
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);
-- 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

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);
TBD
 
CREATE_SYNC_CAPTURE (new 11g)
Creates sync capture process. If the specified capture_name is already being used by an existing synchronous or asynchronous capture,  then an error will be 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);
TBD
 
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 for key columns or all columns in these tables 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 (new 11g)

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);
exec dbms_capture_adm.set_parameter('capture_ex', '_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
Advanced Queuing
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
Streams Demo 2
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [97 users online]    © 2010 psoug.org