CREATE_CHANGE_TABLE Procedure

This procedure creates a change table in a specified schema.

Syntax

The following syntax specifies columns and datatypes using a comma-separated string.

DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE (
owner	IN VARCHAR2,
change_table_name	IN VARCHAR2,
change_set_name	IN VARCHAR2,
source_schema	IN VARCHAR2,
source_table	IN VARCHAR2,
column_type_list	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,
options_string	IN VARCHAR2);

Parameters

Table 25-2 CREATE_CHANGE_TABLE Procedure Parameters

Parameter	Description
owner	Name of the schema that owns the change table.
change_table_name	Name of the change table that is being created.
change_set_name	Name of an existing change set with which this change table is associated. Synchronous change tables must specify SYNC_SET.
source_schema	The schema where the source table is located.
source_table	The source table from which the change records are captured.
column_type_list	Comma-separated list of columns and datatypes that are being tracked.
capture_values	Set this parameter to one of the following capture values for update operations:
	Value OLD	Description Captures the original values from the source table.
	NEW	Captures the changed values from the source table.
	BOTH	Captures the original and changed values from the source table.
rs_id	Adds a column to the change table that contains the row sequence number. This parameter orders the operations in a transaction in the sequence that they were committed in the database. The row sequence ID (rs_id) parameter is optional for synchronous mode. Note: For synchronous mode, the rs_id parameter reflects an operations capture order within a transaction, but you cannot use the rs_id parameter by itself to order committed operations across transactions. Set this parameter to Y or N, as follows:
	Value Y	Description Indicates that you want to add a column to the change table that will contain the row sequence of the change.
	N	Indicates that you do not want to track the rs_id column.
row_id	Adds a column to the change table that contains the row ID of the changed row in the source table, as follows.
	Value Y	Description Indicates that you want to add a column to the change table that contains the row ID of the changed row in the source table.
	N	Indicates that you do not want to track the row_id column.
user_id	Adds a column to the change table that contains the user name of the user who entered a DML statement, as follows.
	Value Y	Description Indicates that you want to add a column to the change table that contains the user name of the user who entered a DML statement.
	N	Indicates that you do not want to track users.
timestamp	Adds a column to the change table that contains the capture timestamp of the change record, as follows:
	Value Y	Description Indicates that you want to add a column to the change table that contains the capture timestamp of the change record.
	N	Indicates that you do not want to track timestamps.
object_id	Adds a column to the change table that contains the object ID of this change record. This is a control column for object support. Specify Y or N, as follows:
	Value Y	Description Indicates that you want to add a column to the change table that contains the object ID of this change record.
	N	Indicates that you do not want to track object IDs.
source_colmap	Adds a column to the change table as a change column vector that indicates which source columns actually changed. Specify Y or N, as follows:
	Value Y	Description Indicates that you want to add a column to the change table to track the source columns that have changed.
	N	Indicates that you do not want to track which source columns changed.
target_colmap	Adds a column to the change table as a column vector indicating which change table user columns actually changed. Specify Y or N, as follows.
	Value Y	Description Indicates that you want to add a column to the change table to track the change table user columns that have changed.
	N	Indicates that you do not want to track changes which change table user columns changed.
options_string	A string that contains syntactically correct options to be passed to a CREATE TABLE DDL statement. The options string is appended to the generated CREATE TABLE DDL statement after the closing parenthesis that defines the columns of the table. See the Usage Notes for more information.

Exceptions

Table 25-3 CREATE_CHANGE_TABLE Procedure Exceptions

Exception	Description
ORA-31409	One or more of the input parameters to the CREATE_CHANGE_TABLE procedure had invalid values. Identify the incorrect parameters and supply the correct values to the procedure.
ORA-31416	The value specified for the source_colmap parameter is invalid. For synchronous mode, specify either Y or N.
ORA-31417	A reserved column name was specified in a column list or column type parameter. Ensure that the name specified does not conflict with a reserved column name.
ORA-31418	While creating a synchronous change table, the name of the source schema did not match any existing schema name in the database.
ORA-31419	When creating a synchronous change table, the underlying source table did not exist when the procedure was called.
ORA-31420	When creating the first change table, a purge job is submitted to the job queue. Submission of this purge job failed.
ORA-31421	The specified change table does not exist. Check the specified change table name to see that it matches the name of an existing change table.
ORA-31422	Owner schema does not exist.
ORA-31438	Duplicate change table. Re-create the change table with a unique name.
ORA-31450	Invalid value was specified for change_table_name.
ORA-31451	Invalid value was specified for the capture_value. Expecting either OLD, NEW, or BOTH.
ORA-31452	Invalid value was specified. Expecting either Y or N.
ORA-31459	System triggers for DBMS_LOGMRN_CDC_PUBLISH package are not installed.
ORA-31467	No column found in the source table. The OBJECT_ID flag was set to Y on the call to CREATE_CHANGE_TABLE and change table belongs to the synchronous change set. The corresponding object column was not detected in the source table.

Usage Notes

• A change table is a database object that contains the change data resulting from DML statements (INSERT, UPDATE, and DELETE) made to a source table. A given change table can capture changes from only one source table.

• A synchronous change table must belong to the SYNC_SET change set.

• A change table is a database table that maintains the change data in these two types of columns:
  • Source columns identify the columns from the source table to capture. Source columns are copies of actual source table columns that reside in the change table.

  • Control columns maintain special metadata for each change row in the container table. Information such as the DML operation performed, the capture time (timestamp), and changed column vectors are examples of control columns.

• The publisher can control a change table's physical properties, tablespace properties, and so on by specifying the options_string parameter. With the options_string parameter, you can set any option that is valid for the CREATE TABLE DDL statement.

• Do not attempt to control a change table's partitioning properties. When Change Data Capture performs a purge operation to remove rows from a change set, it automatically manages the change table partitioning for you.

  Note: How you define the options_string parameter can have an effect on the performance and operations in a Change Data Capture system. For example, if the publisher places several constraints in the options column, it can have a noticeable effect on performance. Also, if the publisher uses NOT NULL constraints and a particular column is not changed in an incoming change row, then the constraint can cause the entire INSERT operation to fail.

Example

execute DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(OWNER => 'cdc1', \ 

CHANGE_TABLE_NAME => 'emp_ct', \
CHANGE_SET_NAME => 'SYNC_SET', \
SOURCE_SCHEMA => 'scott', \
SOURCE_TABLE => 'emp', \
COLUMN_TYPE_LIST => 'empno number, ename varchar2(10), job varchar2(9), mgr 
number, hiredate date, deptno number', \
CAPTURE_VALUES => 'both', \
RS_ID => 'y', \
ROW_ID => 'n', \
USER_ID => 'n', \
TIMESTAMP => 'n', \
OBJECT_ID => 'n',\
SOURCE_COLMAP => 'n', \
TARGET_COLMAP => 'y', \
OPTIONS_STRING => NULL);