oracle 11g 閃回測試過程

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 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                                 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.

[oracle@11g ~]$ 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 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
        8

8 rows selected.