-- 筆記中部分內(nèi)容
SQL> create table tt2 as select * from employee;
Table created.
SQL> drop table tt2;
Table dropped.
SQL> select * from tt2;
select * from tt2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Flashback table tt2 to before drop;
Flashback complete.
SQL> select count(1) from tt2;
COUNT(1)
----------
32
如果在表刪除后,又新建了這個(gè)表,恢復(fù)時(shí)可以重命名,如下:
SQL> drop table tt2;
Table dropped.
SQL> flashback table tt2 to before drop
2 rename to tt2_old;
Flashback complete.
SQL> select count(1) from tt2_old;
COUNT(1)
----------
32
如果這個(gè)表刪除了不止一次,我想恢復(fù)某一次的刪除,怎么辦?
查詢(xún)r(jià)ecyclebin視圖,或者show recyclebin,然后帶名字恢復(fù).
SQL> drop table tt2_old;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TT2_OLD BIN$SOz1cXPCADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:51:49
SQL>
SQL> create table tt2_old as select * from job;
Table created.
SQL> drop table tt2_old;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TT2_OLD BIN$SOz1cXPDADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:52:40
TT2_OLD BIN$SOz1cXPCADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:51:49
SQL> flashback table "BIN$SOz1cXPCADLgQwoKCiEAMg==$0" to before drop;
Flashback complete.
SQL> desc tt2_old
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(15)
MIDDLE_INITIAL VARCHAR2(1)
JOB_ID NUMBER(3)
MANAGER_ID NUMBER(4)
HIRE_DATE DATE
SALARY NUMBER(7,2)
COMMISSION NUMBER(7,2)
DEPARTMENT_ID NUMBER(2)
可以看到上述結(jié)果不是job表的結(jié)構(gòu),而是第一次時(shí)的結(jié)構(gòu)
使用限制:
1 只有非系統(tǒng)表空間,而且是本地管理的表空間
2 表上相關(guān)對(duì)象被保留,除了以下內(nèi)容:
bitmap join indexes, referential integrity constraints (foreign key constraints), and
materialized view logs
3 索引只在刪除表時(shí)被留,顯式地刪除索引不被放入回收站.