Oracle DBMS_CDC_UTILITY
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 Utilities supporting Streams Change Data Capture functionality
AUTHID CURRENT_USER
Constants
Name Data Type Value
CDC_DB_NAME_MAX INTEGER 128
CDC_ID_NAME_MAX INTEGER 30
CDC_DESC_MAX INTEGER 255
CDC_VARCHAR_MAX INTEGER 32767
CDC_SINGLE_CHAR INTEGER 1
CDC_JOB_NAME_MAX INTEGER 4000
CDC_DML_TYPE_MAX INTEGER 6
CDC_ROOT_DIR_MAX INTEGER 2000
Dependencies
ALL_SCHEDULER_JOBS DBMS_CDC_DPUTIL DBMS_LOCK_ALLOCATED
CDC_CHANGE_SETS$ DBMS_CDC_EXPDP DBMS_LOCK_ID
CDC_CHANGE_SOURCES$ DBMS_CDC_EXPVDP DBMS_SYSTEM
CDC_CHANGE_TABLES$ DBMS_CDC_IMPDP DBMS_SYS_ERROR
CDC_SUBSCRIBED_TABLES$ DBMS_CDC_IPUBLISH DUAL
CDC_SUBSCRIBERS$ DBMS_CDC_PUBLISH EXPACT$
CDC_SYSTEM$ DBMS_CDC_SUBSCRIBE OBJ$
COL$ DBMS_CDC_SYS_IPUBLISH PROPS$
DBMS_CDCAPI_LIB DBMS_FLASHBACK USER$
Documented No
First Available 12.1.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmscdcu.sql
Subprograms
 
CDC_ALLOCATE_LOCK
Allocates a unique lock for CDC use dbms_cdc_utility.cdc_allocate_lock(
lockname        IN  VARCHAR2,
lockhandle      OUT VARCHAR2,
expiration_secs IN  INTEGER DEFAULT 864000);
TBD
 
CHECK_PURGE
Checks for a purge job in the job queue. if none, submits one. If submits one returns TRUE otherwise FALSE. dbms_cdc_utility.check_purge RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_cdc_utility.check_purge THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
 
CHK_SECURITY
Verify user has access to a specified change table dbms_cdc_utility.chk_security(
owner       IN  VARCHAR2,
ownerl      IN  BINARY_INTEGER,
table_name  IN  VARCHAR2,
table_namel IN  BINARY_INTEGER,
mvlog       IN  BINARY_INTEGER,
success     OUT BINARY_INTEGER);
TBD
 
CLEANUP_SYNC_TABLE
Performs extra steps to drop a sync. change table dbms_cdc_utility.cleanup_sync_table(
owner      IN VARCHAR2,
table_name IN VARCHAR2);
TBD
 
