...
| No Format | ||
|---|---|---|
| ||
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.
| No Format | ||
|---|---|---|
| ||
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.
...