If tables need to be replicated that contain columns that do not need to be replicated or with current unsupported data types, then these columns can be excluded from replication. Currently supported datatypes are listed in the following section: Supported Datatypes.
There are two methods to exclude the columns:
- Editing the resulting
.dbvrepfile after the setup wizard has completed and before the
*all.batscript is executed.
- Issuing the exclude column command in the console.
Each option is further explained below. For more information about the EXCLUDE COLUMN please see Full Command-Line Reference
Editing the resulting .dbvrep file
The setup wizard does not provide the option to exclude columns, but the resulting .dbvrep file (found in the config directory and named *-setup.dbvrep) can be edited to add the EXCLUDE COLUMN OFFLINE commands before the replication is initiated with the
*all.bat script. The
OFFLINE keyword is necessary at this stage since the replication has not yet been started, and by default EXCLUDE operates in on-line mode.
The steps are as follows:
Edit the *.dbvrep file and after the last line in the dbvrep file, add:
EXCLUDE COLUMN OFFLINE SCHEMA.TABLE.COLUMN_NAME
If the LOAD command is used and the option is selected to have this function (re)create the tables on the target then the location of the EXCLUDE COLUMN command is slightly different. In this case it should be placed after the DELETE TABLE statement, and directly before the CREATE TABLE statement for the table in question, in the *-setup.dbvrep file. In the following example we exclude LONG and LONG RAW columns from HR.TEST1 and HR.TEST2, respectively:
#prepare script for instantiation
DDL DROP REPLICATE "HR"."TEST1"
EXCLUDE COLUMN OFFLINE HR.TEST1.TEST_LONG
DDL CREATE_FROM_DICT REPLICATE "HR"."TEST1"
DDL DROP REPLICATE "HR"."TEST2"
EXCLUDE COLUMN OFFLINE HR.TEST2.TEST_LONG_RAW
DDL CREATE_FROM_DICT REPLICATE "HR"."TEST2"
PROCESS LOAD_REQUEST TABLE "HR"."TEST1" AT INSTANTIATE SCN
PROCESS LOAD_REQUEST TABLE "HR"."TEST2" AT INSTANTIATE SCN
Multiple exclude columns can be added for the same table.
#prepare the tables (we use OFFLINE as neither MINE not APPLY is running; with OFFLINE we won't wait on network timeout) PREPARE OFFLINE SCHEMA OE NODDL ... ... EXCLUDE COLUMN OFFLINE OE.PRODUCT_DESCRIPTIONS.TRANSLATED_NAME EXCLUDE COLUMN OFFLINE OE.PRODUCT_DESCRIPTIONS.XML_DATA
In this example the columns TRANSLATED_NAME and XML_DATA from the table PRODUCT_DESCRIPTIONS from schema OE are excluded in the replication.
Save the .dbvrep file and then run the *all.sh or *all.bat script as per normal.
When the setup wizard is run again for the same replication (or DDC) name, the .dbvrep script will be overwritten.
Issuing the EXCLUDE COLUMN command
The EXCLUDE COLUMN command can also be run in the Dbvisit Replicate command console. Entering this command in the console requires a restart of the MINE process.
dbvrep> EXCLUDE COLUMN SCOTT.DEPT.LOC Connecting to running mine [SCOTT.DEPT.LOC] 2: [Column excluded (1 internal records).] Exclude Column [SCOTT.DEPT.LOC] set. dbvrep> shutdown mine Restart the MINE process $ ./ORCL-run-dbvisit.com.sh
Bulk exclude column commands
You can generate a batch set of EXCLUDE COLUMN commands for unsupported datatypes for a schema that can be added to the file .dbvrep or run at the command line.
set verify off set feedback off set linesize 100 set pagesize 40000 column thestring format A100 spool exclude_cols.txt select 'EXCLUDE COLUMN '||d.owner||'.'||d.table_name||'.'||c.column_name thestring from dba_tab_columns c, dba_tables d where d.owner = UPPER('&owner') and d.owner = c.owner and d.table_name = c.table_name and c.DATA_TYPE not in ( 'NUMBER', 'FLOAT', 'VARCHAR2', 'VARCHAR', 'CHAR', 'NVARCHAR2', 'NCHAR2', 'NCHAR', 'DATE', 'RAW', 'LONG', 'LONG RAW', 'CLOB', 'BLOB', 'LOB', 'NCLOB') order by d.owner,d.table_name,c.column_name; spool off exit
To view the excluded columns from the replication, run the below SQL.
set verify off set lines 200 set pages 5000 spool exclude_col_list.txt select u.name owner, o.name tabname, c.name colname, decode(c.mine_out,'Y','included','excluded') column_status from dbrsobj$ o, dbrscol$ c, dbrsuser$ u where o.obj_ = c.obj_ and o.owner_ = u.user_ and o.mine_out = 'Y' and c.mine_out = 'N' and u.name not like 'DBV%'; spool off exit