ORACLE闪回区清除垃圾表和恢复

ORACLE闪回区清除垃圾表和恢复

http://hi.baidu.com/52hack/item/83f20ed2627fee16d80e4463

问题:删除表后,查询回收站无内容.
解决方法:
1,查询你的库中有没有设置闪回区
SHOW PARAMETER DB_RECOVERY_FILE_DEST

得到下面的结果:
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery _area
db_recovery_file_dest_size big integer 2G
哪就是正常的了.如果没有.哪么就要按照下面更改
把库更改为ARCHIVE模式
SHUTDOWN;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
确认一下是否开启成功:ARCHIVE LOG LIST 或是 SELECT LOG_MODE FROM V$DATABASE;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 64
Next log sequence to archive 66
Current log sequence 66
开启成功...
还可以更改你的闪回区大小:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=1G;
用下面的命令显示回收站的全部内容
show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
A BIN$dleS/T+q703gQKjAdgEUXw==$0 TABLE 2009-10-20:14:00:07
T BIN$dlhLF6aly/3gQKjAdgETkw==$0 TABLE 2009-10-20:13:40:58


清楚一个垃圾表:
PURGE TABLE "BIN$dleS/T+q703gQKjAdgEUXw==$0";

清楚所有的表:
PURGE RECYCLEBIN

恢复表:
第一种:
SQL> flashback table "BIN$dlhLF6aiy/3gQKjAdgETkw==$0" to before drop;
Flashback complete.
第二种:
SQL> flashback table t to before drop;
Flashback complete.
用SCN恢复:
flashback table t to timestamp to_timestamp('2009-10-20 14:30:00','yyyy-mm-dd hh24:mi:ss');
这个先前条件需要ROW MOVEMENT
alter table t enable row move ment;

直接删除一个表,并不放在回收站:
SQL> drop table yayaw purge;
Table dropped.