Oracle Study之事例--数据康复神器Flashback(1)51CTO博客 - 威尼斯人

Oracle Study之事例--数据康复神器Flashback(1)51CTO博客

2019年03月01日11时47分52秒 | 作者: 辰锟 | 标签: 数据,康复,时刻 | 浏览: 787

Oracle Study之事例数据康复神器Flashback(1)

Flashback:

            Flashback 技能是以Undo segment中的内容为根底的, 因而受限于UNDO_RETENTON参数。要运用flashback 的特性,有必要启用主动吊销办理表空间。

            在Oracle 11g里又出了一个新特性:Oracle Flashback Data Archive. FDA经过将改动数据别的存储到创立的闪回归档区(Flashback Archive)中,以和undo差异开来,这样就可认为闪回归档区独自设置存储战略,使之能够闪回到指定时刻之前的旧数据而不影响undo战略。

            在Oracle 10g中, Flash back宗族分为以下成员: Flashback Database, Flashback Drop,Flashback Query(分Flashback Query,Flashback Version Query, Flashback Transaction Query 三种) 和Flashback Table。

用户误操作数据康复:

       关于用户误操作发生的数据丢掉,传统上都选用备份(逻辑备份)或物理备份(不完全康复)来康复数据;假如有逻辑备份,康复相对较简略,假如没有逻辑备份,经过物理备份进行不完全康复,需求支付很大的本钱才干康复数据;而从Oracle 9i以来,Oracle推出了Flashback得东西,能够经过此东西快速康复因用户误操作而丢掉的数据。

    关于DML操作,Flashback能够经过Undo block来康复用户发生的误操作。

1、Flashback  query 特性flashback能够经过undo block查询曩昔时刻点或scn的数据
2、Flashback  version query 特功用够得到特定的表在某一个时刻段内的任何批改记载
3、Flashback transaction query 特功用够约束用户在某一个业务等级上查看数据库的批改操作,适用于确诊问题、剖析功用、审计业务。
4、Flashback table 特性答应oracle经过flashback table句子,将表回滚到前一个时刻点或许scn上。

一、Flashback Query

     Flashback 是ORACLE 自9i 就开端供给的一项特性,在9i 中运用oracle 查询多版别共同的特色,完成从回滚段中读取表必定时刻内操作过的数据,可用来进行数据比对,或许批改意外提交形成的过错数据,该项特性也被称为Flashback Query。

     正如前语中所提,Flashback Query 是运用多版别读共同性的特性从UNDO 表空间读取操作前的记载数据!

什么是多版别读共同性

      Oracle 选用了一种十分优异的规划,经过undo 数据来保证写不阻塞读,简略的讲,不同的业务在写数据时,会将数据的前映像写入undo 表空间,这样假如一起有其它业务查询该表数据,则能够经过undo 表空间中数据的前映像来结构所需的完好记载集,而不需求等候写入的业务提交或回滚。

      flashback query 有多种方法构建查询记载集,记载集的挑选规模能够依据时刻或依据scn,乃至能够一起查询出记载在undo 表空间中不同业务时的前映象。用法与规范查询十分相似,要经过flashback query 查询undo 中的吊销数据,最简略的方法只需求在规范查询句子的表名后边跟上as of timestamp(依据时刻)或as of scn(依据scn)即可。as of timestamp|scn 的语法是自9iR2 后才开端供给支撑。

 事例剖析:

1、依据时刻点(As of Timestamp)

    运用在undo tablespace 里现已被提交的undo block(未被掩盖),能够经过查询的方法将表里面的记载回到曩昔某个时刻点。

1、构建测验环境
07:01:37 SQL> conn scott/tiger                                                                                                         
Connected.
07:01:41 SQL> select * from test;                                                                                                        
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
  -  -   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

2、DML误操作

07:01:45 SQL> delete from test ;                                                                                                        
14 rows deleted.
07:01:59 SQL> commit;                                                                                                                    
Commit complete.

07:02:03 SQL> select * from test;                                                                                                        
no rows selected

