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

Oracle 10g以后的版本中引入了回收站(Recycle Bin)的概念,刪除的對(duì)象統(tǒng)一放入回收站,以BIN$最為前綴命名.用戶刪除某個(gè)表,可以很容易的從回收站中還原回來(lái),但在9i以前是對(duì)于刪除表這樣的DDL操縱是不能直接還原回來(lái)的,通常需要做不完全恢復(fù)或是使用EXP/IMP來(lái)恢復(fù). 1.查看當(dāng)前回收站功能是否開啟(默認(rèn)情況下是開啟的) SQL column value format a10;SQL select value from V$parameter where name = recyclebin; VALUE-on 可以使用如下命令開啟或關(guān)閉該功能. 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.查看回收站里的內(nèi)容 SQL show recyclebin; orSQL select * from user_recyclebin; 下面將創(chuàng)建的表刪除后放入回收站 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.從回收站恢復(fù)刪除的表 可以使用如下語(yǔ)句從回收站恢復(fù)刪除的表 sql flashback table to before drop; 例如我們恢復(fù)剛才刪除的表 SQL flashback table test_rbin to before drop; Flashback complete. SQL select * from test_rbin; VAL- 10 在恢復(fù)的過程的同時(shí)我們可以將表另外命名,命令如下: sqlflashback table to before drop rename to ;sqlflashback table test_rbin to before drop rename to test_rbin1; Oracle從回收站恢復(fù)是按照降序恢復(fù)的,比如連續(xù)3次刪除同樣一個(gè)表(刪除后再創(chuàng)建,再刪除),恢復(fù)的是先恢復(fù)最后一次刪除的表. 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