Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Maxim.

Asked: April 07, 2021 - 6:26 am UTC

Last updated: May 26, 2021 - 1:08 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi!

We would appreciate your advice about problem we have.
We have two tables of the same structure in different DB (DB1 and DB2) . Rows are inserted into table in DB1, and then some of them (based on simple condition) are being prorogated to the table in DB2 via trigger using dblink.
When rows are inserted into those tables different sequences are used to generate primary key values. So, all the data in rows being pushed between DB is identical except for PK values.
Now, we need to synchronize PK values as well and we would like to drop sequence in DB2 and insert rows with incoming PK values.
Is it possible to actualize PK values for already inserted rows in the table in DB2 or the only way in to drop and recreate the table ?

Thank you!

and Connor said...

Yes you can update primary keys in a database table, but if those tables are parents of child tables, then you might be end for a big job ensuring that all those PK updates cascade all the way down into child tables.

I'm not entirely sure what your question is here. Could you perhaps add a comment with some test case data and what you're trying to achieve. It sounds just like you need to write some joins to match two tables on their non-PK columns.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Some details

Maxim, April 26, 2021 - 5:58 am UTC

Hello! Thank you very much for accepting our question! I try to add some detailes to make the problem easiler to understand. There is a table T1 in DB1

Create table t1
(
Id number primary key,
some_column1 varchar2 (50),
some_column2 varchar2 (50)
);

Rows are inserted using a sequence sq_t1.nexval for id value

Insert into table t1
(
Id ,
Some_column1,
Some_column2
)
Values
(
sq_t1.nexval ,
'r',
's'
);

There is an after insert trigger on that table that inserts some of the rows (rows with a specific value of Some_column2 ) into a stage table in DB1. Once in a while a process awakes (every 5 min), gets the rows from the stage table in DB1 and sends them over a DBlink to a stage table in DB2. In DB2 a job every 5 min checks the stage table, and invokes a procedure that inserts data from stage table in a table t1 - a copy of table t1 in DB1, and into a bunch of child tables.
The problem is that rows are being inserted in the parent table t1 (copy of table t1) using its own sequence for id column, and, at the same time, - into child tables with id of rows in DB1.
There are no FK constraints on child tables, so that process was going unnoticed for years.

INSERT INTO t1 -- a table in DB2
(id,
Some_column1,
Some_column2)
VALUES
(sq_t1.nextval,
data1_from_row_from_stage_table,
data2_from_row_from_stage_table);


INSERT INTO t1_child -- a table in DB2
(id,
id_t1,
date_column)
VALUES
(sq_t1_child.nextval,
id_from_row_from_stage_table,
sysdate);

Now, when we have discovered this bug and we are looking for the best way to make it right. We can see two options
1) update PK in t1 in DB2 based on values in t1 DB1 (but the table is big - about 200 million rows) and stop using sequence for id values in it
2) keep using sequence and update id_t1 columns in child tables ( easier way - but the first way is more preferable for us)

We would appreciate your advise about this matter. Does the option 1 seem insane?

Connor McDonald
May 06, 2021 - 5:09 am UTC

200 million rows is big but not *that* big, so you should be able to correct this.

However, an update is probably a very slow way to do it - you'll be quick to recreate the tables and correct the ID as you go.

The process for this would be:

In DB1
- unload a copy of T1 being just the columns you need to do sufficient matching in DB2
- load that table up into DB2 as (say) T1_TEMP

In DB2

- create T1_NEW by joining T1 and T1_TEMP using a CTAS
- do the same with T1_CHILD_NEW
- Then apply indexes, foreign keys etc once those new tables are loaded.
- drop the old tables and rename the _NEW tables to the correct name

Our thanks :)

Maxim, May 26, 2021 - 5:11 am UTC

Thank you very much for your advice. We've tried it in our test environment, and it worked :)
Chris Saxon
May 26, 2021 - 1:08 pm UTC

Great to hear, thanks for letting us know :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.