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_CDC_UTILITY
Version 11.1
 
General Information
Purpose Utilities for Change Data Capture
Source {ORACLE_HOME}/rdbms/admin/dbmscdcu.sql

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_SYS_ERROR
CDC_CHANGE_TABLES$ DBMS_CDC_IMPDP DUAL
CDC_SUBSCRIBED_TABLES DBMS_CDC_IPUBLISH EXPACT$
CDC_SUBSCRIBERS$ DBMS_CDC_PUBLISH OBJ$
CDC_SYSTEM$ DBMS_CDC_SUBSCRIBE PROPS$
COL$ DBMS_CDC_SYS_IPUBLISH USER$
DBMS_CDCAPI_LIB DBMS_FLASHBACK  
Security Model Owned by SYS. No privs granted
 
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

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 call 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.numtohex(42) 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 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_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_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [73 users online]    © 2010 psoug.org