一、先查看undo的保留时间
SQL>SELECT VALUE/60 AS "UNDO_RETENTION_MINUTES"
FROM V$PARAMETER
WHERE NAME = 'undo_retention';
将UNDO_RETENTION 被设置为 3600 秒（即 1 小时）
ALTER SYSTEM SET UNDO_RETENTION = 3600;
二、创建测试表空间、用户和角色
SQL>create tablespace adminSpace datafile '/u01/app/oracle/oradata/XE/admin1.dbf' size 100M autoextend on next 10M MAXSIZE 500M;
SQL>create user test identified by 123456 default tablespace adminSpace;
SQL> grant dba to test;
三、创建测试表
1、创建一个简单的测试表
SQL>CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
data VARCHAR2(100)
);
2、插入测试数据
SQL>INSERT INTO test_table (id, data) VALUES (1, 'Sample data 1');
SQL>INSERT INTO test_table (id, data) VALUES (2, 'Sample data 2');
SQL>INSERT INTO test_table (id, data) VALUES (3, 'Sample data 3');
SQL>commit;
3、查询测试数据
SQL>SELECT * FROM test_table;
四、模拟使用delete删除数据
SQL>delete from test_table where id=2;
五、查看所有表的创建和最后修改时间
SQL>SELECT OBJECT_NAME, CREATED, LAST_DDL_TIME from user_objects;
查看特定表的创建和最后修改时间
SQL>select CREATED,LAST_DDL_TIME from user_objects where object_name=upper('test_table');
六、创建一个临时表，使用AS OF TIMESTAMP进行恢复。
方法1：
#查询20分钟之前的数据
select * from test_table as of timestamp (systimestamp - interval '20' minute); #写法1
select * from test_table as of timestamp sysdate - 20/1440; #写法2
方法2：
#查看时间段之前的数据
SQL>SELECT CREATED, LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME = 'test_table';
#指定恢复到某个时间节点
SQL>create table tmp as select * from test_table AS OF TIMESTAMP to_timestamp('2024-09-04 10:58:00','yyyy-mm-dd hh24:mi:ss');
# 查询数据
SQL>SELECT * FROM tmp;
#转化为insert语句
SQL>select 'INSERT INTO test_table (id,data) VALUES(' || '''' || id ||'''' || ','
|| '''' || data || '''' || ','
From tmp order by ID;