07:02:05 SQL> insert into test select * from emp where rownum <3;                                                                        
2 rows created.
07:02:35 SQL> select * from test;                                                                                                        
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
  -  -   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
07:02:37 SQL> commit;                                                                                                                    
Commit complete.

2、运用logminer东西查找误操作的时刻点(发掘current redo或archive log)

07:03:03 SQL> select * from v$log;                                                                                                       
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
     -  - -
         1          1          0   52428800          1 YES UNUSED                       0
         2          1          1   52428800          1 NO  CURRENT                1261015 17-AUG-11
         3          1          0   52428800          1 YES UNUSED                       0
07:03:20 SQL> col member for a50                                                                                                         
07:03:23 SQL> select group#,member from v$logfile
    GROUP# MEMBER
 
         3 /u01/app/oracle/oradata/prod/redo03.log
         2 /u01/app/oracle/oradata/prod/redo02.log
         1 /u01/app/oracle/oradata/prod/redo01.log

11:19:31 SQL> conn /as sysdba
Connected.
11:19:35 SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
     -  - -
         1          1         12   52428800          2 YES INACTIVE                823116 29-SEP-11
         2          1         14   52428800          2 NO  CURRENT                 828692 29-SEP-11
         3          2          9   52428800          2 YES INACTIVE                824371 29-SEP-11
         4          2         11   52428800          2 NO  CURRENT                 828868 29-SEP-11
         5          1         13   52428800          2 YES INACTIVE                828670 29-SEP-11
         6          2         10   52428800          2 YES INACTIVE                828817 29-SEP-11
6 rows selected.

11:19:41 SQL> col member for a50
11:19:57 SQL> select group# ,member from v$logfile;
    GROUP# MEMBER
 
         2 +DG1/prod/onlinelog/group_2.262.762877491
         2 +RECOVERY/prod/onlinelog/group_2.258.762877501
         1 +DG1/prod/onlinelog/group_1.261.762877473
         1 +RECOVERY/prod/onlinelog/group_1.257.762877479
         3 +DG1/prod/onlinelog/group_3.266.762877849
         3 +RECOVERY/prod/onlinelog/group_3.259.762877855
         4 +DG1/prod/onlinelog/group_4.267.762877859
         4 +RECOVERY/prod/onlinelog/group_4.260.762877867
         6 +DG1/prod/onlinelog/group_6.272.763037401
         6 +RECOVERY/prod/onlinelog/group_6.262.763037407
         5 +DG1/prod/onlinelog/group_5.271.763037441
    GROUP# MEMBER
 
         5 +RECOVERY/prod/onlinelog/group_5.261.763037613
12 rows selected.

发动数据库附加日志:

11:19:58 SQL>Alter database add supplemental log data;

剖析current redolog:

11:20:07 SQL> execute dbms_logmnr.add_logfile(logfilename=>+DG1/prod/onlinelog/group_2.262.762877491,options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.

11:20:57 SQL> alter session set nls_date_format=yyyy-mm-dd;
Session altered.
11:21:32 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.

07:05:21 SQL>  execute dbms_logmnr.end_logmnr;                                                                                           
PL/SQL procedure successfully completed.

查看DML操作的时刻点:
11:23:11 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=EMP1;
USERNAME                              SCN TIMESTAMP  SQL_REDO
   
                                   830293 2011-09-29 delete from "SCOTT"."EMP1" where "EMPNO" = 7369
                                                     and "ENAME" = SMITH and "JOB" = CLERK and "MGR
                                                     " = 7902 and "HIREDATE" = TO_DATE(1980-12-17,
                                                     yyyy-mm-dd) and "SAL" = 800 and "COMM" IS NULL
                                                      and "DEPTNO" = 20 and ROWID = AAAM01AAEAAAAGEA
                                                     AA;
3、flashback query依据时刻点的查询
 07:08:42 SQL> conn scott/tiger                                                                                                           
Connected.

07:08:48 SQL> select * from test as of timestamp to_timestamp(2011-08-17 07:01:59,yyyy-mm-dd hh24:mi:ss);                            
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
  -  -   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

将查询到的数据写入到表中:

