1.闪回技术描述

2.数据库的准备:

--undo表空间要设置成AUTO，设置合适的保存时间、undo表空间：

SYS@ENMOEDU> show parameter undo

NAME                        TYPE        VALUE

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

undo_management           string       AUTO

undo_retention            integer      900

undo_tablespace           string      UNDOTBS1

3.闪回查询数据
（1）闪回查询
①基于timestamp

SCOTT@LGR> create table t1 as select * from dept;

Table created.

SCOTT@LGR> select * from t1;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

18:25:50 SCOTT@LGR> set time on

18:26:00 SCOTT@LGR> delete t1 where deptno=10;

1 row deleted.

18:26:21 SCOTT@LGR> commit;

Commit complete.

18:26:58 SCOTT@LGR> select * from t1;

    DEPTNO DNAME          LOC

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

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

18:31:26 SCOTT@LGR> select * from t1 as of timestamp to_timestamp('2016-12-15 18:24:00','yyyy-mm-dd hh24:mi:ss');

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

②基于SCN

SYS@LGR> grant execute on dbms_flashback to scott;

Grant succeeded.

SYS@LGR> conn scott/tiger

Connected.

SCOTT@LGR> create table t2 as select * from dept;

Table created.

SCOTT@LGR> select * from t2;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual;

       SCN

----------

    539268

SCOTT@LGR> delete t2;

4 rows deleted.

SCOTT@LGR> commit;

Commit complete.

SCOTT@LGR> select * from t2;

no rows selected

SCOTT@LGR> select * from t2 as of scn 539268;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

---也可以通过以下方式获取SCN

SCOTT@LGR> conn / as sysdba

Connected.

SYS@LGR> select current_scn from v$database;

CURRENT_SCN

-----------

     539373

③基于dbms_flashback 包

语法：

 ---会话启用闪回指定时间：

dbms_flashback.enable_at_time(query_time in timestamp);

 ---会话启用闪回指定SCN:

dbms_flashback.enable_at_system_change_number(query_scn in number);

    ---关闭闪回：

dbms_flashback.disable;

---把dbms_flashback的权限授权给scott

SYS@LGR> grant execute on dbms_flashback to scott;

Grant succeeded.

---创建测试表

SCOTT@LGR> create table t3 as select * from dept;

Table created.

SCOTT@LGR> select * from t3;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

---获取当前的SCN

SCOTT@LGR> select dbms_flashback.get_system_change_number as scn from dual;

       SCN

----------

    540384

---对t3表进行操作

SCOTT@LGR> update t3 set dname=null,loc=null;

4 rows updated.

SCOTT@LGR> commit;

Commit complete.

SCOTT@LGR> select * from t3;

    DEPTNO DNAME          LOC

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

        10

        20

        30

        40

---执行dbms_flashback包

SCOTT@LGR> exec dbms_flashback.enable_at_system_change_number(540384);

PL/SQL procedure successfully completed.

---再次查看测试表

SCOTT@LGR> select * from t3;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

---注意的是，在闪回模式中不能进行DML操作

SCOTT@LGR> insert into t3 values (1,'a','a');

insert into t3 values (1,'a','a')

            *

ERROR at line 1:

ORA-08182: operation not supported while in Flashback mode

---结束操作，需要执行如下过程

SCOTT@LGR> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

---查询测试表，恢复原貌，且可以进行DML操作

SCOTT@LGR> select * from t3;

    DEPTNO DNAME          LOC

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

        10

        20

        30

        40

SCOTT@LGR> delete t3 where deptno=20;

1 row deleted.

SCOTT@LGR> select * from t3;

    DEPTNO DNAME          LOC

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

        10

        30

        40

注：sys下不允许使用dbms_flashback包

SYS@LGR> exec dbms_flashback.enable_at_time(sysdate-5/1440);

BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

ORA-06512: at "SYS.DBMS_FLASHBACK", line 3

ORA-06512: at line 1

（2）闪回版本查询

语法：

select column_name[,column_name,...]

from table_name

