COLS | COLSEXCEPT
These parameters are used to Replicat only specific columns from the Source to Target. Suppose, let us consider a scenario, we have 10 columns in the source and we need only 9 columns to be replicated to the target table. Yes, this is possible using the above parameters.
COLS and COLSEXCEPT controls the columns for which data is captured. It means, only data of specific columns are captured and not all the columns.
This parameters are valid for Extract Process and should. Upon using this parameters, only the specified columns values are captured by the Extract Process and not all column’s values.
COLS This clause specifies the columns whose data needs to be captured. The columns which are used in this COLS clause are captured and the remaining columns will be ignored.
COLSEXCEPT This clause specifies the columns which are to be excluded. It means, the columns which are mentioned in this clause will not be captured. If you have a table with numerous columns and you need to only exclude single or less number of columns, then COLSEXCEPT is more efficient than listing all the columns in the COLS clause.
There are also some considerations for using COLSEXCEPT clause or parameter.
- Cannot exclude key columns.
- Columns with data types which are not supported by Oracle GoldenGate cannot be excluded using the COLSEXCEPT.
Similarly, there are also some prerequisites to be considered before the COLS.,
- The table should have one or more key columns.
- If there is no key columns, at KEYCOLS should have been used in the table.
- The key columns or the columns used as KEYCOLS should be there in the COLS clause.
column – The name of the column. You can add more columns by separating the column names by comma-delimited (,)
The example are as below.,
TABLLE SCHEMA_NAME.TABLE_NAME, COLSEXCEPT (COL5);
Note: If the database only logs values for columns that were changed in an update operation, a column specified for capture with COLS might not be available. To make those columns available, use the FETCHCOLS option in the TABLE statement or enable supplemental logging for the column.
The below is the workings.,
In this example, we have a table at source and target with the same table name and structures VEERA.TEST and I need to exclude the column BCOL which is a BLOB. Let us see, how this is achieved using the COLS and COLSEXCEPT.
Already the table has a row in it.
SOURCE SIDE
SQL> desc veera.test Name Null? Type ----------------- ----------------- ------- EMP_NO NOT NULL NUMBER NAME VARCHAR2(10) BCOL BLOB SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- -------------------- 1 SCOTT 178FA3A8
TARGET SIDE
SQL> desc veera.test Name Null? Type ----------------- ----------------- ------- EMP_NO NOT NULL NUMBER NAME VARCHAR2(10) BCOL BLOB SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- -------------------- 1 SCOTT 178FA3A8
The Extract and Extract Pump parameters are as below in the Source side. The name of the Extract process is EXT1 and the name of the Extract Pump process is DMP1.
GGSCI (OGG1.localdomain) 12> view params ext1
EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
DDL INCLUDE MAPPED;
DDLOPTIONS ADDTRANDATA, REPORT
EXTTRAIL /vol3/ogg/dirdat/et
TABLE veera.test, COLS (EMP_NO, NAME);In the above Extract parameter files, I have used the COLS clause. So whenever an operation performed in the table VEERA.TEST, the parameter will extract only the values of the columns EMP_NO and NAME from the Online Redo log files. So the column BCOL will be excluded.
EXTRACT DMP1 PASSTHRU RMTHOST OGG2, MGRPORT 7979 RMTTRAIL /vol3/ogg/dirdat/et TABLE veera.test;
NOTE: Do not use this option for tables that are processed in pass-through mode by a data-pump Extract group. If you are going to use the COLS and COLSEXCEPT in the Extract Pump, then you should not use the PASSTHRU clause in the Extract Pump parameter file.
So, in the Target side, I have the Replicat process REP1 with parameter file as below.,
GGSCI (OGG2.localdomain) 10> view params rep1 REPLICAT REP1 USERID ggadmin, PASSWORD oracle DDL INCLUDE MAPPED INCLUDE OTHER DDLOPTIONS REPORT DDLERROR DEFAULT IGNORE RETRYOP ASSUMETARGETDEFS DISCARDFILE /vol3/ogg/dirrpt/REP1.DSC, PURGE MAP veera.test, TARGET veera.test;
The Oracle GoldenGate processes in source EXT1, DMP1 and in target REP1 are running fine. Check the status as below.,
GGSCI (OGG1.localdomain) 17> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DMP1 00:00:00 00:00:08 EXTRACT RUNNING EXT1 00:00:00 00:00:05
GGSCI (OGG2.localdomain) 14> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:00
SOURCE SIDE
So, now on the source side, I am going to insert a record and commit as below.,
SQL> insert into veera.test values(2,'JACK','178FE3A8'); 1 row created. SQL> commit; Commit complete.
SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- --------------- 1 SCOTT 178FA3A8 2 JACK 178FE3A8
TARGET SIDE
Let us check on the target side, if all the columns are replicated or the BCOL is excluded.,
SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- --------------- 1 SCOTT 178FA3A8 2 JACK
From the above output, we can clearly see that the column BCOL is excluded.
SOURCE SIDE
Let us try the same with the COLSEXCEPT. Only the Extract parameter file will be modified and the Extract Pump and Replicat parameter files are same as we used above.
GGSCI (OGG1.localdomain) 27> view params ext1
EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
DDL INCLUDE MAPPED;
DDLOPTIONS ADDTRANDATA, REPORT
EXTTRAIL /vol3/ogg/dirdat/et
TABLE veera.test, COLSEXCEPT (BCOL);Instead of COLS, I have used the COLSEXCEPT. So in this I have mentioned only the column which I do not wanted to replicat to the target side table.
I just deleted the newly inserted row from the table and brought it back to the original as below.,
SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- ------------- 1 SCOTT 178FA3A8
So, now there is only one row in the table VEERA.TEST which is seen above. Let’s perform the test now.,
In the source, I am inserting a row and committing it as below.,
SQL> insert into veera.test values(2,'JACK','178FE3A8'); 1 row created. SQL> commit; Commit complete.
SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- --------------- 1 SCOTT 178FA3A8 2 JACK 178FE3A8
TARGET SIDE
Now, let us check in the table at the target side, if all the columns are replicated or not.,
SQL> select * from veera.test; EMP_NO NAME BCOL ---------- ---------- --------------- 1 SCOTT 178FA3A8 2 JACK
So in the source, only the columns which are not mentioned in the COLSEXCEPT or the columns which are mentioned in the COLS are been captured by the Extract process. This can be seen from the output of the Logdump which is below.,
Logdump 23 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 21 (x0015) IO Time : 2015/10/10 14:12:25.866.850 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 170 AuditPos : 37083320 Continued : N (x00) RecCount : 1 (x01) 2015/10/10 14:12:25.866.850 Insert Len 21 RBA 3624 Name: VEERA.TEST After Image: Partition 4 G s 0000 0005 0000 0001 3200 0100 0800 0000 044a 4143 | ........2........JAC 4b | K Column 0 (x0000), Len 5 (x0005) ===============> COLUMN 0 - EMP_NO 0000 0001 32 | ....2 Column 1 (x0001), Len 8 (x0008) ===============> COLUMN 1 - NAME 0000 0004 4a41 434b | ....JACK GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4156 376f 4141 4541 4141 4149 5041 4142 0001 | AAAV7oAAEAAAAIPAAB.. TokenID x4c 'L' LOGCSN Info x00 Length 7 3232 3836 3735 39 | 2286759 TokenID x36 '6' TRANID Info x00 Length 9 372e 3130 2e31 3137 30 | 7.10.1170
Hope this article help you understand the concept of using COLS and COLSEXCEPT clauses in the Oracle GoldenGate.
Cheers 🙂
Loading ...
Total Users : 1818562
Its really helped me to configure for Filter columns. Thank you very much.
You are most welcome..
Can we add two more columns on target side compared to source side.
for example source is having 3 columns and target should have 5 columns.
Can we add two more columns on target side compared to source side.
For example source will have 3 columns and target should have 5 columns.(Adding two audit columns)
Thanks in advance.
Hi Santhosh,
You mean, two Audit columns for which the values will be fetched from the Trail files. Yes, you can have it.
Hi,
I tried using cols except – The source table has CLOB columns that we wanted to prevent from replicating.
We made modifications to extract Param file to use colsexcept as below.. Deleted and Unregistered extract. Added and registered it again.
TABLE TEST_DATA COLSEXCEPT (col1, col2, col3…..);
The source and target tables have all the columns. Is there anything further to be done to get this working ?
Hi ,
If the columns which are excluded using COLSEXCEPT are NOT NULL columns, then it would be an issue. The replicat process will try to insert a NULL value to this column in the target and since it is a NOT NULL column, you will end up with the below error,
“Database error 1400 (OCI Error ORA-01400: cannot insert NULL into”
If the columns are NULL allowable, then the replicat will insert a NULL value and proceed with the processing. So, make sure the columns which you are excluding with COLSEXCEPT in the source are NULL allowable in the Target table.
Nice article and it really helped me to accomplish the task on given time.