07:08:50 SQL> insert into test (select * from test as of timestamp to_timestamp(2011-08-17 07:01:59,yyyy-mm-dd hh24:mi:ss));         
14 rows created.

07:09:10 SQL> select * from test;                                                                                                        
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
  -  -   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
16 rows selected.
-至此,数据康复完结!

2、依据SCN(As of SCN)

 -关于业务的scn亦能够经过logminer对日志剖析得到!

07:09:13 SQL> conn /as sysdba                                                                                                            
Connected.
07:10:28 SQL> 
07:10:28 SQL> select current_scn from v$database;                                                                                        
CURRENT_SCN
-
    1263945
07:10:39 SQL> conn scott/tiger                                                                                                           
Connected.
07:13:44 SQL> delete from test;                                                                                                          
16 rows deleted.
07:13:51 SQL> commit;                                                                                                                    
Commit complete.

07:13:56 SQL> select * from test as of scn 1263945 ;                                                                                     
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
  -  -   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      16 rows selected.

Oracle 业务timestamp和scn联系:

      每隔5 分钟,体系发生一次体系时刻符号与scn 的匹配并存入sys.smon_scn_time 表,该表中记载了最近1440个体系时刻符号与scn 的匹配记载,因为该表只保护了最近的1440 条记载,因而假如运用as of timestamp 的方法则只能flashback 最近5 天内的数据(假定体系是在继续不断运转并无中止或关机重启之类操作的话)。

       留意了解体系时刻符号与scn 的每5 分钟匹配一次这句话,举个比方,比方scn:339988,339989 别离匹配08-05-3013:52:00 和2008-13:57:00,则当你经过as of timestamp 查询08-05-30 13:52:00 或08-05-30 13:56:59 这段时刻点

内的时刻时,oracle 都会将其匹配为scn:339988 到undo 表空间中查找,也就说在这个时刻内,不论你指定的时刻点是什么,查询回来的都将是08-05-30 13:52:00 这个时刻的数据。

       查看SCN 和 timestamp 之间的对应联系:

select scn,to_char(time_dp,yyyy-mm-dd hh24:mi:ss)from sys.smon_scn_time;

15:49:45 SYS@ test3 >select scn,to_char(time_dp,yyyy-mm-dd hh24:mi:ss)from sys.smon_scn_time order by 2 desc;
       SCN TO_CHAR(TIME_DP,YY
 -
    623337 2015-01-13 07:45:26
    623229 2015-01-13 07:40:13
    623128 2015-01-13 07:35:28
    623009 2015-01-13 07:30:12
    622906 2015-01-13 07:25:24
    622799 2015-01-13 07:20:11
    622695 2015-01-13 07:15:12
    622580 2015-01-13 07:10:24
    622474 2015-01-13 07:05:11
    622390 2015-01-13 07:01:17
    622161 2015-01-13 06:56:04
    622063 2015-01-13 06:51:20
    621954 2015-01-13 06:46:04
    621855 2015-01-13 06:41:19

二、Flashback version query

        相关于Flashback Query只能看到某一点的目标状况,Oracle 10g引进的Flashback Version Query能够看到曩昔某个时刻段内,记载怎么改动的。依据这个改动的前史,能够决断数据是在什么时刻点发生了过错。

        Oracle10g能够经过Flashback Version Query答应对不一起刻段内数据表的不同版别进行查询,查询能够反映不一起刻段内数据表的改动。
        Oracle Flashback Version Query特性,运用保存的回滚信息,能够看到特定的表在时刻段内的任何批改,如电影的回放相同,能够了解表在该期间的任何改动。Flashback version query相同依赖于AUM,供给了一个查看行改动的功用,能找到一切现已提交了的行的记载,剖析出曩昔时刻都履行了什么操作。Flashback version query选用VERSIONS BETWEEN句子来进行查询,常用的方法:
·VERSIONS_SCN – 体系改动号
·VERSIONS_TIMESTAMP – 时刻

#查看表EMP1的操作前史

col versions_xid format a16 heading XID
col versions_startscn format 99999999 heading Vsn|Start|SCN
col versions_endscn  format 99999999 heading Vsn|End|SCN
col versions_operation format a12  heading Operation

select versions_xid, versions_startscn, versions_endscn,
     decode( versions_operation, I, Insert, U, Update, D, Delete, Original) "operation",
     empno, enamefrom emp1
     versions between scn minvalue and maxvalue;

            留意成果主从下向上看。Original代表最开端的数据。 

            3 相关的伪列

            1) ORA_ROWSCN记载最终一次被批改时的SCN, 这儿的批改是指"被提交的批改",假如没有提交,那么这个伪列不会发生改动。ORA_ROWSCN 缺省是数据块等级的,也就是一个数据块内的一切记载都是一个ORA_ROWSCN. 数据块内的恣意一条记载被批改,这个数据块内的一切记载ORA_ROWSCN都会一起改动。经过在建表时运用要害字rowdependencies, 能够改动这咱缺省行为。运用这个要害字后,每条记载都有自己的ORA_ROWSCN.

