DBMS_LOGMNR_CDC_PUBLISH, 3 of 7

ALTER_CHANGE_TABLE Procedure

This procedure adds columns to, or drops columns from, an existing change table.

Syntax

The following syntax specifies columns and datatypes as a comma-separated list.

DBMS_LOGMNR_CDC_PUBLISH.ALTER_CHANGE_TABLE (
owner	IN VARCHAR2,
change_table_name	IN VARCHAR2,
operation	IN VARCHAR2,
column_list	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);

Parameters

Table 25-4 ALTER_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 altered.
operation	Specifies either the value `DROP` or `ADD` to indicate whether to add or drop the columns in the field column_table or column_list.
column_list	A comma-separated list of column names and datatypes for each column of the source table that should be added to, or dropped from, the change table.
rs_id	Adds or drops the control column that tracks the row sequence (`rs_id`). Set this parameter to Y or N, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the row sequence (`rs_id`).
	N	The `rs_id` control column is not changed in the change table.
row_id	Adds or drops a `row_id` column, as follows:
	Value Y	Description Adds or drops the `row_id` control column for the change table.
	N	The row_id column is not changed in the change table.
user_id	Adds or drops the user name control column. Specify Y or N, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the user name (`user_id`).
	N	The user_id column is not changed in the change table.
timestamp	Adds or drops the timestamp control column to the change table, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the timestamp.
	N	The timestamp control column is not changed in the change table.
object_id	Add or drops the `object_id` column, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the `object_id`.
	N	The object_id control column is not changed in the change table.
source_colmap	Adds or drops the source_colmap control column from the change table, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the source columns (source_colmap).
	N	The source_colmap column is not changed in the change table.
target_colmap	Adds or drops the `target_colmap` control column from the change table, as follows:
	Value Y	Description Adds or drops a column on the change table that contains the target columns (target_colmap).
	N	The target_colmap column is not changed in the change table.

Exceptions

Table 25-5 ALTER_CHANGE_TABLE Procedure Exceptions

Exception	Description
`ORA-31403`	You issued an `ALTER_CHANGE_TABLE` procedure with an ADD operation but a column by this name already exists in the specified table.
`ORA-31409`	One or more of the input parameters to the `ALTER_CHANGE_SET` procedure had invalid values. Identify the incorrect parameters and supply the correct values to the procedure.
`ORA-31417`	A reserved column name was specified in the column list parameter. Ensure that the name specified does not conflict with a reserved column name.
`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-31423`	You issued the `ALTER_CHANGE_TABLE` with a drop operation and the specified column does not exist in the change table.
`ORA-31454`	Illegal value was specified for operation parameter; expecting `ADD` or `DROP`.
`ORA-31455`	Nothing to alter. The specified column list is NULL and all optional control columns are N.
`ORA-31456`	An internal attempt to invoke a procedure within the `DBMS_CDC_UTILITY` package failed. Check the trace logs for more information.
`ORA-31459`	One or more required system triggers are not installed.

Usage Notes

You cannot add and drop user columns in the same call to the ALTER_CHANGE_TABLE procedure; these schema changes require separate calls.
Do not specify the name of the control columns in the user-column lists.

The following table describes what happens when you add a column to a change table:

If the publisher adds . . . .	And . . . .	Then . . .
A user column	A new subscription includes this column	The subscription window starts at the point the column was added.
A user column	A new subscription does not include this newly added column	The subscription window starts at the low-water mark for the change table thus enabling the subscriber to see the entire table.
A user column	Old subscriptions exist	The subscription window remains unchanged and the entire table can be seen.
A control column	There is a new subscription	The subscription window starts at the low-water mark for the change table. The subscription can see the control column immediately. All rows that existed in the change table prior to adding the control column will have the value NULL for the newly added control column field.
A control column	--	Any existing subscriptions can see the new control column when the window is extended (`DBMS_LOGMNR_CDC_PUBLISH.EXTEND_WINDOW` procedure) such that the low watermark for the window crosses over the point when the control column was added.

Example

EXECUTE DBMS_LOGMNR_CDC_PUBLISH.ALTER_CHANGE_TABLE (OWNER => 'cdc1') \

CHANGE_TABLE_NAME => 'emp_ct' \
OPERATION => ADD \
ADD_COLUMN_LIST => '' \
RS_ID => 'Y' \
ROW_ID => 'N' \
USER_ID => 'N' \
TIMESTAMP => 'N' \
OBJECT_ID => 'N' \
SOURCE_COLMAP => 'N' \
TARGET_COLMAP => 'N');