oracle 闪回表定义,oracle闪回技术总结之闪回表(DML)

SQL> conn sys/MANAGER as sysdba

Connected.

SQL>create table scott.emp1 as select * from scott.emp;

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

665401

SQL>delete from scott.emp1 where empno=7788;

SQL>commit;

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

665780

SQL>delete from scott.emp1;

13 rowsdeleted;

SQL>commit;

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

665799

SQL>alter table scott.emp1 enable row movement;闪回表需做row movement;

Tablealtered.

SQL> flashback table scott.emp1 to scn 665780;

SQL>select * from scott.emp1;

EMPNO ENAME     JOB               MGRHIREDATE              SAL        COMM          DEPTNO

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

7369 SMITH     CLERK              790217-DEC-80              800                      20

7499 ALLEN     SALESMAN              769820-FEB-81             1600         300              30

7521 WARD      SALESMAN              769822-FEB-81             1250         500              30

7566 JONES     MANAGER              783902-APR-81             2975                      20

7654 MARTIN    SALESMAN              769828-SEP-81             1250        1400              30

7698 BLAKE     MANAGER              783901-MAY-81             2850                      30

7782 CLARK     MANAGER              783909-JUN-81             2450                      10

7839 KING       PRESIDENT          17-NOV-81             5000                      10

7844 TURNER    SALESMAN              769808-SEP-81             1500           0              30

7876 ADAMS     CLERK              778823-MAY-87             1100                      20

7900 JAMES     CLERK              769803-DEC-81              950                      30

7902 FORD      ANALYST              756603-DEC-81             3000                      20

7934 MILLER    CLERK              778223-JAN-82             1300                      10

13 rowsselected.

SQL>flashback table scott.emp1 to scn 665401;

Flashbackcomplete.

SQL>select * from scott.emp1;

EMPNO ENAME     JOB               MGRHIREDATE              SAL        COMM          DEPTNO

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

7369 SMITH     CLERK              790217-DEC-80              800                      20

7499 ALLEN     SALESMAN              769820-FEB-81             1600         300              30

7521 WARD      SALESMAN              769822-FEB-81             1250         500              30

7566 JONES     MANAGER              783902-APR-81             2975                      20

7654 MARTIN    SALESMAN              769828-SEP-81             1250        1400              30

7698 BLAKE     MANAGER              783901-MAY-81             2850                      30

7782 CLARK     MANAGER              783909-JUN-81             2450                      10

7788 SCOTT     ANALYST              756619-APR-87             3000                      20

7839 KING       PRESIDENT          17-NOV-81             5000                      10

7844 TURNER    SALESMAN              769808-SEP-81             1500           0              30

7876 ADAMS     CLERK              778823-MAY-87             1100                      20

7900 JAMES     CLERK              769803-DEC-81              950                      30

7902 FORD      ANALYST              756603-DEC-81             3000                      20

7934 MILLER    CLERK              778223-JAN-82             1300                      10

14 rowsselected.

SQL>flashback table scott.emp1 to scn 665799;

Flashbackcomplete.

SQL>select * from scott.emp1;

no rowsselected