创立表时,假如没有运用rowdependencies要害字,则ORA_ROWSCN运用的是数据块头的SCN,因而在一个数据块内一切记载的ORA_ROWSCN都相同。假如运用了rowdependencies要害字,则每个记载都有自己的ORA_ROWSCN。这个值来自于ITL或dscn字段。             2)VERSIONS_STARTSCN标明这行记载取得的值是在哪个SCN取得的,假如为NULL,则标明这一行的值早于versions between scn MINVALUE and MAXVALUE中的MINVALUE。

          3) VERSIONS_STARTTIME同上

          4) VERSIONS_ENDSCN这行记载的这个值是到哪个SCN过期的。

          5) VERSIONS_TIME同上

          6) VERSIONS_XID导致业务批改的业务号

          7) VERSIONS_OPERATION批改类型U: updateI: insertD: delete

          两个函数:SCN_TO_TIMESTAMP(current_scn)

          select current_scn,scn_to_timestamp(current_scn) curr_time  from v$database;

TIMESTAMP_TO_SCN(yyyy-mm-dd hh24:mi:ss1)

Notes要运用Flashback Version Query, 用户首要要有目标的Select, flashback权限,对表履行过DDL句子之后,就只能查看DDL句子之后的版别,也就是说Flashback Version Query 不会跨过DDL操作。

事例剖析:

12:50:06 SYS@ test1 >conn scott/tiger
Connected.

12:50:14 SCOTT@ test1 >create table emp1 as select * from emp;
Table created.

12:50:32 SCOTT@ test1 >insert into emp1 select * from emp where empno=7788;
1 row created.

12:51:53 SCOTT@ test1 >commit;
Commit complete.

12:51:56 SCOTT@ test1 >update emp1 set sal=9000 where empno=7369;
1 row updated.

12:52:10 SCOTT@ test1 >commit;
Commit complete.

12:52:11 SCOTT@ test1 >delete from emp where empno=7788;
1 row deleted.

12:52:23 SCOTT@ test1 >rollback;
Rollback complete.


col versions_xid format a16 heading XID
col versions_startscn format 99999999 heading Vsn|Start|SCN
col versions_endscn  format 99999999 heading Vsn|End|SCN
col versions_operation format a12  heading Operation

select versions_xid, versions_startscn, versions_endscn,
decode( versions_operation, I, Insert, U, Update, D, Delete, Original) "operation",
empno, ename
from emp1
versions between scn minvalue and maxvalue;
Elapsed: 00:00:00.03
12:53:12 SCOTT@ test1 >select versions_xid, versions_startscn, versions_endscn,
12:54:33   2  decode( versions_operation, I, Insert, U, Update, D, Delete, Original) "operation",
12:54:33   3  empno, ename
12:54:33   4  from emp1
12:54:33   5  versions between scn minvalue and maxvalue;
                       Vsn       Vsn
                     Start       End
XID                    SCN       SCN operation                             EMPNO ENAME
 - -   
