/*************************************************************************/
实验目的: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 130014 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 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 130014 rows selected. 9、闪回恢复数据
SQL> alter table emp1 enable row movement;Table altered.
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 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 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 /*********************************************************************************************/
实验目的:Flashback table : Cannot span DDL operations（DDL操作为TRUNCATE TABLE emp1）
实验步骤：
-
创建表 emp1;记录时间戳
-
truncate table ；记录时间戳
-
插入数据
-
恢复数据
实验结论: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 800SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_date from dual;CURRENT_DATE-------------------2013-05-17 13:09:54SQL> truncate table emp1;Table truncated.SQL> select empno,ename,sal from emp1 where empno = 7369;no rows selectedSQL> 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 800SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_date from dual;CURRENT_DATE-------------------2013-05-17 13:11:19SQL> 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 enabledSQL> 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 changedSQL> 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 800SQL> select object_name,object_type from user_objects;OBJECT_NAME OBJECT_TYPE------------------------------ -------------------EMP1 TABLESYS_TEMP_FBT TABLESALGRADE TABLEBONUS TABLEPK_EMP INDEXEMP TABLEDEPT TABLEPK_DEPT INDEX8 rows selected.SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') cur_date from dual;CUR_DATE-------------------2013-05-17 13:57:56SQL> create index emp1_empno_idx on emp1(empno);Index created.SQL> select object_name,object_type from user_objects;OBJECT_NAME OBJECT_TYPE------------------------------ -------------------EMP1 TABLEEMP1_EMPNO_IDX INDEXSYS_TEMP_FBT TABLESALGRADE TABLEBONUS TABLEPK_EMP INDEXEMP TABLEDEPT TABLEPK_DEPT INDEX9 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 800SQL> select object_name,object_type from user_objects;OBJECT_NAME OBJECT_TYPE------------------------------ -------------------EMP1 TABLEEMP1_EMPNO_IDX INDEXSYS_TEMP_FBT TABLESALGRADE TABLEBONUS TABLEPK_EMP INDEXEMP TABLEDEPT TABLEPK_DEPT INDEX9 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:33SQL> select empno,ename,sal from emp1 where empno = 7369; EMPNO ENAME SAL---------- ---------- ---------- 7369 SMITH 800SQL> 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 1000SQL> 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