数据库三剑客 · 第5节 · Oracle 完整章节

Oracle 完整章节

Oracle 23ai · The Enterprise Heavyweight

Oracle 是数据库界的劳斯莱斯：贵、稳、强，但也重。它在银行、电信、大型国企核心系统里的地位，至今无人能撼。这一章抓重点：和 MySQL 的语法差异、PL/SQL、优化器、RMAN 备份、Data Guard 高可用。学 Oracle 多半是吃企业饭或考 OCP，不用和 MySQL 死磕，知道差异在哪、能干什么就行。

3.1 Oracle 简介与安装

引子：Oracle 公司 1979 年就做出了第一个商用 SQL 数据库，比 MySQL 早了快二十年。它是"关系型数据库"这个概念的商业标杆。23ai 这一代最大的卖点是 AI 原生——把向量数据库、JSON、图都揉进了一个内核。

Oracle 23ai 的几个新特性（面试能聊一句）
新特性	大白话
AI 向量数据库	原生存储和检索向量 embeddings，不用再外挂一个向量库，RAG 场景直接在 Oracle 里做。
JSON 关系二元性	JSON 列既能当文档查，又能像关系表一样按字段建索引用 SQL 查。
SQL 属性图	原生支持图查询（找"好友的好友"这种关系链路）。
微秒时间戳	TIMESTAMP 精度到微秒，金融级时间敏感场景够用。

安装：免费 XE 版 / Docker

# Oracle XE（Express Edition）免费版，个人学习够用
# 官方提供 Linux 安装包，或直接用 Docker（最省事）
docker run -d --name oracle23ai \
  -p 1521:1521 \
  -e ORACLE_PWD='Oracle_2026' \
  container-registry.oracle.com/database/free:latest

# 客户端：sqlplus 命令行，或图形版 Oracle SQL Developer
sqlplus system/Oracle_2026@//localhost:1521/FREE

Oracle 的几个专有名词先认一下：实例（Instance） = 内存结构 + 后台进程；数据库（Database） = 磁盘上的文件集合；表空间（Tablespace） = 逻辑存储单元；SID / Service Name = 连接时填的库名。Oracle 连接默认端口 1521。

3.2 SQL 基础与 PL/SQL：和 MySQL 不一样的地方

引子：Oracle 的 SQL 和 MySQL 八成像，但处处有"自己一套"。下面这张差异表是重点，写 Oracle SQL 时别按 MySQL 的肌肉记忆来。

Oracle 与 MySQL 的关键语法差异
主题	MySQL	Oracle
整数/小数	`INT / BIGINT / DECIMAL(10,2)`	`NUMBER`（万能），`NUMBER(p,s)` 指定精度；没有独立 INT
变长字符串	`VARCHAR(n)`	`VARCHAR2(n)`（Oracle 专用，官方推荐）
大文本/二进制	`TEXT / BLOB`	`CLOB / BLOB`
日期	`DATE` 只有日期，`DATETIME` 带时间	`DATE` 自带时分秒；更精用 `TIMESTAMP`
自增主键	`AUTO_INCREMENT`	`GENERATED ... AS IDENTITY` 或 SEQUENCE 序列
字符串拼接	`CONCAT(a,b)` 或 `\|\|`	`a \|\| b`（标准写法）
分页	`LIMIT 10 OFFSET 20`	老版本 `ROWNUM`；12c+ 支持 `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`
无表查询	直接 `SELECT NOW()`	必须带 `FROM DUAL`：`SELECT SYSDATE FROM DUAL`
空字符串	`''` 是真字符串	`''` 被当成 `NULL`（著名坑）

Oracle 版建表 + 序列 + 插入

-- 建表：用 VARCHAR2、NUMBER、DATE
CREATE TABLE emp (
  id     NUMBER       PRIMARY KEY,
  name   VARCHAR2(50)  NOT NULL,
  salary NUMBER(10,2),
  hired  DATE
);

-- 序列：手动造自增值（Oracle 祖传写法）
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;