versions between [SCN | TIMESTAMP] [expr | MINVALUE] and [expr | MAXVALUE];

---连续三次对emp表进行修改

SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369;

ENAME      JOB              SAL

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

SMITH      CLERK            800

SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;

1 row updated.

SCOTT@ENMOEDU> commit;

Commit complete.

SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;

1 row updated.

SCOTT@ENMOEDU> commit;

Commit complete.

SCOTT@ENMOEDU> update emp set sal=sal+200 where empno=7369;

1 row updated.

SCOTT@ENMOEDU> commit;

Commit complete.

---查看修改后的sal

SCOTT@ENMOEDU> select ename,job,sal from emp where empno=7369;

ENAME      JOB              SAL

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

SMITH      CLERK           1400

---闪回版本查询，可以清晰的看到每次更新的数据

SCOTT@ENMOEDU> select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,versions_xid,ename,job,sal from emp versions between timestamp sysdate-10/1440 and sysdate where empno=7369;

STARTTIME           VERSIONS_XID     ENAME      JOB              SAL

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

2016-12-16 09:57:13 02001400B3030000 SMITH      CLERK           1400

2016-12-16 09:57:09 05002000B3030000 SMITH      CLERK           1200

2016-12-16 09:56:54 03001900B3030000 SMITH      CLERK           1000

                                     SMITH      CLERK            800

—附：设计到的伪列

—闪回版本注意事项
VERSIONS 子句不能用于：
—外部表
—临时表
—固定表
—视图
VERSIONS不能跨DDL命令。
段收缩操作被过滤掉。

（3）闪回事务处理查询
①11g闪回事务处理处理查询必须要启用最小补充日志

---启用最小补充日志

SYS@ENMOEDU> alter database add supplemental log data; 

Database altered.

---查询最小补充日志是否开启

SYS@ENMOEDU> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

②建立测试表，并对其进行DML操作

SYS@ENMOEDU> create table t (id number(2),name varchar(10)) tablespace users;

Table created.

SYS@ENMOEDU> insert into t values (1,'zhangsan');

1 row created.

SYS@ENMOEDU> update t set name='zs' where id=1;

1 row updated.

SYS@ENMOEDU> commit; 

Commit complete.

③执行闪回版本查询，得到事务ID

SYS@ENMOEDU> select versions_xid,versions_startscn,id,name from t versions between timestamp minvalue and maxvalue;

VERSIONS_XID     VERSIONS_STARTSCN         ID NAME

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

07001600CB020000           1035729          1 zs

④通过事务ID得到相关SQL
事务‘07001600CB020000’做了两个操作：插入数据、更新数据，闪回事务会逆向的返回这个事务的操作，通过这组SQL可修改回事务修改前的样子。

SYS@ENMOEDU> select undo_sql from flashback_transaction_query where xid='07001600CB020000';

UNDO_SQL

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

update "SYS"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAVslAAEAAAAJMAAA';

delete from "SYS"."T" where ROWID = 'AAAVslAAEAAAAJMAAA';

⑤关闭最小补充日志

SYS@ENMOEDU> alter database drop supplemental log data;

Database altered.

4.闪回表
语法：

FLASHBACK TABLE[ schema.] table [, [ schema. ] table ]...

  TO {{{ SCN |TIMESTAMP }expr | RESTORE POINT restore_point}

  [{ ENABLE | DIABLE} TRIGGERS ]| BEFORE DROP [ RENAME TO table]};

注：FLASHBACK TABLE 是DDL命令
①创建测试表 fb_table, 在表 fb_table 上创建触发器（trg_fb_table ），fb_table 的 的 x 字段创建一个索引（idx_fb_table ），此时，与 fb_table 相关的对象就有两个，一个是触发器，一个是索引，这三个对象的状态都是有效的。

SYS@ENMOEDU> conn scott/tiger

Connected.

SCOTT@ENMOEDU> create table fb_table(x number(2),d date);

Table created.

SCOTT@ENMOEDU> create or replace trigger trg_fb_table

  2  before insert on fb_table for each row

  3  begin

  4   if :new.d is null then

  5    :new.d :=to_date('','yyyymmdd');

  6   end if;

  7  end;

  8  /

