這篇文章主要介紹“Oracle回收站概念及功能”，在日常操作中，相信很多人在Oracle回收站概念及功能問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”O(jiān)racle回收站概念及功能”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

長(zhǎng)海ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！

目錄

一、回收站概念

二、回收站功能

三、管理回收站

四、示例

1、先后刪除的表名相同，然后閃回表的問(wèn)題

2、Flashback Drop只能用于非系統(tǒng)表空間和本地管理的表空間

3、理解重命名的過(guò)程
4、刪除表與閃回刪除表對(duì)索引和約束的影響

5、當(dāng)表空間不足時(shí)，無(wú)法閃回表刪除的問(wèn)題

11g官方文檔對(duì)RecycleBin的解釋說(shuō)明

一、回收站概念

從ORACLE 10g開始，引入了回收站(Recycle Bin)的概念。它的全稱叫Tablespace Recycle Bin?；厥照緦?shí)際是一個(gè)邏輯容器（邏輯區(qū)域），原理有點(diǎn)類似于WINDOW系統(tǒng)的回收站。它以表空間中現(xiàn)有已經(jīng)分配的空間為基礎(chǔ)，而不是從表空間上物理劃出一個(gè)固定區(qū)域用作回收站。這意味著回收站和表空間中的對(duì)象共用存儲(chǔ)區(qū)域、系統(tǒng)沒(méi)有給回收站預(yù)留空間。因此，當(dāng)表被DROP后，如果可用空間充足，并且沒(méi)有對(duì)回收站進(jìn)行清理，那么被DROP掉的對(duì)象會(huì)一直存在回收站中，但是如果可用空間緊張的情況下，數(shù)據(jù)庫(kù)會(huì)根據(jù)先進(jìn)先出的順序覆蓋Recycle Bin中的對(duì)象。所以回收站機(jī)制也不是百分百的保險(xiǎn)機(jī)制。另外從原理上來(lái)說(shuō)它就是一個(gè)數(shù)據(jù)字典表，放置用戶Drop掉的數(shù)據(jù)庫(kù)對(duì)象信息。用戶進(jìn)行Drop操作的對(duì)象并沒(méi)有真正被數(shù)據(jù)庫(kù)刪除，仍然會(huì)占用空間。除非是由于用戶手工進(jìn)行Purge或者因?yàn)榇鎯?chǔ)空間不夠而被數(shù)據(jù)庫(kù)清掉。數(shù)據(jù)庫(kù)有了這樣的功能，能夠減少很多不必要的麻煩。當(dāng)用戶、開發(fā)人員、甚至DBA誤操作刪除了表，那么我們不必還原整個(gè)數(shù)據(jù)庫(kù)或表空間，直接使用ORACLE 10g的閃回(FLASHBACK,閃回)功能來(lái)還原被刪除的表。這樣我們就能避免大量的人工誤操作。這是一個(gè)對(duì)DBA相當(dāng)有用的功能。

Flashback Drop 是基于Tablespace RecycleBin 來(lái)實(shí)現(xiàn)恢復(fù)的。 它只支持閃回與table 相關(guān)連的對(duì)象，比如表，索引，約束，觸發(fā)器等。 如果是函數(shù)或者存儲(chǔ)過(guò)程等，就需要使用Flashback Query來(lái)實(shí)現(xiàn)。

二、回收站功能

回收站這個(gè)特性主要的好處就是在誤刪除一個(gè)表時(shí)有一個(gè)恢復(fù)機(jī)制，不必通過(guò)數(shù)據(jù)庫(kù)還原來(lái)實(shí)現(xiàn)。避免大量的人工誤操作。以及數(shù)據(jù)庫(kù)還原等復(fù)雜的操作。讓數(shù)據(jù)庫(kù)的管理、維護(hù)更加簡(jiǎn)單、方便。如果是SQL SERVER數(shù)據(jù)庫(kù)，就必須還原整個(gè)數(shù)據(jù)庫(kù)來(lái)找到被DROP掉的表?？梢?jiàn)回收站功能確實(shí)是一個(gè)開創(chuàng)性的功能。

三、管理回收站

1、開啟、關(guān)閉回收站

首先你可以通過(guò)命令查看數(shù)據(jù)庫(kù)是否開啟了回收站機(jī)制， 如下所示 VALUE= ON表示開啟了回收站機(jī)制。OFF則表示回收站機(jī)制關(guān)閉。

SYS@seiang11g>show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      on

或者

SYS@seiang11g>select name,value from v$parameter where name='recyclebin';

NAME                           VALUE

------------------------------ --------------------

