oracle 闪回drop-pl
闪回drop：将某些误删除的表，恢复到删除前的状态：
数据来源是表空间的回收站机制；
原理：当一个表被删除之后，它并没有被马上覆盖，仅仅是再它所占用的空间上，标识了一个删除标记；
然后，表名被改成了一个回收站的名字，相关的元数据信息存放到了不同的数据字典当中；
只要它的空间，没有被覆盖，那么就能够还原回来；
闪回drop：
1.查看当前的回收站机制是否开启（默认开启的）
SYS@orcl11g> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
--关闭recyclebin
SYS@orcl11g> alter system set recyclebin=off scope=spfile;
--开启recyclebin
SYS@orcl11g> alter system set recyclebin=on scope=spfile;
2.删除机制
drop table table_name；
--这样的删除方式，会实现回收站机制
--可能实现闪回drop的；
drop table table_name purge;
--彻底删除，不会进入回收站的数据字典；
--无法实现闪回drop；
3.样例演示
HR@orcl11g> create table test as select * from employees;
HR@orcl11g> select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE (null)
DEPARTMENTS TABLE (null)
EMPLOYEES TABLE (null)
EMPVU20 VIEW (null)
EMP_DETAILS_VIEW VIEW (null)
JOBS TABLE (null)
JOB_HISTORY TABLE (null)
LOCATIONS TABLE (null)
REGIONS TABLE (null)
SUM_V VIEW (null)
SYS_TEMP_FBT TABLE (null)
TEST TABLE (null)
4.删除test表
HR@orcl11g> drop table test;
HR@orcl11g> select * from tab;
TNAME TABTYPECLUSTERID
------------------------------ ------- ----------
BIN$4B80gCUwS/LgQ/0DqMA3lg==$0 TABLE (null)
COUNTRIES TABLE (null)
DEPARTMENTS TABLE (null)
EMPLOYEES TABLE (null)
EMPVU20 VIEW (null)
EMP_DETAILS_VIEW VIEW (null)
JOBS TABLE (null)
JOB_HISTORY TABLE (null)
LOCATIONS TABLE (null)
REGIONS TABLE (null)
SUM_V VIEW (null)
SYS_TEMP_FBT TABLE (null)
12 rows selected.
5.其他显示方法
--查看当前的recyclebin
HR@orcl11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPEDROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$4B80gCUwS/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:04:01
--通过数据字典查询
HR@orcl11g> select object_name,original_name,operation,type,droptime from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE DROPTIME
------------------------- -------------------
BIN$4B80gCUwS/LgQ/0DqMA3lg==$0 TEST DROP
TABLE 2013-06-27:16:04:01
6.闪回drop
HR@orcl11g> flashback table test to before drop;
7.几个删除场景
--删除
HR@orcl11g> drop table test;
--可以使用回收站的名字查询数据
HR@orcl11g> select employee_id,salary from "BIN$4B80gCU1S/LgQ/0DqMA3lg==$0" where employee_id=100;
EMPLOYEE_ID SALARY
----------- ----------
100 31951
--创建同名表，和原来被删除的同名
HR@orcl11g> create table test as select * from departments;
--再次删除
HR@orcl11g> drop table test;
--查看recyclebin
HR@orcl11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPEDROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$4B80gCU3S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:10:28
TEST BIN$4B80gCU1S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:07:53
--同名表被drop，闪回drop的原则
--后进先出，还原回最近被删除的表
HR@orcl11g> flashback table test to before drop;
--可以根据回收站的名字做闪回
HR@orcl11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPEDROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$4B80gCU5S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:12:53
TEST BIN$4B80gCU1S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:07:53
HR@orcl11g> flashback table "BIN$4B80gCU1S/LgQ/0DqMA3lg==$0" to before drop;
--闪回固定的被删除表
--如果两个同名表都要闪回drop
HR@orcl11g> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPEDROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$4B80gCU+S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:14:09
TEST BIN$4B80gCU5S/LgQ/0DqMA3lg==$0 TABLE 2013-06-27:16:12:53
HR@orcl11g> flashback table test to before drop;
HR@orcl11g> flashback table test to before drop rename to test1;
--闪回的同时，修改表的名字
8.对于闪回drop的一些小问题
HR@orcl11g> alter table test add constraint test_pk primary key(employee_id);
HR@orcl11g> create index test_ind on test(department_id);
HR@orcl11g> select table_name,constraint_name,constraint_type from user_constraints where table_name='TEST';
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TEST TEST_PK P
HR@orcl11g> select table_name,index_name,index_type from user_indexes
2where table_name='TEST';
TABLE_NAME INDEX_NAMEINDEX_TYPE
------------------------------ --------------------- ---------------------------
TEST TEST_IND NORMAL
TEST TEST_PK NORMAL
--删除表test
HR@orcl11g> drop table test;
--闪回drop表
HR@orcl11g> flashback table test to before drop;
--相应的索引和约束也都回来了
HR@orcl11g> select constraint_name,constraint_type from user_constraints
2where table_name='TEST';
CONSTRAINT_NAME C
------------------------------ -
BIN$4B80gCU/S/LgQ/0DqMA3lg==$0 P
HR@orcl11g> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
BIN$4B80gCVAS/LgQ/0DqMA3lg==$0 NORMAL
BIN$4B80gCVBS/LgQ/0DqMA3lg==$0 NORMAL
--可以修改对应的约束和索引的名字
HR@orcl11g> alter table test rename constraint "BIN$4B80gCU/S/LgQ/0DqMA3lg==$0" to test_pk;
HR@orcl11g> select index_name,column_name from user_ind_columns
2where table_name='TEST';
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
BIN$4B80gCVBS/LgQ/0DqMA3lg==$0
EMPLOYEE_ID
BIN$4B80gCVAS/LgQ/0DqMA3lg==$0
DEPARTMENT_ID
HR@orcl11g> alter index "BIN$4B80gCVBS/LgQ/0DqMA3lg==$0" rename to test_pk;
HR@orcl11g> alter index "BIN$4B80gCVAS/LgQ/0DqMA3lg==$0" rename to test_ind;
页:
[1]