Oracle数据库面试题及详细答案（实战完整版）

Oracle数据库面试题及详细答案（实战完整版）一、Oracle基础概念题1、简述Oracle数据库的体系结构Oracle整体分为数据库实例和数据库文件两大部分，是典型的单实例多进程架构。1）数据库实例：由内存结构和后台进程组成，是用户操作数据库的核心载体。内存结构包含SGA（系统全局区）和PGA（程序全局区），SGA共享给所有用户进程，PGA为单个进程私有。2）数据库文件：物理存储文件，核心包含数据文件、控制文件、重做日志文件、参数文件、归档日志文件。数据文件存储真实业务数据，控制文件记录数据库结构信息，重做日志记录所有数据修改操作，用于故障恢复。2、SGA和PGA的区别1）SGA：系统全局区，属于共享内存，数据库启动时分配、关闭时释放。所有连接数据库的用户进程、后台进程都可以共享使用，主要包含数据缓冲区、日志缓冲区、共享池、大池、Java池等，核心作用是缓存数据、SQL语句、执行计划，提升数据库访问效率。2）PGA：程序全局区，属于私有内存，每个客户端连接进程独立分配，进程断开后自动释放。不共享，主要存储会话私有数据，比如变量、排序数据、游标状态、堆栈信息等。3、Oracle表空间的作用及分类表空间是Oracle逻辑存储单元，是数据文件的容器，所有表、索引、视图等对象都存储在表空间中。通过表空间可以统一管理数据存储、权限、磁盘配额，方便数据迁移和备份恢复。常用分类：1）系统表空间：SYSTEM、SYSAUX，存储系统数据字典、元数据、系统组件数据，不建议存储业务数据；2）临时表空间：TEMP，用于排序、分组、临时表操作，断电或会话结束后数据清空；3）回滚表空间：UNDOTBS，存储undo数据，保障事务回滚、读一致性；4）用户自定义表空间：专门存放业务表和索引，实现业务数据与系统数据隔离。4、Oracle常用数据类型有哪些1）字符类型：CHAR（固定长度字符，适合固定长度数据，查询效率高）、VARCHAR2（可变长度字符，节省存储空间，业务最常用）、CLOB（大文本类型，存储超长文字）；2）数值类型：NUMBER（通用数值类型，可存整数、小数，可指定精度）、INTEGER（整数）、FLOAT（浮点数）；3）日期类型：DATE（精确到秒）、TIMESTAMP（精确到微秒）；4）二进制类型：BLOB（存储图片、文件、视频等二进制数据）。二、SQL查询实战题（高频）1、WHERE和HAVING的区别1）过滤时机不同：WHERE是分组之前过滤数据行，HAVING是分组之后过滤分组结果；2）使用范围不同：WHERE不能使用聚合函数（SUM、COUNT、MAX等），HAVING专门用于过滤聚合后的结果，支持聚合函数；3）执行顺序：WHERE筛选数据→GROUPBY分组→聚合计算→HAVING筛选分组结果；4）优先级：优先用WHERE过滤原始数据，减少分组计算的数据量，提升效率，HAVING仅用于分组后的二次筛选。2、UNION和UNIONALL的区别1）去重差异：UNION会自动去除重复数据，UNIONALL直接拼接所有结果，不去重；2）效率差异：UNION需要排序去重，性能较低；UNIONALL无排序、无去重操作，性能远高于UNION；3）排序规则：UNION会默认按字段排序，UNIONALL保持原有查询顺序；实际开发中，确认数据无重复时，优先使用UNIONALL。3、IN和EXISTS的区别及使用场景核心原理：IN是先执行子查询，再匹配主查询；EXISTS是遍历主查询，逐行匹配子查询。1）子查询数据量小、主查询数据量大：优先用IN，效率更高；2）子查询数据量大、主查询数据量小：优先用EXISTS，避免IN子查询结果集过大导致性能卡顿；3）空值差异：IN对子查询空值敏感，会返回空结果；EXISTS不受空值影响；4）NOTIN和NOTEXISTS：NOTEXISTS效率远高于NOTIN，NOTIN存在空值陷阱，开发中禁用NOTIN，统一用NOTEXISTS。4、Oracle分页查询写法（经典面试）Oracle无LIMIT关键字，通过ROWNUM伪列实现分页，分为基础分页和高效分页：1）基础分页（适合小数据量）PlainText

SELECT*FROM(

SELECTROWNUMrn,t.*FROM表名tWHEREROWNUM<=结束行号

)WHERErn>起始行号;2）高效分页（大数据量推荐，先排序再分页，避免ROWNUM排序错乱）PlainText

SELECT*FROM(

SELECTROWNUMrn,t.*FROM(

SELECT*FROM表名ORDERBY排序字段DESC

)tWHEREROWNUM<=结束行号

)WHERErn>起始行号;注意：分页必须内层先排序，否则ROWNUM生成的行号无序，分页结果错乱。5、如何查询表中重复数据，如何删除重复数据假设重复依据为字段name，保留id最小的一条数据：1）查询重复数据PlainText

SELECTname,COUNT(*)FROM表名GROUPBYnameHAVINGCOUNT(*)>1;2）删除重复数据PlainText

