Oracle 其他兼容特性
本节介绍 Oracle 兼容模式下除常规语法/函数/视图之外的其他兼容特性。
闪回查询
闪回查询（Flashback Query）可以查询一张表在过去某个时间点的数据状态，常用于查看误删除或误更新前的数据。
前提条件：
- 在
postgresql.conf中设置track_commit_timestamp = on并重启实例（该参数为 postmaster 级，session 级SET不生效，不开启会报错"无法得到提交时间戳数据"）； - 会话内开启闪回开关：
SET _enable_flashback TO on。
重要：闪回查询只能查询到已提交的数据。未提交事务中的插入/删除/更新无法被闪回查询看到，操作后须 COMMIT。
基本用法
1. 开启闪回并创建示例表：
oracle=# SET _enable_flashback TO on;
SET
oracle=# CREATE TABLE test_flashback (
oracle(# id NUMBER(4),
oracle(# name VARCHAR2(20),
oracle(# updated_time DATE
oracle(# );
CREATE TABLE
oracle=# ALTER TABLE test_flashback SET (
oracle(# autovacuum_enabled = false,
oracle(# toast.autovacuum_enabled = false
oracle(# );
ALTER TABLE
2. 插入数据（每步提交），并记录删除前的时间点：
oracle=# INSERT INTO test_flashback VALUES (1, 'Alice', SYSDATE);
INSERT 0 1
oracle=# COMMIT;
COMMIT
oracle=# SELECT pg_sleep(3);
PG_SLEEP
----------
(1 行记录)
oracle=# INSERT INTO test_flashback VALUES (2, 'Bob', SYSDATE);
INSERT 0 1
oracle=# COMMIT;
COMMIT
oracle=# SELECT pg_sleep(3);
PG_SLEEP
----------
(1 行记录)
oracle=# INSERT INTO test_flashback VALUES (3, 'Charlie', SYSDATE);
INSERT 0 1
oracle=# COMMIT;
COMMIT
oracle=# SELECT * FROM test_flashback ORDER BY id;
ID | NAME | UPDATED_TIME
----+---------+---------------------
1 | Alice | 2026-07-30 15:25:58
2 | Bob | 2026-07-30 15:26:01
3 | Charlie | 2026-07-30 15:26:04
(3 行记录)
-- 记录删除前的时刻（后续用此时间点做闪回查询）
oracle=# SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS t1 FROM DUAL;
T1
---------------------
2026-07-30 15:26:04
(1 行记录)
3. 删除一条记录并提交：
oracle=# DELETE FROM test_flashback WHERE id = 1;
DELETE 1
oracle=# COMMIT;
COMMIT
oracle=# SELECT * FROM test_flashback ORDER BY id;
ID | NAME | UPDATED_TIME
----+---------+---------------------
2 | Bob | 2026-07-30 15:26:01
3 | Charlie | 2026-07-30 15:26:04
(2 行记录)
4. 闪回查询：通过 AS OF TIMESTAMP TO_TIMESTAMP(...) 查询 t1 时刻（删除前）的数据，可见已被删除的 Alice：
-- 查询删除前时刻的数据，闪回到 3 行
oracle=# SELECT * FROM test_flashback
oracle-# AS OF TIMESTAMP TO_TIMESTAMP('2026-07-30 15:26:04', 'YYYY-MM-DD HH24:MI:SS')
oracle-# ORDER BY id;
ID | NAME | UPDATED_TIME
----+---------+---------------------
1 | Alice | 2026-07-30 15:25:58
2 | Bob | 2026-07-30 15:26:01
3 | Charlie | 2026-07-30 15:26:04
(3 行记录)
-- 闪回查询可带条件，单独查看已删除的记录
oracle=# SELECT * FROM test_flashback
oracle-# AS OF TIMESTAMP TO_TIMESTAMP('2026-07-30 15:26:04', 'YYYY-MM-DD HH24:MI:SS')
oracle-# WHERE id = 1;
ID | NAME | UPDATED_TIME
----+-------+---------------------
1 | Alice | 2026-07-30 15:25:58
(1 行记录)
闪回查询的事务行为
闪回查询只反映已提交数据的历史状态，不受当前未提交事务的影响。承接上面的示例表（当前 2 行，id=1 已删除），在未提交事务中插入一条新数据（id=99）：
oracle=# BEGIN;
BEGIN
oracle=# INSERT INTO test_flashback VALUES (99, 'NBA', SYSDATE);
INSERT 0 1
-- 普通查询：事务内可见，3 行（含未提交的 NBA）
oracle=# SELECT * FROM test_flashback ORDER BY id;
ID | NAME | UPDATED_TIME
----+---------+---------------------
2 | Bob | 2026-07-30 15:26:01
3 | Charlie | 2026-07-30 15:26:04
99 | NBA | 2026-07-30 15:26:20
(3 行记录)
-- 闪回查询：回到删除前的时刻，只看到已提交的历史数据，未提交的 NBA 不在其中
oracle=# SELECT * FROM test_flashback
oracle-# AS OF TIMESTAMP TO_TIMESTAMP('2026-07-30 15:26:04', 'YYYY-MM-DD HH24:MI:SS')
oracle-# ORDER BY id;
ID | NAME | UPDATED_TIME
----+-------+---------------------
1 | Alice | 2026-07-30 15:25:58
2 | Bob | 2026-07-30 15:26:01
(2 行记录)
oracle=# COMMIT;
COMMIT
说明：上例中，事务内普通查询返回 3 行（含未提交的 NBA），而闪回到 15:26:04 的查询只返回当时已提交的 Alice、Bob，未提交的 NBA 不会出现在闪回结果中。这说明闪回查询基于提交时间戳还原历史快照，与当前事务的未提交改动相互隔离。示例中的时间点为演示值，实际使用时替换为待查询的历史时刻；关闭表的 autovacuum 可避免旧版本数据被过早清理，保证闪回窗口内的数据可查。
全局临时表
前提：使用全局临时表前，需开启 enable_global_temp_table 参数并重启实例：
ALTER DATABASE oracle SET enable_global_temp_table = on;
该参数为数据库级，修改后需重启数据库服务生效。
全局临时表概念
1. 定义
全局临时表在数据库中定义，但其数据是临时的，仅存在于会话或事务期间。
2. 数据存储
会话级临时表（ON COMMIT PRESERVE ROWS）：数据在会话期间保留，提交或回滚事务不影响数据，会话结束时数据自动清除。事务级临时表（ON COMMIT DELETE ROWS）：数据在事务期间保留，提交或回滚事务后数据自动清除。
3. 特点
数据隔离：每个会话或事务只能看到自己的数据，互不干扰。存储位置：表结构存储在数据库中，数据存储在临时表空间。性能：适合频繁操作的临时数据，减少对永久表的维护开销。
4. 使用场景
复杂查询：存储中间结果，简化查询。批量处理：临时存储批量数据，处理完成后清除。数据隔离：确保不同会话或事务的数据互不影响。
示例
创建全局临时表（ON COMMIT DELETE ROWS）
在 Oracle 模式下，默认行为是 ON COMMIT DELETE ROWS。在会话A里创建一个全局临时表 GTT_1：
oracle=# CREATE GLOBAL TEMPORARY TABLE gtt_1 (
oracle(# id NUMBER,
oracle(# name VARCHAR2(50)
oracle(# ) ON COMMIT DELETE ROWS;
CREATE TABLE
oracle=# \dS+ gtt_1
数据表 "public.gtt_1"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 压缩 | 统计目标 | 描述
------+-----------------------+----------+--------+------+----------+------+----------+------
id | numeric | | | | main | | |
name | character varying(50) | | | | extended | | |
访问方法 heap
选项: on_commit_delete_rows=true
在会话A创建完 GTT_1 后，会话B也能看到 GTT_1 的表结构（全局临时表的定义对所有会话可见）：
-- 会话B
oracle=# \dS+ gtt_1
数据表 "public.gtt_1"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 压缩 | 统计目标 | 描述
------+-----------------------+----------+--------+------+----------+------+----------+------
id | numeric | | | | main | | |
name | character varying(50) | | | | extended | | |
访问方法 heap
选项: on_commit_delete_rows=true
事务内操作与 COMMIT 行为
在事务内插入数据，事务内可见；COMMIT 后数据自动清除：
oracle=# BEGIN;
BEGIN
oracle=# INSERT INTO gtt_1 VALUES (1, 'A_Data1');
INSERT 0 1
oracle=# INSERT INTO gtt_1 VALUES (2, 'A_Data2');
INSERT 0 1
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+---------
1 | A_Data1
2 | A_Data2
(2 行记录)
oracle=# COMMIT;
COMMIT
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+------
(0 行记录)
会话隔离
会话A在事务内插入的数据，会话B不可见；反之亦然。每个会话只能看到自己事务内的临时表数据。
会话A（pid=34770）：
oracle=# BEGIN;
BEGIN
oracle=# INSERT INTO gtt_1 VALUES (1, 'A_Data1');
INSERT 0 1
oracle=# INSERT INTO gtt_1 VALUES (2, 'A_Data2');
INSERT 0 1
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+---------
1 | A_Data1
2 | A_Data2
(2 行记录)
oracle=# COMMIT;
COMMIT
会话B（pid=34771）同时查询和操作：
-- 会话B查询：看不到会话A未提交的数据
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+------
(0 行记录)
-- 会话B写入自己的数据
oracle=# BEGIN;
BEGIN
oracle=# INSERT INTO gtt_1 VALUES (10, 'B_Data1');
INSERT 0 1
oracle=# INSERT INTO gtt_1 VALUES (20, 'B_Data2');
INSERT 0 1
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+---------
10 | B_Data1
20 | B_Data2
(2 行记录)
oracle=# COMMIT;
COMMIT
oracle=# SELECT * FROM gtt_1;
ID | NAME
----+------
(0 行记录)
ON COMMIT PRESERVE ROWS
也支持 ON COMMIT PRESERVE ROWS，COMMIT 后数据保留（会话结束才清除）：
oracle=# CREATE GLOBAL TEMPORARY TABLE gtt_2 (
oracle(# id NUMBER,
oracle(# name VARCHAR2(50)
oracle(# ) ON COMMIT PRESERVE ROWS;
CREATE TABLE
oracle=# INSERT INTO gtt_2 VALUES (100, 'SessionData');
INSERT 0 1
oracle=# SELECT * FROM gtt_2;
ID | NAME
-----+-------------
100 | SessionData
(1 行记录)
oracle=# COMMIT;
COMMIT
oracle=# SELECT * FROM gtt_2;
ID | NAME
-----+-------------
100 | SessionData
(1 行记录)
全局临时表上创建索引
当前的实现支持在全局临时表上创建索引，也支持索引扫描：
oracle=# CREATE INDEX idx_gtt_2_id ON gtt_2(id);
CREATE INDEX
oracle=# \dS+ gtt_2
数据表 "public.gtt_2"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 压缩 | 统计目标 | 描述
------+-----------------------+----------+--------+------+----------+------+----------+------
id | numeric | | | | main | | |
name | character varying(50) | | | | extended | | |
索引：
"idx_gtt_2_id" btree (id)
访问方法 heap
选项: on_commit_delete_rows=false
不同 SCHEMA 下创建全局临时表
当然也支持将全局临时表创建在不同的 SCHEMA 下：
oracle=# CREATE SCHEMA test_schema;
CREATE SCHEMA
oracle=# CREATE GLOBAL TEMPORARY TABLE test_schema.gtt_3 (
oracle(# id NUMBER,
oracle(# val VARCHAR2(20)
oracle(# ) ON COMMIT DELETE ROWS;
CREATE TABLE
oracle=# \dS+ test_schema.gtt_3
数据表 "test_schema.gtt_3"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 压缩 | 统计目标 | 描述
------+-----------------------+----------+--------+------+----------+------+----------+------
id | numeric | | | | main | | |
val | character varying(20) | | | | extended | | |
访问方法 heap
选项: on_commit_delete_rows=true