一、回收站概念

二、回收站功能

三、管理回收站

四、示例

1、先後刪除的表名相同，然後閃回表的問題

2、Flashback Drop只能用於非系統表空間和本地管理的表空間

3、理解重新命名的過程
4、刪除表與閃回刪除表對索引和約束的影響

5、當表空間不足時，無法閃回表刪除的問題

11g官方文件對RecycleBin的解釋說明

一、回收站概念

從ORACLE 10g開始，引入了回收站(Recycle Bin)的概念。它的全稱叫Tablespace Recycle Bin。回收站實際是一個邏輯容器（邏輯區域），原理有點類似於WINDOW系統的回收站。它以表空間中現有已經分配的空間為基礎，而不是從表空間上物理劃出一個固定區域用作回收站。這意味著回收站和表空間中的物件共用儲存區域、系統沒有給回收站預留空間。因此，當表被DROP後，如果可用空間充足，並且沒有對回收站進行清理，那麼被DROP掉的物件會一直存在回收站中，但是如果可用空間緊張的情況下，資料庫會根據先進先出的順序覆蓋Recycle Bin中的物件。所以回收站機制也不是百分百的保險機制。另外從原理上來說它就是一個資料字典表，放置使用者Drop掉的資料庫物件資訊。使用者進行Drop操作的物件並沒有真正被資料庫刪除，仍然會佔用空間。除非是由於使用者手工進行Purge或者因為儲存空間不夠而被資料庫清掉。資料庫有了這樣的功能，能夠減少很多不必要的麻煩。當使用者、開發人員、甚至DBA誤操作刪除了表，那麼我們不必還原整個資料庫或表空間，直接使用ORACLE 10g的閃回(FLASHBACK,閃回)功能來還原被刪除的表。這樣我們就能避免大量的人工誤操作。這是一個對DBA相當有用的功能。

Flashback Drop 是基於Tablespace RecycleBin 來實現恢復的。它只支援閃回與table 相關連的物件，比如表，索引，約束，觸發器等。如果是函式或者儲存過程等，就需要使用Flashback Query來實現。

二、回收站功能

回收站這個特性主要的好處就是在誤刪除一個表時有一個恢復機制，不必透過資料庫還原來實現。避免大量的人工誤操作。以及資料庫還原等複雜的操作。讓資料庫的管理、維護更加簡單、方便。如果是SQL SERVER資料庫，就必須還原整個資料庫來找到被DROP掉的表。可見回收站功能確實是一個開創性的功能。

三、管理回收站

1、開啟、關閉回收站

首先你可以透過命令檢視資料庫是否開啟了回收站機制，如下所示 VALUE= ON表示開啟了回收站機制。OFF則表示回收站機制關閉。

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

可以透過設定初始化引數recyclebin啟用或禁用回收站功能。當然也可以用命令關閉回收站

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

檢視回收站物件

先來看一個例子，如下所示，假如不小心誤操作DROP了表wjq1，然後在回收站檢視被DROP的表物件

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

檢視資料庫當前使用者的回收站物件

SQL> SELECT * FROM USER_RECYCLEBIN;

--檢視資料庫回收站所有物件

SQL> SELECT * FROM DBA_RECYCLEBIN;

四、示例

1、先後刪除的表名相同，然後閃回表的問題

為了避免被刪除的表與同類物件名稱的重複，被刪除的表以及相依的物件放到回收站後，ORACLE資料庫會對被刪除的物件名稱進行重新命名，例如表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

我們又建立了表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

還原回收站物件
還原回收站被刪除的表、索引等物件，是透過Flashback Drop實現的。如下所示。
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

如上所示，如果兩個相同名字的表wjq1被刪除了，此時閃回被DROP的表wjq1，實質是閃回最後一個被刪除的表（後進先出原則）,如果此時繼續閃回操作就會報ORA-38312錯誤

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

此時可以在閃回過程中對錶名進行重新命名解決問題。。
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

當再次檢視回收站中的內容時，發現已經沒有了。

另外，如果回收站有兩個被DROP掉的表wjq1, 如果想閃回第一個被刪除的表，那該怎麼辦呢？
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

其實這個也很好處理,直接指定RECYCLEBIN NAME進行閃回即可。
SEIANG@seiang11g>flashback table "BIN$VwJ/MWH9dIfgUy4BAQq2eQ==$0" to before drop;
Flashback complete.