DELETEFROM表名t1WHEREt1.id>(

SELECTMIN(t2.id)FROM表名t2WHERE=

);三、Oracle常用函数1、NVL和NVL2的用法1）NVL(字段,默认值)：判断字段是否为空，为空则返回默认值，不为空返回原值。常用于空值兜底，避免计算报错。例：NVL(score,0)，分数为空则赋值0。2）NVL2(字段,值1,值2)：三段式判断，字段非空返回值1，字段为空返回值2。例：NVL2(score,'已评分','未评分')。2、DECODE函数用法Oracle特有条件判断函数，简化CASEWHEN写法，语法简洁、执行效率高。语法：DECODE(字段,匹配值1,结果1,匹配值2,结果2,默认值)示例：DECODE(sex,'1','男','2','女','未知')3、ROW_NUMBER、RANK、DENSE_RANK排序函数区别1）ROW_NUMBER()：连续不重复排名，无论值是否相同，排名依次递增（1,2,3,4），适合分页、取第一条数据；2）RANK()：跳跃排名，值相同排名相同，后续排名跳过（1,2,2,4）；3）DENSE_RANK()：连续排名，值相同排名相同，后续排名连续递增（1,2,2,3）。四、索引相关面试题（核心高频）1、Oracle常用索引类型及适用场景1）B树索引：默认索引类型，适合等值查询、范围查询（><、BETWEEN），适用于重复值少的字段，业务最常用；2）位图索引：适合字段重复值极高、基数低的字段（如性别、状态），不适合频繁更新的字段，更新锁表严重；3）唯一索引：保证字段值唯一，禁止重复数据，可用于主键、唯一业务字段；4）复合索引：多个字段联合索引，遵循最左前缀原则，查询条件匹配左侧字段才会触发索引；5）函数索引：对字段函数运算结果建索引，适合经常使用函数查询的场景（如UPPER、SUBSTR）。2、什么情况下索引会失效1）违反最左前缀原则，复合索引未使用最左侧字段；2）索引字段使用函数、运算、类型转换（如WHERESUBSTR(name,1,1)='张'、age+1>10）；3）使用!=、<>、NOTIN、ISNOTNULL（索引字段为空值不存储在索引中）；4）LIKE模糊查询以%开头（如'%测试'）；5）表数据量过小，数据库认为全表扫描比索引查询更快，自动放弃索引；6）统计信息过期，导致执行计划错误，未走索引；7）索引字段存在大量空值，查询条件匹配空值。3、索引一定能提升查询效率吗？为什么不一定。索引仅能提升查询、少量更新效率，会降低新增、修改、删除效率。原因：表数据发生DML操作时，不仅要更新表数据，还要同步更新索引结构，索引越多，维护成本越高。同时，小表、全表数据查询、大量重复值字段建索引，反而会增加数据库开销，导致性能下降。五、事务与锁机制1、Oracle事务四大特性（ACID）1）原子性：事务要么全部执行成功，要么全部回滚，不可部分执行；2）一致性：事务执行前后，数据库数据状态保持合法一致，不会出现中间异常状态；3）隔离性：多个事务并发执行时，相互独立互不干扰；4）持久性：事务提交后，数据永久写入数据库，断电、故障不会丢失。2、Oracle事务隔离级别Oracle仅支持两种核心隔离级别，默认读已提交：1）读已提交（READCOMMITTED）：默认级别，事务只能读取已提交的数据，解决脏读，存在不可重复读、幻读；2）可重复读（SERIALIZABLE）：最高级别，事务执行期间，数据读取结果一致，杜绝脏读、不可重复读、幻读，并发性能极低，极少使用。补充：Oracle通过undo回滚段实现读一致性，查询不会阻塞写入，写入不会阻塞查询。3、行锁和表锁的区别，如何避免死锁1）行锁：DML操作（UPDATE、DELETE）默认行级锁，仅锁定操作的数据行，其他行可正常操作，并发性能高，事务提交后自动释放；2）表锁：锁定整张数据表，其他会话无法操作表中任何数据，并发极差，一般由DDL语句、手动加锁产生；3）死锁避免方案：统一多表操作顺序、缩短事务执行时间、避免长事务、合理设置超时时间。六、数据库优化与调优1、SQL优化的常用思路1）优先优化慢查询，通过执行计划定位低效SQL；2）避免索引失效场景，合理建立索引，删除无用、冗余索引；3）杜绝SELECT*，只查询业务所需字段，减少数据传输开销；4）大表分页、统计避免全表扫描，使用索引分页、分区查询；5）优先使用EXISTS、UNIONALL，替代IN、UNION；6）拆分长SQL、复杂子查询，简化执行逻辑；7）定期更新数据库统计信息，保证执行计划准确。2、大表优化方案1）表分区：按时间、ID范围做分区，查询仅扫描对应分区，减少扫描数据量；2）冷热数据分离：历史冷数据迁移至归档表，业务表仅保留热数据；3）合理建索引，清理无效索引，减少DML开销；4）开启并行查询，针对大表统计、批量查询提升效率；5）定期碎片整理，回收表空间碎片，提升读写性能。3、如何查看SQL执行计划常用两种方式：1）PL/SQLDeveloper工具：选中SQL，直接点击执行计划按钮，直观查看扫描方式、索引使用情况、耗时；重点关注：是否全表扫描、索引是否生效、表连接方式、排序开销。七、备份恢复与日常运维1、Oracle常用备份方式1）逻辑备份：EXPDP/IMPDP（数据泵），导出表、用户、整库数据，适合数据迁移、小批量备份，不备份日志文件；2）物理备份：RMAN备份，企业主流备份方式，支持增量备份、全量备份、断点续备，可基于归档日志实现时间点恢复，数据安全性最高。2、归档日志的作用归档日志是重做日志的归档副本，开启归档模式后，重做日志切换时会自动归档保存。核心作用：实现数据库时间点恢复，RMAN增量备份、数据同步、灾备恢复都依赖归档日志，生产环境必须开启归档模式。八、高频场景简答题1、主键和唯一索引的区别1）主键是非空且唯一，字段不允许为空；唯一索引仅保证唯一，允许存在一个空值；2）一张表只能有一个主键，可创建多个唯一索引；3）主键创建后会自动生成唯一索引，唯一索引需要手动创建；4）主键属于表约束，优