oracle10g以后版本的回收站功能_Recycle Bin.doc

Oracle 10g以后的版本中引入了回收站(Recycle Bin)的概念,删除的对象统一放入回收站,以BIN$最为前缀命名.用户删除某个表,可以很容易的从回收站中还原回来,但在9i以前是对于删除表这样的DDL操纵是不能直接还原回来的,通常需要做不完全恢复或是使用EXP/IMP来恢复. 1.查看当前回收站功能是否开启(默认情况下是开启的) SQL column value format a10;SQL select value from V$parameter where name = recyclebin; VALUE-on 可以使用如下命令开启或关闭该功能. sql alter system set recyclebin = on scope=spfile;orsql alter session set recyclebin = on scope=spfile; sql alter system set recyclebin = off scope=spfile;orsql alter session set recyclebin = off scope=spfile; 2.查看回收站里的内容 SQL show recyclebin; orSQL select * from user_recyclebin; 下面将创建的表删除后放入回收站 SQL show user;USER is HXLSQL create table test_rbin(val number); Table created. SQL insert into test_rbin(val) values(10); 1 row created. SQL commit; Commit complete. SQL drop table test_rbin; Table dropped. SQL show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME- - - - TEST_RBIN BIN$S4u3UNw0QQ6t6LRxMMz7hQ=$0 TABLE 2012-06-22:15:01:30 3.从回收站恢复删除的表 可以使用如下语句从回收站恢复删除的表 sql flashback table to before drop; 例如我们恢复刚才删除的表 SQL flashback table test_rbin to before drop; Flashback complete. SQL select * from test_rbin; VAL- 10 在恢复的过程的同时我们可以将表另外命名,命令如下: sqlflashback table to before drop rename to ;sqlflashback table test_rbin to before drop rename to test_rbin1; Oracle从回收站恢复是按照降序恢复的,比如连续3次删除同样一个表(删除后再创建,再删除),恢复的是先恢复最后一次删除的表. SQL show user;USER is HXLSQL drop table test_rbin; Table dropped. SQL create table test_rbin (col1 number); Table created. SQL insert into test_rbin values (1); 1 row created. SQL commit; Commit complete. SQL drop table test_rbin; Table dropped. SQL create table test_rbin (col1 number); Table created. SQL insert into test_rbin values (2); 1 row created. SQL commit; Commit complete. SQL drop table test_rbin; Table dropped. SQL create table test_rbin (col1 number); Table created. SQL insert into test_rbin values (3); 1 row created. SQL commit; Commit complete. SQL drop table test_rbin; Table dropped. SQL show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME- - - - TEST_RBIN BIN$gGHkV/xQSSaQ5bG0PikSAg=$0 TABLE 2012-06-22:15:31:21TEST_RBIN BIN$xVKygKwJTJa+8zmu4BJzvQ=$0 TABLE 2012-06-22:15:30:53TEST_RBIN BIN$R7As9PsYRva7CY6cnAjROw=$0 TABLE 2012-06-22:15:30:23TEST_RBIN BIN$0R+cRKhDTFu+8ShBjLDpqg=$0 TABLE 2012-06-22:15:29:27