SEIANG@seiang11g>select * from wjq1;

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

清空回收站

資料庫物件刪除後，資料庫會把它重新命名為BIN$開頭的物件，你可以透過ORIGINAL_NAME檢視它對應的原始物件名稱。記住，將表放在回收站裡並不在原始表空間中釋放空間。如果您希望完全刪除該表，而不讓該表放入回收站，可以使用以下命令永久刪除該表。當然這樣操作後，你也不能透過使用閃回特性閃回該表了。

DROP TABLE [TABLE_NAME] PURGE;

如果資料庫中刪除表時都放入回收站，因而沒有釋放所佔空間，那麼當空閒的空間不足時，已經刪除的表是否還會侵佔儲存空間呢?

答案很簡單：當表空間被回收站資料完全佔滿，以至於必須擴充套件資料檔案來容納更多資料時，可以說表空間處於“空間壓力”情況下。此時，物件以先進先出的方式從回收站中自動清除。在刪除表之前，相關物件(如索引)被刪除。

同樣，空間壓力可能由特定表空間定義的使用者限額而引起。表空間可能有足夠的空餘空間，但使用者可能將其在該表空間中所分配的部分用完了。在這種情況下，Oracle 自動清除該表空間中屬於該使用者的物件。

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

PURGE TABLE [TABLE_NAME];

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

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

此命令將從回收站中刪除表 TEST 及所有相關物件，如索引、約束等，從而節省了空間。但是，如果要從回收站中永久刪除索引，則可以使用以下命令來完成工作：

PURGE INDEX [INDEX_NAME];

此命令將僅僅刪除索引，而將表的複製留在回收站中。有時在更高階別上進行清除可能會有用。例如，您可能希望清除表空間 USERS 的回收站中的所有物件。可以執行：

PURGE TABLESPACE USERS;

您也許希望只為該表空間中特定使用者清空回收站。在資料倉儲型別的環境中，使用者建立和刪除許多臨時表，此時這種方法可能會有用。您可以更改上述命令，限定只清除特定的使用者：

PURGE TABLESPACE USERS USER SCOTT;

要釋放整個回收站佔用的空間，您需要使用以下命令清空回收站：

PURGE RECYCLEBIN;

記住PURGE RECYCLEBIN只是清除當前使用者回收站中的物件，DBA_RECYCLEBIN下的的物件並沒有刪除，如果你要清除當前資料庫回收站的物件，必須使用下面命令（DBA許可權）

PURGE DBA_RECYCLEBIN

2、Flashback Drop只能用於非系統表空間和本地管理的表空間

--建立的sys_table表屬於非系統表空間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

--建立的sys_table1表屬於系統表空間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

由上面的例子可以發現，在系統表空間中，表物件刪除後就真的從系統中刪除了，而不是存放在回收站中。

3、重新命名的過程

--建立基於emp表的tab_emp表
SCOTT@seiang11g>create table tab_emp as select * from emp;
Table created.

--新增主鍵約束，將自動產生主鍵索引
SCOTT@seiang11g>alter table tab_emp add constraint pk_empno primary key(empno);
Table altered.

--新增唯一鍵約束，將自動產生唯一索引
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表物件的id
SYS@seiang11g>select object_name,object_id from dba_objects where object_name='TAB_EMP';

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
TAB_EMP                             89445

--對tab_emp表進行重新命名為tab_employee
SCOTT@seiang11g>alter table tab_emp rename to tab_employee;
Table altered.

--檢視重新命名錶tab_employee所在檔案的id,塊的起始id,大小，發現沒有發生任何變化
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物件的id，發現沒有發生任何的變化
SYS@seiang11g>select object_name,object_id from dba_objects where object_name='TAB_EMPLOYEE';

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
TAB_EMPLOYEE                        89445

--檢視重新命名錶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

從上面的演示可以看出對於表的重新命名僅僅是修改了表名，而對於表物件的ID，以及表存放的位置，塊的起始，大小等並未發生實質性的變化。

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

--刪除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表無法進行查詢
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表後，可以透過回收站的名來進行檢視
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.

-- 閃回後表存在，並且可以進行訪問
SCOTT@seiang11g>select count(*) from tab_employee;
COUNT(*)
----------
14

