Case A – Additional Column on Target Table
In this example the JOBS table on the target database oradb2 has an additional IDENTITY COLUMN called ID
SQL> conn demo/oracle@oradb1
Connected.
SQL> desc jobs
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
SQL> conn demo/oracle@oradb2
Connected.
SQL> alter table jobs
add (ID NUMBER GENERATED ALWAYS AS IDENTITY);
Table altered.
SQL> desc jobs
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
ID NOT NULL NUMBER
Note the clause used in the Replicat
COLMAP(USEDEFAULTS, ID=@COLSTAT(MISSING));
GGSCI (rac01.localdomain as oggadmin@oradb2) 13> view params rep1 REPLICAT rep1 USERIDALIAS oggadmin_oradb2 MAP DEMO.JOBS, TARGET DEMO.JOBS, COLMAP(USEDEFAULTS, ID=@COLSTAT(MISSING)); MAP DEMO.COUNTRIES, TARGET DEMO.COUNTRIES; MAP DEMO.LOCATIONS, TARGET DEMO.LOCATIONS; MAP DEMO.EMPLOYEES, TARGET DEMO.EMPLOYEES; MAP DEMO.JOB_HISTORY, TARGET DEMO.JOB_HISTORY;
Test GoldenGate replication – note ID column on the target table is populated automatically
SQL> insert into jobs values ('DBA','Oracle DBA',5000,7500); 1 row created. SQL> commit; Commit complete. SQL> conn demo/oracle@oradb2 Connected. SQL> select * from jobs where job_id='DBA'; JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ID DBA Oracle DBA 5000 7500 21
Case B – Additional Column on Source Table
We now drop the ID column on the target database table and add the additional IDENTITY COLUMN on the source table instead.
[oracle@rac01 ~]$ sqlplus demo/oracle@oradb2 SQL> alter table jobs drop column id; Table altered. [oracle@rac01 ~]$ sqlplus demo/oracle@oradb1 SQL> alter table jobs add (ID NUMBER GENERATED BY DEFAULT AS IDENTITY); Table altered.
Note the clause in the Extract to handle the additional column on the source table – COLSEXCEPT (ID)
GGSCI (rac01.localdomain as oggadmin@oradb1) 46> view params ext1 EXTRACT ext1 USERIDALIAS oggadmin_oradb1 EXTTRAIL ./dirdat/xx TABLE DEMO.JOBS, COLSEXCEPT(ID); TABLE DEMO.*;
Test GoldenGate replication – note that the source JOBS table ID column is now populated automatically and this column is not present in the target JOBS table.
SQL> insert into jobs (job_id,job_title,min_salary,max_salary) values ('RSH','Market Research',10000,11000); 1 row created. SQL> commit; Commit complete. SQL> select * from jobs where job_id='RSH'; JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ID RSH Market Research 10000 11000 41 SQL> conn demo/oracle@oradb2 Connected. SQL> select * from jobs where job_id='RSH'; JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY RSH Market Research 10000 11000