Skip to main content
GoldenGate

COLS and COLSEXCEPT in Oracle GoldenGate

By October 10, 2015September 12th, 20168 Comments

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.

  1. Cannot exclude key columns.
  2. 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.,

  1. The table should have one or more key columns.
  2. If there is no key columns, at KEYCOLS should have been used in the table.
  3. The key columns or the columns used as KEYCOLS should be there in the COLS clause.

 

                                       COLS | COLSEXCEPT (column1, column2…);

column – The name of the column. You can add more columns by separating the column names by comma-delimited (,)

 

The example are as below.,

TABLE SCHEMA_NAME.TABLE_NAME, COLS (COL1,COL2,COL3);

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 🙂

8 Comments

  • Ashok says:

    Its really helped me to configure for Filter columns. Thank you very much.

  • Santhosh says:

    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.

  • Laks says:

    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.

  • Balaji says:

    Nice article and it really helped me to accomplish the task on given time.

Leave a Reply

© 2020 ORACLE-SCN. All Rights Reserved.