Appearance

Oracle 商业场景训练营 ​

Oracle 学习不能停在“实例、SGA、PGA、Undo、Redo、SCN”这些词。商业项目里真正要会的是：能建表和约束，能看执行计划，能解释一致性读，能处理锁等待，能理解 snapshot too old，能知道 Redo、归档、RMAN、Data Guard 为什么和恢复有关。

训练目标：把 Oracle 的对象、索引、Undo、Redo、SCN、执行计划、等待事件和恢复思路放到金融、政企、医疗资产这类商业场景中理解。

训练总流程 ​

mermaid
flowchart TD
    A["建用户和表空间"] --> B["建业务表和约束"]
    B --> C["建序列和索引"]
    C --> D["执行查询并看计划"]
    D --> E["模拟事务和锁等待"]
    E --> F["解释 Undo / Redo / SCN"]
    F --> G["排查等待事件和快照过旧"]
    G --> H["整理面试回答"]

训练一：资产表、序列和约束 ​

场景 ​

大型政企或医疗系统里，Oracle 常作为核心关系库。资产编号不能重复，资产状态必须合法，创建时间要可审计。

建表 Demo ​

sql
create table medical_asset (
  id number primary key,
  asset_no varchar2(64) not null,
  hospital_id number not null,
  department_id number not null,
  asset_name varchar2(128) not null,
  status varchar2(20) not null,
  created_at date default sysdate not null,
  updated_at date default sysdate not null,
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create sequence seq_medical_asset start with 1 increment by 1 cache 100;

create index idx_asset_hospital_status_time
on medical_asset(hospital_id, status, created_at);

插入：

sql
insert into medical_asset(
  id, asset_no, hospital_id, department_id, asset_name, status
) values (
  seq_medical_asset.nextval, 'A-ORACLE-001', 1, 10, 'CT 设备', 'USED'
);

commit;

为什么 Oracle 常用 sequence ​

Oracle 早期常用 sequence 生成主键。sequence 是独立对象，可以缓存一批值，减少每次取号的开销。cache 100 表示预先缓存 100 个序列值，提高并发性能。

注意：sequence 保证唯一递增取号，但不保证无空洞。实例重启或事务回滚都可能让已经取出的号不再使用。商业系统不要把 sequence 主键当成必须连续的业务编号。

设计原因不这样会怎样
asset_no 唯一约束防止重复资产编号并发导入可能重复
check status数据库兜底状态合法性脚本绕过应用写入脏状态
sequence 主键高并发生成唯一 ID应用自生成不当可能冲突
组合索引支持医院状态列表查询可能全表扫描和排序

训练二：执行计划和 ROWID 回表 ​

查询某医院使用中的资产：

sql
explain plan for
select id, asset_no, asset_name
from medical_asset
where hospital_id = 1
  and status = 'USED'
order by created_at desc;

select * from table(dbms_xplan.display);

重点看什么 ​

执行计划节点含义
TABLE ACCESS FULL全表扫描
INDEX RANGE SCAN索引范围扫描
TABLE ACCESS BY INDEX ROWID通过索引找到 ROWID 后回表
NESTED LOOPS嵌套循环 Join
HASH JOIN哈希 Join
SORT ORDER BY排序

原理图 ​

mermaid
flowchart TD
    A["where hospital_id/status"] --> B["INDEX RANGE SCAN"]
    B --> C["拿到 ROWID"]
    C --> D["TABLE ACCESS BY ROWID"]
    D --> E["取 asset_name 等列"]
    E --> F["返回结果"]

Oracle B-tree 索引叶子块里通常保存 key 和 ROWID。ROWID 能定位到数据文件、块和块内行。查询字段不都在索引里时，要通过 ROWID 访问表块。

不这样会怎样 ​

只看“有没有索引”不够。即使走索引，如果范围很大、回表很多、排序很多，仍然可能慢。要结合执行计划、统计信息、返回行数和等待事件判断。

原理跳转：索引与优化器、核心全过程原理。

训练三：一致性读、Undo 和 SCN ​

场景 ​

事务 A 查询资产列表时，事务 B 正在修改某条资产。Oracle 普通查询要看到查询开始时的一致结果，而不是读到一半新一半旧。

mermaid
flowchart TD
    A["查询开始"] --> B["获得查询 SCN"]
    B --> C["读取数据块"]
    C --> D{"块中行版本是否晚于查询 SCN"}
    D -- "否" --> E["直接返回当前块中版本"]
    D -- "是" --> F["通过 Undo 构造旧版本"]
    F --> G["返回查询 SCN 对应版本"]

会话 A ​

sql
set transaction read only;

select asset_no, status
from medical_asset
where asset_no = 'A-ORACLE-001';

-- 暂时不结束事务

会话 B ​

sql
update medical_asset
set status = 'REPAIR'
where asset_no = 'A-ORACLE-001';

commit;

会话 A 再查，仍应看到自己只读事务开始时的一致视图。

原理解释 ​

Oracle 用 SCN 标记数据库中的逻辑时间点。查询开始时确定一个一致性 SCN。如果读到的数据块中某行已经被之后的事务修改，Oracle 会根据 Undo 信息构造该 SCN 时刻的旧版本。

Undo 的作用不是只有回滚：

作用解释
回滚事务失败时撤销修改
一致性读根据查询 SCN 构造旧版本
Flashback 基础支持按历史时间点查询或恢复

训练四：锁等待排查 ​

场景 ​

两个事务更新同一资产，后来的事务会等待。

会话 A：

sql
update medical_asset
set status = 'REPAIR'
where asset_no = 'A-ORACLE-001';

-- 不提交

会话 B：

sql
update medical_asset
set status = 'USED'
where asset_no = 'A-ORACLE-001';

会话 B 会等待会话 A 的行锁。

排查思路 ​

sql
select sid, serial#, username, status, blocking_session, event, seconds_in_wait
from v$session
where blocking_session is not null;

查看锁相关对象：

sql
select s.sid, s.serial#, s.username, o.object_name, l.locked_mode
from v$locked_object l
join dba_objects o on l.object_id = o.object_id
join v$session s on l.session_id = s.sid;

原理 ​

Oracle 普通一致性读不阻塞写，写写冲突才会等待。排查时要找到阻塞会话、被锁对象、事务持续时间和正在执行的 SQL。

训练五：snapshot too old ​

现象 ​

Oracle 报 ORA-01555: snapshot too old，很多初学者只知道“Undo 不够”，但真正原因是：查询需要构造旧版本时，相关 Undo 已经被覆盖或不可用。

mermaid
flowchart TD
    A["长查询开始"] --> B["获得旧 SCN"]
    B --> C["其他事务频繁修改"]
    C --> D["产生大量 Undo"]
    D --> E["旧 Undo 被覆盖"]
    E --> F["长查询需要旧版本"]
    F --> G["找不到 Undo"]
    G --> H["snapshot too old"]

常见原因 ​

原因解释
长查询时间太久查询需要很早的旧版本
更新太频繁Undo 产生和覆盖很快
Undo 表空间不足保留旧版本能力弱
批处理事务太大产生大量 Undo，影响其他查询

处理方向 ​

  1. 优化长查询，减少运行时间。
  2. 拆分大事务，避免一次产生过多 Undo。
  3. 增加 Undo 表空间或调整 Undo 保留策略。
  4. 将报表查询转到备库或报表库。

训练六：Redo、归档和恢复思路 ​

Oracle 提交事务时，关键是 LGWR 把 Redo 写到 redo log，而不是立刻把所有数据块刷盘。

mermaid
flowchart TD
    A["事务修改数据块"] --> B["生成 Undo 和 Redo"]
    B --> C["提交"]
    C --> D["LGWR 刷 Redo"]
    D --> E["commit 返回成功"]
    E --> F["DBWR 后台刷脏块"]
    F --> G["宕机后用 Redo 恢复"]

如果开启归档模式，redo log 切换后会归档，归档日志配合备份可以支持时间点恢复。Data Guard 也依赖 Redo 传输到备库。

最终验收清单 ​

做完这页后，你要能回答：

  1. Oracle 的实例和数据库有什么区别？
  2. sequence 为什么可能有空洞？
  3. Oracle B-tree 索引为什么通过 ROWID 回表？
  4. SCN 在一致性读中起什么作用？
  5. Undo 除了回滚还有什么作用？
  6. 普通 select 为什么不阻塞 update？
  7. 写写冲突怎么排查阻塞会话？
  8. snapshot too old 为什么和 Undo 有关？
  9. commit 为什么关键是 Redo 刷盘？
  10. 归档日志、RMAN、Data Guard 和恢复有什么关系？

关联知识点 ​

知识点入口
Oracle 主线从零到生产级掌握
核心流程核心全过程原理
Undo/Redo/SCNUndo、Redo 与 SCN 原理
事务与锁事务与锁
性能排查性能排查
面试Oracle 面试