从 Oracle ROWID 到 PostgreSQL OID/CTID 的逻辑

CREATETABLE IF NOT EXISTS ora_rowid_demo (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);
INSERTINTO ora_rowid_demo VALUES (1, 'Oracle'), (2, 'ROWID'), (3, 'Demo');
COMMIT;

-- 核心查询：解析ROWID并提取所有物理地址信息
SELECT
    -- 原始ROWID和可读格式
    ROWID AS 原生ROWID,
    ROWIDTOCHAR(ROWID) AS 可读ROWID,
    -- 解析ROWID的核心组成部分
    DBMS_ROWID.ROWID_OBJECT(ROWID) AS 数据对象编号,
    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS 数据文件编号,
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS 数据块编号,
    DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS 块内行槽号,
    -- 业务字段
    id,
    name
FROM ora_rowid_demo;

-- 替换为步骤2中解析出的「数据对象编号」
SELECT
    object_name AS 表名,
    object_id AS 数据对象编号,
    object_type AS 对象类型
FROM USER_OBJECTS 
WHERE object_id = 73541; -- 此处替换为你的数据对象编号

-- 替换为步骤2中解析出的「数据文件编号」
SELECT
    file_name AS 数据文件路径,
    file_id AS 数据文件编号,
    tablespace_name AS 表空间名
FROM DBA_DATA_FILES 
WHERE file_id = 4; -- 此处替换为你的数据文件编号

-- 查看指定数据文件+数据块的信息
SELECT
    block_id,
    file_id,
    tablespace_name
FROM DBA_EXTENTS 
WHERE
    file_id = 4-- 数据文件编号
    AND block_id <= 120-- 数据块编号
    AND block_id + blocks > 120
    AND segment_name = 'ORA_ROWID_DEMO'; -- 表名

-- 1. 记录迁移前行的ROWID解析结果
SELECT
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS 迁移前数据块编号,
    DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS 迁移前行槽号
FROM ora_rowid_demo WHERE id = 2;

-- 2. 触发行迁移（调整表存储参数+更新大字段）
ALTERTABLE ora_rowid_demo STORAGE (INITIAL 1K NEXT 1K);
UPDATE ora_rowid_demo SET name = RPAD('Large Data', 2000, 'x') WHERE id = 2;
COMMIT;

-- 3. 记录迁移后行的ROWID解析结果
SELECT
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS 迁移后数据块编号,
    DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS 迁移后行槽号
FROM ora_rowid_demo WHERE id = 2;

-- 仅 PostgreSQL 11 及以下支持 WITH OIDS，12+ 需降级或使用系统表测试
CREATETABLE pgsql_oid_demo (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) WITH OIDS; -- 显式启用 OID

-- 插入测试数据
INSERTINTO pgsql_oid_demo VALUES
    (1, 'PostgreSQL'), 
    (2, 'OID Demo'), 
    (3, 'Object ID');
COMMIT;

-- 查询表中行的 OID（OID 是伪列，可直接查询）
SELECT
    oid, -- 行的 OID
    id, 
    name 
FROM pgsql_oid_demo;

-- 1. 确认表的物理文件路径（含数据库OID、表relfilenode）
SELECT
    pg_database.oidAS 数据库OID,
    pg_class.relfilenodeAS 表物理文件标识,
    pg_relation_filepath('pgsql_ctid_demo') AS 完整物理路径
FROM pg_database, pg_class
WHERE
    pg_database.datname = current_database() -- 当前数据库名
    AND pg_class.relname = 'pgsql_ctid_demo'; -- 测试表名

# 1. 切换到 postgres 用户（必须，否则无权限访问数据文件）
su- postgres

# 2. 安装 pg_filedump（CentOS/RHEL 示例，其他系统见备注）
## CentOS 7 + PostgreSQL 11
yum install -y postgresql11-contrib
## Ubuntu/Debian + PostgreSQL 11
# apt-get install -y postgresql-contrib-11

# 3. 进入数据目录（避免绝对路径错误）
cd$PGDATA# 等价于 /var/lib/pgsql/11/data/

# 4. 解析表的物理文件（块 0），并过滤关键信息（含OID）
pg_filedump -b0 base/16384/16389 | grep-E'Block|Tuple|OID|ctid'

# 块级信息：Block 0 对应 CTID 中的块号 0
Block 0 (0x00000000):
    Block Header Data:
        Block Number: 0              -- CTID 的「块号」
        Checksum: 0x0000 (no verify)
        Flags: 0x0000 (USED)
        Lower Bound: 0x0078 (120)
        Upper Bound: 0x1f98 (8088)
        Special Bound: 0x2000 (8192) -- 单个块大小 8KB（8192 字节）
        Page Size: 8192 bytes        -- 验证默认块大小
        Version: 4
        OID of Relation: 16389       -- 表的 OID（对应 pg_class.oid）
        Number of Tuple Items: 10    -- 块内 10 行，与插入数据一致