Trigger created.

SCOTT@ENMOEDU> create unique index idx_fb_table on fb_table(x);

Index created.

SCOTT@ENMOEDU> COL OBJECT_NAME FOR A15

SCOTT@ENMOEDU> /

OBJECT_NAME     STATUS

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

IDX_FB_TABLE    VALID

FB_TABLE        VALID

TRG_FB_TABLE    VALID

②插入测试数据，验证触发器

SCOTT@ENMOEDU> insert into fb_table(x) values(1);

1 row created.

SCOTT@ENMOEDU> insert into fb_table(x,d) values (2,sysdate);

1 row created.

SCOTT@ENMOEDU> commit;

Commit complete.

SCOTT@ENMOEDU> select * from fb_table;

         X D

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

         1 06-JUN-16

         2 16-DEC-16

③返回当前时间

SCOTT@ENMOEDU> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SCOTT@ENMOEDU> select sysdate from dual;

SYSDATE

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

2016-12-16 11:33:29

④删除表中的一条记录

SCOTT@ENMOEDU> delete fb_table where x=2;

1 row deleted.

SCOTT@ENMOEDU> commit;

Commit complete.

SCOTT@ENMOEDU>  select * from fb_table;

         X D

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

         1 2016-06-06 00:00:00

⑤尝试用闪回表的方式恢复数据

--第一次闪回报错很明显是因为没有启动行移动，启动后，成功闪回表

SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss');

flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss')

                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE';

TABLE_NAME                     ROW_MOVE

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

FB_TABLE                       DISABLED

SCOTT@ENMOEDU> alter table fb_table enable row movement;

Table altered.

SCOTT@ENMOEDU> select table_name,row_movement from user_tables where table_name='FB_TABLE';

TABLE_NAME                     ROW_MOVE

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

FB_TABLE                       ENABLED