recyclebin                     on

可以通過(guò)設(shè)置初始化參數(shù)recyclebin啟用或禁用回收站功能。當(dāng)然也可以用命令關(guān)閉回收站

SYS@seiang11g>alter system set recyclebin=off scope=spfile;

System altered.

SYS@seiang11g>alter session set recyclebin=off;

Session altered.

SYS@seiang11g>show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      OFF

可以用命令開啟回收站

SYS@seiang11g>alter system set recyclebin=on scope=spfile;

System altered.

SYS@seiang11g>alter session set recyclebin=on;

Session altered.

SYS@seiang11g>show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      ON

查看回收站對(duì)象

先來(lái)看一個(gè)例子，如下所示，假如不小心誤操作DROP了表wjq1，然后在回收站查看被DROP的表對(duì)象

SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';

OWNER                          TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

SEIANG                         WJQ1                           SEIANG

SYS@seiang11g>drop table seiang.wjq1;

Table dropped.

SEIANG@seiang11g>show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

WJQ1             BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:14:35:07

其中RECYCLEBIN是USER_RECYCLEBIN 的同義詞。

SEIANG@seiang11g>select object_name,original_name,operation,type,droptime,ts_name from recyclebin;

OBJECT_NAME                    ORIGINAL_N OPERATION TYPE                      DROPTIME            TS_NAME

------------------------------ ---------- --------- ------------------------- ------------------- ---------------

BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 WJQ1       DROP      TABLE                     2017-08-18:14:35:07 SEIANG 

查看數(shù)據(jù)庫(kù)當(dāng)前用戶的回收站對(duì)象

SQL> SELECT * FROM USER_RECYCLEBIN;

--查看數(shù)據(jù)庫(kù)回收站所有對(duì)象

SQL> SELECT * FROM DBA_RECYCLEBIN;

四、示例

1、先后刪除的表名相同，然后閃回表的問(wèn)題

為了避免被刪除的表與同類對(duì)象名稱的重復(fù)，被刪除的表以及相依的對(duì)象放到回收站后，ORACLE數(shù)據(jù)庫(kù)會(huì)對(duì)被刪除的對(duì)象名稱進(jìn)行重命名，例如表wjq1表
SEIANG@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
WJQ1             BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:14:35:07

我們又創(chuàng)建了表wjq1，然后刪除了該表wjq1，如下所示，雖然ORIGINAL_NAME一致，但是RECYCLEBIN NAME則有所不同。
SEIANG@seiang11g>create table wjq1(name varchar2(20),address varchar2(20)) tablespace good;
Table created.
 
SEIANG@seiang11g>insert into wjq1 values('wjq','beijing');
1 row created.
 
SEIANG@seiang11g>insert into wjq1 values('seiang','lanzhou');
1 row created.
 
SEIANG@seiang11g>commit;
Commit complete.
 
SEIANG@seiang11g>drop table wjq1;
Table dropped.
 
SEIANG@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
WJQ1             BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0 TABLE        2017-08-18:14:47:04
WJQ1             BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:14:35:07
 
SEIANG@seiang11g>select * from "BIN$VwJ/MWH8dIfgUy4BAQq2eQ==$0";
 
NAME                 ADDRESS
-------------------- --------------------
wjq                  beijing
seiang               lanzhou 

SEIANG@seiang11g>select * from "BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0";
 
        ID NAME
---------- ------------------------------
         1 wjq1
         2 wjq2
         3 wjq3

還原回收站對(duì)象
還原回收站被刪除的表、索引等對(duì)象， 是通過(guò)Flashback Drop實(shí)現(xiàn)的。如下所示。
SEIANG@seiang11g>flashback table wjq1 to before drop;
Flashback complete. 
 
SEIANG@seiang11g>select * from wjq1;
 
NAME                 ADDRESS
-------------------- --------------------
wjq                  beijing
seiang               lanzhou
 
SEIANG@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
WJQ1             BIN$VwJUfr+CbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:14:35:07
 
如上所示，如果兩個(gè)相同名字的表wjq1被刪除了，此時(shí)閃回被DROP的表wjq1，實(shí)質(zhì)是閃回最后一個(gè)被刪除的表（后進(jìn)先出原則）,如果此時(shí)繼續(xù)閃回操作就會(huì)報(bào)ORA-38312錯(cuò)誤

SEIANG@seiang11g>flashback table wjq1 to before drop;
flashback table wjq1 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
 
此時(shí)可以在閃回過(guò)程中對(duì)表名進(jìn)行重命名解決問(wèn)題。。
SEIANG@seiang11g>flashback table wjq1 to before drop rename to wjq1_test;
Flashback complete. 
 