COUNT_EXISTING_COL
Count if a column exists in a table dbms_cdc_utility.count_existing_col(
tabobjn IN  BINARY_INTEGER,
colnam  IN  VARCHAR2,
count   OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 objn   BINARY_INTEGER;
 colcnt BINARY_INTEGER;
BEGIN
  dbms_cdc_utility.get_table_objn('UWCLASS', 'SERVERS', objn);
  dbms_cdc_utility.count_existing_col(objn, 'SRVR_ID', colcnt);
  dbms_output.put_line(colcnt);
END;
/
 
COUNT_OBJECT_COL
Counts the number of object columns dbms_cdc_utility.count_object_col(
owner  IN  VARCHAR2,
tabnam IN  VARCHAR2,
count  OUT BINARY_INTEGER);
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TYPE AddressType AS OBJECT (
street VARCHAR2(15),
city   VARCHAR2(15),
state  VARCHAR(2),
zip    VARCHAR2(5));
/

CREATE OR REPLACE TYPE PersonType AS OBJECT (
pid     NUMBER,
fname   VARCHAR2(10),
lname   VARCHAR2(10),
dob     DATE,
phone   VARCHAR2(12),
address AddressType) NOT FINAL;
/

CREATE OR REPLACE TYPE Business_PersonType UNDER PersonType (
title   VARCHAR2(20),
company VARCHAR2(20));
/

CREATE TABLE obc OF Business_PersonType;

SELECT table_name FROM user_tables;

SELECT table_name, table_type
FROM user_all_tables;

conn / as sysdba

set serveroutput on

DECLARE
 colcnt INTEGER;
BEGIN
  dbms_cdc_utility.count_object_col('UWCLASS', 'OBC', colcnt);
  dbms_output.put_line(colcnt);
END;
/
 
COUNT_PURGE_JOB
Count the number of purge jobs dbms_cdc_utility.count_purge_job(
purge_job IN  VARCHAR2,
job_cnt   OUT BINARY_INTEGER);
TBD
 
COUNT_SUBSCRIBERS
Counts the number of subscribers on a change table dbms_cdc_utility.count_subscribers(
change_table_objn  IN  BINARY_INTEGER,
num_of_subscribers OUT BINARY_INTEGER);
TBD
 
DELETE_EXPORT_ACTION
Deletes the export action associated with the change table dbms_cdc_utility.delete_export_action(
change_table_owner IN VARCHAR2,
change_table_name  IN VARCHAR2);
TBD
 
DROP_USER
Drops Change Tables in schema when doing DROP USER CASCADE dbms_cdc_utility.drop_user(user_name IN VARCHAR2);
exec dbms_cdc_utility.drop_user('CDCADMIN');
 
EXPORT_CHANGE_TABLE
Produces an IMPORT_CHANGE_TABLE all during export dbms_cdc_utility.export_change_table(schema_comma_table IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
EXTEND_WINDOW_LIST
CDC specific implementation of EXTEND_WINDOW_LIST() dbms_cdc_utility.extend_window_list(
subscription_list     IN  VARCHAR2,
source_schema_list    IN  VARCHAR2,
source_table_list     IN  VARCHAR2,
rollback_segment_list IN  VARCHAR2,
check_source          IN  BOOLEAN,
read_consistency      IN  BOOLEAN,
timestamp_scn_list    OUT VARCHAR2,
tablemod_scn_list     OUT VARCHAR2,
read_consistent_scn   OUT NUMBER);
TBD
 
FIXUP_SYNC_TABLE
Performs extra steps to alter a sync. change table dbms_cdc_utility.fixup_sync_table(
owner      IN VARCHAR2,
table_name IN VARCHAR2);
TBD
 
GETSYNCSCN
Returns the next "batch" SCN for a SYNC change table dbms_cdc_utility.getSyncSCN(
highest_scn IN NUMBER,
highest_len IN NUMBER) RETURN NUMBER;
TBD
 
GET_CURRENT_SCN
API to the DBMS_FLASHBACK package dbms_cdc_utility.get_current_scn RETURN NUMBER;
SELECT dbms_flashback.get_system_change_number FROM dual;

SELECT dbms_cdc_utility.get_current_scn FROM dual;
 
GET_EVENT_LEVEL
Used for dynamic ChangeTable echo/debug dbms_cdc_utility.get_event_level(event IN NUMBER) RETURN NUMBER;
TBD
 
GET_INSTANCE
Returns the database name, major version, and minor version dbms_cdc_utility.get_instance(
major_version OUT NUMBER,
minor_version OUT NUMBER,
db_name       OUT VARCHAR2);
set serveroutput on

DECLARE
 majver NUMBER;
 minver NUMBER;
 dbname VARCHAR2(9);
BEGIN
   dbms_cdc_utility.get_instance(majver, minver, dbname);
   dbms_output.put_line(majver);
   dbms_output.put_line(minver);
   dbms_output.put_line(dbname);
END;
/
 
GET_ORACLE_EDITION
Returns 1 if Enterprise Edition, otherwise returns 0 dbms_cdc_utility.get_oracle_edition RETURN NUMBER;
SELECT dbms_cdc_utility.get_oracle_edition FROM dual;
 
GET_TABLE_OBJN
Returns a table's object number dbms_cdc_utility.get_table_objn(
owner   IN  VARCHAR2,
tabnam  IN  VARCHAR2,
tabobjn OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 objn BINARY_INTEGER;
BEGIN
  dbms_cdc_utility.get_table_objn('UWCLASS', 'SERVERS', objn);
  dbms_output.put_line(objn);
END;
/
 
IMPORT_CHANGE_TABLE
Produces metadata for a Change Table during IMPORT dbms_cdc_utility.import_change_table(
change_table_type IN VARCHAR2,
major_version     IN VARCHAR2,
minor_version     IN VARCHAR2,
database_name     IN VARCHAR2,
owner             IN VARCHAR2,
change_table_name IN VARCHAR2,
change_set_name   IN VARCHAR2,
source_schema     IN VARCHAR2,
source_table      IN VARCHAR2,
created_scn       IN VARCHAR2,
lowest_scn        IN VARCHAR2,
highest_scn       IN VARCHAR2,
column_type_list  IN VARCHAR2,
col_created       IN VARCHAR2,
capture_values    IN VARCHAR2,
rs_id             IN CHAR,
row_id            IN CHAR,
user_id           IN CHAR,
timestamp         IN CHAR,
object_id         IN CHAR,
source_colmap     IN CHAR,
target_colmap     IN CHAR,
ddl_markers       IN CHAR,
opt_created       IN VARCHAR2);
TBD
 
IS_CONTROL_COLUMN
Determines if a column name is a CDC control column dbms_cdc_utility.is_control_column(column_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
IS_CONTROL_COLUMNMV
Determines if a column name is a CDC control column for MVs dbms_cdc_utility.is_control_columnmv(column_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
LOCK_CHANGE_SET
Used internally for testing purposes dbms_cdc_utility.lock_change_set(change_set_name IN VARCHAR2);
TBD
 
NUMTOHEX
Converts a number to a hex string dbms_cdc_utility.numtohex(num IN NUMBER) RETURN VARCHAR2;
SELECT dbms_xdbutil_int.numtohex42) FROM dual;

SELECT dbms_cdc_utility.numtohex(42) FROM dual;

SELECT dbms_xdbutil_int.numtohex(43) FROM dual;

SELECT dbms_cdc_utility.numtohex(43) FROM dual;

SELECT dbms_xdbutil_int.numtohex(47) FROM dual;

SELECT dbms_cdc_utility.numtohex(47) FROM dual;

SELECT dbms_xdbutil_int.numtohex(420128)FROM dual;

SELECT dbms_cdc_utility.numtohex(420128) FROM dual;
 
PURGEMVLOGLOGICAL
Performs a logical purge of data from all change tables that are MV logs related to a subscription dbms_cdc_utility.purgeMVLogLogical(
subscription_handle     IN  NUMBER,
purge_this_subscription IN  CHAR,    -- Y = ignore subscription
updated_something       OUT NUMBER); -- 0 = nothing to do otherwise > 0
TBD
 
PURGEMVLOGPHYSICAL
Performs a physical purge of a change table that is an MV log dbms_cdc_utility.purgeMVLogPhysical(
schema_name IN  VARCHAR2,
table_name  IN  VARCHAR2,
rows_purged OUT NUMBER);
TBD
 
QCCGELVL
Used for dynamic ChangeTable echo/debug dbms_cdc_utility.qccgelvl(
event IN  BINARY_INTEGER,
level OUT BINARY_INTEGER);
TBD
 
QCCGETEE
Undocumented but note the name of the parameter and its likely relationship to editioning dbms_cdc_utility.qccgetee(edition_o OUT BINARY_ITEGER);
TBD
 
QCCGSCN
Undocumented dbms_cdc_utility.qccgscn(
scnbase_o OUT BINARY_INTEGER,
scnwrap_o OUT BINARY_INTEGER);
TBD
 
QCCSGNBS
Returns the next "batch" SCN for a SYNC change table dbms_cdc_utility.qccsgnbs(
highest_scn IN  NUMBER,
highest_len IN  BINARY_INTEGER,
next_scn    OUT NUMBER);
TBD
 
SETUP_SYNC_TABLE
Performs extra steps to create a sync. change table dbms_cdc_utility.setup_sync_table(
owner      IN VARCHAR2,
table_name IN VARCHAR2);
TBD
 
SET_PURGEBOUNDARY
Sets the purge boundary using SPLIT PARTITION dbms_cdc_utility.set_purgeboundary(
subscription_handle IN BINARY_INTEGER);
TBD
 
SET_WINDOW_START
Sets subscription window starting SCN (EARLIEST) dbms_cdc_utility.set_window_start(subscription_handle IN NUMBER);
TBD
 
VERIFY_CDC_NAME
Verifies param_value does not exceed the limit and conforms to CDC identifier naming rules dbms_cdc_utility.verify_cdc_name(
param_name  IN VARCHAR2,
param_value IN VARCHAR2,
param_max   IN BINARY_INTEGER);
TBD
 
VERIFY_VARCHAR_PARAM
Undocumented dbms_cdc_utility.verify_varchar_param(
param_name  IN VARCHAR2,
param_value IN VARCHAR2,
param_max   IN BINARY_INTEGER);
TBD

Related Topics
DBMS_CDC_EXPVDP
DBMS_CDC_IMPDPV
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
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