DB Wiki

Create a period using existing columns

CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  PERIOD FOR student_module_period (start_date, end_date)
);

Create a period with system generated hidden columns

CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  PERIOD FOR student_module_period
  );

We can now create AS OF PERIOD FOR queries.
The below query finds all students on a currently active course.

SELECT * FROM student_modules AS OF PERIOD FOR student_module_period SYSDATE sm;

Can also create VERSIONS PERIOD FOR … BETWEEN queries.
The below query finds all students that were active in the last week.

SELECT * FROM student_modules VERSIONS PERIOD FOR 
              student_module_period BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE);

You can also use the DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME procedure

BEGIN
  DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ('ASOF','20-JUL-15 12.00.01 PM');
END;

BEGIN
  DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ('CURRENT');
END;

BEGIN
  DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ('ALL');
END;