ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle闪回实验

oracle闪回实验

原创 Linux操作系统 作者:zhujiancomnet 时间:2012-03-30 23:38:33 0 删除 编辑
ONE:---------------闪回表实验

1.启动表的行移动属性

SCOTT@orcl2>alter table emp enable row movement;

表已更改。

2.查看表更改前时间
SCOTT@orcl2>$time

SCOTT@orcl2>select empno,ename,sal from emp;

     EMPNO ENAME             SAL                                                
---------- ---------- ----------                                                
      7369 SMITH             800                                                
      7499 ALLEN            1600                                                
      7521 WARD             1250                                                
      7566 JONES            2975                                                
      7654 MARTIN           1250                                                
      7698 BLAKE            2850                                                
      7782 CLARK            2450                                                
      7839 KING             5000                                                
      7844 TURNER           1500                                                
      7900 JAMES             950                                                
      7902 FORD             3000                                                

     EMPNO ENAME             SAL                                                
---------- ---------- ----------                                                
      7934 MILLER           1300                                                

已选择12行。

3.对表作更新操作
SCOTT@orcl2>update emp set sal=1000  where empno=7369;

已更新 1 行。

SCOTT@orcl2>commit;

提交完成。

3.闪回表
SCOTT@orcl2>flashback table emp to timestamp to_timestamp('2012-03-07 8:38:55','yyyy-mm-dd HH24:mi:ss');

闪回完成。

TWO:--------------闪回删除实验

1.查看初始化参数,启用回收站

SCOTT@orcl2>conn / as sysdba
已连接。
SYS@orcl2>show parameter recyclebin

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------
recyclebin                           string      on                             

SYS@orcl2>conn scott/tiger
已连接。

2.创建一张副表,用来做删除实验
SCOTT@orcl2>create table emp_copy as select * from emp;

表已创建。

SCOTT@orcl2>select * from emp_copy;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30                                                                     
                                                                               
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7566 JONES      MANAGER         7839 02-4月 -81           2975           
        20                                                                     
                                                                               
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30                                                                     
                                                                               
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850           
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7782 CLARK      MANAGER         7839 09-6月 -81           2450           
        10                                                                     
                                                                               
      7839 KING       PRESIDENT            17-11月-81           5000           
        10                                                                     
                                                                               
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7900 JAMES      CLERK           7698 03-12月-81            950           
        30                                                                     
                                                                               
      7902 FORD       ANALYST         7566 03-12月-81           3000           
        20                                                                     
                                                                               
      7934 MILLER     CLERK           7782 23-1月 -82           1300           
        10                                                                     
                                                                               

已选择12行。

3.删除副表

SCOTT@orcl2>drop table emp_copy;

表已删除。

4.使用闪回,恢复副表
SCOTT@orcl2>flashback table emp_copy to before drop;

闪回完成。

5.重新查看副表内容
SCOTT@orcl2>select * from emp_copy;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7369 SMITH      CLERK           7902 17-12月-80            800           
        20                                                                     
                                                                               
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30                                                                     
                                                                               
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7566 JONES      MANAGER         7839 02-4月 -81           2975           
        20                                                                     
                                                                               
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30                                                                     
                                                                               
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850           
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7782 CLARK      MANAGER         7839 09-6月 -81           2450           
        10                                                                     
                                                                               
      7839 KING       PRESIDENT            17-11月-81           5000           
        10                                                                     
                                                                               
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30                                                                     
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO                                                                     
----------                                                                     
      7900 JAMES      CLERK           7698 03-12月-81            950           
        30                                                                     
                                                                               
      7902 FORD       ANALYST         7566 03-12月-81           3000           
        20                                                                     
                                                                               
      7934 MILLER     CLERK           7782 23-1月 -82           1300           
        10                                                                     
                                                                               

已选择12行。

SCOTT@orcl2>set linesize 100

----将删除的表闪回,并实现重命名
1.删除表
SCOTT@orcl2>drop table emp_copy;

表已删除。
2.查看副表,确定改表已不存在,其实是在recyclebin中

SCOTT@orcl2>select * from emp_copy;
select * from emp_copy
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

3.闪回删除的副表,并重命名,关键字rename
SCOTT@orcl2>flashback table emp_copy to before drop rename to emp_temp;

闪回完成。

THREE:-----------利用闪回查询闪回数据

1.查看emp表中deptno=10的数据
SCOTT@orcl2>select empno,ename,sal from emp where deptno=10;

     EMPNO ENAME             SAL                                                                   
---------- ---------- ----------                                                                   
      7782 CLARK            2450                                                                   
      7839 KING             5000                                                                   
      7934 MILLER           1300                                                                   

2.查看当前时间
SCOTT@orcl2>$time

3.更新emp表中数据
SCOTT@orcl2>update emp set sal = sal+100 where deptno=10;

已更新3行。

SCOTT@orcl2>commit;

提交完成。

4.闪回查询更新之前的数据
SCOTT@orcl2>select * from emp as of timestamp to_timestamp('2012-03-07  8:57:57','yyyy-mm-dd HH24:mi:ss') where deptno=10;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO         
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------         
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10         
      7839 KING       PRESIDENT            17-11月-81           5000                    10         
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10         

5.使用闪回查询出的数据恢复
SCOTT@orcl2>ed
已写入 file afiedt.buf
  1  update emp t set t.sal = (select sal from emp as of timestamp to_timestamp(
  2  '2012-03-07 8:57:57','yyyy-mm-dd hh24:mi:ss') o where o.deptno=10 and o.empno=t.empno)
  3* where t.empno in (select empno from emp as of timestamp to_timestamp('2012-03-07 8:57:57','yyyy-mm-dd hh24:mi:ss') where deptno=10)
SCOTT@orcl2>/

已更新3行。

SCOTT@orcl2>commit;

提交完成。

SCOTT@orcl2>spool off;
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26839357/viewspace-719994/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-03-30

  • 博文量
    2
  • 访问量
    1532