-- 插入：从序列取下一个值，字符串用 || 拼
INSERT INTO emp(id, name, salary, hired)
VALUES (emp_seq.NEXTVAL, '张' || '三', 18000, SYSDATE);

-- 分页（12c+ 标准写法）
SELECT * FROM emp
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 同义词：给长名字起个短别名
CREATE SYNONYM e FOR scott.emp;

-- 老系统里常见的 ROWNUM 分页（两层嵌套）
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY salary DESC
  ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

PL/SQL：Oracle 的过程化扩展

PL/SQL 是 Oracle 在 SQL 上套的一门过程语言，有变量、分支、循环、异常、游标，比 MySQL 的存储过程功能强得多。基本块长这样：

DECLARE
  v_sal   emp.salary%TYPE;          -- %TYPE：自动跟某列同类型
  v_emp   emp%ROWTYPE;              -- %ROWTYPE：整行记录类型
  v_name  VARCHAR2(50);
BEGIN
  SELECT salary INTO v_sal FROM emp WHERE id = 1;

  IF v_sal > 30000 THEN
    DBMS_OUTPUT.PUT_LINE('高薪: ' || v_sal);
  ELSE
    DBMS_OUTPUT.PUT_LINE('普通: ' || v_sal);
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没找到这个人');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出错了: ' || SQLCODE);
END;

PL/SQL 常用结构
结构	说明
`%TYPE / %ROWTYPE`	变量类型跟着表列/整行走，表结构改了不用改代码。
`显式游标 / 隐式游标 / 参数游标`	把查询结果集当指针一条条处理。
`EXCEPTION ... WHEN`	预定义异常（NO_DATA_FOUND、TOO_MANY_ROWS）+ 自定义异常。
`PROCEDURE / FUNCTION / PACKAGE`	存储过程、函数；PACKAGE 把相关过程、函数、变量打包成模块。

一个最小 Package（包头 + 包体）

-- 包头：声明接口
CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE raise_sal(p_id NUMBER, p_pct NUMBER);
  FUNCTION  get_sal(p_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

-- 包体：实现细节
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE raise_sal(p_id NUMBER, p_pct NUMBER) IS
  BEGIN
    UPDATE emp SET salary = salary * (1 + p_pct/100) WHERE id = p_id;
  END;
  FUNCTION  get_sal(p_id NUMBER) RETURN NUMBER IS
    v_sal emp.salary%TYPE;
  BEGIN
    SELECT salary INTO v_sal FROM emp WHERE id = p_id;
    RETURN v_sal;
  END;
END emp_pkg;
/

3.3 索引与优化：CBO、AWR、Hint

引子：Oracle 的优化体系比 MySQL 深得多。它的优化器叫 CBO（基于成本的优化器），会根据统计信息自己算一条 SQL 的最优执行计划。

Oracle 索引类型
索引类型	用在哪
B 树索引	默认，绝大多数场景。
位图索引	低基数列（性别、状态），数据仓库只读场景，OLTP 别用（锁粒度大）。
函数索引	`CREATE INDEX ON emp(UPPER(name))`，解决对列用函数导致索引失效。
分区索引	配合分区表，只扫相关分区，大表神器。

看执行计划 + Hint 干预 + 绑定变量

-- 看执行计划（先 EXPLAIN PLAN，再 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)）
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE dept_id = 3;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Hint：强迫优化器按你的来（一般别滥用）
SELECT /*+ INDEX(emp idx_dept) */ * FROM emp WHERE dept_id = 3;

-- 绑定变量：硬解析变软解析，性能飞跃、防注入
SELECT * FROM emp WHERE id = :v_id;

AWR 报告是 Oracle 诊断性能的法宝：自动快照采样数据库负载（默认每小时一次），生成一份报告告诉你 Top SQL、等待事件、瓶颈在哪。和它配套的是 ASH（Active Session History，活动会话历史）：AWR 是"每小时拍一张照"看宏观趋势，ASH 是"每秒都在录像"看当下——数据库突然变慢时，AWR 快照还没攒够，ASH 就能告诉你"这一秒哪些会话在等什么资源"。一个看趋势，一个抓现场，DBA 两个都要会。SQL Trace / 事件 10046 则能把一条 SQL 的每一步耗时打到 trace 文件里。这三样是 Oracle DBA 的看家本领。

