Do we have similar kind of checksum function (SQL) in oracle function. I would to store it in a table and use key/value for update statements. One of my current process contains 15 columns, need to check any change is there between source and destination, instead of checking one by one column whether change is happened or not, would like to have single column in table to helps whether change happened in 15 columns
1 Answers
Databases are good at keeping track of changes, that's what they are for.
Maybe the built-in ORA_ROWSCN is good enough for your change tracking? By default it tracks only the changes per block, but you can enable a table to track the change for each row with the - oddly named - clause ROWDEPENDENCIES:
CREATE TABLE t (
a NUMBER,
b NUMBER,
c NUMBER
) ROWDEPENDENCIES;
INSERT INTO t VALUES (1,1,1);
INSERT INTO t VALUES (2,2,2);
SELECT current_scn FROM v$database;
2380496
After a while:
UPDATE t SET b=20, c=20 WHERE a=2;
SELECT current_scn FROM v$database;
2380665
Now you can query the system change number SCN each row was changed:
SELECT ORA_ROWSCN AS scn, a,b,c FROM t;
SCN A B C
2380496 1 1 1
2380665 2 20 20
For the last 5 days, Oracle can translate a SCN to approximate real time (in Oracle 10 ± 5 minutes, from Oracle 11 ± 3 seconds):
SELECT ORA_ROWSCN AS SCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) AS ts, a,b,c FROM t;
SCN TS A B C
2380496 2020-05-15 20:20:38 1 1 1
2380665 2020-05-15 20:23:06 2 20 20
It's documented in Oracles SQL Language Reference.
Related