flashback 实现用户误操作解决方案（闪回查询和闪回版本 闪回事务查询）

SYS@standby1/2011-04-27 17:21:05>create table table_tab as select rownum id,dbms_random.string('U',3) as t1 from dual connect by level<=10;

Table created.

Elapsed: 00:00:00.26

SYS@standby1/2011-04-27 17:21:29>col id for 9999
SYS@standby1/2011-04-27 17:21:41>col t1 for a5
SYS@standby1/2011-04-27 17:21:47>select * from table_tab;

   ID T1
----- -----
    1 QVV
    2 WQK
    3 NGY
    4 IYN
    5 ZYR
    6 NHV
    7 CLO
    8 QET
    9 MYJ
   10 AEE

10 rows selected.

Elapsed: 00:00:00.04

SYS@standby1/2011-04-27 17:22:11>select current_scn from v$database;

CURRENT_SCN
-----------
    1536663

1 row selected.

Elapsed: 00:00:00.00


SYS@standby1/2011-04-27 17:22:24>alter system switch logfile;

System altered.

Elapsed: 00:00:00.03
SYS@standby1/2011-04-27 17:23:49>@log_file.sql

FILE                                                         TYPE       GROUP  SEQ   SIZE MEM ARC STATUS
------------------------------------------------------------ ---------- ----- ---- ------ --- --- ----------
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh23t_.log     ONLINE         1    7    512   3 YES ACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh2c1_.log     ONLINE         1    7    512   3 YES ACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh3nm_.log     ONLINE         1    7    512   3 YES ACTIVE
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh5wx_.log     ONLINE         2    8    512   3 NO  CURRENT
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh6b7_.log     ONLINE         2    8    512   3 NO  CURRENT
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh8mn_.log     ONLINE         2    8    512   3 NO  CURRENT
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc7m7_.log     ONLINE         3    6    512   3 YES INACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc80d_.log     ONLINE         3    6    512   3 YES INACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_3_6sxscb5g_.log     ONLINE         3    6    512   3 YES INACTIVE

9 rows selected.

Elapsed: 00:00:00.02


SYS@standby1/2011-04-27 17:23:57>select current_scn from v$database;

CURRENT_SCN
-----------
    1536733

1 row selected.

Elapsed: 00:00:00.00


SYS@standby1/2011-04-27 17:24:51>delete table_tab where id>7;

3 rows deleted.

Elapsed: 00:00:00.00
SYS@standby1/2011-04-27 17:25:11>commit;

Commit complete.

Elapsed: 00:00:00.00
SYS@standby1/2011-04-27 17:25:15>select current_scn from v$database;

CURRENT_SCN
-----------
    1536759

1 row selected.

Elapsed: 00:00:00.00

SYS@standby1/2011-04-27 17:25:41>delete table_tab where id<4;

3 rows deleted.

Elapsed: 00:00:00.00
SYS@standby1/2011-04-27 17:26:06>commit;

Commit complete.

Elapsed: 00:00:00.00
SYS@standby1/2011-04-27 17:26:08>select current_scn from v$database;

CURRENT_SCN
-----------
    1536788

1 row selected.

Elapsed: 00:00:00.01
SYS@standby1/2011-04-27 17:26:12>select * from table_tab;

   ID T1
----- -----
    4 IYN
    5 ZYR
    6 NHV
    7 CLO

4 rows selected.

Elapsed: 00:00:00.00



SYS@standby1/2011-04-27 17:27:27>select * from table_tab as of scn 1536788;

   ID T1
----- -----
    4 IYN
    5 ZYR
    6 NHV
    7 CLO

4 rows selected.

Elapsed: 00:00:00.00
SYS@standby1/2011-04-27 17:27:52>select * from table_tab as of scn 1536663;

   ID T1
----- -----
    1 QVV
    2 WQK
    3 NGY
    4 IYN
    5 ZYR
    6 NHV
    7 CLO
    8 QET
    9 MYJ
   10 AEE

10 rows selected.

