Oracle数据库闪回FLASHBACK命令总结

港控/mmm° 2022-06-06 13:38 490阅读 0赞

一、闪回查询：  
1. 方法一  
SQL> select current\_scn from v$database;

CURRENT\_SCN  
\-----------  
   43491990

适当的缩小scn的值，可以查到以往的数据  
SQL> select \* from mytest1 as of scn 43491990;

ID NAME  
\---------- --------------------  
  3 43  
  3 43  
  3 43  
  3 43  
  2 y

2. 方法二  
假设被删除了数据的表是table\_name,通过as of timestamp构造表被删除前某个时刻的状态

create table table\_name\_bak as select \* from table\_name as of  timestamp to\_timestamp('2017-11-10 12:00:00','yyyy-mm-dd hh24:mi:ss');

insert into table\_name select \* from table\_name\_bak where 符合被delete条件的数据

二、闪回表

alter table zyscm.mytest enable row movement;

flashback table mytest to timestamp to\_timestamp(SYSTIMESTAMP - INTERVAL '1' minute);  
或者  
flashback table mytest to timestamp to\_timestamp('2017-11-10 12:00:00','yyyy-mm-dd hh24:mi:ss');

alter table zyscm.mytest disable row movement;

三、闪回drop：

FLASHBACK TABLE <table\_name> TO BEFORE DROP \[RENAME TO <new\_name>\];

四、闪回数据库：  
配置闪回数据库  
SQL> SHUTDOWN IMMEDIATE;  
SQL> STARTUP MOUNT EXCLUSIVE;  
SQL> ALTER SYSTEM SET DB\_FLASHBACK\_RETENTION\_TARGET=2880 SCOPE=BOTH;  
SQL> ALTER DATABASE FLASHBACK ON;  
SQL> ALTER DATABASE OPEN;  
使用示例：  
RMAN> FLASHBACK DATABASE TO TIME = "TO\_DATE('2004-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS')";  
RMAN> FLASHBACK DATABASE TO SCN=23565;  
RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;

SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24);  
SQL> FLASHBACK DATABASE TO SCN 53943;  
SQL> FLASHBACK DATABASE TO RESTORE POINT b4\_load;

闪回数据库操作完成后，必须使用以下一种方法打开数据库：  
1. 在只读模式下验证是否使用了正确的目标时间或 SCN  
2. 使用 RESETLOGS 参数才允许进行更新

===========10g 清空recyclebin方法======================  
按用户  
purge recyclebin;  
purge table "BIN$....";

清除所有  
PURGE DBA\_RECYCLEBIN  
=======================================================

===========查询回收站==================================  
SELECT owner, original\_name, object\_name,  
       type, ts\_name, droptime, related, space  
FROM dba\_recyclebin  
WHERE can\_undrop = 'YES';

SELECT original\_name, object\_name,  
       type, ts\_name, droptime, related, space  
FROM user\_recyclebin  
WHERE can\_undrop = 'YES';

SQL> SHOW RECYCLEBIN