sql> select flashback_on from v$database; FLASHBACK_ON ------------------------------------------------------ YES sql> set linesize 3000; sql> select * from scott.dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON sql> delete from scott.dept where deptno=40; 1 row deleted. sql> commit; Commit complete. sql> select * from scott.dept as of timestamp sysdate-10/1440; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON sql> select * from scott.dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO sql> flashback table scott.dept to timestamp to_timestamp(‘2019-07-15 10:50:00‘,‘yyyy-mm-dd hh24:mi:ss‘); Flashback complete. sql> select * from scott.dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON sql> select * from scott.dept; DEPTNO DNAME LOC ---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON sql> select * from scott.t1; ID ---------- 1 2 3 4 5 6 7 8 8 rows selected. sql> drop table scott.t1; Table dropped. sql> select * from scott.t1; select * from scott.t1 * ERROR at line 1: ORA-00942: ??????? sql> show recyclebin; sql> conn scott/tiger Connected. sql> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T1 BIN$jbDeCRWbPyvgUww4qMBWZQ==$0 TABLE 2019-07-15:11:29:27 sql> flashback table t1 to before drop; Flashback complete. sql> select * from t1; ID ---------- 1 2 3 4 5 6 7 8 8 rows selected. sql> drop table t1; Table dropped. sql> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T1 BIN$jbDjk1f0Qg/gUww4qMDc8w==$0 TABLE 2019-07-15:11:31:00 sql> flashback table t1 to before drop; Flashback complete. sql> select * from t1; ID ---------- 1 2 3 4 5 6 7 8 8 rows selected. [[email protected] ~]$ sqlplus / as sysdba sql*Plus: Release 11.2.0.4.0 Production on 星期一 7月 15 11:40:51 2019 Copyright (c) 1982,2013,Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning,OLAP,Data Mining and Real Application Testing options sql> select to_char(systimestamp,‘yyyy-mm-dd HH24:MI:SS‘) as sysdt,dbms_flashback.get_system_change_number scn from dual; SYSDT SCN --------------------------------------------------------- ---------- 2019-07-15 11:40:53 990134 sql> truncate table scott.t1; Table truncated. sql> sql> select * from scott.t1; no rows selected sql> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sql> startup mount; ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2253624 bytes Variable Size 973081800 bytes Database Buffers 603979776 bytes Redo Buffers 7393280 bytes Database mounted. sql> flashback database to timestamp to_timestamp(‘2019-07-15 11:40:53‘,‘yyyy-mm-dd hh24:mi:ss‘); Flashback complete. sql> alter database open resetlogs; Database altered. sql> select * from scott.t1; ID ---------- 1 2 3 4 5 6 7 8 8 rows selected.