绑定变量为什么重要

Oracle 对每条 SQL 都要做硬解析（生成执行计划），代价大。如果业务每次都拼新常量（id=1、id=2...），等于每次都重新解析，共享池被打爆。用绑定变量 id=:v_id，SQL 文本不变，执行计划可复用，硬解析变软解析，性能差几十倍。这也是防 SQL 注入的标配。

物化视图：把查询结果"存"下来，下次直接读

引子：有个报表查询要 JOIN 五张大表跑 30 秒，每天早上领导都要看一遍。每次都实时跑一遍，数据库累瘫。物化视图（Materialized View）就是把这个查询的结果物理存下来（不像普通视图只是一段 SQL），下次直接读这张存好的结果，毫秒出。这是数据仓库/报表场景的杀招，Oracle、PostgreSQL 都支持，MySQL 8.0 之前没有原生物化视图。

-- 建一个物化视图，把"各部门月度销售汇总"算好存起来
CREATE MATERIALIZED VIEW mv_dept_monthly_sales
REFRESH FORCE              -- 能快速刷新就增量，不能就全量
ON DEMAND                  -- 什么时候刷新（手动/定时）
AS
SELECT dept_id, TRUNC(sale_date, 'MM') AS month, SUM(amount) AS total
FROM sales
GROUP BY dept_id, TRUNC(sale_date, 'MM');

-- 手动刷新（生产上配个 DBMS_SCHEDULER 定时每天刷一次）
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_MONTHLY_SALES');

-- 查询时直接读物化视图，不用再跑那 30 秒的大 JOIN
SELECT * FROM mv_dept_monthly_sales WHERE dept_id = 3;

物化视图不是实时的

物化视图最大的坑是"数据不是最新的"。它存的是上次刷新那一刻的快照，源表改了它不知道。所以：① 对实时性要求高的业务别用，用普通视图；② 定时刷新的间隔就是数据延迟的上限；③ 全量刷新大表很慢，Oracle 支持增量刷新（基于日志 MVIEW LOG），复杂场景要单独配。一句话：拿"数据新鲜度"换"查询速度"，想清楚值不值。

3.4 事务、锁与闪回

Oracle 的 ACID 和 MySQL 一样讲，但默认隔离级别是读已提交（READ COMMITTED），不是可重复读。它也用 MVCC，读不堵写，但实现方式是用 undo 段构造一致性读。

锁与死锁

-- 行锁：SELECT ... FOR UPDATE 显式锁行
SELECT * FROM emp WHERE id = 1 FOR UPDATE;

-- 死锁：两个会话互相锁对方要的行，Oracle 报 ORA-00060
-- 会话 A：锁 id=1
UPDATE emp SET salary=1 WHERE id=1;
-- 会话 B：锁 id=2
UPDATE emp SET salary=2 WHERE id=2;
-- A 再去锁 id=2（等 B）；B 再去锁 id=1（等 A）→ 死锁 ORA-00060

论闪回技术：Oracle 的"后悔药"

Oracle 有一套独有的闪回（Flashback）家族，能把数据"倒带"：

闪回查询 Flashback Query：SELECT * FROM emp AS OF TIMESTAMP (SYSDATE-1/24); 查一小时前的数据长什么样。

闪回表：FLASHBACK TABLE emp TO TIMESTAMP(...) 把整张表恢复到过去某个点。

闪回删除：误删的表先进"回收站"，FLASHBACK TABLE emp TO BEFORE DROP; 能捞回来。这是 DBA 最喜欢的功能之一。

3.5 备份恢复：RMAN 与闪回

Oracle 的备份恢复工具叫 RMAN（Recovery Manager），是个客户端命令，专门管数据库的备份和还原，比 mysqldump 专业得多。

# RMAN 连接目标库
rman target /

