Excluding columns from replication

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. See here for current Supported Datatypes.

There are 2 methods to exclude the columns:

Editing the resulting .dbvrep file after the setup wizard has completed and before the *all.sh or *all.bat script is run.
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 the column, but the resulting .dbvrep file (found in the config directory and named *-setup.dbvrep) can be edited to add the EXCLUDE COLUMN commands before the replication is initiated with the *all.sh or *all.bat script.

The steps are:

Edit the *.dbvrep file and after the last line in the file add:

EXCLUDE COLUMN 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:

PROCESS SWITCH_REDOLOG
#prepare script for instantiation
DDL DROP REPLICATE "HR"."TEST1"
EXCLUDE COLUMN HR.TEST1.TEST_LONG
DDL CREATE_FROM_DICT REPLICATE "HR"."TEST1"
DDL DROP REPLICATE "HR"."TEST2"
EXCLUDE COLUMN 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.

Example:

#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 OE.PRODUCT_DESCRIPTIONS.TRANSLATED_NAME
EXCLUDE COLUMN 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 schema.tablename.columnname
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 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