Which three statements are true concerning the use of the Valid Time Temporal feature for the EMPLOYEES table?

A
C – Invisible columns and must be explicitly mentioned in SIUD.
E

A B and E

C is wrong as per Oracle student Guide.

So the answer must be ABE

Why C is false?
The valid-time columns are not populated automatically. The columns are create automatically but not populate.

we can use update or insert for populate employee_time_start and time_end.

ACE

b:combination “as of timestamp” and “period for”, not likely. one can use “as of period for” and to_timestamp.
d: incorrect, as you have to explicitly select the columns.
e: ddl is default visible, so that is a blemish on the answer.

SQL > CREATE TABLE emp2
2 ( empno number, salary number, deptid number,
3 name VARCHAR2(100),
3 PERIOD FOR user_time);

A valid-time dimension, represented by the new PERIOD FOR clause, consists of two datetime columns that can be specified in the table definition, as shown in the first example or that are automatically created. Hence, A is correct.

To hide valid-time dimension columns, just specify a PERIOD FOR clause name without any date columns. Oracle creates two hidden columns using the name of the valid-time dimension as a prefix for the names of the two columns. The valid-time dimension name is used to drop the dimension if required. As shown in the second example, you defined the user_time as the name of the valid-time dimension and user_time is used as the prefix for the two date columns automatically created USER_TIME_START and USER_TIME_END.

How do you filter on valid-time columns? Use the SELECT statement with the PERIOD FOR clause or use the DBMS_FLASHBACK_ARCHIVE procedure.
• There is one set of data that is “valid” based on its valid-start and valid-end times and the query time (AS OF or undecorated). Hence, B is correct.
• On the other hand there is the other set of rows where the query time falls outside the
valid-start and valid-end times.

Both sets of rows data reside in the same table. However by controlling the visibility of data to the valid rows, you can limit what queries and DMLs affect. Choice D is incorrect. Until now, you could do as-of and versions queries for transaction time. Now, you can do as-of and versions queries for valid time.
For each new employee that you inserted in the table, you included the hire dates, valid-time start dates, and valid end dates. The dates represent the activeness of each row. These dates are entered by the application and correspond to valid dates. The time that the rows were inserted and committed in the table corresponds to the transaction date.
You can filter the active employees by using the following new PERIOD FOR clause. The
query displays all active employees who were valid at the explicit date of ’01-DEC-1992′, which is the date that belongs to the valid period; that is, between USER_TIME_START and USER_TIME_END.

Visibility control applies to queries and DML.
• Full visibility applies to DDL.
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. Hence, Choice E is incorrect.

Temporal Validity columns are not automatically updated by
Oracle. Hence, C is correct.

Temporal Validity and temporal history can be used in the same query. B is correct.

I would go with A, B, C

A, B, C

A Correct

SQL> select TABLE_NAME, COLUMN_NAME from dba_tab_cols where owner ‘HR’ and table_name=’EMPLOYEES’;

TABLE_NAME COLUMN_NAME
———- ——————–
EMPLOYEES NAME
EMPLOYEES DEPID
EMPLOYEES SALARY
EMPLOYEES EMPNO
EMPLOYEES EMPLOYEE_TIME
EMPLOYEES EMPLOYEE_TIME_END
EMPLOYEES EMPLOYEE_TIME_START

B Correct

SQL> select NAME,
to_char(valid_time_start, ‘dd-mon-yyyy’) “Start”,
to_char(valid_time_end, ‘dd-mon-yyyy’) “End”
from hr.emp AS OF PERIOD FOR valid_time
to_date(’31-DEC-1992′, ‘dd-mon-yyyy’) ; 2 3 4 5

NAME Start End
——– ——————– ——————–
ADAM 01-jan-1990 31-dec-2010
SCOTT 01-jan-1991 31-dec-2011
JIM 01-jan-1992 31-dec-2013
JEAN 01-jan-1992 31-dec-2012
JAMES 31-dec-1992 31-dec-1994

C correct

SQL> CREATE TABLE HR.EMP
(EMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER,
NAME VARCHAR2(100),
PERIOD FOR VALID_TIME ); 2 3 4

Table created.

SQL> select TABLE_NAME, COLUMN_NAME from dba_tab_cols where owner=’HR’ and table_name=’EMP’;

TABLE_NAME COLUMN_NAME
———- ——————–
EMP VALID_TIME_START
EMP VALID_TIME_END
EMP VALID_TIME
EMP EMPNO
EMP SALARY
EMP DEPTID
EMP NAME

7 rows selected.

SQL> DESC hr.emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NUMBER
SALARY NUMBER
DEPTID NUMBER
NAME VARCHAR2(100)

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (101,1900,90,’ADAM’,to_date(’01-JAN-1990′, ‘dd-mon-yyyy’) ,to_date(’31-DEC-2010′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (102,1991,91,’SCOTT’,to_date(’01-JAN-1991′, ‘dd-mon-yyyy’) ,to_date(’31-DEC-2011′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (103,1992,92,’JIM’,to_date(’01-JAN-1992′, ‘dd-mon-yyyy’) ,to_date(’31-DEC-2013′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (104,1992,92,’JEAN’,to_date(’01-JAN-1992′, ‘dd-mon-yyyy’),to_date(’31-DEC-2012′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (105,1993,93,’MARIA’,to_date(’01-JAN-1993′, ‘dd-mon-yyyy’) ,to_date(’31-DEC-2011′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START)
VALUES (106,1994,94,’TOM’,to_date(’01-JAN-1994′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (107,1996,92,’KIM’,to_date(’01-JAN-1994′, ‘dd-mon-yyyy’) ,to_date(’30-JUN-1994′, ‘dd-mon-yyyy’));

INSERT INTO hr.emp
(empno , salary , deptid , name , VALID_TIME_START, VALID_TIME_END)
VALUES (108,1996,92,’JAMES’,to_date(’31-DEC-1992′, ‘dd-mon-yyyy’) ,to_date(’31-DEC-1994′, ‘dd-mon-yyyy’));

commit;

E incorrect

Use new procedures of DBMS_FLASHBACK_ARCHIVE package to set the time visibility.
Set the visibility of temporal data to currently valid data within the valid time period at the
session level.

I chose A,B,E in exam and failed it.

ABC

ABE

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html