# 全量备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 增量备份
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# 还原与恢复（先 mount，再 restore + recover）
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

sqlplus 常用命令

SQL> SHOW USER; <-- 当前用户 SQL> SELECT table_name FROM user_tables; <-- 我有哪些表 SQL> DESC emp; <-- 看表结构 SQL> SET LINESIZE 200; <-- 调宽输出 SQL> EXIT;

Oracle 备份恢复全家桶
工具/技术	用途
RMAN	专业物理备份/恢复，支持全量、增量、块级、并行。
ARCHIVELOG 归档模式	开启后所有 redo log 归档保存，是做 PITR 时间点恢复的前提。
数据泵 expdp / impdp	逻辑导出导入（比老 exp/imp 快），迁数据、迁 schema 用。
Flashback Database / Table / Drop	不用备份，直接把库/表倒带回到过去某点。

3.6 高可用：Data Guard 与 RAC

Oracle 高可用三剑客
技术	干什么
Data Guard	主库 + 备库（物理备库做块级复制，逻辑备库传 SQL）。主库挂了切到备库，做容灾。
RAC 真正应用集群	多台服务器共享一套存储，跑同一个库，一台挂了别的继续，是"集群级"高可用。
GoldenGate	异构数据同步工具，能在 Oracle、MySQL、PG 之间实时复制数据。

3.7 Oracle 面试重点 15 题

Oracle 面试 15 题（折叠答案）

1. Oracle 和 MySQL 最大的区别？

答案

Oracle 商业付费、功能强、支持 PL/SQL、RAC/Data Guard；MySQL 开源轻量、互联网生态大、默认 InnoDB。语法上 Oracle 用 NUMBER/VARCHAR2、序列、DUAL、|| 拼接。

2. 实例和数据库的区别？

答案

实例=内存(SGA)+后台进程；数据库=磁盘上的物理文件。一个实例同一时间只能挂载打开一个数据库。

3. 什么是表空间？

答案

表空间是逻辑存储单元，由一个或多个数据文件组成。表、索引都存在表空间里。

4. VARCHAR2 和 VARCHAR 区别？

答案

Oracle 官方推荐 VARCHAR2，语义由 Oracle 定义（空串=NULL）；VARCHAR 标准行为未来可能变，别用。

5. ROWNUM 怎么分页？

答案

老版本用两层嵌套：内层先 ROWNUM rn 排序，外层 WHERE rn BETWEEN 21 AND 30。12c+ 直接用 OFFSET FETCH。

6. 序列的 NEXTVAL 和 CURRVAL？

答案

NEXTVAL 取下一个值；CURRVAL 取当前会话刚生成的值（用之前必须先 NEXTVAL 过）。

7. CBO 是什么？

答案

基于成本的优化器，根据表统计信息估算各执行计划代价，选最小的。统计信息过时会导致选错执行计划。

8. 绑定变量为什么重要？

答案

避免反复硬解析，软解析复用执行计划，提升共享池效率和并发，同时防 SQL 注入。

9. AWR 是干嘛的？

答案

自动负载信息库，按快照采样数据库负载，生成性能报告定位 Top SQL、等待事件。

10. Oracle 默认隔离级别？

答案

读已提交 READ COMMITTED。也支持可重复读和串行化。

11. ORA-00060 是什么错？

答案

死锁。两个会话互相持有对方要的锁。Oracle 自动检测并回滚其中一个。

12. 闪回查询怎么用？

答案

SELECT ... FROM 表 AS OF TIMESTAMP ... 查过去某时刻的数据，靠 undo 段构造一致性读。

13. RMAN 干什么？

答案

Oracle 官方备份恢复工具，做全量/增量备份、还原、恢复。

14. Data Guard 和 RAC 区别？

答案

Data Guard 是主备容灾（异地、数据冗余）；RAC 是多实例共享存储的集群（同地、高可用高并发）。

15. 位图索引用在哪？

答案

低基数列（性别、状态）、数据仓库只读场景。OLTP 高并发写别用，锁粒度太大。

← 上一节数据库三剑客 · 课程总览下一节 →