--刪除後檢視約束和索引的名稱，仍然是回收站的名稱BIN$，同時發現外來鍵約束消失
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$開頭，由系統生成的名字，可以將其改回，但外來鍵約束已經不存在了

嘗試對錶進行DML操作

--插入資料，發現可以成功插入，deptno列的外來鍵約束已經被刪除，故deptno為70號成功插入
SCOTT@seiang11g>insert into tab_employee(empno,ename,job,sal,deptno) select 6666,'seiang','DBA',5000,70 from dual;
1 row created.

--將BIN$開頭的索引、約束改回原來的名字
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、當表空間不足時，無法閃回表刪除的問題

--建立一個tab_test表空間，大小為1M,並且不能自動擴充套件
SYS@seiang11g>create tablespace tab_test datafile '/u01/app/oracle/oradata/OraDB11g/tab_test01.dbf' size 1M;
Tablespace created.

--檢視tab_test表空間不是自動擴充套件
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表空間上建立一張test表，隸屬於scott使用者，建立的同時並插入資料
SYS@seiang11g>create table scott.test tablespace tab_test as select * from dba_objects where rownum<6000;
Table created.
-插入資料後檢視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,在需要表空間時,將自動清除回收站最老的物件,以滿足當前空間需求
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表物件位於回收站中
SCOTT@seiang11g>show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$VwJUfr+EbQDgUy4BAQqy8Q==$0 TABLE        2017-08-18:17:35:33

--然後，建立test2表，隸屬於tab_test表空間
SYS@seiang11g>create table test2 tablespace tab_test as select * from dba_objects where rownum<6000;
Table created.

--建立完成後，檢視回收站的資訊，此時回收站中原來的表test的記錄被自動清除
SCOTT@seiang11g>show recyclebin
SCOTT@seiang11g>select object_name,original_name,can_undrop,base_object from user_recyclebin;
no rows selected

--此時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

總結：

1、表的刪除被對映為將表的重新命名，然後將其置於回收站。

2、表的索引,觸發器,授權閃回後將不受到影響.索引,觸發器名字可以根據需要進行更改回原來名稱。

3、對於約束，如果是外來鍵約束，表刪除之後將不可恢復，其餘的約束不受影響。

4、當刪除表時，依賴於該表的物化檢視也會同時刪除，但是由於物化檢視並不會放入recycle bin中，因此當你執行flashback drop時，並不能恢復依賴其的物化檢視。需要DBA手工重建。

5、如果要查詢回收站中的物件，建議將物件名使用雙引號括起來。

6、對於回收站（Recycle Bin）中的物件，只支援查詢。不支援任何其他DML、DDL等操作。

7、閃回的實質並不能撤銷已提交的事務，而是構造倒退原有事務影響的另一個事務。

8、對於已經刪除的表如果在所在的表空間新增物件由於空間不足的壓力而被重用將導致閃回失敗。

9、對於表空間不足時，系統會自動清除回收站中最老的物件，以滿足當前需求，即採用FIFO原則。

10、閃回表的常用方法

flashback table tbname to before drop ;

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

第二條語句用於被刪除的表名已經被再次重用,故閃回之前必須將其改名為新表名,schema不變化

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

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

12、lashback drop 不能閃回truncate命令截斷的表，而是隻能恢復drop 之後的表

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

14、在system表空間中儲存的表無法啟用flashback drop，且這些表會被立即刪除

以下是11g官方文件對於recyclebin的說明：

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

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?

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.

回收站實際上是一個資料字典表，其中包含關於刪除物件的資訊。 刪除的表和其任何關聯的物件（如索引，約束，巢狀表等）都不會被刪除，仍然佔用空間。 他們繼續計算使用者空間配額，直到從回收站特別清除，或者由於表空間空間限制，資料庫必須清除它們的不太可能的情況。

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:

每個使用者可以被認為擁有自己的回收站，因為除非使用者具有SYSDBA許可權，否則使用者在回收站中訪問的唯一物件是使用者擁有的物件。

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:

當你刪除包含其內容的表空間時，表空間中的物件不會放置在回收站中，資料庫會清除回收站中位於表空間中的物件的任何條目。 當您刪除表空間（不包括內容）時，資料庫還會清除表空間中物件的任何回收站條目，否則表空間為空。同樣：

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.

· 當你刪除使用者時，屬於使用者的任何物件都不會放置在回收站中，並且清理回收站中的任何物件。

