数据闪回要满足以下几个条件开启闪回和设置undo保留的时间闪回利用undo表空间里的记录修改前的数据对应ROLL.DBF如果一个表期间做了DDL操作该表不能恢复DDL操作之前的数据1.数据闪回1.1查看roll大小SQL SELECTTABLESPACE_NAME AS 表空间名,ROUND(SUM(BYTES)/1024/1024, 2) AS 总大小MB,COUNT(FILE_NAME) AS 文件个数FROM DBA_DATA_FILESWHERE TABLESPACE_NAME ROLLGROUP BY TABLESPACE_NAME; 2 3 4 5 6 7行号 表空间名 总大小MB 文件个数---------- ------------ ----------- --------------------1 ROLL 128 1已用时间: 21.687(毫秒). 执行号:1105.SQL SELECTdf.tablespace_name AS 表空间名,ROUND(df.bytes/1024/1024, 2) AS 总大小(MB),ROUND((df.bytes - fs.bytes)/1024/1024, 2) AS 已用(MB),ROUND(fs.bytes/1024/1024, 2) AS 剩余(MB),ROUND(100*(df.bytes - fs.bytes)/df.bytes) AS 使用率(%)FROM(SELECT tablespace_name, SUM(bytes) bytesFROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name, SUM(bytes) bytesFROM dba_free_space GROUP BY tablespace_name) fsWHEREdf.tablespace_name fs.tablespace_nameAND df.tablespace_name ROLL;行号 表空间名 总大小(MB) 已用(MB) 剩余(MB) 使用率(%)---------- ------------ ------------- ---------- ---------- ------------1 ROLL 128 47 81 371.2开启闪回SQL ALTER SYSTEM SET ENABLE_FLASHBACK1;SQL select *from V$PARAMETER WHERE NAMEENABLE_FLASHBACK;1.3设置保留时间UNDO_RETENTION 默认时间90秒SQL ALTER SYSTEM SET UNDO_RETENTION 3600 BOTH;SQL select * from V$PARAMETER WHERE NAMEUNDO_RETENTION;行号 ID NAME TYPE VALUE SYS_VALUE FILE_VALUE---------- ----------- -------------- ---- ----------- ----------- -----------DESCRIPTION DEFAULT_VALUE ISDEFAULT---------------------------------------------------------------------------------------- ------------- -----------1 445 UNDO_RETENTION SYS 3600.000000 3600.000000 3600.000000Maximum retention time in seconds for undo pages since relative transaction is committed 90.000000 02.闪回恢复2.1通过LSN恢复创建测试数据SQL CREATE TABLE T(C1 INT);SQL INSERT INTO T SELECT LEVEL CONNECT BY LEVEL 4;SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 3记录当前lsn号SQL SELECT CUR_LSN FROM V$RLOG;行号 CUR_LSN---------- --------------------1 282263删除对应数据插入新数据4SQL DELETE FROM T WHERE C13;SQL INSERT INTO T VALUES(4);SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 4恢复至查询的lsn时的数据SQL FLASHBACK TABLE T TO LSN 282263;SQL select * from T;行号 C1---------- -----------1 12 23 32.2通过时间恢复创建测试数据SQL CREATE TABLE T(C1 INT);SQL INSERT INTO T SELECT LEVEL CONNECT BY LEVEL 4;SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 3记录下当前的时间SQL select now();行号 NOW()---------- --------------------------1 2026-10-12 14:19:54.865842删除后插入一条新数据4SQL DELETE FROM T WHERE C13;SQL INSERT INTO T VALUES(4);SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 4数据库恢复至 2026-10-12 14:19:54SQL FLASHBACK TABLE T TO TIMESTAMP 2026-10-12 14:19:54;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 32.3恢复至临时表不管是2.1和2.2方式恢复 通过lsn或者时间点恢复方式恢复至lsn和时间点之后的新数据就不存在可以使用以下方式恢复至临时表不影响现有生产数据表以下为时间点为例LSN方式一样创建测试数据SQL CREATE TABLE T(C1 INT);SQL INSERT INTO T SELECT LEVEL CONNECT BY LEVEL 4;SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 3记录下当前的时间SQL select now();行号 NOW()---------- --------------------------1 2026-10-12 14:24:03.432962已用时间: 4.885(毫秒). 执行号:1140.删除数据插入一条新数据4SQL DELETE FROM T WHERE C13;SQL INSERT INTO T VALUES(4);SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 4数据恢复至临时表T_tempSQL CREATE TABLE T_temp AS SELECT * FROM T AS OF TIMESTAMP 2026-10-12 14:24:03;数据查询数据恢复到了T_temp临时表对于现有的生产表T数据不影响新插入的4根据需求在将T_temp临时表需要的数据恢复至生产表T2.4闪回期间DDL操作如果一个表期间有DDL操作期间表不能做闪回数据恢复创建测试数据SQL CREATE TABLE T(C1 INT);SQL INSERT INTO T SELECT LEVEL CONNECT BY LEVEL 4;SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 3记录下当前的时间SQL select now();行号 NOW()---------- --------------------------1 2026-10-12 14:28:49.721054删除数据和插入新数据SQL DELETE FROM T WHERE C13;SQL INSERT INTO T VALUES(4);SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 4期间表做DDL操作SQL ALTER TABLE T ADD C2 INT;数据恢复会报错SQLFLASHBACK TABLE T TO TIMESTAMP 2026-10-12 14:28:49;第1 行附近出现错误[-7009]:对象定义被修改.已用时间: 2.882(毫秒). 执行号:0.3.回收站恢复drop和truncate表3.1开启回收站SQL SELECT * FROM V$PARAMETER WHERE NAMERECYCLEBIN;行号 ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION DEFAULT_VALUE ISDEFAULT---------- ----------- ---------- ------- ----- --------- ---------- ----------------------------- ------------- -----------1 941 RECYCLEBIN IN FILE 2 2 2 Whether to enable recycle bin 0 0RECYCLEBIN1回收站只保留drop表RECYCLEBIN2回收站drop和truncateSQL ALTER SYSTEM SET RECYCLEBIN 2 SPFILE;重启数据库后生效3.2设置回收站保留时间SQL ALTER SYSTEM SET RECYCLEBIN_RETENTION 3600 SPFILE;RECYCLEBIN_RETENTION 0表示永久保留重启数据库后生效3.3恢复drop创建数据SQL CREATE TABLE T(C1 INT);SQL INSERT INTO T SELECT LEVEL CONNECT BY LEVEL 4;SQL COMMIT;SQL SELECT * FROM T;行号 C1---------- -----------1 12 23 3删除表TSQL drop table T;回收站查看SQL SELECT *FROM DBA_RECYCLEBIN ;行号 OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME PARTITION_NAME BASE_OBJECT---------- ------ ----------- ------------- --------- ----- ------- ------------------- ------------------- -------------- -----------1 SYSDBA BIN$1277$0 T DROP TABLE MAIN 2026-10-12:14:35:53 2026-10-12:14:36:07 NULL 12773.3.1恢复至原表SQL FLASHBACK TABLE T TO BEFORE DROP;3.3.2恢复成另外表名SQL FLASHBACK TABLE T TO BEFORE DROP RENAME TO T100;SQL select * from T100;行号 C1---------- -----------1 12 23 33.4恢复truncatetruncate不能直接将恢复的表进行重命名SQL truncate table T;SQL SELECT *FROM DBA_RECYCLEBIN ;行号 OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME PARTITION_NAME BASE_OBJECT---------- ------ ----------- ------------- --------- ----- ------- ------------------- ------------------- -------------- -----------1 SYSDBA BIN$1279$0 T TRUNCATE TABLE MAIN 2026-10-12:14:41:56 2026-10-12:14:41:56 NULL 1279SQL FLASHBACK TABLE T TO BEFORE TRUNCATE;SQL select * from T;行号 C1---------- -----------1 12 23 34.回收站清理drop和truncate后表在回收站并不会释放表占用的空间还给操作系统当前登录的用户清理对应用户的回收站PURGE RECYCLEBIN;整个数据库的回收站清理需要sysdba权限PURGE DBA_RECYCLEBIN;清理特定的表PURGE TABLE 表名;精确清理回收站某个对象PURGE TABLE BIN$...;更多资讯请上达梦技术社区了解https://eco.dameng.com