Hello!
I am using Oracle DB with "Flashback Data Archive" enabled.
I try to get one row as it was some time ago. For this I do "as of query"
But I got 2 rows instead of 1, although ID is Primary Key:
SQL> SELECT ID, STATE$DOC
2 FROM WB.CLAIMS AS OF TIMESTAMP TO_TIMESTAMP('2020-09-04 13:34:12', 'YYYY-MM-DD HH24:MI:SS')
3 WHERE ID = 521228;
ID STATE$DOC
---------- -------------------------------
521228 Draft
521228 Confirmed
query with AS OF SCN:
SQL> SELECT ID, STATE$DOC
2 FROM WB.CLAIMS AS OF SCN timestamp_to_scn ( TO_TIMESTAMP('2020-09-03 13:34:12', 'YYYY-MM-DD HH24:MI:SS'))
3 WHERE ID = 521228;
ID STATE$DOC
---------- -------------------------------------------------------------------------------------------------------------------------
521228 Draft
521228 Confirmed
Is something wrong with database?
Or this is a normal behavior. If so how can I understand which row is right?
Thanks in advance.
That looks like a bug to me.
Check out the following MOS note
Duplicate Data/Wrong Results with Flashback Data Archive Enabled (Doc ID 1998690.1)
There are several reparative actions to look at.