Elapsed: 00:00:00.00

set linesize 200
col versions_xid for 99999 heading "XID"
col versions_startscn for 99999999999 heading "STARTSCN"
col versions_endscn for 99999999999 heading "ENDSCN"
col versions_starttime for a20 heading "STARTTIME"
col versions_endtime for a20 heading "ENDTIME"
col versions_operation for a5 heading "OPER"

SYS@standby1/2011-04-27 17:31:27>select versions_xid,versions_startscn,versions_starttime,
versions_endscn,versions_endtime,versions_operation,id,t1 from table_tab versions between scn 1536733 and 1536788;

XID                  STARTSCN STARTTIME                  ENDSCN ENDTIME              OPER     ID T1
---------------- ------------ -------------------- ------------ -------------------- ----- ----- -----
0A000D000D020000      1536786 27-APR-11 05.26.06 P                                   D         3 NGY
                              M

0A000D000D020000      1536786 27-APR-11 05.26.06 P                                   D         2 WQK
                              M

0A000D000D020000      1536786 27-APR-11 05.26.06 P                                   D         1 QVV
                              M

06001B00D5020000      1536757 27-APR-11 05.25.14 P                                   D        10 AEE
                              M

06001B00D5020000      1536757 27-APR-11 05.25.14 P                                   D         9 MYJ
                              M

06001B00D5020000      1536757 27-APR-11 05.25.14 P                                   D         8 QET
                              M

                                                        1536786 27-APR-11 05.26.06 P           1 QVV
                                                                M

                                                        1536786 27-APR-11 05.26.06 P           2 WQK
                                                                M

                                                        1536786 27-APR-11 05.26.06 P           3 NGY
                                                                M

                                                                                               4 IYN
                                                                                               5 ZYR
                                                                                               6 NHV
                                                                                               7 CLO
                                                        1536757 27-APR-11 05.25.14 P           8 QET
                                                                M

                                                        1536757 27-APR-11 05.25.14 P           9 MYJ
                                                                M

                                                        1536757 27-APR-11 05.25.14 P          10 AEE
                                                                M


16 rows selected.

Elapsed: 00:00:00.03




set linesize 220
col xid for 99999999
col start_scn for 9999999999999
col start_timestamp for a20
col commit_scn for 9999999999999
col logon_user for a10
col operation for a5 heading "OPER"
col table_name for a10
col undo_sql for a50
SYS@standby1/2011-04-27 17:36:00>
select xid,start_scn,start_timestamp,commit_scn,logon_user,operation,
table_name,undo_sql from flashback_transaction_query
where xid in (select versions_xid from table_tab versions between scn 1554552 and 1554712);

XID                   START_SCN START_TIMESTAMP          COMMIT_SCN LOGON_USER OPER  TABLE_NAME UNDO_SQL
---------------- -------------- -------------------- -------------- ---------- ----- ---------- --------------------------------------------------
06001B00D5020000        1536754 2011-04-27 17:25:08         1536757 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                10','AEE');

06001B00D5020000        1536754 2011-04-27 17:25:08         1536757 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                9','MYJ');

06001B00D5020000        1536754 2011-04-27 17:25:08         1536757 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                8','QET');

06001B00D5020000        1536754 2011-04-27 17:25:08         1536757 SYS        BEGIN
0A000D000D020000        1536784 2011-04-27 17:26:03         1536786 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                3','NGY');

0A000D000D020000        1536784 2011-04-27 17:26:03         1536786 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                2','WQK');

0A000D000D020000        1536784 2011-04-27 17:26:03         1536786 SYS        DELET TABLE_TAB  insert into "SYS"."TABLE_TAB"("ID","T1") values ('
                                                                               E                1','QVV');

0A000D000D020000        1536784 2011-04-27 17:26:03         1536786 SYS        BEGIN

8 rows selected.

Elapsed: 00:00:13.45
SYS@standby1/2011-04-27 17:38:09>

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

转载于:http://blog.itpub.net/24890594/viewspace-694043/