Skip to Main Content
  • Questions
  • Flashback Data Archive query returning duplicates

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, mikhail.

Asked: September 09, 2020 - 2:34 pm UTC

Answered by: Connor McDonald - Last updated: September 14, 2020 - 2:53 am UTC

Category: SQL - Version: 12.1.0.2.0

You Asked

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.


and we said...

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.



and you rated our response

  (1 rating)

Reviews

Thank you!

September 15, 2020 - 9:49 am UTC

Reviewer: Mikhail from Russia

Thank you for your time, I'll try to contact Oracle support.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.