[bbk4774] 第30集 - 第三章 Flashback Table 07

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

实验目的:Flashback table : Cannot span DDL operations

实验步骤：见下图

实验结论:Cannot span DDL operations

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

1、建表->查询数据->记录时间戳

SQL> create table emp1 as select * from emp;

Table created.

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') current_date from dual;

CURRENT_DATE
-------------------
2013-05-17 12:46:34

2、模拟误操作，更新empno=7369的用户工资为1000

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

1 row updated.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1000

3、执行DDL操作，添加约束（ename）

SQL> alter table emp1
  2  add constraint ename_un unique (ename);

Table altered.

4、记录系统时间戳

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

CURRENT_DATE
-------------------
2013-05-17 12:50:53

5、模拟误操作，更新empno=7369的用户工资为2000

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

1 row updated.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            2000

6、记录系统时间戳

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

CURRENT_DATE
-------------------
2013-05-17 12:52:05

7、模拟误操作，更新empno=7369的用户工资为3000

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

1 row updated.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            3000

8、闪回版本查询

SQL> select empno,ename,sal from emp1
  2  as of timestamp to_timestamp('2013-05-17 12:46:34','yyyy-mm-dd hh24:mi:ss');
select empno,ename,sal from emp1
                            *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
闪回查询DDL操作之前的数据；不可以
SQL> select empno,ename,sal from emp1
  2  as of timestamp to_timestamp('2013-05-17 12:50:53','yyyy-mm-dd hh24:mi:ss');

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1000
      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.
闪回查询DDL之后的数据；可以

9、闪回恢复数据

SQL> alter table emp1 enable row movement;

Table altered.
执行闪回表执勤啊，需要先开启row movement功能 
SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 12:46:34','yyyy-mm-dd hh24:mi:ss');
flashback table emp1 to timestamp to_timestamp('2013-05-17 12:46:34','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
闪回到DDL操作之前的数据，不可以；
SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 12:50:53','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1000
闪回到DDL之后数据，可以；2013-05-17 12:50:53
SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 12:52:05','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            2000
闪回到DDL之后数据，可以；2013-05-17 12:52:05

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

实验目的:Flashback table : Cannot span DDL operations（DDL操作为TRUNCATE TABLE emp1）

实验步骤：

  1. 创建表 emp1;记录时间戳

  2. truncate table ；记录时间戳

  3. 插入数据

  4. 恢复数据

实验结论:Cannot span DDL operations

如果需要恢复truncate之前的数据，只能使用不完全恢复或者flashback database方法.

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

SQL> create table emp1 as select * from emp;

Table created.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

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

CURRENT_DATE
-------------------
2013-05-17 13:09:54

SQL> truncate table emp1;

Table truncated.

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

no rows selected

SQL> insert into emp1 select * from emp where empno=7369;

1 row created.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

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

CURRENT_DATE
-------------------
2013-05-17 13:11:19

SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 13:09:54','yyyy-mm-dd hh24:mi:ss');
flashback table emp1 to timestamp to_timestamp('2013-05-17 13:09:54','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table emp1 enable row movement;

Table altered.

SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 13:09:54','yyyy-mm-dd hh24:mi:ss');
flashback table emp1 to timestamp to_timestamp('2013-05-17 13:09:54','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 13:11:19','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

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

实验目的:Flashback table : Cannot span DDL operations（DDL操作为CREATE INDEX emp_empno_idx除外）

实验步骤:见如下代码 

实验结论:Cannot span DDL operations,但是create index操作除外

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

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
EMP1                           TABLE
SYS_TEMP_FBT                   TABLE
SALGRADE                       TABLE
BONUS                          TABLE
PK_EMP                         INDEX
EMP                            TABLE
DEPT                           TABLE
PK_DEPT                        INDEX

8 rows selected.

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

CUR_DATE
-------------------
2013-05-17 13:57:56

SQL> create index emp1_empno_idx on emp1(empno);

Index created.

SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
EMP1                           TABLE
EMP1_EMPNO_IDX                 INDEX
SYS_TEMP_FBT                   TABLE
SALGRADE                       TABLE
BONUS                          TABLE
PK_EMP                         INDEX
EMP                            TABLE
DEPT                           TABLE
PK_DEPT                        INDEX

9 rows selected.

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

1 row updated.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1000SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 13:57:56','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

验证数据，数据恢复成功，但是索引仍旧存在.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
EMP1                           TABLE
EMP1_EMPNO_IDX                 INDEX
SYS_TEMP_FBT                   TABLE
SALGRADE                       TABLE
BONUS                          TABLE
PK_EMP                         INDEX
EMP                            TABLE
DEPT                           TABLE
PK_DEPT                        INDEX

9 rows selected.
SQL> select index_name,status from user_indexes where table_name='EMP1';

INDEX_NAME                     STATUS
------------------------------ --------
EMP1_EMPNO_IDX                 VALID

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

实验目的:Flashback table : Cannot span DDL operations（DDL操作为CREATE TRIGGER emp1_trig除外）

实验步骤:见下面代码

实验结论:Cannot span DDL operations,但是create trigger操作除外

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

 

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

CUR_DATE
-------------------
2013-05-17 14:15:33

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

SQL> create trigger emp1_trig
  2  before insert or update or delete
  3  on emp1
  4  for each row
  5  begin
  6     null;
  7  end;
  8  /

Trigger created.
SQL> update emp1 set sal=1000 where empno = 7369;

1 row updated.

SQL> commit;

Commit complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1000

SQL> flashback table emp1 to timestamp to_timestamp('2013-05-17 14:15:33','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
SQL> select trigger_name,status from user_triggers
  2  where table_name='EMP1';

TRIGGER_NAME                   STATUS
------------------------------ --------
EMP1_TRIG                      ENABLED

 

posted @ 2013-05-17 12:19  ArcerZhang  阅读(206)  评论(0)  编辑  收藏  举报