FLASHBACK 应用example (flashback笔记-03)

 

flashback 特性

 

 

启用 flashback

conn / as sysdba

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

alter system set db_flashback_retention_target=480  scope=both;

--DB_FLASHBACK_RETENTION_TARGET A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.

 

 

 

Flashback  drop table   示例

 

SQL> create table test_lihq as select * from emp;

 

Table created

 

SQL>

SQL> select * from test_lihq;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

7369 SMITH      CLERK      7902 1980/12/17     800.00               20

7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

7839 KING       PRESIDENT       1981/11/17    5000.00               10

7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

7900 JAMES      CLERK      7698 1981/12/3      950.00               30

7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

13 rows selected

 

SQL> drop table test_lihq;

 

Table dropped

 

SQL>

SQL> select * from  test_lihq;

 

select * from  test_lihq

 

ORA-00942: table or view does not exist

 

SQL> flashback table test_lihq to before drop;

 

Done

 

SQL> select * from test_lihq;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

7369 SMITH      CLERK      7902 1980/12/17     800.00               20

7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

7839 KING       PRESIDENT       1981/11/17    5000.00               10

7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

7900 JAMES      CLERK      7698 1981/12/3      950.00               30

7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 

13 rows selected

                           

Flashback table 示例

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> create table banchu(id number);

 

Table created

 

SQL> insert into banchu values (100);

 

1 row inserted

 

SQL> insert into banchu values (200);

 

1 row inserted

 

SQL> insert into banchu values (300);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select * from banchu;

 

        ID

----------

       100

       200

       300

 

SQL>

SQL> desc dbms_lock.sleep;

Parameter Type   Mode Default?

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

SECONDS   NUMBER IN           

 

SQL> exec dbms_lock.sleep(180);

等待 3 分钟

PL/SQL procedure successfully completed

 

 

SQL> insert into banchu values(400);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select * from banchu;

 

        ID

----------

       100

       200

       300

       400

 

 

SQL> alter table banchu enable row movement;

 

Table altered

 

SQL> flashback table banchu to timestamp(systimestamp - interval '3' minute);

-- 月光宝盒

Done

 

SQL> select * from banchu;

 

        ID

----------

       100

       200

       300

-- 时光回到 3 分钟前，少了刚刚插入的数据

SQL>

 

Flashback Version Query 示例

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> CREATE TABLE flashback_version_query (id NUMBER(10), description VARCHAR2(50));

 

Table created

 

SQL> INSERT INTO flashback_version_query (id, description) VALUES (1, 'Kunal');

 

1 row inserted

 

SQL> COMMIT;

 

Commit complete

 

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 

CURRENT_SCN TO_CHAR(SYSTIMESTAMP,'YYYY-MM-

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

     649924 2011-04-08 17:11:04

 

SQL> UPDATE flashback_version_query SET description = 'John' WHERE id = 1;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> UPDATE flashback_version_query SET description = 'Mathew' WHERE id = 1;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 

CURRENT_SCN TO_CHAR(SYSTIMESTAMP,'YYYY-MM-

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

     649945 2011-04-08 17:11:50

 

SQL>

SQL> COLUMN versions_startscn FORMAT 99999999999999999

SQL> COLUMN versions_starttime FORMAT A24

SQL> COLUMN versions_endscn FORMAT 99999999999999999

SQL> COLUMN versions_endtime FORMAT A24

SQL> COLUMN versions_xid FORMAT A16

SQL> COLUMN versions_operation FORMAT A1

SQL> COLUMN description FORMAT A11

SQL> SET LINESIZE 200

SQL>

SQL>

SQL>

SQL> SELECT versions_startscn,

  2         versions_starttime,

  3         versions_endscn,

  4         versions_endtime,

  5         versions_xid,

  6         versions_operation,

  7         description

  8    FROM flashback_version_query VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2011-04-08 17:11:04', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2011-04-08 17:11:50', 'YYYY-MM-DD HH24:MI:SS')

  9   WHERE id = 1;

 

VERSIONS_STARTSCN VERSIONS_STARTTIME         VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION

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

           649939 08-APR-11 05.11.34 PM                                               050025003E010000 U Mathew

           649933 08-APR-11 05.11.29 PM               649939 08-APR-11 05.11.34 PM    0100040037010000 U John

                                                      649933 08-APR-11 05.11.29 PM                       Kunal

 

SQL>

SQL>

SQL>

SQL> SELECT versions_startscn, versions_starttime,

  2  versions_endscn, versions_endtime,

  3  versions_xid, versions_operation,

  4  description

  5  FROM flashback_version_query

  6  VERSIONS BETWEEN SCN 649924 AND 649945

  7  WHERE id = 1;

 

VERSIONS_STARTSCN VERSIONS_STARTTIME         VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION

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

           649939 08-APR-11 05.11.34 PM                                               050025003E010000 U Mathew

           649933 08-APR-11 05.11.29 PM               649939 08-APR-11 05.11.34 PM    0100040037010000 U John

                                                      649933 08-APR-11 05.11.29 PM                       Kunal

 

SQL>

 

Flashback Transaction 示例

 

SQL> SELECT xid,

  2         operation,

  3         start_scn,

  4         commit_scn,

  5         logon_user,

  6         table_name,

  7         undo_sql

  8    FROM flashback_transaction_query A

  9   WHERE A.logon_user = 'SCOTT'

10     AND A.table_name = 'FLASHBACK_VERSION_QUERY';

 

XID              OPERATION   START_SCN COMMIT_SCN LOGON_USER  TABLE_NAME                UNDO_SQL

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

0100040037010000 UPDATE         649931     649933 SCOTT       FLASHBACK_VERSION_QUERY   update "SCOTT"."FLASHBACK_VERSION_QUERY" set "DESCRIPTION" = 'Kunal' where ROWID

020027003B010000 INSERT         649914     649916 SCOTT       FLASHBACK_VERSION_QUERY   delete from "SCOTT"."FLASHBACK_VERSION_QUERY" where ROWID = 'AAAM1jAAEAAAAHGAAA'

050025003E010000 UPDATE         649937     649939 SCOTT       FLASHBACK_VERSION_QUERY   update "SCOTT"."FLASHBACK_VERSION_QUERY" set "DESCRIPTION" = 'John' where ROWID

 

SQL>

这个最有用的就是 undo sql 列啦