Oracle Drop 表数据恢复
利用Oracle 数据回闪机制进行恢复,当一个表被drop掉,表会被放入recyclebin回收站,可通过回收站做表的闪回。表上的索引、约束等同样会被恢复
不支持sys/system用户表空间对象,可通过alter system set recyclebin=off;关闭回收站功能。
Retrieving a Dropped Table: Example If you accidentally drop the pm.print_media
table and want to retrieve it, then issue the following statement:
FLASHBACK TABLE print_media TO BEFORE DROP;
If another print_media
table has been created in the pm
schema, then use the RENAME
TO
clause to rename the retrieved table:
FLASHBACK TABLE print_media TO BEFORE DROP RENAME TO print_media_old;
If you know that the employees table has been dropped multiple times, and you want to retrieve the oldest version, then query the USER_RECYLEBIN
table to determine the system-generated name, and then use that name in the FLASHBACK
TABLE
statement. (System-generated names in your database will differ from those shown here.)
SELECT object_name, droptime FROM user_recyclebin WHERE original_name = 'PRINT_MEDIA'; OBJECT_NAME DROPTIME ------------------------------ ------------------- RB$$45703$TABLE$0 2003-06-03:15:26:39 RB$$45704$TABLE$0 2003-06-12:12:27:27 RB$$45705$TABLE$0 2003-07-08:09:28:01
其他回闪语句:
1.闪回数据库 FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2019-10-14 14:28:33','yyyy-mm-dd HH24:MI:SS');; flashback database to scn 16813234; 2.闪回表 flashback table table_name to scn scn_number; flashback table table_name to timestamp to_timestamp('2019-10-14 14:28:33','yyyy-mm-dd hh24:mi:ss'); 3.闪回查询 select * from table_name as of timestamp to_timestamp('2019-10-14 14:28:33','yyyy-mm-dd hh24:mi:ss'); select * from scott.dept as of scn 16801523; 4.闪回快照 create restore point before_201910151111 guarantee flashback database; flashback database to restore point before_201910151111;
参考:https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm
微信
![](https://files.cnblogs.com/files/Dev0ps/WeChat.gif)
支付宝
![](https://files.cnblogs.com/files/Dev0ps/Alipay.gif)