闪回（关于闪回查询）

--整理以前的学习笔记

1、闪回版本查询：
闪回版本查询功能依赖于AUM（Automatic Undo Management），AUM指的是采用撤销表空间记录来增、删、改数据的方法。
conn hr/hr
SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> conn /as sysdba
Connected.
SQL> grant execute on dbms_flashback to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291547

create table emp as select * from employees;

SQL> update emp set salary=salary*1.5 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291590

SQL> delete from emp where employee_id=196;

1 row deleted.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291600

SQL> insert into departments values (660,'Security',100,1700);

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291604

SQL> update emp set manager_id=100 where employee_id=195;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291631

SQL> update emp set department_id=660 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291663

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2291667

两个事务
在第一个事务中：
将195这条数据的salary由2800改为4200
删除196这条数据
往departments表中插入660这条数据
将195这条数据的manager_id由123改为100
在第二个事务中：
将195这条数据的department_id由50改为660

SQL> select versions_startscn startscn,versions_endscn endscn,
  2  versions_xid xid,versions_operation oper,
  3  employee_id empid,last_name name,manager_id mgrid,salary sal
  4  from emp
  5  versions between scn 2291547 and 2291667
  6  where employee_id in (195,196);
from emp
     *
ERROR at line 4:
ORA-01466: unable to read data - table definition has changed

为什么会出现这个错误？其实是因为我emp这张表是在我查询了这个SCN2291547之后创建的，如果versions between的SCN差或者时间差，中间包含了DDL语句，那么这个闪回查询会返回错误。

即：不能在查询中使用跨越更改表结构的DDL语句的versions子句。也就是说，只能返回到表上最后一条DDL语句（当然包括创建表本身）后的第一个事务的时间点。

好吧，现在重新来一遍
drop table emp purge;
create table emp as select * from employees;
delete from departments where department_id=660;
commit;
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293439

SQL> update emp set salary=salary*1.5 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293446

SQL> delete from emp where employee_id=196;

1 row deleted.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293460

SQL> insert into departments values (660,'Security',100,1700);

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  2293469

SQL> update emp set manager_id=100 where employee_id=195;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293482

SQL> update emp set department_id=660 where employee_id=195;

1 row updated.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293496

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2293508

两个事务
在第一个事务中：
将195这条数据的salary由2800改为4200
删除196这条数据
往departments表中插入660这条数据
将195这条数据的manager_id由123改为100
在第二个事务中：
将195这条数据的department_id由50改为660

select versions_startscn startscn,versions_endscn endscn,
versions_xid xid,versions_operation oper,
employee_id empid,last_name name,manager_id mgrid,salary sal
from emp
versions between scn 2293439 and 2293508
where employee_id in (195,196);
SQL> /

STARTSCN     ENDSCN             XID            O      EMPID        NAME       MGRID           SAL
----------   ----------     -----------------  -    ---------- ----------   ----------  ----------
2293505                     0500040084060000   U        195        Jones        100          4200
2293480       2293505       07002000E0050000   U        195        Jones        100          4200
              2293480                                   195        Jones        123          2800
2293480                     07002000E0050000   D        196        Walsh        124          3100
              2293480                                   196        Walsh        124          3100

从查询结果中可一看到，首先显示最新更改，endscn为空是最新的版本，startscn为空是最早的版本

 

select versions_starttime,versions_endtime,versions_operation, id
from sunwg
versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;
--这个应该是显示行的所有的DML记录，不限时间

 

2、闪回事务查询
conn hr/hr
select * from flashback_transaction_query;--hr需要有execute on dbms_flashback的权限和select any transaction的权限

conn /as sysdba
grant execute on dbms_flashback to hr;
grant select any transaction to hr;

SQL> select t.start_scn,t.commit_scn,t.logon_user,
  2  t.operation,t.table_name,t.undo_sql
  3  from flashback_transaction_query t where t.xid='07002000E0050000';
 
 START_SCN COMMIT_SCN LOGON_USER OPERATION  TABLE_NAME      UNDO_SQL
----------- ---------- ---------- ---------- --------------- --------------------
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         UNKNOWN    DEPARTMENTS    
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         UNKNOWN    EMP            
   2293027    2293480 HR         BEGIN

这里的operation都已经变成unknown了，说明undo已经过期，如果undo还未过期，那么operation的值是DML操作的反操作，如insert的反操作是delete，这里就不重复做实验了，可以参考http://sunwgneuqsoft.itpub.net/post/34741/456879

要使undo不那么快过期，可以考虑为undo表空间设置retention guarantee，并延长undo_retention的时间

回头去查
SQL> select versions_startscn startscn,versions_endscn endscn,
  2  versions_xid xid,versions_operation oper,
  3  employee_id empid,last_name name,manager_id mgrid,salary sal
  4  from emp
  5  versions between scn 2293439 and 2293508
  6  where employee_id in (195,196);
from emp
     *
ERROR at line 4:
ORA-30052: invalid lower limit snapshot expression--下限快照表达式无效，也说明是undo已经过期

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26524307/viewspace-1061377/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26524307/viewspace-1061377/