一、基于时间（as of timestamp）的flashback

1、创建表

create table flash_tab(id,vl) as

select rownum,oname from ( select substr(object_name,1,1) oname from all_objects

group by substr(object_name,1,1) order by 1)

where rownum<=20;

2、查询表内容

SQL> select * from flash_tab;

ID VL

---------- --

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

20 rows selected

3、删除表数据.

SQL> delete flash_tab where id<10;

9 rows deleted

SQL> select * from flash_tab;

ID VL

---------- --

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

11 rows selected

4、执行Flashbach Query

SQL> select * from flash_tab as of timestamp sysdate-2/1440;

ID VL

---------- --

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

20 rows selected

===============

说明：

AS OF TIMESTAMP表示基于时间

SYSDATE-2/1440表示当前系统时间两分钟之前；1440表示60分*24小时

5、将删除的记录恢复

insert into flash_tab

select * from flash_tab as of timestamp sysdate-2/1440

where id<10;

commit;

SQL> select * from flash_tab;

ID VL

---------- --

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

20 rows selected

二、基于SCN(as of scn)的flashback

scn值的查询可使用dbms_flashback.get_system_change_number函数；或是从v$database视图中查询current_scn值。

1、授权scott用户具有查询scn值的权限

SQL> grant execute on dbms_flashback to scott;

授权成功。

SQL> grant select on v_$database to scott;

授权成功。

2、 查询当前scn值

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

1564606

SQL> select current_scn from v$database; --不同时间点查询的scn值不一致

CURRENT_SCN

-----------

1564631

3、查询指定scn值时对象中的记录

SQL> select * from flash_tab as of scn 1564606;

ID VL

---------- --

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

20 rows selected

4、删除对象修改

SQL> delete from flash_tab;

20 rows deleted

SQL> commit;

Commit complete

5、执行flashback as of scn恢复数据

SQL> select * from flash_tab;

ID VL

---------- --

SQL> select * from flash_tab as of scn 1564606;

ID VL

---------- --

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

20 rows selected

SQL> insert into flash_tab select * from flash_tab as of scn 1564606;

20 rows inserted

SQL> select * from flash_tab;

ID VL

---------- --

10 I

11 J

12 K

13 L

14 M

15 N

16 O

17 P

18 Q

19 R

20 S

1 /

2 A

3 B

4 C

5 D

6 E

7 F

8 G

9 H

20 rows selected

============================

说明：

SCN会比TIMESTAMP更精确。事实上timestamp同样是被转换成SCN。因为oracle是通过SCN来标记操作而不是时间。

每个时间点都对应一个SCN值。在10g中，系统平均每隔3秒产生一次系统时间与SCN的匹配存入sys.SMO_SCN_TIME表中。SCN和TIMESTAMP也可相互转换。

SQL> DESC SMON_SCN_TIME;

Name Type Nullable Default Comments

------------ --------- -------- ------------------------------- --------

THREAD NUMBER Y

TIME_MP NUMBER Y

TIME_DP DATE Y

SCN_WRP NUMBER Y

SCN_BAS NUMBER Y

NUM_MAPPINGS NUMBER Y

TIM_SCN_MAP RAW(1200) Y

SCN NUMBER Y 0

ORIG_THREAD NUMBER Y 0 /* for downgrade */

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

-------------------------

1569124

SQL> select to_char(scn_to_timestamp(1569124),'YYYY-MM-DD HH24:MI:SS') from dual; --时间可精确到毫秒

TO_CHAR(SCN_TO_TIMESTAMP(15691

------------------------------

2015-01-05 22:55:00