Oracle数据库面试题｜2025最新全解析

2.1 经典面试题：删除重复数据（保留最新一条）

某表user_log结构如下：

CREATE TABLE user_log (
  id NUMBER,
  user_id NUMBER,
  action VARCHAR2(50),
  created_at DATE
);

要求： 删除重复的(user_id, action)组合，仅保留created_at最新记录

DELETE FROM user_log
WHERE ROWID NOT IN (
  SELECT MAX(ROWID)
  FROM user_log
  GROUP BY user_id, action
);

DELETE FROM user_log a
WHERE created_at < (
  SELECT MAX(created_at)
  FROM user_log b
  WHERE a.user_id = b.user_id
    AND a.action = b.action
);

2.2 经典面试题：分组取TOP N

需求： 每个部门工资最高的前3名员工

SELECT deptno, ename, sal
FROM (
  SELECT deptno, ename, sal,
         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn
  FROM emp
)
WHERE rn <= 3;

3.1 SQL性能陷阱TOP 5

3.2 网友真实经历

“面试官问：‘为什么这个查询用了索引却仍很慢？’ 我答：‘可能索引选择性差’。实际答案是——索引字段有函数处理，且WHERE条件未加SARGABLE谓词。” —— @上海· 猎头推荐

2.1 真实案例：工资调整存储过程

需求： 根据部门绩效系数调整工资，失败时回滚

CREATE OR REPLACE PROCEDURE adjust_salary (
  p_deptno   IN NUMBER,
  p_ratio    IN NUMBER
) AUTHID CURRENT_USER IS
  v_count    NUMBER;
BEGIN
  -- 验证部门存在
  SELECT COUNT(*) INTO v_count FROM dept WHERE deptno = p_deptno;
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '部门ID无效：' || p_deptno);
  END IF;
  -- 更新工资（避免死锁：按empno排序）
  UPDATE emp
  SET sal = sal * p_ratio
  WHERE deptno = p_deptno
  ORDER BY empno;
  DBMS_OUTPUT.PUT_LINE('已调整 ' || SQL%ROWCOUNT || ' 名员工工资');
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20099, '工资调整失败：' || SQLERRM);
END;

2.2 包（Package）设计最佳实践

3.1 PL/SQL性能优化三板斧

3.2 网友血泪教训

“面试时写了个FOR循环更新10万行数据，面试官：‘你确定生产环境能跑完？’” —— @广州· 应届生

2.1 SQL重写实战：从全表扫描到索引优化

原始SQL：

SELECT *
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.order_id
    AND oi.quantity > 100
);

问题：子查询未UNNEST，导致嵌套循环性能差

优化方案：

1. 改写为LEFT JOIN + IS NULL（更易被优化器接受）
2. 添加函数索引：CREATE INDEX idx_oi_qty ON order_items(quantity);
3. 使用HINT强制UNNEST

SELECT o.*
FROM orders o
LEFT JOIN (
  SELECT DISTINCT order_id FROM order_items WHERE quantity > 100
) oi ON o.order_id = oi.order_id
WHERE oi.order_id IS NULL;

2.2 执行计划关键操作码速查

3.1 内存结构核心考点

3.2 PGA溢出典型场景

3.3 网友真实案例

“数据库CPU 100%，AWR显示‘SQL*Net message from client’等待事件异常。定位发现：应用未关闭游标，导致PGA泄漏。” —— @深圳· DBA

2.1 Data Guard三种保护模式

2.2 物理备库 vs 逻辑备库

3.1 ASM磁盘组类型

3.2 ASM常见故障处理

• ORA-15042： 磁盘组空间不足 → 增加磁盘或清理
• ORA-15186： 磁盘组未挂载 → CHECK DISKGROUP STATUS
• ASM实例崩溃： 检查ASM alert日志（+ASM/alert_+ASM1.log）

2.1 成本优化策略

2.2 典型误区

• 误区： 云上使用Oracle Standard Edition可无限扩展CPU
  真相： SE2最大支持2个物理CPU插槽（非核心数）
• 误区： 自动扩展（Auto Scaling）适用于数据库
  真相： 仅适用于应用层，数据库需手动扩容或使用RAC

3.1 云环境安全加固

3.2 合规性要求

• 等保2.0： 需启用数据库审计（AUDIT_TRAIL=DB,EXTENDED）
• 《个人信息保护法》： 敏感字段必须加密（如身份证、手机号）
• 金融行业： 数据库备份需异地双活（OCI跨区域复制）

开发岗真题

1. 问题： PL/SQL中如何避免ORA-01422错误？
   要点： 使用FETCH INTO前检查%NOTFOUND，或用BULK COLLECT限制行数
2. 问题： 解释MERGE语句的USING子句作用。
   要点： 定义源表，实现INSERT/UPDATE/DELETE的组合操作（UPSERT）
3. 问题： 如何优化含DISTINCT的慢查询？
   要点： 检查是否可改用GROUP BY，或创建函数索引

架构师真题

1. 问题： 设计一个高可用Oracle架构，要求RPO<10秒，RTO<5分钟。
   要点： RAC + Data Guard Maximum Availability模式 + GoldenGate灾备，配合Active Data Guard只读查询
2. 问题： 如何评估迁移至云数据库的成本？
   要点： 对比本地硬件成本（服务器/存储/ licenses）、云服务费（OCPUs/存储/网络）、迁移成本、运维成本
3. 问题： 分布式事务如何保证一致性？
   要点： 使用Oracle Two-Phase Commit（2PC），但需注意性能瓶颈；现代方案倾向Saga模式+事件驱动

2.1 免费工具推荐

3.1 学习路径建议

2.1 职业发展路径

关键建议： 30岁前积累扎实技术能力，30岁后向架构设计与业务理解延伸

3.1 非科班转行建议

• 优势： 逻辑思维强、学习能力好（如数学/物理专业）
• 路径： 先考OCA → 参与开源项目 → 从测试/运维转DBA
• 避坑： 避免“速成班”，选择实战项目驱动学习（推荐GitHub数据库项目）