08001F007D260000  11966074           Update                                 7369 SMITH
                            11966074 Original                               7369 SMITH
                                     Original                               7499 ALLEN
                                     Original                               7521 WARD
                                     Original                               7566 JONES
                                     Original                               7654 MARTIN
                                     Original                               7698 BLAKE
                                     Original                               7782 CLARK
                                     Original                               7788 SCOTT
                                     Original                               7839 KING
                                     Original                               7844 TURNER
                                     Original                               7876 ADAMS
                                     Original                               7900 JAMES
                                     Original                               7902 FORD
                                     Original                               7934 MILLER
                       Vsn       Vsn
                     Start       End
XID                    SCN       SCN operation                             EMPNO ENAME
 - -   
0A0020006B260000  11966067           Insert                                 7788 SCOTT
16 rows selected.

三、Flashback transaction query

      Flashback Transaction Query 与Flashback Version Query相似,也是运用UNDO信息来完成,运用这个功用能够查看某个业务履行的一切改动。运用这个功用,需求拜访Flashback_transaction_query视图

      Oracle Flashback Transaction Query特性保证查看数据库的任何改动在一个业务等级,能够运用此功用进行确诊问题、功用剖析和审计业务。它其实是Flashback Version Query查询的一个扩大,Flashback Version Query说明晰能够审计一段时刻内表的一切改动,可是也仅仅是能发现问题,关于过错的业务,没有好的处理方法。而Flashback Transaction Query供给了从FLASHBACK_TRANSACTION_QUERY视图中取得业务的前史以及Undo_sql(回滚业务对应的sql句子),也就是说审计一个业务究竟做了什么,乃至能够回滚一个现已提交的业务。

select xid, operation, commit_scn, undo_sql
      from flashback_transaction_query
          where xid in(select versions_xid 
                              from emp1 versions between scn minvalue and                                          maxvalue);

事例剖析:

在运用flashback transaction query之前需求发动Oracle附加日志(supplemental redo log)

发动数据库附加日志:

13:04:58 SQL>Alter database add supplemental log data;

13:06:14 SYS@ test1 >col undo_sql for a50
13:07:17 SYS@ test1 >select xid, operation, commit_scn, undo_sql
  2  from flashback_transaction_query
  3  where xid in(
  4  select versions_xid
  5  from scott.emp1
  6* versions between scn minvalue and maxvalue)
  
XID              OPERATION                        COMMIT_SCN UNDO_SQL
   
0A0020006B260000 INSERT                             11966067 delete from "SCOTT"."EMP1" where ROWID = AAAEgIAA
                                                             JAAAAC3AAA;
0A0020006B260000 BEGIN                              11966067
08001F007D260000 UPDATE                             11966074 update "SCOTT"."EMP1" set "SAL" = 800 where ROWI
                                                             D = AAAEgIAAJAAAACzAAA;
08001F007D260000 BEGIN                              11966074

事例2:
13:08:40 SYS@ test1 >conn scott/tiger
Connected.
14:03:14 SCOTT@ test1 >insert into emp1 select * from emp where rownum=1;
1 row created.

14:03:30 SCOTT@ test1 >commit;
Commit complete.

14:03:45 SCOTT@ test1 >update emp set sal=9000 where empno=7788;
1 row updated.

14:03:55 SCOTT@ test1 >commit;
Commit complete.

14:03:57 SCOTT@ test1 >delete from emp where empno=7369;
0 rows deleted.

14:04:08 SCOTT@ test1 >commit;
Commit complete.
14:08:10 SYS@ test1 >col undo_sql for a50
14:08:24 SYS@ test1 >select xid, operation, commit_scn, undo_sql
  2  from flashback_transaction_query
  3  where xid in(
  4  select versions_xid
  5  from scott.emp1
  6   versions between timestamp
  7   to_timestamp(2015-01-14 14:00:00,yyyy-mm-dd hh24:mi:ss)
  8* and to_timestamp(2015-01-14 14:05:00,yyyy-mm-dd hh24:mi:ss))

XID              OPERATION                        COMMIT_SCN UNDO_SQL
   
04001B0028260000 INSERT                             11985210 delete from "SCOTT"."EMP1" where ROWID = AAAEgIAA
                                                             JAAAAC1AAA;

04001B0028260000 BEGIN                              11985210





版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章