Skip to Main Content
  • Questions
  • How to Identify the MINVALUE AND MAXVALUE SCN in the flashback versions query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Revanth.

Asked: June 03, 2021 - 4:50 am UTC

Last updated: June 16, 2021 - 6:58 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 100+ times

You Asked

Hi TOM,
I am facing an issue with flashback versions query. I have described my issue below.

Query 1:
               select 
               versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN 
               from employees 
                              VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2021-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND 
                              TO_TIMESTAMP('2021-06-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')  
               where employee_id='xyz'


the above query returns 2 records.

XID                        START_SCN                        END_SCN
0B0017008F7B0300         39280796004                    39282671828                    [INSERT]
2D001B0016420000         39282671828                         (null)                    [UPDATE]

But on passing the versions_startscn value from the 1st query result in the filter condition of 2nd query,
I got 0 records returned instead of 1 record.

Query 2:
               select 
               versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN  
               from employees 
                              VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 
               where versions_endscn = '39282671828'

the above query returns 0 records.

Is there a way to identify the MINVALUE and MAXVALUE passed in the second query?
On what cases the MINVALUE gets set?

and we said...

Remove

 
where versions_endscn = '39282671828'


Lets see what SCN's come out of your second query.

Rating

  (2 ratings)

Comments

Result after removing filter

REVANTH S, June 11, 2021 - 2:54 pm UTC

on removing the filter,
where versions_endscn = '39282671828',

XID START_SCN END_SCN
null null null
null null null
null null null
. . .
. . .
. . .


with the filter "where versions_startscn is not null" , 0 records are returned.
with the filter "where versions_endscn is not null" , 0 records are returned.
Connor McDonald
June 16, 2021 - 6:58 am UTC

Hmm...

Take a look at sys.smon_scn_time and see what the max and min for TIME_DP is.

(Thats the table where we map timestamps to scns and vice-versa)

REVANTH S, June 15, 2021 - 7:29 am UTC

Also to mention, we had changed the retention period from 1 year to 1 month recently, could this be the reason behind the behavior of MINSCN?

More to Explore

Backup/Recovery

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