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

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

问题:删除表后,查询回收站无内容. 解决方法: 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.