SEIANG@seiang11g>select * from wjq1_test;
 
        ID NAME
---------- ------------------------------
         1 wjq1
         2 wjq2
         3 wjq3

當(dāng)再次查看回收站中的內(nèi)容時(shí)，發(fā)現(xiàn)已經(jīng)沒(méi)有了。

另外，如果回收站有兩個(gè)被DROP掉的表wjq1, 如果想閃回第一個(gè)被刪除的表，那該怎么辦呢？
SEIANG@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
WJQ1             BIN$VwJ/MWH+dIfgUy4BAQq2eQ==$0 TABLE        2017-08-18:14:59:11
WJQ1             BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0 TABLE        2017-08-18:14:58:05
 
其實(shí)這個(gè)也很好處理,直接指定RECYCLEBIN NAME進(jìn)行閃回即可。
SEIANG@seiang11g>flashback table "BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0" to before drop;
Flashback complete.
 
SEIANG@seiang11g>select * from wjq1;

NAME                 ADDRESS
-------------------- --------------------
wjq                  beijing
seiang               lanzhou

清空回收站

數(shù)據(jù)庫(kù)對(duì)象刪除后，數(shù)據(jù)庫(kù)會(huì)把它重命名為BIN$開頭的對(duì)象，你可以通過(guò)ORIGINAL_NAME查看它對(duì)應(yīng)的原始對(duì)象名稱。記住，將表放在回收站里并不在原始表空間中釋放空間。如果您希望完全刪除該表，而不讓該表放入回收站，可以使用以下命令永久刪除該表。當(dāng)然這樣操作后，你也不能通過(guò)使用閃回特性閃回該表了。

DROP TABLE  [TABLE_NAME] PURGE;

如果數(shù)據(jù)庫(kù)中刪除表時(shí)都放入回收站，因而沒(méi)有釋放所占空間，那么當(dāng)空閑的空間不足時(shí)，已經(jīng)刪除的表是否還會(huì)侵占存儲(chǔ)空間呢?

答案很簡(jiǎn)單：當(dāng)表空間被回收站數(shù)據(jù)完全占滿，以至于必須擴(kuò)展數(shù)據(jù)文件來(lái)容納更多數(shù)據(jù)時(shí)，可以說(shuō)表空間處于“空間壓力”情況下。此時(shí)，對(duì)象以先進(jìn)先出的方式從回收站中自動(dòng)清除。在刪除表之前，相關(guān)對(duì)象(如索引)被刪除。

同樣，空間壓力可能由特定表空間定義的用戶限額而引起。表空間可能有足夠的空余空間，但用戶可能將其在該表空間中所分配的部分用完了。在這種情況下，Oracle 自動(dòng)清除該表空間中屬于該用戶的對(duì)象。

此外，有幾種方法可以手動(dòng)控制回收站。如果在刪除名為 TEST 的特定表之后需要從回收站中清除它，可以執(zhí)行

PURGE TABLE [TABLE_NAME];

或者使用其回收站中的名稱：

PURGE TABLE "BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0";

此命令將從回收站中刪除表 TEST 及所有相關(guān)對(duì)象，如索引、約束等，從而節(jié)省了空間。但是，如果要從回收站中永久刪除索引，則可以使用以下命令來(lái)完成工作：

PURGE INDEX [INDEX_NAME];

此命令將僅僅刪除索引，而將表的拷貝留在回收站中。有時(shí)在更高級(jí)別上進(jìn)行清除可能會(huì)有用。例如，您可能希望清除表空間 USERS 的回收站中的所有對(duì)象?？梢詧?zhí)行：

PURGE TABLESPACE USERS;

您也許希望只為該表空間中特定用戶清空回收站。在數(shù)據(jù)倉(cāng)庫(kù)類型的環(huán)境中，用戶創(chuàng)建和刪除許多臨時(shí)表，此時(shí)這種方法可能會(huì)有用。您可以更改上述命令，限定只清除特定的用戶：

PURGE TABLESPACE USERS USER SCOTT;

要釋放整個(gè)回收站占用的空間，您需要使用以下命令清空回收站：

PURGE RECYCLEBIN;

記住PURGE RECYCLEBIN只是清除當(dāng)前用戶回收站中的對(duì)象，DBA_RECYCLEBIN下的的對(duì)象并沒(méi)有刪除，如果你要清除當(dāng)前數(shù)據(jù)庫(kù)回收站的對(duì)象，必須使用下面命令（DBA權(quán)限）

