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
相关文章
- Oracle创建数据库
- Oracle查询日期字段是否包含时分秒 TRUNC() 函数
- Oracle授权允许远程访问--Oracle配置允许远程连接
- Oracle数据库使用出现错误-状态: 失败 ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
- Windows下Oracle 11g的下载与安装
- 【C/C++学院】(24)Oracle数据库编程--管理oracle
- Oracle允许IP访问配置
- 《oracle每天一练》Oracle冷备份与数据恢复
- ORACLE SQL调优案例一则
- cx_Oracle连接oracle数据库
- 数据库客户端快捷键(oracle+sybase)
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之RAC 工作原理和相关组件(三)
- Oracle 11g Rac rman把本地数据文件迁移到ASM
- Oracle 杀死INACTIVE,SNIPED会话
- Oracle UNDO_RETENTION作用
- oracle 判断列是否在数据库中存在
- Atitit oracle新特性5 6 7 8 9 10 11 12 18 19 20 attilax总结 目录 1.1. :ora 20c1 1.2. Oracle Database 19c 的
- linux操作系统中oracle数据库的密码过期问题解决
- 柯南君 :Oracle 分区技术 之 怎样支撑大数据操作?
- Oracle控制文件的管理
- About Oracle Database Performance Method
- Oracle数据库问题必知,最全的错误码(BUG)和解决方式汇总
- 【Mysql异构实时同步Oracle】OGG12异构同步mysql到oracle(windows mysql实时同步数据到linux oracle)详细文档