Oracle10g里面有个Recyclebin，删除时没加purge参数并非真正删除，而是先通过修改数据字典的方式，并将其改名放入Recycle Bin中。
从Recycle Bin 中恢复：
1.创建测试数据
SQL> conn scott/oracle
Connected.
SQL> create table temp(id int primary key);
Table created.
SQL> insert into temp values(1);
1 row created.
SQL> insert into temp values(2);
1 row created.
SQL> insert into temp values(3);
1 row created.
SQL> drop table temp;
Table dropped.
2.查看Recycle Bin
SQL> desc recyclebin;
Name Null Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
SQL> select ORIGINAL_NAME,OBJECT_NAME from recyclebin;
ORIGINAL_NAME OBJECT_NAME
-------------------------------- ------------------------------
TEMP BIN$9fpGUw34tFvgQAB/AQAb+w==$0 //TEMP表
SYS_C006287 BIN$9fpGUw33tFvgQAB/AQAb+w==$0 //主键
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
FLASH_TBL TABLE
TEST TABLE
BIN$9fpGUw34tFvgQAB/AQAb+w==$0 TABLE
3.恢复表
SQL> flashback table temp to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
TEMP TABLE
FLASH_TBL TABLE
TEST TABLE
SQL> select * from temp;
ID
----------
1
2
3
SQL> desc user_indexes;
Name Null Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
SQL> select table_name,index_name from user_indexes where table_name='TEMP';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TEMP BIN$9fpGUw33tFvgQAB/AQAb+w==$0
4.手动修改索引
SQL> alter index "BIN$9fpGUw33tFvgQAB/AQAb+w==$0" rename to SYS_C006287;
Index altered.
复杂点的表恢复：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------