[bbk4778] 第31集 - 第三章 Flashback Table 08

Quiz

Select all correct statements:

1. The database can remain open when a table is flashed back.
2. Flashback Table is executed as a single transaction.
3. Flashback Table is requies backups to be available.(flashback table 操作，是不依赖于backup的)
4. Flashback Table is based on undo data.

Flashback Transaction Query

/**************************************************************************************************************/

实验目的：flashback transaction

/**************************************************************************************************************/

SQL> show user
USER is "SCOTT"
SQL> !clear

SQL> select * from flashback_transaction_query;
select * from flashback_transaction_query
              *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> conn /as sysdba
Connected.
SQL> grant select any transaction to SCOTT;

Grant succeeded.

SQL> conn SCOTT/TIGER
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY TRANSACTION

11 rows selected.

SQL> create table dept1
  2  as
  3  select * from dept;

Table created.

SQL> create table emp1
  2  as
  3  select * from emp;

Table created.

SQL> select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select empno,ename,sal from emp1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') cur_date from dual;

CUR_DATE
-------------------
2013-05-17 17:05:57

SQL> update emp1 set sal=1800 where empno = 7369;

1 row updated.

SQL> select empno,ename,sal from emp1 where empno = 7369;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1800

SQL> commit;

Commit complete.

SQL> update emp1 set sal=2800 where empno = 7369;

1 row updated.

SQL> insert into dept1 values(50,'d50','loc50');

1 row created.

SQL> insert into emp1(empno,ename,sal,deptno) values(9999,'arcerzhang',10000,50);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 d50            loc50

SQL> select empno,ename,sal,deptno from emp1;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            2800         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      9999 arcerzhang      10000         50

15 rows selected.

flashback transaction query

flashback versions query

SQL> /

VERSIONS_XID     VERSIONS_STARTTIME        VERSIONS_ENDTIME               EMPNO        SAL
---------------- ------------------------- ------------------------- ---------- ----------
06001D00D9080000 17-MAY-13 05.09.36 PM                                     7369       2800
050017006B090000 17-MAY-13 05.07.05 PM     17-MAY-13 05.09.36 PM           7369       1800
                                           17-MAY-13 05.07.05 PM           7369        800

根据VERSIONS_XID查询事务信息

SQL> select table_name,operation,undo_sql from flashback_transaction_query where xid='06001D00D9080000';

TABLE_NAME OPERATION    UNDO_SQL
---------- ------------ ----------------------------------------
EMP1       UNKNOWN
DEPT1      UNKNOWN
EMP1       UNKNOWN
           BEGIN

根据上面的UNDO_SQL执行相应的SQL,即可恢复这个事务的所有操作.

Flashback Transaction Query

Using Enterprise Manager to Perform Flashback Transaction Query

Flashback Transaction Query:Considerations

• DDL commands are seen as dictionary updates.
• Flashback transaction Query to a transaction underlying a DDL command displays the data dictionary changes.
• Dropped objects appear as object numbers.
• Dropped users appear as user identifiers.