You create a table with the period for clause to enable the use of the Temporal Validity
feature of Oracle Database 12c. Which three statements are true concerning the use of the
Valid Time Temporal feature for the EMPLOYEES table?
A.
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.
B.
The valid time columns employee_time_start and employee_time_end are automatically
created.
C.
The same statement may filter on both transaction time and valid temporal time by using
the AS OF TIMESTAMP and PERIOD FOR clauses.
D.
The valid time columns are not populated by the Oracle Server automatically.
E.
The valid time columns are visible by default when the table is described.
BCD
ABC
B, C, D
B 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
C 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
D 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;
A 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.