Oracle高频面试题及详细答案（实战版）

Oracle高频面试题及详细答案（实战版）一、基础概念篇1、简述Oracle数据库的体系结构，核心组成有哪些？参考答案：Oracle体系结构主要分为三大核心部分：数据库实例、数据库文件、网络服务，日常工作核心关注实例和文件两大模块。1）数据库实例（Instance）：是Oracle运行在内存中的临时机制，由内存结构和后台进程组成，实例启动后才能加载和操作数据库。核心包含SGA（系统全局区）和PGA（程序全局区），以及各类后台进程。2）数据库文件（物理文件）：永久存储数据的物理文件，核心包括：数据文件（存储表、索引等业务数据）、控制文件（记录数据库结构、文件位置、日志信息）、重做日志文件（记录所有数据修改操作，用于故障恢复）、参数文件、密码文件等。3）网络服务：用于客户端和数据库服务端通信，核心是TNS监听。简单来说：实例是内存+进程，负责干活；数据文件是磁盘文件，负责存数据。2、SGA和PGA的区别是什么？各自包含哪些核心区域？参考答案：1）SGA（系统全局区）：共享内存区域，所有用户进程、后台进程共享使用，数据库启动时分配，关闭时释放。核心组成：数据缓冲区（DBBufferCache）：缓存磁盘读取的数据块，减少磁盘IO，提升查询效率；重做日志缓冲区（RedoLogBuffer）：缓存用户的增删改操作日志，定时写入重做日志文件；共享池（SharedPool）：核心包含库缓存（缓存SQL语句、执行计划）、数据字典缓存（缓存表结构、权限等元数据）；大池、Java池（辅助内存区域，用于大数据操作、Java程序调用）。2）PGA（程序全局区）：私有内存区域，每个客户端连接独享，连接创建时分配，断开时释放，进程之间互不干扰。核心组成：排序区、哈希区、会话变量、堆栈空间等，主要用于SQL执行过程中的排序、关联、数据临时处理。核心区别：SGA是共享内存，全局通用；PGA是私有内存，单连接独占。3、Oracle事务的四大特性是什么？分别解释？参考答案：事务四大特性简称ACID，是数据库数据一致性的核心保障：原子性（Atomicity）：事务是最小执行单元，要么全部执行成功，要么全部回滚失败，不存在部分执行的情况。比如转账业务，扣款和入账必须同时成功或同时失败。一致性（Consistency）：事务执行前后，数据库数据的完整性、约束性保持一致，不会出现数据错乱、约束失效的情况。比如主键唯一、外键关联、字段约束不会被事务破坏。隔离性（Isolation）：多个并发事务之间相互隔离，互不干扰，一个事务的执行不会影响其他并发事务的数据读写。持久性（Durability）：事务提交成功后，数据修改永久生效，即使数据库宕机、服务器重启，数据也不会丢失，依靠重做日志保障。4、Oracle有哪些事务隔离级别？默认级别是什么？参考答案：Oracle一共支持3种事务隔离级别，区别于MySQL的4种，不支持读未提交：读提交（ReadCommitted）：Oracle默认隔离级别。事务只能读取其他事务已提交的数据，可避免脏读，但会出现不可重复读、幻读。可重复读（Serializable）：最高隔离级别，事务启动后，读取的数据快照固定，杜绝脏读、不可重复读、幻读，但并发性能最低，容易产生锁等待。只读隔离（ReadOnly）：事务内只能查询数据，不能执行增删改操作，多用于数据统计、报表查询。日常业务90%场景使用默认的读提交级别，兼顾数据一致性和并发性能。二、SQL查询与实战篇1、UNION和UNIONALL的区别？各自使用场景？参考答案：两者都是用于合并多条SELECT查询结果，核心区别在去重和性能：UNION：合并结果后自动去重、排序，底层会执行排序、比对去重逻辑，性能较差；UNIONALL：直接拼接所有查询结果，不去重、不排序，无额外运算，性能远高于UNION。使用场景：业务明确需要剔除重复数据，结果有序：用UNION；数据无重复、允许重复数据存在，追求查询性能：优先用UNIONALL（工作中最常用）。2、IN和EXISTS的区别？什么场景用哪个？参考答案：核心原理：IN是先执行子查询，再匹配主查询；EXISTS是遍历主查询，逐行校验子查询是否存在数据。场景选型：主表数据量大，子表数据量小：优先用IN。子查询结果集小，匹配效率高；主表数据量小，子表数据量大：优先用EXISTS。无需加载子表全部数据，逐行校验，节省内存；特殊注意：IN对子查询NULL值敏感，子查询返回NULL时，整体查询结果为空；EXISTS不受NULL值影响。补充：Oracle新版本优化了IN和EXISTS的执行计划，性能差距缩小，但大数据量场景下的选型规则依然适用。3、LEFTJOIN、RIGHTJOIN、INNERJOIN的区别？参考答案：INNERJOIN（内连接）：只返回两张表中匹配关联条件的数据，两边无匹配的数据全部过滤，是最常用的连接方式；LEFTJOIN（左连接）：以左表为基准，返回左表所有数据，右表匹配不到的字段填充NULL；RIGHTJOIN（右连接）：以右表为基准，返回右表所有数据，左表匹配不到的字段填充NULL；工作中基本不用RIGHTJOIN，所有右连接场景都可以改写为左连接，可读性更强。4、如何实现Oracle分页查询？写出通用语句参考答案：Oracle无LIMIT关键字，分页依赖ROWNUM伪列，分两种场景：1）基础分页（通用适配所有版本）sql

