Skip to Main Content
  • Questions

  • Flashback Query using Flashback Data Archive and minvalue does not work as expected

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arvid.

Asked: September 20, 2021 - 11:06 am UTC

Last updated: September 27, 2021 - 11:00 am UTC

Version: "Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0"

Viewed 1000+ times

You Asked

Hello,

I created a Flashback Data Archive and gave it a retention of one year.
Now I have enabled flashback archive for a test table.
My problem is that minvalue does not work as I expected. My expectation is that the specified query will return ALL values that are present in the flashback archive. In fact, it only returns the data from the last 15 min.

Is this the correct behavior? If yes, how can I query all previous versions of a row with a query?

The given example shows the DDL of the test objects and an example query with timestamps when they were executed. Sorry for not using LiveSQL, but i wasn't able to recreate the Problem there. 

create table test (
    tst_id number generated by default as identity,
    tst varchar2(255)
)

create flashback archive flashback_test
tablespace users_flashback
retention 1 year;

alter table test flashback archive flashback_test;

15.09.21 11:41:28,534508000 +02:00
insert into test(tst)
values('Test1');

15.09.21 11:43:15,736558000 +02:00
update test
set tst = 'Test2' 
where tst_id = 1; 

15.09.21 11:45:47,551388000 +02:00
update test
set tst = 'Test3' 
where tst_id = 1; 

select tst, versions_starttime, versions_endtime
from test
versions between scn minvalue and maxvalue
where tst_id = 1;


tst     versions_starttime      versions_endtime

15.09.21 11:48:09,833296000 +02:00
Test3   15.09.21 11:45:47 
Test2   15.09.21 11:43:22       15.09.21 11:45:47
Test1   15.09.21 11:41:22       15.09.21 11:43:22

15.09.21 11:58:20,512213000 +02:00
Test3   15.09.21 11:45:47 
Test2   15.09.21 11:43:22       15.09.21 11:45:47
Test1                           15.09.21 11:43:22

15.09.21 11:59:52,966693000 +02:00
Test3   15.09.21 11:45:47 
Test2                           15.09.21 11:45:47

15.09.21 12:04:07,629023000 +02:00
Test3



and Connor said...

Sorry - I'm a bit lost.

If you have only done 2 inserts and 1 update since the table was create, that's all you are going to see in the flashback archive.

In a years time, that same query will show all the changes in the past year, but assuming you've *just* created the table and archive, then you only see the changes you've just done.

Rating

  (3 ratings)

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

Comments

The Timestamps are important

Arvid Regenberg, September 23, 2021 - 6:38 am UTC

Hello,

please have a closer look at the given timestamps. I make the first VERSIONS query after the last insert. The problem is that the amount of rows supplied becomes less over time, although the number of versions present remains the same.
If I run the VERSIONS query and specify an explicit timestamp instead of MINVALUE, all available versions are delivered.

Based on your answer I assume that this behavior is not correct and MINVALUE should return me all available versions, is that correct?

Many greetings
Arvid Regenberg

Translated with www.DeepL.com/Translator (free version)
Connor McDonald
September 27, 2021 - 10:59 am UTC

That is correct, and I suspect it might be a bug that has been fixed in more recent versions because I cannot reproduce on my 19c version


SQL> create table test (
  2      tst_id number generated by default as identity,
  3      tst varchar2(255)
  4  );

Table created.

SQL>
SQL> create flashback archive flashback_test
  2  tablespace demo
  3  retention 1 year;

Flashback archive created.

SQL>
SQL> alter table test flashback archive flashback_test;

Table altered.

SQL>
SQL> insert into test(tst) values('Test1');

1 row created.

SQL> commit;

Commit complete.

SQL> host sleep 120

SQL>
SQL> update test set tst = 'Test2'  where tst_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> host sleep 120

SQL>
SQL> update test set tst = 'Test3'  where tst_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL> select tst, versions_starttime, versions_endtime
  2  from test
  3  versions between scn minvalue and maxvalue
  4  where tst_id = 1;

TST
----------------------------------------------------------------------------------------------------------------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME
---------------------------------------------------------------------------
Test1
27-SEP-21 06.25.58.000000000 PM
27-SEP-21 06.28.09.000000000 PM

Test3
27-SEP-21 06.30.20.000000000 PM


Test2
27-SEP-21 06.28.09.000000000 PM
27-SEP-21 06.30.20.000000000 PM


SQL> host sleep 120

SQL> select tst, versions_starttime, versions_endtime
  2  from test
  3  versions between scn minvalue and maxvalue
  4  where tst_id = 1;

TST
----------------------------------------------------------------------------------------------------------------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME
---------------------------------------------------------------------------
Test1
27-SEP-21 06.25.58.000000000 PM
27-SEP-21 06.28.09.000000000 PM

Test3
27-SEP-21 06.30.20.000000000 PM


Test2
27-SEP-21 06.28.09.000000000 PM
27-SEP-21 06.30.20.000000000 PM


SQL> host sleep 120

SQL> select tst, versions_starttime, versions_endtime
  2  from test
  3  versions between scn minvalue and maxvalue
  4  where tst_id = 1;

TST
----------------------------------------------------------------------------------------------------------------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME
---------------------------------------------------------------------------
Test1
27-SEP-21 06.25.58.000000000 PM
27-SEP-21 06.28.09.000000000 PM

Test3
27-SEP-21 06.30.20.000000000 PM


Test2
27-SEP-21 06.28.09.000000000 PM
27-SEP-21 06.30.20.000000000 PM


I suggest you raise an SR for this one

Additional Info + Example

Arvid Regenberg, September 23, 2021 - 7:11 am UTC

select tst, versions_starttime, versions_endtime
from test
versions between scn minvalue and maxvalue
where tst_id = 1;

This query still returns only one row:

tst versions_starttime versions_endtime
Test3


select tst, versions_starttime, versions_endtime
from test
versions between timestamp to_timestamp('15.09.2021 11:41:28', 'DD.MM.YYYY HH24:Mi:SS') and maxvalue
where tst_id = 1;

This query returns all available versions. This is what i would expect from the first query as well.

Maybe is related to undo_retention

Ramón Caballero, September 23, 2021 - 1:39 pm UTC

Hi,
I tried to search from where Oracle calculates the scn minvalue and I could not find it. What I found was that all examples using minvalue assume UNDO_RETENTION meaning you are using Flashback Query, the original.
All examples for Flashback Data Archival do not use minvalue but specific scn values or timestamps.
So please perform 2 tests:
1.-change the undo_retention to validate how minvalue works
2.-Use SCN 0 (zero), there is one bug reported with this but it might work as you want
Hope it helps!
Connor McDonald
September 27, 2021 - 11:00 am UTC

Here's some background on how we store the information on SCNs

https://connor-mcdonald.com/2019/06/13/smon_scn_time-and-ora-8161-digging-deeper/

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

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