Sunday, September 7, 2014

Implementing Temporal Validity


oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Sep 7 10:08:36 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Sep 03 2014 22:28:04 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> create table emp_temp as
  2  select employee_id, first_name, salary
  3  from hr.employees
  4  where rownum<4;

Table created.

SQL> desc emp_temp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                        NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
SALARY                                             NUMBER(8,2)


SQL>  alter table emp_temp add period for valid_time;

Table altered.

SQL> desc emp_temp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                        NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
SALARY                                             NUMBER(8,2)

 
SQL> select column_name,data_type from dba_tab_columns where table_name='EMP_TEMP';

COLUMN_NAME          DATA_TYPE
-------------------- --------------------
SALARY               NUMBER
FIRST_NAME           VARCHAR2
EMPLOYEE_ID          NUMBER


SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald
Douglas
Jennifer

SQL> insert into emp_temp values (100,'Donghua',5000);

1 row created.

SQL> commit;

Commit complete.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald
Douglas
Jennifer
Donghua

SQL>

SQL> update emp_temp
  2  set valid_time_start = to_date('01-JUN-1995','dd-MON-yyyy'), valid_time_end = to_date('15-SEP-2010','dd-MON-yyyy')
  3 where first_name in ('Donald');

1 row updated.

SQL> update emp_temp
  2  set valid_time_start = to_date('01-AUG-1999','dd-MON-yyyy'), valid_time_end = to_date('01-MAR-2012','dd-MON-yyyy')
  3  where first_name in ('Douglas');

1 row updated.

SQL> update emp_temp
  2  set valid_time_start = to_date('20-MAY-1998','dd-MON-yyyy')
  3  where first_name in ('Jennifer');

1 row updated.

SQL> update emp_temp
  2  set valid_time_end = to_date('20-MAY-2017','dd-MON-yyyy')
  3 where first_name in ('Donghua');

1 row updated.

SQL> commit;

Commit complete.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Donald               01-jun-1995 15-sep-2010
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> select first_name,
  2 to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 as of period for valid_time to_date('01-JUN-2011')
  6 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4 from emp_temp
  5 versions period for valid_time
  6  between to_date('01-SEP-1995') and to_date('01-SEP-1996')
  7  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Donald               01-jun-1995 15-sep-2010
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3 to_char(valid_time_end,'dd-mon-yyyy') "End"
  4 from emp_temp
  5  order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Donald               01-jun-1995 15-sep-2010
Jennifer             20-may-1998
Donghua                          20-may-2017

SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_timestamp('2012-01-01','yyyy-mm-dd'));

PL/SQL procedure successfully completed.

SQL> select first_name,
  2  to_char(valid_time_start,'dd-mon-yyyy') "Start",
  3  to_char(valid_time_end,'dd-mon-yyyy') "End"
  4  from emp_temp
  5 order by 2;

FIRST_NAME           Start       End
-------------------- ----------- -----------
Douglas              01-aug-1999 01-mar-2012
Jennifer             20-may-1998
Donghua                          20-may-2017


SQL> alter table emp_temp add (Last_Name varchar2(20) default 'Unknown');

Table altered.

SQL> truncate table emp_temp;
truncate table emp_temp
                      *
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
30x0C1618A880x0BD751A000x0C5208FA0


SQL> select * from emp_temp;

EMPLOYEE_ID FIRST_NAME               SALARY LAST_NAME
----------- -------------------- ---------- --------------------
        199 Douglas                    2600 Unknown
        200 Jennifer                   4400 Unknown
        100 Donghua                    5000 Unknown