FLASHBACK 应用example (flashback笔记-03)

 

flashback 特性

 

 

启用flashback

conn / as sysdba

shutdownimmediate;

startup mount;

alter databaseflashback on;

alter databaseopen;

alter system setdb_flashback_retention_target=480  scope=both;

--DB_FLASHBACK_RETENTION_TARGET A parametervalue that determines how far back in time you can recover the flashbackdatabase, 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         MGRHIREDATE         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

7521WARD       SALESMAN   76981981/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

7839KING      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      76981981/12/3     950.00              30

7902FORD       ANALYST    75661981/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 viewdoes not exist

 

SQL>flashback table test_lihq to before drop;

 

Done

 

SQL>select * from test_lihq;

 

EMPNO ENAME     JOB         MGRHIREDATE         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

7521WARD       SALESMAN   76981981/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

7839KING      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      76981981/12/3     950.00              30

7902FORD       ANALYST    75661981/12/3     3000.00              20

 

13 rows selected

                           

Flashback table 示例

Connected to Oracle Database 10g EnterpriseEdition 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   NUMBERIN           

 

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 rowmovement;

 

Table altered

 

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

--月光宝盒

Done

 

SQL> select * from banchu;

 

        ID

----------

       100

       200

       300

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

SQL>

 

Flashback Version Query 示例

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.1.0

Connected as scott

 

SQL> CREATE TABLEflashback_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-0817:11:04

 

SQL> UPDATE flashback_version_query SETdescription = 'John' WHERE id = 1;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> UPDATE flashback_version_query SETdescription = '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-0817:11:50

 

SQL>

SQL> COLUMN versions_startscn FORMAT99999999999999999

SQL> COLUMN versions_starttime FORMATA24

SQL> COLUMN versions_endscn FORMAT99999999999999999

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    FROMflashback_version_query VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2011-04-0817: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_STARTSCNVERSIONS_STARTTIME        VERSIONS_ENDSCNVERSIONS_ENDTIME        VERSIONS_XID     V DESCRIPTION

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

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

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

                                                     649933 08-APR-11 05.11.29PM                      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 649924AND 649945

  7  WHERE id = 1;

 

VERSIONS_STARTSCNVERSIONS_STARTTIME        VERSIONS_ENDSCNVERSIONS_ENDTIME        VERSIONS_XID     V DESCRIPTION

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

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

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

                                                     649933 08-APR-11 05.11.29PM                      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    FROMflashback_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

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

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

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

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

 

SQL>

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