oracle 回收站

回收站(RecycleBin）是一个数据字典表，放置用户删除（drop）掉的数据库对象信息。用户进行删除操作的对象并没有被数据库删除，仍然会占用空间。如果一个表被删除，那么与该表有关联的对象，例如表、索引、约束和其他依赖对象都会在前面加bin$$这个前缀。

hr@HX> create table t as select * from dba_objects;

Table created.

hr@HX> create index idx_t_id on t(object_id);

Index created.

hr@HX> drop table t;

Table dropped.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_NAME                   |OPERATION|TYPE                     |     SPACE

------------------------------|--------------------------------|---------|-------------------------|----------

BIN$7hpJclDOBi3gQwEAAH+23A==$0|T                               |DROP     |TABLE                    |       256

BIN$7hpJclDNBi3gQwEAAH+23A==$0|IDX_T_ID                        |DROP     |INDEX                    |        40

2 rows selected.

1.回收站功能启动和关闭

回收站功能受参数recyclebin影响，默认是开启的

hr@HX> show parameter recyclebin;

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

recyclebin                          |string     |on

可以在会话或者系统级别开启或者关闭回收站功能：

ALTER SYSTEM SET recyclebin = ON;      

ALTER SESSION SET recyclebin = ON;      

ALTER SYSTEM SET recyclebin = OFF;      

ALTER SESSION SET recyclebin = OFF;   

获取回收站里的内容

SELECT * FROM RECYCLEBIN;      

SELECT * FROM USER_RECYCLEBIN;      

SELECT * FROM DBA_RECYCLEBIN; 

2.从回收站还原表：  

删除表后，可以利用回收站还原：

FLASHBACK TABLE <> TO BEFORE DROP RENAME TO <>;  

这里的RENAME是给删除的对象进行重新命名，可选:

hr@HX> flashback table t to before drop rename to t1;  

Flashback complete.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

no rows selected

恢复后索引仍然可以使用：

hr@HX> select index_name,table_name,status from user_indexes  where table_name='T1';

INDEX_NAME               |TABLE_NAME     |STATUS

-------------------------|---------------|--------

BIN$7hpJclDNBi3gQwEAAH+23|T1             |VALID

A==$0                    |               |

1 row selected.

hr@HX> select count(object_id) from t1;

COUNT(OBJECT_ID)

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

15596

1 row selected.

Execution Plan

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

Plan hash value: 572134920

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

| Id  | Operation             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |                                |     1 |    13 |    12   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |                                |     1 |    13 |            |          |

|   2 |   INDEX FAST FULL SCAN| BIN$7hpJclDNBi3gQwEAAH+23A==$0 | 18409 |   233K|    12   (0)| 00:00:01 |

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

3. 清空回收站

清空回收站包含两种情况，有条件清空和全部清空

(1)清空特定的表：

PURGE TABLE <>; 

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_NAME                   |OPERATION|TYPE           |  SPACE

------------------------------|--------------------------------|---------|---------------|----------

BIN$7hpJclDQBi3gQwEAAH+23A==$0|T1                              |DROP     |TABLE          |    256

BIN$7hpJclDPBi3gQwEAAH+23A==$1|BIN$7hpJclDNBi3gQwEAAH+23A==$0  |DROP     |INDEX          |     40

2 rows selected.

hr@HX> purge table t1;

Table purged.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

no rows selected

和表相关的对象会被一并清除

(2)清空一个特定的索引：

PURGE INDEX <>; 

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_N|OPERATION|TYPE      |     SPACE

------------------------------|----------|---------|----------|----------

BIN$7hpJclDSBi3gQwEAAH+23A==$0|T         |DROP     |TABLE     |       256

BIN$7hpJclDRBi3gQwEAAH+23A==$0|IDX_T_ID  |DROP     |INDEX     |        40

2 rows selected.

hr@HX> purge index idx_t_id;

Index purged.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_N|OPERATION|TYPE      |     SPACE

------------------------------|----------|---------|----------|----------

BIN$7hpJclDSBi3gQwEAAH+23A==$0|T         |DROP     |TABLE     |       256

1 row selected.

(3)清空与该表空间有关联的对象：

PURGE TABLESPACE <>; 

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE,ts_name from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_N|OPERATION|TYPE      |     SPACE|TS_NAME

------------------------------|----------|---------|----------|----------|------------------------------

BIN$7hpJclDSBi3gQwEAAH+23A==$0|T         |DROP     |TABLE     |       256|HR

1 row selected.

hr@HX> purge tablespace hr;

Tablespace purged.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE,ts_name from RECYCLEBIN;

no rows selected

(4)清空一个表空间中特定用户的信息：

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE,ts_name from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_N|OPERATION|TYPE      |     SPACE|TS_NAME

------------------------------|----------|---------|----------|----------|---------------

BIN$7hpJclDWBi3gQwEAAH+23A==$0|T         |DROP     |TABLE     |       256|HR

BIN$7hpJclDVBi3gQwEAAH+23A==$0|IDX_T_ID  |DROP     |INDEX     |       256|HR_20M

2 rows selected.

hr@HX> purge tablespace hr_20m user hr;

Tablespace purged.

hr@HX> select object_name,ORIGINAL_NAME,OPERATION,TYPE,SPACE,ts_name from RECYCLEBIN;

OBJECT_NAME                   |ORIGINAL_N|OPERATION|TYPE      |     SPACE|TS_NAME

------------------------------|----------|---------|----------|----------|----------------

BIN$7hpJclDWBi3gQwEAAH+23A==$0|T         |DROP     |TABLE     |       256|HR

1 row selected.

(5)清空回收站（比较常用）：

PURGE RECYCLEBIN; 

(6)删除表时直接清除回收站信息：

DROP TABLE <> PURGE;