Today is PI Day. March 14 or: 3-14 in the US. Of course it can be March 14th 2015 – 3-14-15 – only once but for some reason March 14 is baptized PI Day and is not unlike April Fool’s day the international day for nerdi stuff. You may want to check out https://314piday.com/ where Oracle is offering a range of PI Day specials, such as tech demos and hands-on labs to talks about Linux, Graal, MySQL, and Oracle Cloud Infrastructure (OCI) including deep dives into analytics and AI/machine learning. Dozens of members of the Oracle community are publishing special content as well, on a wide range of topics and with various levels of seriousness and applicability.
I was reading on the source code control system git – all kinds of wonderful things (in Head First Git which I can recommend). Git allows you to access the history of files and of commit transactions using a simple notation,. HEAD refers to present, HEAD~1 is the last generation before the current state of affairs, HEAD~2 is the generation before that. And so forth. For example: to view the version of file <repository-root>/src/readme.md from 3 commits ago, use:
git show HEAD~3:src/readme.md
The Oracle Database supports similar behavior. You can perform a select query that does not return the present, but that instead returns glimpses of the past. Not with quite the same syntax, but something similar. This feature is called Flashback and it is available in every edition of the Oracle Database. It goes like this:
SELECT
empno,
ename,
job,
sal,
comm,
deptno,
generation,
versions_starttime,
versions_endtime,
versions_operation,
last_operation
FROM ( SELECT
empno,
ename,
job,
sal,
comm,
deptno,
versions_starttime,
versions_endtime,
versions_operation,
first_value(versions_operation) over (PARTITION BY empno ORDER BY versions_startscn desc NULLS last ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_operation,
-1* (row_number() OVER (PARTITION BY empno ORDER BY versions_startscn desc NULLS last)-1) as generation
FROM
EMP versions between timestamp minvalue and maxvalue
)
WHERE
empno = 7782
Querying EMP must be a considered a nerdi thing worthy of PI day, right? And just to be sure: I have done no manipulation of table EMP. I went to Live SQL, ran the scripts provided by Mike Hichwa to create EMP and DEPT, performed a few updates and commits and ran the above query.
The result of this query gives us the full history (still available to us) of BLAKE (empno == 7782):
The expression appended to the table name EMP: versions between timestamp minvalue and maxvalue is an instruction to the Oracle Database to query not just the current records in table EMP but also all past records it can still reconstruct from the UNDO data (also used for rollback operations and long running queries) or possibly from a Flashback Data Archive assigned to table EMP (which in this case there is not)
The versions_starttime and versions_endtime colummns indicate when the specific version of the database record was created – time of commit – and when it was superseded by the next version. The original time of creation is not available though – that depends on your religion perhaps.
VERSIONS_OPERATION contains U or D for Update or Delete – depending on what was done to the record in this specific generation. I for Insert is not returned, but can easily be derived for a version without value for VERSIONS_OPERATION
Note that we can even query the record after it has been deleted. In this example, BLAKE does no lo longer exist in EMP. The LAST_OPERATION column contains the value D for Deleted and the current generation has D for VERSIONS_OPERATION.
To query a specific generation – say HEAD~3 – we can define this where clause:
Of course we can also compare different generations of a record to find out what was changed exactly, just as Git can show us a file comparison between versions. That is an exercise for the reader – or perhaps for next PI day?
Resources
Check out my presentation – Oracle OpenWorld 2015, talking about glimpses from the past – for a detailed overview of Flashback Query and Versions Query: https://technology.amis.nl/database/oracle12/time-is-of-the-essence-the-fourth-dimension-in-oracle-database-12c-on-flashback-and-temporal-database/
Try out LiveSQL to run SQL against a live Oracle Database: Live SQL
PI Day many at https://314piday.com/
