RecycleBin - 数据库编程

SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> !cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
回收站就是一个虚拟的容器，用于存储删除的对象。在回收站中，被删除的对象将占有创建时同样的空间
我们可以通过参数 recyclebin来关闭开启回收站功能：
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5160.htm#REFRN29036

下面不重点介绍Flashback。
SQL> show parameter recyclebin

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on

SQL> alter system set recyclebin=off scope=spfile;

系统已更改。

SQL> show parameter recyclebin

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF

创建表diy_Z??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vc6O6PGJyPgoKU1FMJmd0OyBjcmVhdGUgdGFibGUgZGl5X29zKGlkIGludCk7PGJyPgqx7dLRtLS9qKGjPGJyPgpTUUwmZ3Q7IHNob3cgdXNlcjs8YnI+ClVTRVIgzqogJnF1b3Q7SFImcXVvdDs8YnI+ClNRTCZndDsgZHJvcCB0YWJsZSBkaXlfb3M7PGJyPgqx7dLRyb6z/aGjPGJyPgqy6b+00tG+rcm+s/21xLHtZGl5X29zOjxicj4KU1FMJmd0OyBzZWxlY3QgKiBmcm9tIHRhYiB3aGVyZSByb3dudW09MTs8YnI+Cjxicj4KPGJyPgpUTkFNRSAgICAgICAgICAgICAgICAgICAgICAgICAgVEFCVFlQRSAgQ0xVU1RFUklEPGJyPgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLSAtLS0tLS0tLS0tPGJyPgpCSU4kR0N2U0x4RkQ1bG5nVUFCL0FRQTV2Zz09JDAgVEFCTEU8YnI+Cjxicj4KU1FMJmd0OyBzaG93IHJlY3ljbGViaW48YnI+Ck9SSUdJTkFMIE5BTUUgICAgUkVDWUNMRUJJTiBOQU1FICAgICAgICAgICAgICAgIE9CSkVDVCBUWVBFICBEUk9QIFRJTUU8YnI+Ci0tLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tPGJyPgpESVlfT1MgICAgICAgICAgIEJJTiRHQ3ZTTHhGRDVsbmdVQUIvQVFBNXZnPT0kMCBUQUJMRSAgICAgICAgMjAxNS0wNi0xMDoyMzo0OTozMjxicj4KPGJyPgoKPGJyPgpTUUwmZ3Q7ICBzZWxlY3QgKiBmcm9tIGRiYV9yZWN5Y2xlYmluIHdoZXJlIG9yaWdpbmFsX25hbWU9"DIY_OS';

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- --- --- ---------- ----------- ------------ ----------
HR BIN$GCvSLxFD5lngUAB/AQA5vg==$0 DIY_OS DROP TABLE USERS 2015-06-10:23:49:16 2015-06-10:23:49:32 9610985 YES YES 78194 78194 78194 0

注意，如果删除的是sys用户的对象，则回收站里无记录，oracle不建议在sys用户里创建对象：
SQL> show user;
USER 为 "SYS"
SQL> create table kernel(id int);
表已创建。
SQL> drop table kernel; 表已删除。

SQL> select * from dba_recyclebin where original_name='KERNEL';
未选定行

使用purge recyclebin可以清除回收站里的所有对象，可以purge user_recyclebin或者purge dba_recyclebin，通过purge tablespace tablespace_name,purge table user.table_name来选择删除
回收站里的对象
还需注意的是，当我们使用drop tablespace.....including contents命令来删除表空间，表空间中的所有对象会被删除，包括回收站里的内容。当使用drop user .....cascade命令来删除用户时，该用户
下的所有对象会被删除，包括回收站里的内容

SQL> purge table hr.diy_os;

表已清除。

SQL> select * from dba_recyclebin where original_name='DIY_OS';

未选

RecycleBin(一)