In order to exclude columns and not to propagate them to target side, you have to load the target table with methods like CTAS, insert+select etc.
Edit .dbvrep File to Add Tables and Exclude Columns
Replicate has an inbuilt mechanism, that can use load of table by select statement instead of datapumps. It is not the fastest, but in this case it might be handy. In Step 2, when you define replication pair, you can choose load as an instantiation method:
What data instantiation script to create?
dp_networklink : Data Pump with network link. No export datafile is created (APPLY.sh) dp_exp : Data Pump with export datafile. Creates an export datafile (APPLY.sh) exp : Pre-datapump exp/imp with export datafile (APPLY.sh) ddl_file : DDL file created (APPLY.sql) ddl_run : DDL is automatically executed on target load : All replicated data is created and loaded automatically none (dp_networklink/dp_exp/exp/ddl_file/ddl_run/load/none) [dp_networklink] load
After you finish the wizard (before
*all.sh script is run) , you have to add the exclude column commands to
<DDC>/config/*.dbvrep file. I recomend putting it after the
#prepare script for instantiation section. For Example:
#prepare script for instantiation EXCLUDE COLUMN AHOREJSP.TEST_EXCLUDE.COL3
After you run
all.sh script and start the processes, the table should be loaded to target without the excluded columns.
Adding Other Table to Replication
If you want to add the table later on to the replication, you have to:
- Shutdown MINE
prepare table AHOREJSP.TEST_EXCLUDE Table AHOREJSP.TEST_EXCLUDE instantiated at SCN 1108430512Remember the SCN from this result
EXCLUDE COLUMN AHOREJSP.TEST_EXCLUDE.COL3
- create your own CTAS table on the TARGET without the columns you do not want to replicate eg.:
create table AHOREJSP.TEST_EXCLUDE as select col1, col2, col4 from AHOREJSP.TEST_EXCLUDE@SOURCE as of SCN 1108430512 -- <SCN from previous step>
- Start the MINE back up.
Use Mechanism of Replicate to Create and Load Target Tables
This method uses dbvrep mechanisms to recreate and load target tables. The following grants are necessary in order to use this method:
- Grant select on tables mentioned to be prepared.
- Grant flashback on tables mentioned to be prepared.
Or you can use following power grants:
- Grant select on tables necessary to load or grant select any table to dbvrep.
- Grant flashback on all tables, necessary to load or grant flashback any table to dbvrep.
Then you should follow these steps:
- Shutdown MINE.
- Prepare tables for load:
PREPARE TABLE "AHOREJSP"."TEST_EXCLUDE2" PREPARE TABLE "AHOREJSP"."TEST_EXCLUDE3"
- Exclude columns from loading:
EXCLUDE COLUMN AHOREJSP.TEST_EXCLUDE2.COL3
- Command mine to load the tables:
DDL DROP REPLICATE "AHOREJSP"."TEST_EXCLUDE2" DDL CREATE_FROM_DICT REPLICATE "AHOREJSP"."TEST_EXCLUDE2" PROCESS LOAD_REQUEST TABLE "AHOREJSP"."TEST_EXCLUDE2" AT INSTANTIATE SCN
- Start the MINE:
Petr Horejs July 23, 2015 02:15