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
No comments:
Post a Comment