· 當你刪除叢集時，其成員表不會放置在回收站中，並且清除回收站中的任何以前的成員表。

· 當你刪除型別時，任何依賴物件（如子型別）都不會放置在回收站中，並且清除回收站中的任何以前的依賴物件。

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:

當刪除的表移動到回收站時，表及其關聯的物件將被給予系統生成的名稱。 這是必要的，以避免在多個表具有相同名稱時可能出現的名稱衝突。 這可能發生在以下情況下：

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.

· 使用者刪除表，重新建立一個表，然後再次刪除它。

· 兩個使用者具有相同名稱的表，兩個使用者都刪除它們的表。

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是此物件的26個字元的全域性唯一識別符號，這使得回收站名稱在所有資料庫中都是唯一的

· version是由資料庫分配的版本號

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).

當啟用回收站時，丟棄的表及其相關物件將被放置在回收站中。 當回收站被禁用時，丟棄的表及其相關物件不會放置在回收站中; 它們剛剛被刪除，您必須使用其他方法來恢復它們（例如從備份恢復）。

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

禁用回收站不會清除或影響已經在回收站中的物件。 預設情況下啟用回收站。

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.

透過更改recyclebin初始化引數啟用和禁用回收站。 此引數不是動態的，因此使用ALTER SYSTEM語句更改資料庫時，需要重新啟動資料庫。

To disable the recycle bin:

Issue one of the following statements:

2. ALTER SESSION SET recyclebin = OFF;

3.

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

If you used ALTER SYSTEM, restart the database.

To enable the recycle bin:

Issue one of the following statements:

2. ALTER SESSION SET recyclebin = ON;

3.

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

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:

View

Description

USER_RECYCLEBIN

This view can be used by users to see their own dropped objects in the recycle bin. It has a synonymRECYCLEBIN, for ease of use.

使用者可以使用此檢視在回收站中檢視自己刪除的物件。它具有同義詞RECYCLEBIN，方便使用。

DBA_RECYCLEBIN

This view gives administrators visibility to all dropped 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.

如果您決定不再從回收站中恢復專案，則可以使用PURGE語句從回收站中刪除專案及其關聯物件並釋放其儲存空間。 您需要有與刪除該專案相同的許可權。

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 .

當您使用PURGE語句清除表時，可以使用回收站中的表名稱或表的原始名稱。 可以從DBA_或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:

PURGE TABLE "BIN$jsleilx392mk2=293$0";

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

如果你具有SYSDBA許可權，則可以透過在上一個語句中指定DBA_RECYCLEBIN而不是RECYCLEBIN清除整個回收站。

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

你還可以使用PURGE語句從回收站中清除索引，或從回收站清除指定表空間中的所有物件。

Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either the DBA_or USER_RECYCLEBIN view as shown in . To use the FLASHBACK TABLE ... TO BEFORE DROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP

RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of theint2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin;

OBJECT_NAME ORIGINAL_NAME CREATETIME

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

BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:05:52

BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:21:25:13

BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP 2006-02-05:22:05:53

FLASHBACK TABLE "BIN$yrMKlZaVMhfgNAgAIMenRA==$0" TO BEFORE DROP;

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

從回收站還原表時，依賴物件（如索引）不會返回其原始名稱; 它們保留系統生成的回收站名稱。 您必須手動重新命名依賴物件來恢復其原始名稱。 如果計劃手動恢復依賴物件的原始名稱，請確保在還原表之前記下每個從屬物件的系統生成的回收站名稱。

The following is an example of restoring the original names of some of the indexes of the dropped table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

2. SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

3.

4. OBJECT_NAME ORIGINAL_NAME TYPE

5. ------------------------------ ------------------------- --------

6. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX

7. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX

8. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX

9. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX

10. BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE

Restore the table with the following command:

12. FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

14. SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

15.

16. INDEX_NAME

17. ------------------------------

18. BIN$DBo9UChwZSbgQFeMiAdCcQ==$0

19. BIN$DBo9UChtZSbgQFeMiAdCcQ==$0

20. BIN$DBo9UChuZSbgQFeMiAdCcQ==$0

21. BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

Restore the original names of the first two indexes as follows:

23. ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;

24. ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

Note that double quotes are required around the system-generated names.

作者：SEian.G（苦練七十二變，笑對八十一難）

Oracle回收站機制

相關文章