oracle中关于删除表purge语句和闪回语句的基本使用
时间:2022-03-16 09:59
语法: 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