--查询第10-20条数据

SELECT*FROM(

SELECTROWNUMrn,t.*FROM业务表tWHEREROWNUM<=20

)WHERErn>10;2）排序分页（必写，避免数据错乱）sql

--分页必须内层排序，否则ROWNUM分配无序

SELECT*FROM(

SELECTROWNUMrn,t.*FROM(

SELECT*FROM业务表ORDERBY排序字段DESC

)tWHEREROWNUM<=20

)WHERErn>10;核心注意点：分页查询必须内层排序，否则多次分页会出现数据重复、遗漏的问题。三、索引优化篇1、Oracle常用索引类型及适用场景？参考答案：B树索引（默认索引）：最常用，适合等值查询、范围查询（><、between），有序存储，查询效率稳定，普通业务字段优先创建B树索引；唯一索引：基于B树结构，限制字段值唯一（可存一个NULL），用于手机号、身份证、订单号等唯一字段；复合索引（联合索引）：多个字段组合创建的索引，遵循最左前缀原则，适合多字段联合查询场景；位图索引：适合字段基数低（重复值多）的场景，比如性别、状态、类型字段，查询快，但增删改效率极低，仅适用于静态数据表；函数索引：针对查询中使用函数的字段创建，比如upper(name)、substr(phone,1,3)，解决函数导致的索引失效问题。2、什么是索引最左前缀原则？复合索引如何避免失效？参考答案：复合索引(a,b,c)，查询条件必须包含最左侧的a字段，索引才会生效，跳过最左字段直接查b、c会导致索引失效。索引失效常见场景（高频考点）：不满足最左前缀原则；索引字段使用函数、运算、模糊查询前置匹配（like'%xxx'）；字段隐式类型转换（字符串字段传数字、数字字段传字符串）；OR连接非索引字段、NULL判断不当；数据量过少、全表扫描成本低于索引扫描，数据库自动放弃索引。3、索引是不是越多越好？为什么？参考答案：不是，索引是查询快、写入慢，过多索引会严重影响数据库性能。弊端：增删改数据时，不仅要更新数据表，还要同步更新所有关联索引，写入耗时大幅增加；索引会占用磁盘空间，大量冗余索引浪费存储资源；过多索引会增加优化器选择执行计划的开销，可能导致执行计划出错。索引创建原则：高频查询字段建索引、高频写入字段少建索引、避免创建冗余重复索引。四、锁与并发篇1、Oracle常见锁类型有哪些？行锁和表锁的区别？参考答案：Oracle锁核心分为行级锁和表级锁，无页级锁，并发性能优于其他数据库。1）行锁：锁定单条或多条数据行，粒度最细，并发度最高。执行UPDATE、DELETE、SELECTFORUPDATE时触发，只锁定操作数据，其他行可正常读写，事务提交后自动释放。2）表锁：锁定整张数据表，粒度粗，并发度低。手动LOCKTABLE、DDL操作（建表、改字段、删表）、行锁升级时触发，锁定期间整张表无法写入。日常业务锁等待问题，90%都是行锁未及时释放（事务未提交/回滚）导致。2、什么是死锁？死锁产生的条件及解决办法？参考答案：死锁定义：两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，导致事务永久阻塞的现象。死锁四大必要条件：互斥条件、请求保持条件、不可剥夺条件、循环等待条件。解决和规避方案：统一业务SQL执行顺序，避免交叉锁等待；缩短事务周期，增删改后及时提交，不长时间占用锁；避免单事务操作多张关联表，减少锁竞争；出现死锁后，Oracle会自动检测并终止其中一个事务，释放锁资源，无需人工干预，只需排查业务代码优化逻辑。五、数据库优化篇1、SQL慢查询优化的通用步骤？参考答案：工作中标准化优化流程，简单高效：定位慢SQL：通过v$sql、AWR报告、日志工具抓取执行耗时高、逻辑读多的SQL；查看执行计划：explainplan分析SQL，重点看是否全表扫描、索引失效、排序量大、表连接不合理；优化SQL语句：避免索引失效场景、优先用UNIONALL替代UNION、合理选择IN/EXISTS、减少子查询嵌套、避免SELECT*；优化索引：缺失高频查询索引则新增，冗余索引删除，失效索引重建；优化表结构：大表分表、冷热数据分离、字段类型精简；数据库参数调优：调整SGA、PGA大小，优化排序区、连接池参数。2、大表查询优化有哪些方案？参考答案：针对千万、亿级数据大表，常用落地优化方案：分区表：按时间、月份、区域分区，查询仅扫描对应分区，减少扫描数据量（最常用）；索引优化：创建精准复合索引、函数索引，避免全表扫描；冷热数据分离：历史冷数据迁移至归档表，业务表仅保留热数据；分页、分批查询：避免一次性查询大量数据，防止内存溢出；物化视图：针对固定报表、统计查询，预计算结果，定时刷新，替代实时聚合查询；禁止SELECT*：只查询所需字段，减少IO和数据传输量。六、运维故障篇1、数据库宕机后，重启恢复的原理是什么？参考答案：Oracle重启会自动执行实例恢复，依靠重做日志（redolog）和回滚日志（undolog）完成，分为三步：前滚（RollForward）：读取重做日志，将已提交但未写入数据文件的操作，同步到磁盘数据文件中，恢复所有已完成事务的数据；打开数据库：前滚完成后，数据库对外提供访问；回滚（RollBack）：读取undo日志，回滚宕机时未提交、中断的事务，释放锁资源，保证数据一致性。整个过程全自动执行，无需人工干预，也是事务持久性的核心保障。2、如何查询Oracle当前锁等待、阻塞会话？参考答案：工作中常用实时查询语句，快速定位阻塞源头：sql

--查询锁阻塞、等待会话

SELECT

s.sid,s.serial#,s.username,gram,

b.sid阻塞SID,b.serial#阻塞SERIAL

FROMv$sessions

LEFTJOINv$locklONs.sid=l.sid

LEFTJOINv$sessionbONl.blocking_session=b.sid

WHEREl.blocking_sessionISNOTNULL;解锁操作：定位阻塞会话后，执行语句杀掉阻塞进程sql

ALTERSYSTEMKILLSESSION'sid,serial#';3