PURGE DBA_RECYCLEBIN

2、Flashback Drop只能用于非系統(tǒng)表空間和本地管理的表空間

--創(chuàng)建的sys_table表屬于非系統(tǒng)表空間SEIANG
SYS@seiang11g>create table sys_table(id number,idcard number) tablespace seiang;
Table created.
SYS@seiang11g>insert into sys_table values(1,123456);
1 row created.
 
SYS@seiang11g>commit;
Commit complete.
 
SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where table_name='SYS_TABLE';
 
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            SYS_TABLE                     SEIANG
 
SYS@seiang11g>drop table sys_table;
Table dropped.
 
SYS@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
SYS_TABLE        BIN$VwJUfr+DbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:15:08:17
 
--創(chuàng)建的sys_table1表屬于系統(tǒng)表空間SYSTEM
SYS@seiang11g>create table sys_table1(id number,name varchar2(20));
Table created.
 
SYS@seiang11g>insert into sys_table1 values(1,'www');
1 row created.
 
SYS@seiang11g>commit;
Commit complete.
 
SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where table_name='SYS_TABLE1';
 
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            SYS_TABLE1                     SYSTEM
 
SYS@seiang11g>drop table sys_table1;
Table dropped.
 
SYS@seiang11g>show recyclebin
 
由上面的例子可以發(fā)現(xiàn)，在系統(tǒng)表空間中，表對(duì)象刪除后就真的從系統(tǒng)中刪除了，而不是存放在回收站中。

3、重命名的過(guò)程

--創(chuàng)建基于emp表的tab_emp表
SCOTT@seiang11g>create table tab_emp as select * from emp;
Table created.
 
--添加主鍵約束，將自動(dòng)產(chǎn)生主鍵索引
SCOTT@seiang11g>alter table tab_emp add constraint pk_empno primary key(empno);
Table altered.
 
--添加唯一鍵約束，將自動(dòng)產(chǎn)生唯一索引
SCOTT@seiang11g>alter table tab_emp add constraint uk_ename unique(ename);
Table altered.
 
--添加check約束
SCOTT@seiang11g>alter table tab_emp add constraint ck_sal check(sal>0);
Table altered.
 
--添加非空約束
SCOTT@seiang11g>alter table tab_emp modify job constraint nn_job not null;
Table altered.
 
--添加外鍵約束
SCOTT@seiang11g>alter table tab_emp add constraint fk_dept foreign key(deptno) references dept(deptno) on delete cascade;
Table altered.
 
--查看tab_emp表上的所有約束和索引
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
  2  where table_name='TAB_EMPLOYEE'
  3   union all
  4  select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
  5  where table_name='TAB_EMPLOYEE';
 
OWNER      CONSTRAINT_NAME      CONSTRAINT_TYPE      TABLE_NAME
---------- -------------------- -------------------- ------------------------------
SCOTT      PK_EMPNO             P                    TAB_EMPLOYEE
SCOTT      UK_ENAME             U                    TAB_EMPLOYEE
SCOTT      CK_SAL               C                    TAB_EMPLOYEE
SCOTT      NN_JOB               C                    TAB_EMPLOYEE
SCOTT      FK_DEPT              R                    TAB_EMPLOYEE
SCOTT      UK_ENAME             NORMAL               TAB_EMPLOYEE
SCOTT      PK_EMPNO             NORMAL               TAB_EMPLOYEE
 
 
--查看tab_emp表所在文件的id,塊的起始id,大小
SYS@seiang11g>select file_id,block_id,bytes from dba_extents where segment_name='TAB_EMP';
 
   FILE_ID   BLOCK_ID      BYTES
---------- ---------- ----------
         4        528      65536
 
--查看tab_emp表對(duì)象的id
SYS@seiang11g>select object_name,object_id from dba_objects where object_name='TAB_EMP';
 
OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
TAB_EMP                             89445
 
 
--對(duì)tab_emp表進(jìn)行重命名為tab_employee
SCOTT@seiang11g>alter table tab_emp rename to tab_employee;
Table altered.
 
--查看重命名表tab_employee所在文件的id,塊的起始id,大小，發(fā)現(xiàn)沒(méi)有發(fā)生任何變化
SYS@seiang11g>select file_id,block_id,bytes from dba_extents where segment_name='TAB_EMPLOYEE';
 
   FILE_ID   BLOCK_ID      BYTES
---------- ---------- ----------
         4        528      65536
 
--查看重命名表tab_employee對(duì)象的id，發(fā)現(xiàn)沒(méi)有發(fā)生任何的變化
SYS@seiang11g>select object_name,object_id from dba_objects where object_name='TAB_EMPLOYEE';
 
OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
TAB_EMPLOYEE                        89445
 
--查看重命名表tab_emp上的所有約束和索引，發(fā)現(xiàn)沒(méi)有發(fā)生任何變化
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
  2  where table_name='TAB_EMPLOYEE'
  3   union all
  4  select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
  5  where table_name='TAB_EMPLOYEE';
 
OWNER      CONSTRAINT_NAME      CONSTRAINT_TYPE      TABLE_NAME
---------- -------------------- -------------------- ------------------------------
SCOTT      PK_EMPNO             P                    TAB_EMPLOYEE
SCOTT      UK_ENAME             U                    TAB_EMPLOYEE
SCOTT      CK_SAL               C                    TAB_EMPLOYEE
SCOTT      NN_JOB               C                    TAB_EMPLOYEE
SCOTT      FK_DEPT              R                    TAB_EMPLOYEE
SCOTT      UK_ENAME             NORMAL               TAB_EMPLOYEE
SCOTT      PK_EMPNO             NORMAL               TAB_EMPLOYEE
 
從上面的演示可以看出對(duì)于表的重命名僅僅是修改了表名，而對(duì)于表對(duì)象的ID，以及表存放的位置，塊的起始，大小等并未發(fā)生實(shí)質(zhì)性的變化。

4、刪除表與閃回刪除表對(duì)索引和約束的影響

 
--刪除tab_employee表，并查看回收站
SCOTT@seiang11g>drop table tab_employee;
Table dropped.
 
SCOTT@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP T
-----------------------------------------------------------------------------------------------------------------------------
TAB_EMPLOYEE     BIN$VwRUrQdoe8vgUy4BAQoEBw==$0 TABLE        2017-08-18:16:58:20

SCOTT@seiang11g>select object_name,original_name,can_undrop,base_object from user_recyclebin;
 
OBJECT_NAME                    ORIGINAL_NAME                    CAN BASE_OBJECT
------------------------------ -------------------------------- --- -----------
BIN$VwRUrQdme8vgUy4BAQoEBw==$0 PK_EMPNO                         NO        89445
BIN$VwRUrQdne8vgUy4BAQoEBw==$0 UK_ENAME                         NO        89445
BIN$VwRUrQdoe8vgUy4BAQoEBw==$0 TAB_EMPLOYEE                     YES       89445
 
-- tab_employee表無(wú)法進(jìn)行查詢
SCOTT@seiang11g>select count(*) from tab_employee;
select count(*) from tab_employee
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
--刪除tab_employee表后，可以通過(guò)回收站的名來(lái)進(jìn)行查看
SCOTT@seiang11g>select count(*) from "BIN$VwRUrQdoe8vgUy4BAQoEBw==$0";
 
  COUNT(*)
----------
        14
 
--查看tab_employee表上的所有約束和索引
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
  2  where table_name='TAB_EMPLOYEE';
no rows selected
 
SCOTT@seiang11g>
SCOTT@seiang11g>select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
  2  where table_name='TAB_EMPLOYEE';
no rows selected
 
--從回收站中閃回刪除的tab_employee表
SCOTT@seiang11g>flashback table tab_employee to before drop;
Flashback complete.
 
--閃回后表存在，并且可以進(jìn)行訪問(wèn)
SCOTT@seiang11g>select count(*) from tab_employee;
 COUNT(*)
----------
        14

--刪除后查看約束和索引的名稱，仍然是回收站的名稱BIN$，同時(shí)發(fā)現(xiàn)外鍵約束消失
SCOTT@seiang11g>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints
  2  where table_name='TAB_EMPLOYEE'
 3  union all
  4  select TABLE_OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes
 5  where table_name='TAB_EMPLOYEE';

OWNER      CONSTRAINT_NAME                                    CONSTRAINT_TYPE      TABLE_NAME
---------- -------------------------------------------------- -------------------- ------------------------------
SCOTT      BIN$VwRUrQdie8vgUy4BAQoEBw==$0                     P                    TAB_EMPLOYEE
SCOTT      BIN$VwRUrQdje8vgUy4BAQoEBw==$0                     U                    TAB_EMPLOYEE
SCOTT      BIN$VwRUrQdke8vgUy4BAQoEBw==$0                     C                    TAB_EMPLOYEE
SCOTT      BIN$VwRUrQdle8vgUy4BAQoEBw==$0                     C                    TAB_EMPLOYEE
SCOTT      BIN$VwRUrQdne8vgUy4BAQoEBw==$0                     NORMAL               TAB_EMPLOYEE
SCOTT      BIN$VwRUrQdme8vgUy4BAQoEBw==$0                     NORMAL               TAB_EMPLOYEE
 
