oracle—回收站

（1）目前oracle（ Release 10.2.0.1.0）回收站不支援系統表空間
[1]實驗
SQL> SELECT tablespace_name FROM USER_TABLES WHERE table_name='T';
TABLESPACE_NAME
------------------------------
SYSTEM

SQL> select count(*) from t;
  COUNT(*)
----------
     10000
SQL> drop table t;
Table dropped.
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> rollback;
Rollback complete.
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist （可見oracle的drop是自動提交的）

SQL> SELECT original_name, object_name, TYPE, droptime FROM user_recyclebin;
no rows selected

可見我們刪除的t表沒有放入回收站！
（2） 閃回表特性，可以毫不費力地恢復被意外刪除的表
[1]實驗
一 建立使用者和授權
SQL> create user test identified by ss123456;
User created.
SQL> grant connect,create table ,resource  to test;
Grant succeeded.

C:\Documents and Settings\Administrator>sqlplus [email=test/ss123456@ot]test/ss123456@ot[/email]
SQL*Plus: Release 9.0.1.0.1 - Production on Thu May 14 15:00:26 2009
(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
二 建demo表
SQL> create table demo (id int);
Table created.
SQL> insert into demo values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT tablespace_name FROM USER_TABLES WHERE table_name='DEMO';
TABLESPACE_NAME
------------------------------
USERS

可見使用者的表建在了users表空間中；
三 刪除demo
SQL> drop table demo;
Table dropped.
SQL> commit;
Commit complete.
SQL> select * from demo;
select * from demo
              *
ERROR at line 1:
ORA-00942: table or view does not exist
四 利用閃回表特性，可以毫不費力地恢復被意外刪除的表
1 檢視回收站
SQL> SELECT original_name, object_name, TYPE, droptime FROM user_recyclebin;
ORIGINAL_NAME     OBJECT_NAME       TYPE                                   DROPTIME
-------------    ---------------------------          ------------                              -------------------
DEMO              BIN$cI/hTpOYT4GOLWfF+7O78Q==$0   TABLE        2009-05-14:15:09:40
2 恢復
SQL> FLASHBACK TABLE demo TO BEFORE DROP;
閃回完成。
SQL> select * from demo;
        ID
----------
         1