# 行级信息：每个 Tuple 对应 CTID 的「行号」，含 OID 字段（若表启用 OID）
Tuple 1 (Offset: 0x0078, Size: 28):
    Tuple Header Data:
        t_xmin: 672          -- 插入事务ID
        t_xmax: 0            -- 删除事务ID（0=未删除）
        t_cid: 0             -- 命令ID
        t_ctid: (0,1)        -- CTID！对应查询结果的 (0,1)
        t_infomask2: 0x0002 (HEAP_HAS_FIELDS)
        t_infomask: 0x0008 (HEAP_HASNULL)
        t_hoff: 0x14 (20)
        t_bits: 0x00000000 []
        t_oid: 16390         -- 行的 OID（仅表启用 WITH OIDS 时存在！）
    Tuple Data (HeapTupleHeader + Data):
        Column 1: 1          -- id 字段值 = 1

Tuple 2 (Offset: 0x0094, Size: 28):
    Tuple Header Data:
        t_ctid: (0,2)        -- CTID (0,2)
        t_oid: 16391         -- 行的 OID
    Tuple Data:
        Column 1: 2          -- id 字段值 = 2

...

Tuple 10 (Offset: 0x016c, Size: 28):
    Tuple Header Data:
        t_ctid: (0,10)       -- CTID (0,10)
        t_oid: 16399         -- 行的 OID
    Tuple Data:
        Column 1: 10         -- id 字段值 = 10

-- 查询系统表（如 pg_user）的 OID，确认不与用户表 OID 重复
SELECT oid, usename FROM pg_user LIMIT3;

-- 典型输出（无重复）：
| oid  | usename  |
|------|----------|
| 10   | postgres |
| 16383 | testuser |
| 16384 | demo     |

-- 验证用户表 OID 未出现在系统表中
SELECT EXISTS (
    SELECT1FROM pg_user WHERE oid IN (16385, 16386, 16387)
) AS 是否重复;

-- 输出：是否重复 = f（false），验证 OID 全局唯一

-- 步骤1：记录更新前行的 OID
SELECT oid, id, name FROM pgsql_oid_demo WHERE id = 2;
-- 输出：oid=16386，id=2，name='OID Demo'

-- 步骤2：更新行数据
UPDATE pgsql_oid_demo SET name = 'OID Changed'WHERE id = 2;
COMMIT;

-- 步骤3：查询更新后的 OID
SELECT oid, id, name FROM pgsql_oid_demo WHERE id = 2;
-- 典型输出：oid=16388，id=2，name='OID Changed'

-- 步骤4：验证旧 OID 失效（无法查询到数据）
SELECT * FROM pgsql_oid_demo WHERE oid = 16386;
-- 输出：无结果，旧 OID 已失效

-- 查看 OID 相关系统参数
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('oid_cleanup', 'autovacuum_oid_wrap_limit');

-- 典型输出：
| name                      | setting  | unit |
|---------------------------|----------|------|
| oid_cleanup               | on       |      |
| autovacuum_oid_wrap_limit | 1073741824 |    |

-- 模拟 OID 接近上限的提示（无需实际插入40亿数据）
-- PostgreSQL 会在 OID 接近上限时触发自动清理，避免重复
SELECT
    'OID 最大值：' || (2^32 - 1) AS oid_max_value, -- 计算 32 位最大值
    '当前数据库最大 OID：' || (SELECT max(oid) FROM pg_class) AS current_max_oid;

-- 创建无 OID 的普通表
CREATETABLE pgsql_oid_disabled (
    id INT,
    name VARCHAR(50)
);

-- 尝试查询 OID，会报错
SELECT oid, id FROM pgsql_oid_disabled;

-- 错误提示：
-- ERROR:  column "oid" does not exist
-- LINE 1: SELECT oid, id FROM pgsql_oid_disabled;

-- 验证表的 OID 启用状态（通过 pg_class 的 relhasoids 字段）
SELECT
    relname AS 表名,
    relhasoids AS 是否启用OID
FROM pg_class 
WHERE relname IN ('pgsql_oid_demo', 'pgsql_oid_disabled');

-- 输出：
| 表名               | 是否启用OID |
|--------------------|-------------|
| pgsql_oid_demo     | t           |
| pgsql_oid_disabled | f           |

-- 创建测试表
CREATETABLE pgsql_ctid_demo (
    id NUMERIC PRIMARY KEY
);

-- 插入 10 条测试数据
INSERTINTO pgsql_ctid_demo VALUES (generate_series(1,10));
COMMIT;

