oracle中关于删除表purge语句和闪回语句的基本使用

时间:2020-12-21 20:43:00
语法: drop table ... purge;
例子:drop table test purge;
purge是直接删除表,不保留到回收站,10G开始默认drop表式改名移动到回收站;
闪回(flashback)语句:
1、能在一个语句中把表恢复到指定的时间点;
2、恢复表数据连同索引与约束信息;
3、能返回表及其内容到指定时间点或系统变更号(SCN);
4、修复表的误操作
闪回简单示例:
SQL> drop table emp2;
 
Table dropped
 
SQL> select original_name,operation,droptime from recyclebin;
 
ORIGINAL_NAME                    OPERATION DROPTIME
-------------------------------- --------- -------------------
EMP2                             DROP      2012-11-16:10:49:13
 
SQL> flashback table emp2 to before drop;
 
Done
 
SQL> select count(*) from emp2;
 
  COUNT(*)
----------
       107
 
--恢复误删表数据示例:
SQL>  select sysdate 时间, timestamp_to_scn(sysdate) SCN from dual;
 
时间               SCN
----------- ----------
2012/11/16     1230043
 
SQL> delete from emp2;
 
107 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select count(*) from emp2;
 
  COUNT(*)
----------
         0
 
SQL> flashback table emp2 to scn 1230043;
 
flashback table emp2 to scn 1230043
 
ORA-08189: cannot flashback the table because row movement is not enabled
 
SQL> alter table emp2 enable row movement;
 
Table altered
 
SQL> flashback table emp2 to scn 1230043;
 
Done
 
SQL> select count(*) from emp2;
 
  COUNT(*)
----------
       107