Using Temporal Validity

Temporal Validity enables you to track time periods for real world validity. Valid times can be set by users and applications for data, and data can be selected by a specified valid time, or a valid time range.

Applications often note the validity (or effectivity) of a fact recorded in a database with dates or timestamps that are relevant to the management of a business. For example, the hire-date of an employee in a human resources (HR) application, which determines the effective date of coverage in the insurance industry, is a valid date. This date is in contrast to the date or time at which the employee record was entered in the database. The former temporal attribute (hire-date) is called the valid time (VT) while the latter (date entered into the database) is called the transaction time (TT). While the valid time is usually controlled by the user, the transaction-time is system-managed.

For ILM, the valid time attributes can signify when a fact is valid in the business world and when it is not. Using valid time attributes, a query could just show rows that are currently valid, while not showing rows that contains facts that are not currently valid, such as a closed order or a future hire.

Concepts that are integral to valid time temporal modeling include:

• Valid time

  This is a user-defined representation of time. Examples of a valid time include project start and finish dates, and employee hire and termination dates.

• Tables with valid-time semantics

  These tables have one or more dimensions of user-defined time, each of which has a start and an end.

• Valid-time flashback queries

  This is the ability to do as-of and versions queries using a valid-time dimension.

A valid-time period consists of two date-time columns specified in the table definition. You can add a valid-time period by explicitly adding columns, or the columns can be created automatically. A valid-time period can be added during the create table or alter table process.

To support session level visibility control for temporal table queries, the DBMS_FLASHBACK_ARCHIVE PL/SQL package provides the ENABLE_AT_VALID_TIME procedure. To execute the procedure, you need the required system and object privileges.

The following PL/SQL procedure sets the valid time visibility as of the given time.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time 
          ('ASOF', '31-DEC-12 12.00.01 PM');

The following PL/SQL procedure sets the visibility of temporal data to currently valid data within the valid time period at the session level.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

The following procedure sets the visibility of temporal data to the full table, which is the default temporal table visibility.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');