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. Currently supported datatypes are listed in the following section: Supported Datatypes.

There are two 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 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.sh or *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:

PROCESS SWITCH_REDOLOG
#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.

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 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',
 'BINARY_FLOAT',
 'BINARY_DOUBLE',
 'ROWID',
 'VARCHAR2',
 'VARCHAR',
 'CHAR',
 'NVARCHAR2',
 'NCHAR',
 'NCHAR2',
 'LONG',
 'LONG RAW',
 'DATE',
 'RAW',
 'BFILE',
 'CLOB',
 'BLOB',
 'NCLOB')
and data_type not like 'INTERVAL%'
and data_type not like 'TIMESTAMP%'
order by d.owner,d.table_name,c.column_name;
spool off
exit

Bulk exclude column commands for SQL Server

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')
and data_type not like 'TIMESTAMP%'
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