從上面的查詢可以看出閃回之后索引、約束的名字還是使用了以BIN$開頭，由系統(tǒng)生成的名字，可以將其改回，但外鍵約束已經(jīng)不存在了

嘗試對(duì)表進(jìn)行DML操作
 
--插入數(shù)據(jù)，發(fā)現(xiàn)可以成功插入，deptno列的外鍵約束已經(jīng)被刪除，故deptno為70號(hào)成功插入
SCOTT@seiang11g>insert into tab_employee(empno,ename,job,sal,deptno) select 6666,'seiang','DBA',5000,70 from dual;
1 row created.
 
--將BIN$開頭的索引、約束改回原來(lái)的名字
SCOTT@seiang11g>alter index "BIN$VwRUrQdme8vgUy4BAQoEBw==$0" rename to pk_empno;
Index altered.
 
SCOTT@seiang11g>alter index "BIN$VwRUrQdne8vgUy4BAQoEBw==$0" rename to UK_ENAME;
Index altered.
 
SCOTT@seiang11g>alter table tab_employee rename constraint "BIN$VwRUrQdle8vgUy4BAQoEBw==$0" to NN_JOB;
Table altered.
 
SCOTT@seiang11g>alter table tab_employee rename constraint "BIN$VwRUrQdke8vgUy4BAQoEBw==$0" to ck_sal;
Table altered.
 
SCOTT@seiang11g>alter table tab_employee rename constraint "BIN$VwRUrQdje8vgUy4BAQoEBw==$0" to uk_ename;
Table altered.
 
SCOTT@seiang11g>alter table tab_employee rename constraint "BIN$VwRUrQdie8vgUy4BAQoEBw==$0" to pk_empno;
Table altered.

5、當(dāng)表空間不足時(shí)，無(wú)法閃回表刪除的問(wèn)題

--創(chuàng)建一個(gè)tab_test表空間，大小為1M,并且不能自動(dòng)擴(kuò)展
SYS@seiang11g>create tablespace tab_test datafile '/u01/app/oracle/oradata/OraDB11g/tab_test01.dbf' size 1M;
Tablespace created.
 
 
--查看tab_test表空間不是自動(dòng)擴(kuò)展
SYS@seiang11g>select t.tablespace_name,d.file_name,d.autoextensible
  2  from dba_tablespaces t,dba_data_files d
  3  where t.tablespace_name=d.tablespace_name;
 
TABLESPACE_NAME                FILE_NAME                                          AUT
------------------------------ -------------------------------------------------- ---
USERS                          /u01/app/oracle/oradata/OraDB11g/users01.dbf       YES
UNDOTBS1                       /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf     YES
SYSAUX                         /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf      YES
SYSTEM                         /u01/app/oracle/oradata/OraDB11g/system01.dbf      YES
EXAMPLE                        /u01/app/oracle/oradata/OraDB11g/example01.dbf     YES
SEIANG                         /u01/app/oracle/oradata/OraDB11g/seiang01.dbf      NO
GOOD                           /u01/app/oracle/oradata/OraDB11g/good01.dbf        NO
TAB_TEST                       /u01/app/oracle/oradata/OraDB11g/tab_test01.dbf    NO
 
 
--查看tab_test表空間的空閑空間
SYS@seiang11g>select tablespace_name,sum(bytes/1024/1024) ||' M'
  2  from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name;
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)||'M'
------------------------------ ------------------------------------------
TAB_TEST                       .9375 M
 
 
--在tab_test表空間上創(chuàng)建一張test表，隸屬于scott用戶，創(chuàng)建的同時(shí)并插入數(shù)據(jù)
SYS@seiang11g>create table scott.test tablespace tab_test as select * from dba_objects where rownum<6000;
Table created.
 -插入數(shù)據(jù)后查看tab_test表空間的空閑空間
SYS@seiang11g>select tablespace_name,sum(bytes/1024/1024) ||' M'
  2  from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name;
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)||'M'
------------------------------ ------------------------------------------
TAB_TEST                       .25 M
 
 
--刪除test表，但是不purge
SYS@seiang11g>drop table scott.test;
Table dropped.
 
 
--刪除test表之后，查看tab_test表空間的空閑大小，為1M，但并不是真正的1M,在需要表空間時(shí),將自動(dòng)清除回收站最老的對(duì)象,以滿足當(dāng)前空間需求
SYS@seiang11g>select tablespace_name,sum(bytes/1024/1024) ||' M'
  2  from dba_free_space where tablespace_name='TAB_TEST' group by tablespace_name;
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)||'M'
------------------------------ ------------------------------------------
TAB_TEST                       .9375 M
 
 
--查看回收站的信息，刪除的test表對(duì)象位于回收站中
SCOTT@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$VwJUfr+EbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:17:35:33
 
