一、先查看undo的保留時間

SQL>SELECT VALUE/60 AS "UNDO_RETENTION_MINUTES"

FROM V$PARAMETER

WHERE NAME = 'undo_retention';

將UNDO_RETENTION 被設(shè)置為 3600 秒（即 1 小時）

ALTER SYSTEM SET UNDO_RETENTION = 3600;

二、創(chuàng)建測試表空間、用戶和角色

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;

三、創(chuàng)建測試表

1、創(chuàng)建一個簡單的測試表

SQL>CREATE TABLE test_table (

? id NUMBER PRIMARY KEY,

? data VARCHAR2(100)

);

2、插入測試數(shù)據(jù)

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、查詢測試數(shù)據(jù)

SQL>SELECT * FROM test_table;

四、模擬使用delete刪除數(shù)據(jù)

SQL>delete from test_table where id=2;

五、查看所有表的創(chuàng)建和最后修改時間

SQL>SELECT OBJECT_NAME, CREATED, LAST_DDL_TIME from user_objects;

查看特定表的創(chuàng)建和最后修改時間

SQL>select CREATED,LAST_DDL_TIME from user_objects where object_name=upper('test_table');

六、創(chuàng)建一個臨時表，使用AS OF TIMESTAMP進(jìn)行恢復(fù)。

方法1：

#查詢20分鐘之前的數(shù)據(jù)

select * from test_table as of timestamp (systimestamp - interval '20' minute);? ?#寫法1

select * from test_table? as of timestamp sysdate - 20/1440; #寫法2

方法2：

#查看時間段之前的數(shù)據(jù)

SQL>SELECT CREATED, LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME = 'test_table';

#指定恢復(fù)到某個時間節(jié)點

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');

# 查詢數(shù)據(jù)

SQL>SELECT * FROM tmp;

#轉(zhuǎn)化為insert語句

SQL>select 'INSERT INTO test_table (id,data) VALUES(' || '''' || id ||'''' || ','

|| '''' || data || '''' || ','

From tmp order by ID;