in genernal

Oracle 12c, ILM Management Part-2, Temporal Validity….

In the last post, I discussed that how using the In-Database Archiving, you can manage the rows being marked as Active or Inactive which can be very handy and useful to control the display of the inactive rows and also about their participation in the DML’s even when they are not moved anywhere and would keep on existing in the same table itself. In this post, we shall see one more enhancement in the ILM management given in 12c with Temporal Validity.


In many businesses, the idea of making a record marked as non-required is often based on the business rules which are defined by the application or by the business people. For example, if you are an employee and going to leave your current company to join somewhere else, you would need an experience letter. Now the experience letter must mark your tenure from your date of hiring and not from that date when your details were keyed in the system and you were issued a badge and an official email. The same is going to be applied for an insurance policy which must mark the tenure of the policy expiry from the date you signed it and not from the date the insurance agent has sent it to the main branch to get it entered in the system. So the validity of the data is actually from the business point of view using the date which is important for the business. To get it done, usually developers add columns denoting such “start” and “end” dates in separate columns. From 12c, this is built-in right in the database itself and is called, well no prizes for the right guess-Temporal Validity!

New Terms which needs to be understood

There are few terms that are going to be required to be understood before you start making tables with the temporal validity support and playing with the data and the terms are,

  • Valid Time
  • Transaction Time

Valid Time

This is the date and time which is termed by the business as important. For example, if you have signed the bond for your car insurance on today i.e. 02-July-2013, this is the Valid time for your insurance and from today it would be termed as effective and would be expired according to the business rules, like for an insurance the tenure would be a year.

Transaction Time

This is the date and time when the required data is actually entered in the database. Since its a part of the database, the management of this time is done by the database by its own internal mechanics like SCN. Because this is an internally managed component, you can use this also to query the data but to the level the db would allow. To use Transaction Time in the queries was already there in the database using the Flashback clauses but now, using the Valid Time, businesses can decide that when the data is considered Valid according to their norms.

Period For clause

To implement Temporal Validity(TV), 12c offers the option to have two date columns in that table which is having TV enabled using the new clause Period For in the Create Table for the newly created tables or in the Alter Table for the existing ones. The columns that are used can be defined while creating the table itself and will be used in the Period For clause or you can skip having them in the table’s definition in the case of which, the Period For clause would be creating them internally. Let’s see both the create table definitions in action,


In the above statement, the table NEW_EMP has already got two columns HIRED and RELIEVED which are further used in the PERIOD FOR clause. Since the columns were present in the table’s definition right from the beginning, they would be displayed while describing the table as well as we can see below,


Now we shall create a table in which there won’t be any date/time specific columns(for example, an app developer doesn’t want the hiring date and termination date shown to the user describing the table) but would have a Period For clause.


We can see that the table is created but there are no date-time columns present in the description of the table which is understandable as there were none present in the definition of the table when it was created.


But what do we have in the DBA_TAB_COLS view?

We can see that db has created for us new implicitly created columns USER_TIME_START and USER_TIME_END usin the prefix USER_TIME which we mentioned in the PERIOD FOR clause. The clause used USER_TIME is NOT any keyword and to demonstrate the same, let’s recreate the same table with a new valid-time dimension name.

You can see that the name of the columns are now using the prefix that we have mentioned.

If you have noticed carefully, there is also one additional hidden column that got created with the name USER_T (or USER_TIME as per the last table’s definition)  and this column would be actually used by us in selecting the table based on the valid time and thus can be called valid-time dimension. Not just for the selection of the data but if in the future, you won’t want to have the table using the TV feature, you can use the name of the valid-time dimension and drop it as well.

To implement the TV, there is an implicit Check constraint also gets created in the table.


Temporal Validity in action

So we have understood by now that how to create a table supporting the TV but the important question is how do we use it in real world? If you thought so too, the answer is using the new AS OF clause. To understand it, let’s enter some values in the table NEW_EMP.



Adn this is how the table is looking finally!


Now.let’s try an AS OF PERIOD clause. I shall explain what it did in a moment.



So what is happening is that Oracle is going to search the mentioned date, 01-DEC-01 between the valid time range columns which in our case are HIRED and RELIEVED. You can confirm this by looking at the execution plan of the query.

So we can see that Oracle is applying a filter which is limiting the data using a Between AND clause over the date-time columns of our table HIRED & RELIEVED.


Now let’s modify the query for a different date.



You can see that now the output is including the rows that are within the range corresponding to this given date of ours. Let’s use different date values and see what’s the result is going to be.


As expected, the results are based on the given value and its occurance in the columns HIRED & RELIEVED.Now, we shall use that date which is not there in our table.

Obviously, there is no match found for this date in the table and we got no output.

So all what you, as a business user would need to do is to write a query with his desired date expression and Oracle would evaluate the existing data with it and based on the comparison results, the rows would be displayed and would be treated as Current or not displayed and would be treated as Non-Current. Note that the terms Current and Non-Current are actually my own terms over here to explain the concept.


We saw above that how we can use the expression to control the output of the result. There is another way to do it as well and that is to use the procedure ENABLE_AT_VALID_TIME of the package DBMS_FLASHBACK_ARCHIVE. This procedure lets you see ALL the rows of the table(default) or CURRENT rows i.e the rows which are considered to be valid. The last option for the procedure is ASOF which is similar to what the AS OF clause does when used in the query itself. For example,

Note:- I couldn’t make the values CURRENT and ALL to work properly or may be I am lacking the understanding about them somewhere. For me, the CURRENT works in the same session when the table is initially created and not afterwards. Have a look,



So till now, it worked as expected. But see what happens when I end the session and do the login again.


So I am not sure what I am missing? If anyone has a better clarity, feel free to put the explanation in the comments and I shall add that here.

Disabling Temporal Validity

As I said somewhere above, if you don’t want to use the TV feature, all what you need to do is to drop the valid-time dimension with the Alter Table command like below,

By this Drop command, if the data-time columns were implicitly created, they would now be removed and the AS OF PERIOD FOR clause won’t work.

So we can see that now even using the PERIOD FOR clause is not making any impact on the result and the entire result is shown from the table which means that the TV feature is now no longer working.


Oracle always had the functionality to move “back” in the time using its Flashback technology. But the requirement to move the data in the “future” was not really there till now. With Temporal Validity, now you can even do the “flashfront” without asking your developers writing some kind of code for it.