--然后，創(chuàng)建test2表，隸屬于tab_test表空間
SYS@seiang11g>create table test2 tablespace tab_test as select * from dba_objects where rownum<6000;
Table created.
 
 
--創(chuàng)建完成后，查看回收站的信息，此時(shí)回收站中原來(lái)的表test的記錄被自動(dòng)清除
SCOTT@seiang11g>show recyclebin 
SCOTT@seiang11g>select object_name,original_name,can_undrop,base_object from user_recyclebin;
no rows selected
 
 
--此時(shí)test表不能夠被閃回
SCOTT@seiang11g>flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

總結(jié)：

1、表的刪除被映射為將表的重命名，然后將其置于回收站。

2、表的索引,觸發(fā)器,授權(quán)閃回后將不受到影響.索引,觸發(fā)器名字可以根據(jù)需要進(jìn)行更改回原來(lái)名稱。

3、對(duì)于約束，如果是外鍵約束，表刪除之后將不可恢復(fù)，其余的約束不受影響。

4、當(dāng)刪除表時(shí)，依賴于該表的物化視圖也會(huì)同時(shí)刪除，但是由于物化視圖并不會(huì)放入recycle bin中，因此當(dāng)你執(zhí)行flashback drop時(shí)，并不能恢復(fù)依賴其的物化視圖。需要DBA手工重建。

5、如果要查詢回收站中的對(duì)象，建議將對(duì)象名使用雙引號(hào)括起來(lái)。

6、對(duì)于回收站（Recycle Bin）中的對(duì)象，只支持查詢。不支持任何其他DML、DDL等操作。

7、閃回的實(shí)質(zhì)并不能撤銷已提交的事務(wù)，而是構(gòu)造倒退原有事務(wù)影響的另一個(gè)事務(wù)。

8、對(duì)于已經(jīng)刪除的表如果在所在的表空間新增對(duì)象由于空間不足的壓力而被重用將導(dǎo)致閃回失敗。

9、對(duì)于表空間不足時(shí)，系統(tǒng)會(huì)自動(dòng)清除回收站中最老的對(duì)象，以滿足當(dāng)前需求，即采用FIFO原則。

10、閃回表的常用方法

        flashback table tbname to before drop ;

        flashback table [tbname] to before drop rename to [newtbname];

        第二條語(yǔ)句用于被刪除的表名已經(jīng)被再次重用,故閃回之前必須將其改名為新表名,schema不變化

11、如回收站中存在兩個(gè)相同的原表名，則閃回時(shí)總是閃回最近的版本，如果閃回特定的表，需要指定該表在回收站中的名稱。如

        flashback table "BIN$k1zC3yEiwZvgQAB/AQBRVw==$0" to before drop;

12、lashback drop 不能閃回truncate命令截?cái)嗟谋?，而是只能恢?fù)drop 之后的表

13、flashback drop 不能閃回drop user scott cascade刪除方案的操作,此只能用flashback database

14、在system表空間中存儲(chǔ)的表無(wú)法啟用flashback drop，且這些表會(huì)被立即刪除

以下是11g官方文檔對(duì)于recyclebin的說(shuō)明：

********************************************************************************

Using Flashback Drop and Managing the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

This section contains the following topics:

• What Is the Recycle Bin?

• Viewing and Querying Objects in the     Recycle Bin

• Purging Objects in the Recycle Bin

• Restoring Tables from the Recycle     Bin

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space.They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

回收站實(shí)際上是一個(gè)數(shù)據(jù)字典表，其中包含關(guān)于刪除對(duì)象的信息。刪除的表和其任何關(guān)聯(lián)的對(duì)象（如索引，約束，嵌套表等）都不會(huì)被刪除，仍然占用空間。他們繼續(xù)計(jì)算用戶空間配額，直到從回收站特別清除，或者由于表空間空間限制，數(shù)據(jù)庫(kù)必須清除它們的不太可能的情況。

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

每個(gè)用戶可以被認(rèn)為擁有自己的回收站，因?yàn)槌怯脩艟哂蠸YSDBA權(quán)限，否則用戶在回收站中訪問(wèn)的唯一對(duì)象是用戶擁有的對(duì)象。

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