SCOTT@ENMOEDU> flashback table fb_table to timestamp to_timestamp('2016-12-16 11:33:29','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SCOTT@ENMOEDU> select * from fb_table;

         X D

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

         1 2016-06-06 00:00:00

         2 2016-12-16 11:33:19

⑥查看与测试表相关对象

--所有对象的状态均为有效的，测试的时候我们创建触发器和索引意义不大了，不过要说明的是，11g在闪回表上做了升级，10g闪回表后，触发器为失效的状态。

SCOTT@ENMOEDU> select object_name,status from user_objects where object_name like '%FB_TABLE';

OBJECT_NAME     STATUS

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

TRG_FB_TABLE    VALID

FB_TABLE        VALID

IDX_FB_TABLE    VALID

⑦也可以基于时间间隔、scn闪回一张或者多张表。

---基于时间间隔闪回

SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-7/1440;

Flashback complete.

SCOTT@ENMOEDU> flashback table fb_table to timestamp sysdate-interval''minute;

Flashback complete.

---基于scn闪回

SCOTT@ENMOEDU> flashback table fb_table to scn 1041216;

Flashback complete.

5.闪回归档

语法：

CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive

TABLESPACE tablespace_name

[QUOTA integer {K| M| G| T| P| E}]

RETENTION integer {YEAR | MONTH | DAY};

① 创建测试表空间用户，分配角色

SYS@ENMOEDU> create tablespace ts_users datafile'/u01/app/oracle/oradata/ENMOEDU/ts_users.dbf' size 10M;

Tablespace created.

SYS@ENMOEDU> create user lgr identified by oracle default tablespace ts_users;

User created.

SYS@ENMOEDU> grant connect,resource to lgr;

Grant succeeded.

② 创建flashback archive

SYS@ENMOEDU> create flashback archive fbad_ts_users tablespace ts_users retention 1 day;

Flashback archive created.

③创建表t1，启用闪回归档数据（这个动作需要有“flashback archive”权限）

SYS@ENMOEDU> conn lgr/oracle

Connected.

LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;

create table t1(x int) flashback archive fbad_ts_users

*

ERROR at line 1:

ORA-55620: No privilege to use Flashback Archive

LGR@ENMOEDU> conn / as sysdba

Connected.

SYS@ENMOEDU> grant flashback archive on fbad_ts_users to lgr;

Grant succeeded.

SYS@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;

create table t1(x int) flashback archive fbad_ts_users

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SYS@ENMOEDU> conn lgr/oracle

Connected.

LGR@ENMOEDU> create table t1(x int) flashback archive fbad_ts_users;

Table created.

④对已存在的表启动闪回归档数据，如下

LGR@ENMOEDU> create table t2(x int);

Table created.

LGR@ENMOEDU> alter table t2 flashback archive fbad_ts_users;

Table altered.

⑤查看已启用闪回归档数据的表（其中：E ARCHIVE_TABLE_NAME 为记录闪回数据的表，我们无法查看它）

TABLE_NAME OWNER_NAME  FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS

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

T1         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88884   ENABLED

T2         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88885   ENABLED

⑥禁用闪回归档数据

使用“no flashback archive”子句可以disable flashback archive(注：需要有权限）

LGR@ENMOEDU> alter table t2 no flashback archive;

alter table t2 no flashback archive

*

ERROR at line 1:

ORA-55620: No privilege to use Flashback Archive

LGR@ENMOEDU> conn / as sysdba

Connected.

SYS@ENMOEDU> alter table lgr.t2 no flashback archive;

Table altered.

TABLE_NAME OWNER_NAME  FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS

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

T1         LGR         FBAD_TS_USERS   SYS_FBA_HIST_88884   ENABLED

⑦删除数据

---查看t1表数据

SYS@ENMOEDU> select * from lgr.t1;

         X

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10

10 rows selected.

---查看当前的scn

SYS@ENMOEDU> select dbms_flashback.get_system_change_number as scn from dual;

       SCN

----------

   1046908

---删除t1表的数据

SYS@ENMOEDU> delete lgr.t1 where x<10;

9 rows deleted.

---查看删除后的数据

SYS@ENMOEDU> select * from lgr.t1;

         X

----------

        10

SYS@ENMOEDU> commit;

Commit complete.

SYS@ENMOEDU> select * from lgr.t1 as of scn 1046908;

         X

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10

10 rows selected.

最新文章

  1. jquery.qrcode.js 插件生成二维码
  2. jquery 操作select 语句大全
  3. 匹配url - 正则表达式
  4. 48、tensorflow入门二，线性模型的拟合
  5. HDU 2102 A计划 经典搜索
  6. Thymeleaf引擎支持Multi Prefix
  7. Android Studio 插件开发详解四：填坑
  8. 【mmall】Jackson序列化时isSuccess()方法的注意点
  9. alpha冲刺7/10
  10. 详细解析HTML基础结构
  11. linux shell 指令搜索顺序
  12. VS2010自定义添加创建者、创建时间等个人信息新建文件模版
  13. django之 F&amp;Q 聚合与分组
  14. 120分钟React快速扫盲教程
  15. MATLAB 到 Python之路1_数据结构和简单操作
  16. AppManager
  17. 利用apt-get 进行jdk安装
  18. 160308、java排序（形如1.1、1.2.1）
  19. LinQ to SQL 及 non-LinQ方式实现Group的Performance对比
  20. cocos2d-X学习之主要类介绍：动作：CCAction

热门文章

  1. [poj1325] Machine Schedule (二分图最小点覆盖)
  2. Linux思维导图之shell脚本编程基础、习题
  3. kissui.scrollanim页面滚动动画库插件
  4. 【codeforces 798A】Mike and palindrome
  5. 使用idea搭建maven项目时 java目录下的xml文件没有加载的解决方法
  6. ExtJs之Ext.Model的MemoryProxy
  7. 【ACM】hdu_zs1_1004_第二小整数_201307271529
  8. CentOS 6.9安装过程
  9. wifi断线问题
  10. mysql选择上一条、下一条数据记录，排序上移、下移、置顶