rollback产生redo及logmnr查看日志内容

scott@ORCL> truncate table t ;

表被截断。

scott@ORCL> insert into t select * from dba_objects where rownum<2000;

已创建1999行。

scott@ORCL> select b.name, a.value
  2    from v$mystat a, v$statname b
  3   where a.statistic# = b.statistic#
  4     and b.name = 'redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          11118664

scott@ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                15150477

scott@ORCL> rollback;

回退已完成。

scott@ORCL> select b.name, a.value
  2    from v$mystat a, v$statname b
  3   where a.statistic# = b.statistic#
  4     and b.name = 'redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          11133776

scott@ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                15150549

scott@ORCL> select MEMBER from v$logfile where group# in (select group# from v$log where status ='CURRENT');

MEMBER
----------------------------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG

scott@ORCL> exec sys.dbms_logmnr.add_logfile(logfilename => 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG', options => sys.dbms_logmnr.new)

PL/SQL 过程已成功完成。

scott@ORCL> exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog,startscn =>15150477,endscn => 15150549);

PL/SQL 过程已成功完成。

回滚产生了redo，下面看看rollback产生了哪些内容：

scott@ORCL> col sql_undo for a40;
scott@ORCL> col sql_redo for a40;
scott@ORCL> select sql_undo,sql_redo from v$logmnr_contents where rownum<20;

SQL_UNDO                                 SQL_REDO
---------------------------------------- ----------------------------------------

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAA';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAB';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAC';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAD';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAE';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAF';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAG';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAH';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAI';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAJ';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAK';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAL';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAM';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAN';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvAAAO';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvHAAO';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvHAAP';

                                         delete from "SCOTT"."T" where ROWID = 'A
                                         AAV12AAEAAAKvHAAQ';


已选择19行。

redo中把之前插入的数据删除