當(dāng)你刪除包含其內(nèi)容的表空間時(shí)，表空間中的對(duì)象不會(huì)放置在回收站中，數(shù)據(jù)庫(kù)會(huì)清除回收站中位于表空間中的對(duì)象的任何條目。當(dāng)您刪除表空間（不包括內(nèi)容）時(shí)，數(shù)據(jù)庫(kù)還會(huì)清除表空間中對(duì)象的任何回收站條目，否則表空間為空。同樣：

• When you drop a user, any     objects belonging to the user are not placed in the recycle bin and any     objects in the recycle bin are purged.

• When you drop a cluster,     its member tables are not placed in the recycle bin and any former member     tables in the recycle bin are purged.

• When you drop a type, any     dependent objects such as subtypes are not placed in the recycle bin and     any former dependent objects in the recycle bin are purged.

·         當(dāng)你刪除用戶時(shí)，屬于用戶的任何對(duì)象都不會(huì)放置在回收站中，并且清理回收站中的任何對(duì)象。

·         當(dāng)你刪除集群時(shí)，其成員表不會(huì)放置在回收站中，并且清除回收站中的任何以前的成員表。

·         當(dāng)你刪除類型時(shí)，任何依賴對(duì)象（如子類型）都不會(huì)放置在回收站中，并且清除回收站中的任何以前的依賴對(duì)象。

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

當(dāng)刪除的表移動(dòng)到回收站時(shí)，表及其關(guān)聯(lián)的對(duì)象將被給予系統(tǒng)生成的名稱。這是必要的，以避免在多個(gè)表具有相同名稱時(shí)可能出現(xiàn)的名稱沖突。這可能發(fā)生在以下情況下：

• A user drops a table,     re-creates it with the same name, then drops it again.

• Two users have tables     with the same name, and both users drop their tables.

·         用戶刪除表，重新創(chuàng)建一個(gè)表，然后再次刪除它。

·         兩個(gè)用戶具有相同名稱的表，兩個(gè)用戶都刪除它們的表。

The renaming convention is as follows:

BIN$unique_id$version

where:

• unique_id is a 26-character globally unique     identifier for this object, which makes the recycle bin name unique across     all databases

• version is a version number assigned by the     database

·         unique_id是此對(duì)象的26個(gè)字符的全局唯一標(biāo)識(shí)符，這使得回收站名稱在所有數(shù)據(jù)庫(kù)中都是唯一的

·         version是由數(shù)據(jù)庫(kù)分配的版本號(hào)

Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

當(dāng)啟用回收站時(shí)，丟棄的表及其相關(guān)對(duì)象將被放置在回收站中。當(dāng)回收站被禁用時(shí)，丟棄的表及其相關(guān)對(duì)象不會(huì)放置在回收站中;它們剛剛被刪除，您必須使用其他方法來(lái)恢復(fù)它們（例如從備份恢復(fù)）。

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

禁用回收站不會(huì)清除或影響已經(jīng)在回收站中的對(duì)象。默認(rèn)情況下啟用回收站。

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

通過(guò)更改recyclebin初始化參數(shù)啟用和禁用回收站。此參數(shù)不是動(dòng)態(tài)的，因此使用ALTER SYSTEM語(yǔ)句更改數(shù)據(jù)庫(kù)時(shí)，需要重新啟動(dòng)數(shù)據(jù)庫(kù)。

To disable the recycle bin:

1. Issue     one of the following statements:

2.  ALTER SESSION SET recyclebin = OFF;

3.   

4.  ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

1. If     you used ALTER SYSTEM, restart the database.

To enable the recycle bin:

1. Issue     one of the following statements:

2.  ALTER SESSION SET recyclebin = ON;

3.   

4.  ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

1. If     you used ALTER SYSTEM, restart the database.

Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin

   WHERE owner = 'HR';

OBJECT_NAME                    ORIGINAL_NAME

------------------------------ --------------------------------

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMPLOYEES        BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE        2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

如果您決定不再?gòu)幕厥照局谢謴?fù)項(xiàng)目，則可以使用PURGE語(yǔ)句從回收站中刪除項(xiàng)目及其關(guān)聯(lián)對(duì)象并釋放其存儲(chǔ)空間。您需要有與刪除該項(xiàng)目相同的權(quán)限。

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin".

當(dāng)您使用PURGE語(yǔ)句清除表時(shí)，可以使用回收站中的表名稱或表的原始名稱?？梢詮腄BA_或USER_RECYCLEBIN視圖獲取回收站名稱；

The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was placed in the recycle bin: