ORACLE 10g 中恢復已刪除的表_flashback
2024-08-29 13:53:48
供稿：網友
-- 筆記中部分內容
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
如果在表刪除后,又新建了這個表,恢復時可以重命名,如下:
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
如果這個表刪除了不止一次,我想恢復某一次的刪除,怎么辦?
查詢recyclebin視圖,或者show recyclebin,然后帶名字恢復.
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)
可以看到上述結果不是job表的結構,而是第一次時的結構
使用限制:
1 只有非系統表空間,而且是本地管理的表空間
2 表上相關對象被保留,除了以下內容:
bitmap join indexes, referential integrity constraints (foreign key constraints), and
materialized view logs
3 索引只在刪除表時被留,顯式地刪除索引不被放入回收站.
