Streams Change Tables provide us with the ability to capture and replicate "data change audit information" that is often required to meet Regulatory Standards such as SOX, and FISMA. The Streams Change Tables are new in 11gR2 and are created and maintained using the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE
procedure. As discussed in Chapter 1, this procedure configures a separate change audit table for a Streamed table, and creates all the components necessary to capture, send, and record data change information to the change table. This procedure can also be used to create one-way replication of a table along with the change capture from a source to a destination database. Change tables can be implemented for local or downstream capture, and local or remote apply configurations.
Before configuring Change tables you will want to have made the following decisions:
KEEP_COLUMNS
TransformationFor our purposes, we will make the following decisions:
Based on this, we make the following call to DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE:
begin DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE( change_table_name => 'HR.EMPLOYEES_CHANGES', source_table_name => 'HR.EMPLOYEES', column_type_list => 'EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, JOB_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4)', extra_column_list => 'username,source_time', capture_values => '*', options_string => NULL, script_name => 'changetable_employees.sql', script_directory_object => 'SCRIPT_DIR', perform_actions => TRUE, capture_name =>'HR_CAPT_EMP', propagation_name => 'HR_PROP_EMP', apply_name => 'HR_APPLY_EMP', source_database => 'STRM1', destination_database => 'STRM2', keep_change_columns_only => TRUE, execute_lcr => TRUE); end; /
Note: At the time of writing, the Authors have experienced potential SQL parsing issues with the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE
procedure on Linux. The Authors are working with Oracle to determine if the issues are related to parameter syntax/datatype or with internal processing. When the cause of the issues have been determined and resolved, this section will be updated to include additional information for the functionality of this new feature.
Some things to keep in mind when running the script:
destination_database
parameter. destination_database
parameter. apply_name
parameter can only be null if there are no Apply processes at the destination database. apply_name
is specified, no messaging client with the same name can exist at the destination database. execute_lcr = TRUE
as it could end up attempting to apply the same change back to the source table. DBMS_STREAMS_ADM.MAINTAIN_*
procedures, and with a new functionality, we highly recommend that you first run the procedure with perform_actions = FALSE
, and have it generate a script that you can then review to fully understand what the procedure is going to do before it does it. You can then either run the script as-is from SQLPlus as the Streams Administrator, or modify the script as you see necessary.