-- 查看 CTID 及组成
SELECT
    ctid,
    -- 提取 CTID 的块号（通过字符串截取）
    split_part(ctid::TEXT, ',', 1)::INT AS 块号,
    -- 提取 CTID 的行号（通过字符串截取）
    split_part(ctid::TEXT, ')', 1) AS 行号_raw,
    split_part(split_part(ctid::TEXT, ',', 2), ')', 1)::INT AS 行号,
    id
FROM pgsql_ctid_demo;

-- 获取表的 relfilenode（物理文件名称）
SELECT
    relname AS 表名,
    relfilenode AS 物理文件标识,
    pg_relation_filepath(relname) AS 物理文件路径
FROM pg_class 
WHERE relname = 'pgsql_ctid_demo';

# 安装 pg_filedump（以 CentOS 为例）
yum install postgresql11-contrib -y

# 解析表的物理文件，查看块 0 的内容
pg_filedump -b0 /var/lib/pgsql/11/data/base/16384/16389

Block 0 (0x00000000):
    Block Header:
        Block Number: 0
        Tuple Count: 10  # 块内有 10 行，与插入数据一致
    Tuple 1 (Offset: 8192):
        ctid: (0,1)
        Data: id = 1
    Tuple 2 (Offset: 8224):
        ctid: (0,2)
        Data: id = 2
    ...
    Tuple 10 (Offset: 8480):
        ctid: (0,10)
        Data: id = 10

-- 插入大字段数据，超出单个块容量
INSERTINTO pgsql_ctid_demo 
SELECT generate_series(11, 200) || RPAD('', 1000, 'x'); -- 每行约 1KB
COMMIT;

-- 查询新增数据的 CTID
SELECT
    ctid,
    split_part(ctid::TEXT, ',', 1)::INT AS 块号,
    split_part(split_part(ctid::TEXT, ',', 2), ')', 1)::INT AS 行号,
    id
FROM pgsql_ctid_demo 
WHERE id > 10
LIMIT5;

-- 记录更新前行的 CTID
SELECT ctid, id FROM pgsql_ctid_demo WHERE id = 2;
-- 输出：ctid=(0,2), id=2

-- 更新行数据（生成新行版本）
UPDATE pgsql_ctid_demo SET id = 999WHERE id = 2;
COMMIT;

-- 查询更新后的 CTID
SELECT ctid, id FROM pgsql_ctid_demo WHERE id = 999;
-- 典型输出：ctid=(0,201), id=999

-- 验证旧 CTID 失效
SELECT * FROM pgsql_ctid_demo WHERE ctid = '(0,2)';
-- 输出：无结果，旧行版本被标记为失效

-- 执行 VACUUM FULL 清理失效行，重构物理存储
VACUUM FULL pgsql_ctid_demo;
COMMIT;

-- 查询清理后的 CTID
SELECT
    ctid,
    split_part(ctid::TEXT, ',', 1)::INT AS 块号,
    split_part(split_part(ctid::TEXT, ',', 2), ')', 1)::INT AS 行号,
    id
FROM pgsql_ctid_demo 
WHERE id = 999;

-- 安装 pgrowlocks 扩展（解析行锁与 CTID）
CREATE EXTENSION IF NOT EXISTS pgrowlocks;

-- 解析 CTID 对应的行锁和物理位置
SELECT
    locked_row AS ctid,
    lock_type,
    page AS 块号,
    tuple AS 行号
FROM pgrowlocks('pgsql_ctid_demo');

-- 1. 查询当前数据库的系统级 OID（对应路径中的 16384）
SELECT oid, datname FROM pg_database WHERE datname = current_database();
-- 输出：oid=16384, datname=postgres

-- 2. 查询表的系统级 OID（早期 relfilenode = 表 OID）
SELECT oid AS 表的系统OID, relname, relfilenode 
FROM pg_class WHERE relname = 'pgsql_ctid_demo';
-- 输出：表的系统OID=16389, relname=pgsql_ctid_demo, relfilenode=16389

-- 查看表的初始 relfilenode
SELECT relname, relfilenode FROM pg_class WHERE relname = 'pgsql_ctid_demo';
-- 输出：relfilenode=16389

-- 执行 VACUUM FULL 触发 relfilenode 变更
VACUUM FULL pgsql_ctid_demo;

-- 再次查看 relfilenode
SELECT relname, relfilenode FROM pg_class WHERE relname = 'pgsql_ctid_demo';
-- 输出：relfilenode=16400（已变更）

-- 但表的系统级 OID 不变
SELECT oid FROM pg_class WHERE relname = 'pgsql_ctid_demo';
-- 输出：oid=16389（仍为原值）