I have 6 tables to extract data from each one of the 4 databases. I have to replicate all that data in 6 tables of single database. Target tables have just one extra column 'instance_id' which shows that we are getting data from which Database. Now I have one extract process for each database and 4 replicate process in target database. I want to update 'instance_id' column automatically as soon as row is entered in target table using OGG replication. I know there is SQLEXEC statement which can run SQL queries in OGG. I don't know where and how to use it to solve my problem here.
If you have 4 sources, you have 4 sets of trail files and 4 replicats. In the replicats include your instance_id in the column MAP. Also - if getting data from the 4 sources is going to cause primary key collisions, you will have to include instance_id in your PK definition. Would look something like:
MAP schema.table, TARGET schema.table,
COLMAP(USEDEFAULTS, instance_id = 1),
KEYCOLS(pkcol, instance_id);