Oracle 11g flashback test process
SQL> select flashback_on from v$database;FLASHBACK_ON------------------------------------------------------YESSQL> set linesize 3000;SQL> select * from scott.dept; DEPTNO DNAME LOC---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> 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 BOSTONSQL> select * from scott.dept; DEPTNO DNAME LOC---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGOSQL> flashback table scott.dept to timestamp to_timestamp('2019-07-15 10:50:00','yyyy-mm-dd hh34:mi:ss');Flashback complete.SQL> select * from scott.dept; DEPTNO DNAME LOC---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> select * from scott.dept; DEPTNO DNAME LOC---------- ------------------------------------------ --------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> select * from scott.t1; ID---------- 1 2 3 4 5 6 7 88 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/tigerConnected.SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------T1 BIN$jbDeCRWbPyvgUww4qMBWZQ==$0 TABLE 2019-07-15:11:29:27SQL> flashback table t1 to before drop;Flashback complete.SQL> select * from t1; ID---------- 1 2 3 4 5 6 7 88 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:00SQL> flashback table t1 to before drop;Flashback complete.SQL> select * from t1; ID---------- 1 2 3 4 5 6 7 88 rows selected. [oracle@11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Monday July 15 11:40:51 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 990134SQL> truncate table scott.t1;Table truncated.SQL> SQL> select * from scott.t1;no rows selectedSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1586708480 bytesFixed Size 2253624 bytesVariable Size 973081800 bytesDatabase Buffers 603979776 bytesRedo Buffers 7393280 bytesDatabase mounted.SQL> flashback database to timestamp to_timestamp('2019-07-15 11:40:53','yyyy-mm-dd hh34:mi:ss');Flashback complete.SQL> alter database open resetlogs;Database altered.SQL> select * from scott.t1; ID---------- 1 2 3 4 5 6 7 88 rows selected.
