You create a table with the PERIOD FOR clause to enable the use of the Temporal Validity
feature of Oracle Database 12c.
Examine the table definition: 
Which three statements are true concerning the use of the Valid Time Temporal feature for the
EMPLOYEES table?
A.
The valid time columns employee_time_start and employee_time_end are automatically
created.
B.
The same statement may filter on both transaction time and valid temporal time by using the AS
OF TIMESTAMP and PERIOD FOR clauses.
C.
The valid time columns are not populated by the Oracle Server automatically.
D.
The valid time columns are visible by default when the table is described.
E.
Setting the session valid time using
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data
manipulation language (DML), data definition language (DDL), and queries performed by the
session.
Explanation:
A: 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.
E: ENABLE_AT_VALID_TIME Procedure
This procedure enables session level valid time flashback.
ACE?
A,B,C correct.
*
correct A The valid time columns employee_time_start and employee_time_end are automatically created.:
Oracle automatically creates two hidden columns whose name is prefixed with the name of the valid-time dimension, which is specified as USER_TIME in this example. Because the valid-time dimension is named USER_TIME, Oracle automatically creates two columns named USER_TIME_START and USER_TIME_END.
correct C The valid time columns are not populated by the Oracle Server automatically.:
Because the implicitly created valid-time dimension columns are hidden in the second table (EMP2), you must explicitly specify the two valid-time date columns during INSERT and SELECT operations (and DELETE/UPDATE operations as well)
See: OCP Upgrade to Oracle Database 12c Exam Guide (Exam 1Z0-060)
*
correct B The same statement may filter on both transaction time and valid temporal time by using the AS OF TIMESTAMP and PERIOD FOR clauses.:
Temporal validity dates or time stamps are different than the dates or timestamps indicating
when the fact was recorded in the database. The date or timestamp when the fact was
recorded in the database are attributes of Temporal History (also known as Flashback Data
Archive) and are system-managed. The Oracle Database 11g Total Recall feature (or FDA)
implements the transaction time of temporal history.
In the slide, employee 400 was hired on Mar 22, but the row was entered in the HR.EMP table
on Mar 23. Mar 22 is the valid time temporal start date and Mar 23 is the transaction time
temporal start date.
By using the valid time temporal implicit filter on the valid-time dimension, queries can show
rows that are currently valid or that will be valid in the future. The query is able to hide rows
whose facts are not currently valid.
Bi-temporal queries can use both valid time temporal and transaction time temporal date.
You can filter the active employees by using the following new VERSIONS PERIOD FOR
BETWEEN clause:
SQL> select * from hr.emp VERSIONS PERIOD FOR user_time
2 BETWEEN to_date(’31-DEC-2011′, ‘dd-mon-yyyy’)
3 AND to_date(’31-DEC-2012′, ‘dd-mon-yyyy’) ;
The query displays all employees whose VALID_TIME_START is less than or equal to ’31-
DEC-2011′ and VALID_TIME_END greater than or equal to ’31-DEC-2012′.
Queries that mix valid-time and transaction-time dimensions are called bi-temporal queries.
This example shows rows as of the specified transaction time, which are valid now.
SQL> select * from hr.emp
2 as of period for user_time to_date(’31-DEC-1992′, ‘dd-mon-yyyy’)
3 as of timestamp to_date (’30-mar-2012′,’dd-mon-yyyy’);
incorrect: E Setting the session valid time using
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME sets the visibility for data manipulation language (DML), data definition language (DDL), and queries performed by the session.–
(DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME –incorrect for DLL):
DBMS_FLASHBACK_ARCHIVE
• Visibility control applies to queries and DML.
• Full visibility applies to DDL.
• Visibility set with DBMS_FLASHBACK_ARCHIVE:
– Set the visibility to data valid as of the given time
– Set the visibility to data currently valid within the valid time
period at the session level.
– Set the visibility to data to the full table level.
Users can modify visibility within a session via new DBMS_FLASHBACK_ARCHIVE package.
Visibility control applies to all SQL SELECT and DML statements.
DDLs will default to getting full visibility to the table data. For example, CTAS, online
redefinition and ALTER TABLE MOVE operations will have full visibility of the table data.
Hidden columns are also visible to the DDL operations, resulting in preservation of those
columns and their data.
The first example sets the valid time visibility to ‘29-SEP-2010’, showing only rows
overlapping the given date.
The second example sets the visibility of to data currently valid within the period time at the
session-level.
The third example sets the visibility to the full table which is the default temporal table visibility.
See: Oracle Database 12c Student Guide vol-1
A,B,E is correct
E correct: We can filter on valid-time columns using the SELECT statement with the PERIOD FOR clause or use the DBMS_FLASHBACK_ARCHIVE procedure.
The DBMS_FLASHBACK_ARCHIVE controls the visibility of the data as at a given time or data which is currently valid within the valid time period at the session level or can also set the visibility to the full table level.
ABC are correct. E is not correct since DBMS_FLASHBACK_ARCHIVE can set visibility control for DMLs and SELECTs but not DDLs. DDLs are fully visible.
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html
From these examples A,B,C correct
E is not correct since DBMS_FLASHBACK_ARCHIVE can set visibility for DML and SELECT but DDL are always fully visible.
ABC
SQL> create table employees(empno number, salary number, deptid number,
2 name varchar2(100), period for employee_time);
Table created.
SQL> desc employees
Name Null? Type
—————————————– ——– —————————-
EMPNO NUMBER
SALARY NUMBER
DEPTID NUMBER
NAME VARCHAR2(100)
SQL> insert into employees(empno, salary, deptid, name) values(1,1000,99.’Siva’)
;
insert into employees(empno, salary, deptid, name) values(1,1000,99.’Siva’)
*
ERROR at line 1:
ORA-00917: missing comma
SQL> ed
Wrote file afiedt.buf
1* insert into employees(empno, salary, deptid, name) values(1,1000,99,’Siva’)
SQL> /
1 row created.
SQL> select * from employees;
EMPNO SALARY DEPTID
———- ———- ———-
NAME
——————————————————————————–
1 1000 99
Siva
SQL> select empno, employee_time_start, employee_time_end from employees;
EMPNO
———-
EMPLOYEE_TIME_START
—————————————————————————
EMPLOYEE_TIME_END
—————————————————————————
1
SQL> commit;
Commit complete.
SQL> /
Commit complete.
SQL> select empno, employee_time_start, employee_time_end from employees;
EMPNO
———-
EMPLOYEE_TIME_START
—————————————————————————
EMPLOYEE_TIME_END
—————————————————————————
1
So C is correct along with A and B.