闪回表

实验步骤：
删除hr用户下的job_history表的数据；
1.使用闪回查询查看删除前的数据；
2.利用闪回删除恢复删除的数据；

操作如下：
（1） 为HR用户授予闪回表的权限；
[oracle@GMDBA ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 3 23:16:58 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@GMDBA> grant flashback any table to hr;
Grant succeeded.
SYS@GMDBA>conn hr/oracle
（2） 查看job_history表数据和时间点；
HR@GMDBA> select * from job_history order by 1;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110
102 13-JAN-01 24-JUL-06 IT_PROG 60
114 24-MAR-06 31-DEC-07 ST_CLERK 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
176 24-MAR-06 31-DEC-06 SA_REP 80
176 01-JAN-07 31-DEC-07 SA_MAN 80
200 17-SEP-95 17-JUN-01 AD_ASST 90
200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90
201 17-FEB-04 19-DEC-07 MK_REP 20
10 rows selected.
HR@GMDBA> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
HR@GMDBA> select sysdate from dual
2 ;
SYSDATE
-------------------
2013-12-03 23:15:27
（3） 删除job_history表数据；
HR@GMDBA> delete from job_history;
10 rows deleted.
HR@GMDBA> commit;
Commit complete.
HR@GMDBA> select * from job_history;
no rows selected
（4） 开启job_history表的行迁移；
HR@GMDBA> alter table job_history enable row movement;
Table altered.
（5） 执行闪回表操作；
HR@GMDBA> flashback table job_history to timestamp to_timestamp('2013-12-03 23:15:27','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
（6） 查看闪回结果；
HR@GMDBA> select * from job_history;
EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
----------- ------------------- ------------------- ---------- ------------
102 2001-01-13 00:00:00 2006-07-24 00:00:00 IT_PROG 60
101 1997-09-21 00:00:00 2001-10-27 00:00:00 AC_ACCOUNT 110
101 2001-10-28 00:00:00 2005-03-15 00:00:00 AC_MGR 110
201 2004-02-17 00:00:00 2007-12-19 00:00:00 MK_REP 20
114 2006-03-24 00:00:00 2007-12-31 00:00:00 ST_CLERK 50
122 2007-01-01 00:00:00 2007-12-31 00:00:00 ST_CLERK 50 
200 1995-09-17 00:00:00 2001-06-17 00:00:00 AD_ASST 90
176 2006-03-24 00:00:00 2006-12-31 00:00:00 SA_REP 80
176 2007-01-01 00:00:00 2007-12-31 00:00:00 SA_MAN 80
200 2002-07-01 00:00:00 2006-12-31 00:00:00 AC_ACCOUNT 90
10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29319205/viewspace-1062225/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29319205/viewspace-1062225/