Skip to Main Content
  • Questions
  • How to obtain the actual numerical value of the TIMESTAMP MINVALUE or TIMESTAMP MAXVALUE Keyword

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Partha.

Asked: May 19, 2021 - 6:42 pm UTC

Last updated: June 08, 2021 - 2:34 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello Tom,

In the SELECT statement for FLASHBACK VERSION QUERY, one can successfully use

"VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE"

clause.

I am looking for some way to obtain the numerical value of the TIMESTAMP MINVALUE or TIMESTAMP MAXVALUE keywords using SELECT TIMESTAMP MINVALUE FROM DUAL like syntax or some other way if possible.

Please help if it is possible to view the actual numeric value of these keywords.

Best Regards

and Connor said...

Keeping within the realms of SMON_SCN_TIME is probably the best option here, and to dig into that you can read here

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

because outside of this, you are most likely to hit scns and timestamps of null or hit the ORA-08181 error referred to in the post.

If you really are looking at digging into those values, then you probably want to add Flashback Data Archive into the mix, at which point you can query the underlying history tables for SCNs and timestamps that are tracked


Rating

  (1 rating)

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

Comments

FLASHBACK DATA ARCHIVE is enabled condition

Partha ROY, June 03, 2021 - 11:40 am UTC

Hello Tom,
Thank you for the detailed link and video. It was helpful.
In my database, FLASHBACK DATA ARCHIVE feature is already enabled.
Under this setup where FLASHBACK DATA ARCHIVE is already enabled, is there any way to get the SCN for MINVALUE and MAXVALUE by some query?
Or, I will have to understand the link you sent earlier to create some PL/SQL code to query internal tables [undocumented features] and map MINVALUE/MAXVALUE to SCN?
I thought MINVALUE and MAXVALUE are documented Keywords and hence there must be some documented way to map MINVALUE/MAXVALUE to SCN when FLASHBACK DATA ARCHIVE feature is enabled for an User TABLE.
Best Regards
Connor McDonald
June 08, 2021 - 2:34 am UTC

Checking when the TCRV/HGIST tables were created would be a reasonable approximation to when flashback data archive was enabled. I say *reasonable* because they get created on demand as they are needed, so it might be slightly *after* the time you enabled FDA.


More to Explore

Backup/Recovery

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