oracle 过程 测试,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.