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