Oracle面试总结_oracle面试题

1.体系结构

1.1 DATA BLOCK，EXTENT和SEGMENT的区别是什么？

答案：DATA BLOCK是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的DATA BLOCK就组成了EXTENT。一个数据库对象拥有的所有EXTENT被称为该对象的SEGMENT。

1.2 在Oracle中，数据块、Redo日志块及控制文件数据块的大小分别是多少？如何查询？

1）数据块（Data Block），是读写数据文件的最小单位，默认是8KB，可以通过SQL语句“SELECT FILE#,NAME,BLOCK_SIZE FROM V$DATAFILE;”查询，单位为BYTE。2）Redo日志数据块（RedoLogBlock），大小一般等于操作系统的系统块的大小，一般为512或4096，可以通过SQL语句“SELECTBLOCKSIZEFROMV$LOG;”或“SELECT LEBSZ FROM X$KCCLE;”查询，单位为BYTE。3）控制文件数据块（ControlFileBlock），默认为16KB，可以通过SQL语句“SELECTBLOC{K}_{S}IZEFROMV$CONTROLFILE;”查询，单位为BYTE。

1.3 行链接和行迁移有什么区别？

答案：当一行的数据过长而不能存储在单个数据块中时，可能发生两种事情：行链接（Row Chaining）或行迁移（Row Migration）。
1）行链接（Row Chaining）：当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。在这种情况下，Oracle会使用与该块链接的一个或多个数据块来容纳该行的数据。行链接经常在插入比较大的行时才会发生，例如包含LONG、LONG ROW、LOB等类型的数据。在这些情况下，行链接是不可避免的。行链接通常由INSERT操作引起。
2）行迁移（Row Migration）：当一个行上的更新操作导致当前的数据增加以致于不能再容纳在当前块，这个时候就需要进行行迁移，在这种情况下，Oracle将会迁移整行数据到一个新的数据块中。一个行迁移意味着整行数据都将会移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。发生行迁移的时候行的ROWID不会改变。行迁移的情况主要是由于表上的PCTFREE参数设置过小导致，所以必须设置一个合适的PCTFREE参数。可以使用exp/imp工具导入导出来处理行迁移。行迁移通常由UPDATE操作引起。

1.4 如何解决ORA-04030和ORA-04031错误？

1）是否仍然有足够的可用内存？
2）是否设置了操作系统限制？
3）是否设置了Oracle限制？
4）哪个进程需要的内存过多？

1.5 进程mman、mmnl和mmon这3个进程的作用分别是什么？

1）MMAN（Memory Manager Process，内存管理进程）进程会随着时间推移，根据系统负载的变化和内存需要，自动调整SGA中各个组件的内存大小。
2）MMON（Manageability Monitor Process，可管理性监视器进程）和它的slave进程（Mnnn）主要用来维护AWR信息和各种与可管理性相关的后台任务，具体包括：①启动slave进程Mnnn去做AWR快照。若MMON进程HANG住，则AWR不可用。②当某个测量值（metrics）超过了其度量阀值（threshold value）时发出ALERT告警。③为最近改变过的SQL对象捕获指标信息。
3）MMNL（Manageability Monitor Lite Process）将SGA中的ASH（Active Session History）Buffer中的统计资料写到磁盘。当ASH Buffer满的时候MMNL会把它写到磁盘上。

1.6 什么是检查点？如何调优检查点？

（1）什么是检查点？ 一般所说的检查点是一种将内存中的已修改数据块与磁盘上的数据文件进行同步的数据库事件（Event），是Oracle在数据库一致性关闭、实例恢复和Oracle基本操作不可缺少的机制。
（2）检查点调优 检查点的主要任务就是催促DBWn刷新脏块，如果DBWn刷新脏块时的等待事件太多，那么就说明脏块太多、存储设备的写速度太慢，或者就是增量检查点的频率设置不合理。DBWn写脏块的等待事件是db file parallel write。如果系统增量检查点频率很低，系统大量产生该事件，在排除了存储设备写性能的问题后，那么应该将增量检查点频率设置得高一些。反之，如果增量检查点频率本身很高，若出现了db file parallel write事件，则说明检查点频率太高了。

1.7 什么是大文件表空间（Bigfile Tablespace）？

答案：从Oracle 10g开始推出了大文件表空间的概念，即表空间从Oracle 10g以后就分为两种类型，小文件表空间（Smallfile Tablespace）和大文件表空间（Bigfile Tablespace）。所谓大文件表空间最显著的特点就是一个表空间只能对应一个数据文件。大文件表空间虽只对应一个数据文件，但数据文件对应的最大体积大大增加，可以达到32TB。传统的小文件表空间中每个数据文件最大文件大小为32G，但每个小文件表空间理论上能够包括1024个数据文件，所以，小文件表空间的最大值为32TB大小。所以，理论上小文件表空间和大文件表空间总容量相同。

1.8 本地管理表空间（LMT）和字典管理表空间（DMT）的特点有哪些？

字典管理表空间（Dictionary Managed Tablespace，DMT），它是Oracle 8i及以前版本使用的一种表空间管理模式，不过在Oracle 8i及以后的版本中仍然保存有该特性。DMT是通过数据字典管理表空间的空间使用（其实是管理区）。每当分配或取消分配区后，Oracle服务器会更新数据字典中的相应表。用于管理的两个数据字典表分别是：UET$（UsedExtents）和FET$（Free Extents）。DMT是为了实现向后兼容而提供的，建议使用本地管理的表空间。
**本地管理表空间（Locally Managed Tablespace，LMT）**是从Oracle 8i出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用（其实是管理区）。位图中的每个位都对应于一个块或一组块。Oracle 在分配区或释放区后可以重新使用，Oracle 服务器通过更改位图值来显示块的新状态。LMT 在Oracle 9i及以后版本中成了默认选项。

1.9 Redo日志文件（Redo Log Files）的作用是什么？

答案：Redo日志文件包含所有的数据库变化历史记录，例如所有的DML变化（INSERT、UPDATE、DELETE和SELECT FOR UPDATE）和所有DDL语句造成的数据字典对象的更改及递归语句的更改等，所以，日志文件可以最大限度地保证数据的一致性与安全性。当发生提交动作时，后台进程 LGWR 将Redo日志缓冲区中的数据刷到Redo日志文件里。

1.10 Oracle系统进程主要有哪些，作用是个啥？

1. 数据写进程(DBWR)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件
2. 日志写进程(LGWR)：将重做日志缓冲区中的更改写入在线重做日志文件
3. 系统监控 (SMON): 检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复
4. 进程监控 (PMON): 负责在一个Oracle 进程失败时清理资源
5. 检查点进程(CKPT)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。
6. 归档进程 (ARCH)：在每次日志切换时把已满的日志组进行备份或归档
7. 恢复进程 (RECO): 保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;
8. 作业调度器(CJQ ): 负责将调度与执行系统中已定义好的job,完成一些预定义的工作.

1.11 Oracle Undo相关面试题

1.11.1 请描述什么是Oracle Undo？

Oracle数据库创建和管理用于回滚或撤消对数据库的更改的数据。
这些信息主要包括交易行为的记录，主要是在交易被提交之前。
这些记录统称为undo。

1.11.2 请描述UNDO的作用?

1.回滚事务。
2.实例恢复。
实例恢复过程中，想通过redo记录对checkpoint之后的脏块队列进行前滚操作。
对于所有未提交的脏块，oracle根据undo的前镜像进行回滚（行级别的逻辑反操作），重新将内存中缓存的相关数据脏块换为非脏块。
3.提供一致性读。
执行查询时，服务器进程扫描这个表中的数据块时，会把每个数据块ITL槽中最大的SCN与查询SCN进行比较，如果比查询SCN小则说明这个数据块没有被修改服务器进程直接进行数据读取即可。
如果数据块ITL槽中的SCN大于查询SCN那么说明这个数据块在发起查询后被修改了，需要借助undo去获取发起查询那个时刻数据块的数据。
4.闪回部分相关操作

1.11.3 请描述对UNDO Retention的理解？

undo_retention表示已经提交或回滚的事物在UNDO EXTENT中保留的时间；

1.11.4 请描述ORA-01555错误原因和解决思路。

构建一致性读时，需要的undo数据已经被覆盖。
通常原因如下：
(1)SQL语句执行时间太长。
(2)UNDO表空间过小。
(3)事务量过大。
(4)过于频繁的提交。
比如对一个块上的10行数据，每次修改1行并提交，就会对这个块生成10次UNDO镜像数据。
(5)导致执行SQL过程中进行一致性读时，SQL执行后修改的前镜像（即UNDO数据）在UNDO表空间中已经被覆盖，不能构造一致性读块（CR blocks）。
建议：
（1）增加UNDO表空间大小。
（2）增加undo_retention 时间，默认只有15分钟。
（3）优化出错的SQL，减少查询的时间，首选方法。
（4）避免频繁的提交。

1.11.5 如何找到ORA-01555对应的SQL

How to find the complete SQL statement caused ORA-1555 :
If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :
select SQL_TEXT from v$sqlarea where SQL_ID=‘’;
If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID=‘’;

1.11.6 请描述ORA-30036错误原因和解决思路。

ORA-30036：unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
当当前UNDO表空间没有更多可用空间用于活动事务时，将报告ORA-30036错误。
UNDO空间分配按以下顺序进行：
 1.在没有活动事务的UNDO段中分配数据块。Oracle尝试将事务分发到所有UNDO段。
 2.如果找不到UNDO段，则oracle会尝试将脱机UNDO段联机并使用它。
 3.如果没有要联机的UNDO段，则创建一个新的UNDO段并使用它。
 4.如果空间不允许创建undo段，那么我们尝试重用现有undo段中过期的区段。
对于与UNDO段/extent关联的正在运行的事务，如果它需要更多的UNDO空间，则：
 1.如果当前extent有更多可用块，则使用下一个已准备好分配给该extent的可用块。
 2.如果当前区段没有空闲块，并且该段的下一区段已过期，则在下一区段中包装该区段并返回第一个区段。
 3.如果下一个extent尚未过期，则从UNDO表空间获取空间。如果有可用的扩展数据块，则将其分配给UNDO段，并返回新扩展数据块中的第一个块。
 4.如果没有可用的extent，则从脱机UNDO段进行窃取。从脱机UNDO段取消分配数据块，并将其添加到当前UNDO段。返回数据块的第一个空闲块。
 5.从联机UNDO段窃取。从联机UNDO段取消分配数据块，并将其添加到当前UNDO段。返回数据块的第一个空闲块。
 6.在UNDO表空间中扩展文件。如果文件可以扩展，则向当前UNDO段添加一个区段，然后返回块。
 7.否则，尝试从自己的UNDO段重用未过期的区段。如果所有扩展数据块当前都很忙（它们包含未提交的信息），请转至步骤8。否则，请换行到下一个扩展数据块。
 8.从脱机UNDO段随机窃取未过期的数据块。如果失败，则尝试联机UNDO段以供重用。
 9.如果上述操作都失败，则返回ORA-30036无法将段扩展%s（在UNDO表空间“%s”中）

1.11.7 如何评估所需UNDO大小？

How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
调整UNDO表空间的大小需要三个数据。
Sizing an UNDO tablespace requires three pieces of data.
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)
所需的undo空间计算如下：
The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)
以下公式计算每秒生成的峰值undo块：
The following formula calculates the peak undo blocks generated per second:
SELECT undoblks / ((end_time - begin_time) * 86400) “Peak Undo Block Generation”
  FROM v$undostatWHEREundoblks=(SELECTMAX(undoblks)FROMv$undostat);
列结束时间和开始时间是日期数据类型。
减去日期数据类型后，结果值为两个日期之间的天数。
要将天转换为秒，需要将86400乘以一天中的秒数（24小时60分钟60秒）。
Column END_TIME and BEGIN_TIME are DATE data types. 
When DATE data types are subtracted, the resulting value is the # of days between both dates. 
To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).
以下查询计算处理峰值撤消活动所需的字节数：
The following query calculates the number of bytes needed to handle a peak undo activity:
SELECT (UR * (UPS * DBS)) AS “Bytes”
  FROM (SELECT value AS UR FROM v$parameterWHEREname{=}^{\prime }und{o}_{r}etentio{n}^{\prime }),(SELECTundoblks/((en{d}_{t}ime-begi{n}_{t}ime)\ast 86400)ASUPSFROMv$undostat
         WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),(SELECTbloc{k}_{s}izeASDBSFROMdb{a}_{t}ablespacesWHEREtablespac{e}_{n}ame=(SELECTUPPER(value)FROMv$parameter
                 WHERE name = ‘undo_tablespace’));
对于使用调优撤消保留的10g及更高版本，请使用以下查询： 
For 10g and Higher Versions where Tuned undo retention is being used,please use below query:
SELECT (UR * (UPS * DBS)) AS “Bytes”
  FROM (select max(tuned_undoretention) AS UR from v$undostat),(SELECTundoblks/((en{d}_{t}ime-begi{n}_{t}ime)\ast 86400)ASUPSFROMv$undostat
         WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),(SELECTbloc{k}_{s}izeASDBSFROMdb{a}_{t}ablespacesWHEREtablespac{e}_{n}ame=(SELECTUPPER(value)FROMv$parameter
                 WHERE name = ‘undo_tablespace’));

2.索引相关

2.1 什么是索引分裂？

索引分裂（Index Block Split）就是索引块的分裂。当一次DML操作修改了索引块上的数据，但是旧有的索引块没有足够的空间去容纳新修改的数据时，将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上，这个过程就称为索引块的分裂，简称索引分裂。

3.SQL优化相关

3.1 表和表之间的关联方式有哪几种？

目前为止，无论连接操作符如何，典型的连接类型共有3种：
（1）**排序合并连接（SMJ）**如果连接属性上都建有索引，那么可利用索引已有的排序做合并连接。但如果在连接属性上没有索引时，那么需要首先对两表在连接属性上排序，对排序结果再做连接。
（2）嵌套循环（NL） NL是一种比较高效的连接方式，内部表循环与外部表相匹配。这个连接方法用到了驱动表（外部表）的概念，该连接过程是一个2层嵌套循环。
（3）哈希连接（HJ） HJ的连接原理如下：首先把小表的哈希操作存放到内存中，然后用大表的每条记录做哈希，与之前小表的哈希值匹配。这种连接是在Oracle 7.3引入的，从理论上来说比NL和SMJ更高效，而且只用在CBO（Cost Based Optimization，基于代价的优化器）优化器中。

3.2 表的访问方式有哪几种？

访问表的方式也称为优化器访问路径，主要有3种访问路径：全表扫描（FULL TABLE SCAN，FTS）、索引扫描（INDEX SCAN）和ROWID扫描。

3.2 索引扫描的类型

第一种： index unique  scan 索引唯一扫描，当可以优化器发现某个查询条件可以利用到主键、唯一键、具有外键约束的列，或者只是访问其中某行索引所在的数据的时候，优化器会选择这种扫描类型。
第二种： index range scan索引范围扫描，当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWEEN等就会使用范围扫描，在组合列上只使用部分进行查询，导致查询出多行数据。对非唯一的索引列上进行任何活动都会使用 index range scan。
第三种： index full scan 全索引扫描，如果要查询的数据可以全部从索引中获取，则使用全索引扫描。
第四种：** index fast full scan索引快速扫描**，扫描索引中的全部的数据块，与全索引扫描的方式基本上类似。两者之间的明显的区别是，索引快速扫描对查询的数据不进行排序，数据返回的时候不是排序的。“在这种存取方法中，可以使用多块读功能，也可以使用并行读入，从而得到最大的吞吐量和缩短执行时间”。
第五种：**索引跳跃式扫描（INDEX SKIP SCAN）**适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

3.3 Hash Join是不是有排序？Hash Join会在什么时候慢？

答案：哈希连接（Hash Join，HJ）自身不需要排序，这是区别排序合并连接（Sort Merge Join，SMJ）的特点之一。Hash Join原理比较复杂，但是如果HASH_AREA_SIZE过小，HASH TABLE不能完全放到内存中，那么会发生磁盘HASH运算，这样的情况下Hash Join连接就比较慢。

3.4 什么是物理读和逻辑读？

答案：当会话所需要的数据在内存的Buffer Cache中找不到，此时就要去磁盘上的数据文件中读取，这样就产生了物理读（Physical Reads），即物理读是从磁盘文件把需要的数据读入内存（SGA中的Buffer Cache）。
需要注意的是，物理读过大表现为磁盘I/O较高，逻辑读过大表现为CPU使用率过高。

3.5 什么是直接路径访问？

答案：直接路径访问是绕过SGA，直接把数据读入到PGA中，这个过程数据不经过SGA的缓冲，所以在理论上性能更快。在 PGA 中的数据只能由当前会话进程访问，当其他会话需要访问这部分数据时需要从磁盘读取数据，会发生物理读。若多个会话同时需要相同的数据，则会导致系统物理读增大，从而影响了数据库的性能。
保存数据的方法分为常规路径加载和直接路径加载：
（1）常规路径加载 执行INSERT语句来填充数据库中的表。直接路径加载会格式化Oracle数据块，并将数据块直接写入数据库文件，从而可消除大量数据库开销。直接加载不与其他用户竞争数据库资源，因此通常可以用接近磁盘速度的速度加载数据。常规路径加载使用SQL处理和数据库COMMIT操作来保存数据。插入记录数组后会执行COMMIT操作。每次数据加载可能涉及多个事务处理。
（2）直接路径加载 使用数据保存将数据块写入Oracle数据文件。这就是为什么直接路径加载比常规路径加载快很多的原因。数据保存与COMMIT的区别在于：
1）在数据保存期间，仅将整个数据库块写入数据库中。这些块是在表的高水位标记（HWM）之后写入的。
2）完成数据保存后，HWM会移动。
3）完成数据保存后不会释放内部资源。
4）完成数据保存不会结束事务处理。
5）每次执行数据保存时不会更新索引。

3.6 执行计划里的access和filter有什么区别？

一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）；filter表示谓词条件的值不会影响数据的访问路劲，只起到过滤的作用。NOT IN或MIN函数等容易产生filter操作。

3.7 什么是基数（Cardinality）和可选择率（Selectivity）？

答案：
**基数（Cardinality）**是Oracle预估的返回行数，即对目标SQL的某个具体执行步骤的执行结果所包含记录数的估算值。如果是针对整个目标SQL，那么此时的Cardinality就表示该SQL最终执行结果所包含记录数的估算值。
**可选择率（Selectivity）**是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的取值范围显然是0～1，它的值越小，就表明可选择性越好。

4.统计信息

4.1 什么是动态采样（Dynamic Sampling）？

对于没有收集统计信息的表，Oracle为了能够得到相对准确的执行计划，会在执行SQL之前对SQL语句涉及的表做动态采样（Dynamic Sampling，从Oracle 11gR2开始称之为Dynamic Statistic）。
有两种方法可以开启动态采样：
1）将参数OPTIMIZER_DYNAMIC_SAMPLING的值设为大于或等于1。从Oracle 10g开始，该值默认为2，若设置为0，则禁用动态采样。
2）使用动态采样的Hint：DYNAMIC_SAMPLING（T LEVEL）。该Hint表示对目标表T强制使用等级为参数level指定值的动态采样。
默认采样数据块数量受隐含参数“_OPTIMIZER_DYN_SMP_BLKS”的控制，其默认值是 32，表示动态采样时默认采样数据块数量为32。

4.2 什么是待定的统计信息（Pending Statistic）？

简单地说，DBA可以对一系列的数据表设置PENDING属性。设置PENDING属性之后，数据的统计信息在数据字典中相当于已经锁定。当新的统计信息生成之后，不是直接替换原有的数据，而是存放在PENDING数据字典中。在PENDING字典中的统计信息在默认情况下是不会参与SQL执行计划的生成的。只有在进行SQL测试通过的时候，经过用户手工的确定，才会将其PUBLISH出来，替换原有的统计信息。

4.3 什么是直方图（Histogram）？

直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。如果对目标列收集了直方图，那么意味着 CBO 将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。

5.等待事件

5.1 等待事件的分类？常见等待事件？

Oracle的等待事件主要可以分为两类：空闲（Idle）等待事件和非空闲（Non-Idle）等待事件。
1）空闲等待事件是指 Oracle 正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。
2）非空闲等待事件专门针对Oracle的活动，是指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。
一些常见的、重要的等待事件如下：
（1）数据文件I/O相关的等待事件 db file sequential read、db file scattered read 、db file parallel read、direct path read、direct path write。
（2）控制文件I/O相关的等待事件 control file parallel write、control file sequential read、control file single write。
（3）Redo日志文件I/O相关的等待事件 log file parallel write、log file sync、log file sequential read、log file single write、switch logfile command、log file switch completion、log file switch （clearing log file）、log file switch （checkpoint incomplete）、log switch/archive、log file switch （archiving needed）。
（4）高速缓存区I/O相关的等待事件 db file parallel write、db file single write、write complete waits、free buffer waits。

6.Oracle性能相关

6.1 ROWID和ROWNUM有什么区别？

（1）ROWID ROWID是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个ROWID的伪列，但是表中并不物理存储ROWID列的值。不过可以像使用其他列那样使用它，但是不能删除该列，也不能对该列的值进行修改、插入。
（2）ROWNUM ROWNUM是一个伪列，不是真正的列，在表中并不真实存在，它是Oracle数据库从数据文件或缓冲区中读取数据的顺序。切勿理解成记录的行号

6.2 什么是死锁？

所谓死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。Oracle对于“死锁”是要做处理的。

6.3 在高并发、高负载的情况下，如何给表添加字段并设置DEFAULT值？

当Oracle表数据量上亿时，对表执行“ALTER TABLE XXX ADD COLUMN_XX VARCHAR2（2）DEFAULT ‘XXX’;”操作时，效率及安全性是必须要考虑的因素。若直接执行，则会在该过程中给表加上6级表锁，也就是连查询都需要等待，这在生产库上是相当危险的操作。因为Oracle在执行上述操作过程中，不仅要更新数据字典，还会刷新全部的记录，并且会使得 Undo表空间暴涨，所以，正确的做法是将更新数据字典和更新字段值分开。

6.4 若临时表空间使用率过高有什么调优思路？

临时表空间是Oracle数据库的重要组成部分，尤其是对于大型的频繁操作，如创建索引、排序等都需要在临时表空间完成来减少内存的开销。当然对于查询性能要求较高的操作应尽可能地避免在磁盘上完成这些操作。
若临时表空间占用过大，首先，要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。通过查询视图GV$SOR{T}_{U}SAGE（GV$SORT_USAGE和GV$TEMPSE{G}_{U}SAGE查询的结果是一致的）和GV$SESSION 可以获取到临时表空间的占用情况和临时段的类型等信息。

6.5 如何有效地删除一个大表（即表的EXTENT数很多）？

答案：一个有很多EXTENT（100k+）的表，如果只是简单地用DROP TABLE的话，那么会大量消耗CPU（在DMT管理下，Oracle要对FET$、UET$数据字典进行操作），可能会用上几天的时间，较好的方法是分多次删除EXTENT，以减轻这种消耗

6.6 Oracle 降低高水位线的方法

释放表的高水位通常有如下几种办法：
（1）对表进行MOVE操作：ALTER TABLE TABLE_NAME MOVE;。若表上存在索引，则记得重建索引。
（2）对表进行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;，注意，在执行该指令之前必须开启行移动：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;。该方法的优点是：在碎片整理结束后，表上相关的索引仍然有效，缺点是会产生大量的UNDO和REDO。
（3）复制要保留的数据到临时表T，DROP原表，然后RENAME临时表T为原表。
（4）exp/imp或expdp/impdp重构表。
（5）若表中没有数据则直接使用TRUNCATE来释放高水位。

7.数据库诊断

7.1 数据库运行很慢，如何解决？

导致数据库运行很慢的原因非常多，例如可能是开发人员SQL语句写的不好导致执行性能比较差。所以，碰到这类问题，不能给出一个非常精确的答案，但是可以按照如下的步骤去检测排除：
1）top或topas查看系统的CPU利用率是否正常，找到最耗费资源的Oracle进程，然后进入数据库查询相关的会话，找到SQL语句再进行具体分析。如果CPU正常，那么就很可能是由于开发人员写的SQL语句不好，导致SQL执行时间过长，因此，开发人员误认为是数据库运行缓慢。
2）进入数据库查看等待事件是否正常

7.2 Undo的作用

1）事务回滚（Rollback Transaction）
2）事务恢复（Transaction Recovery）
3）提供一致性读（Consistent Read）
4）实现闪回功能

7.3 常用的10046及10053诊断事件的区别是什么？

10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。Oracle的10046事件可以跟踪应用程序所执行的SQL语句，并且得到其解析次数、执行次数、CPU使用时间等信息。这对DBA来分析、定位数据库性能问题是非常有用的。
10053事件是最常用的Oracle优化器跟踪Trace。10053事件解析优化器为什么选择某个执行计划但并不记录这个执行计划到底运行地如何。10046事件并不解释优化器的工作，但它记录了在SQL解析阶段所遇到的等待事件和所消耗的CPU等资源，以及执行阶段的各项指标。需要注意的是，如果一个SQL语句已经被解析过，那么就不会生成10053的trace文件，但10046的trace文件可以重复生成。
简而言之，10046事件记录SQL如何运行，而10053记录优化器为什么为这个SQL选择某个执行计划。

8.会话

9.ASM和RAC维护

9.1 集群（Cluster）特有的后台进程有哪些？

集群由若干进程组成，其中，最重要的几个进程包括 OCSSD、CRSD、EVMD 等

9.2 集群有哪几种心跳机制？

在几乎所有高可用的环境中都有心跳的存在，心跳的主要目的是为了检测集群中节点的状态。如果检测失败，那么管理软件会认为某个节点存在故障，并根据一定的算法来做出适当地处理，避免对环境的破坏，即高可用性软件进行自动修复。
Oracle集群有3种心跳机制，分别为网络心跳（Network HeartBeat，NHB）、磁盘心跳（Disk HeartBeat， DHB）和本地心跳（Local HeartBeat，LHB）

9.3 RAC的脑裂和健忘分别指的是什么？

（1）脑裂（SplitBrain） 在集群中，节点间通过心跳来了解彼此的健康状态，以确保各节点协调工作。假设当“心跳”出现问题，但各个节点还在正常运行，这时，每个节点都认为其他的节点宕机了，自己才是整个集群环境中的“唯一健在者”，自己应该获得整个集群的“控制权”。在集群环境中，存储设备都是共享的，这就意味着数据灾难。简单点说，就是如果由于私有网络硬件或软件的故障，导致集群节点间的私有网络在一定时间内无法进行正常的通信，这种现象称为脑裂。在发生脑裂情况后，集群的某些节点间的网络心跳丢失，但磁盘心跳依然正常，集群根据投票算法（Quorum Algorithm）将不正确的节点踢出集群。磁盘心跳的主要目的是当集群发生脑裂时可以帮助指定脑裂的解决方案。
（2）健忘（Amnesia） 集群环境的配置文件不是集中存放的，而是每个节点都有一个本地副本，在集群正常运行时，用户可以在任何节点更改集群的配置，并且这种更改会自动同步到其他节点。健忘是由于某个节点更新了OCR（Oracle Cluster Registry，Oracle集群注册）中的内容，而集群中的另外一些节点此时处于关闭、维护或重启阶段，OCR Master进程来不及将其信息更新到这些异常节点缓存而导致的状态不一致。譬如，A节点发出了添加OCR镜像的命令，在这个时候B节点处于重启阶段。重启后A已经更新完毕，而此时B并不知道已经为OCR增加了一个新的镜像磁盘，健忘由此而生。OCR用于解决健忘问题。

9.4 什么是GPnP？

网格即插即用（Grid Plug and Play，GPnP）是Oracle 11gR2 RAC提供的新组件，该组件的功能由gpnpd.bin守护进程实现。GPnP可以提供一个动态的GI环境，能随着负载的增加而动态改变GI环境。GPnP能非常容易地添加、替换或者移除集群中的节点，就像电源插头一样即插即用。

9.5 oraInventory目录的作用是什么？

答案：由于Oracle支持将多个Oracle软件（或者多版本的数据库软件）安装到同一台服务器上，这就需要一个位置统一记录安装的软件信息。中央目录（Central Inventory）实际上就是一台主机上安装的Oracle产品清单。

9.6 谈谈对Oracle中RAC用到的Cache Fusion的理解。

答案：Cache Fusion即缓存融合，它能实现RAC在各个节点之间同步SGA中的缓存信息，从而达到提高访问速度的效果，也保证了数据的一致性。要发挥Cache Fusion的作用，要有一个前提条件，那就是网络的速度要比访问磁盘的速度要快。否则，没有引入Cache Fusion的意义。

9.7 如果$GRID_HOME下的权限被人为修改过，那么如何来修复该权限问题？

1）根据MOS文档提供的建议通过$GRID_HOME/crs/install/rootcrs.pl -init或roothas.pl -init进行解决。
2）采用MOS文档1515018.1上提供的脚本在正常库上生成脚本，然后将生成的脚本在异常库上执行从而来修复权限问题。
3）Oracle 11gR2可以deconfig crs的配置，然后重新运行root.sh即可。
4）MOS文档1515018.1上提供了一个修复脚本：permission.pl。可以根据该脚本来修复。

9.8 RAC等待事件gc buffer busy acquire和gc buffer busy release的区别是什么？

gc buffer busy acquire是当会话1尝试请求访问远程实例上的数据块，但是在会话1之前已经有相同实例上另外一个会话2请求访问了相同的数据块，并且没有完成，那么会话1等待gc buffer busy acquire。
gc buffer busy release是在会话1之前已经有远程实例的会话2请求访问了相同的数据块，并且没有完成，那么会话1等待gc buffer busy release。

9.9 RAC节点被踢出可能有哪些原因？

答案：可能的原因包括服务器负载严重或内核HANG住、网络心跳丢失、磁盘心跳丢失、CSSD进程HANG住。

9.10 Oracle11g RAC 启动流程梳理

启动流程步骤层次梳理：
第一层：OHASD 启动：

• cssdagent - 负责启动 CSSD 的 Agent。
• orarootagent - 负责启动所有 root 用户下的 ohasd 资源 的Agent。
• oraagent - 负责启动所有 oracle 用户下的 ohasd 资源的 Agent。
• cssdmonitor - 监控 CSSD 以及节点健康（和 cssdagent 一起）。

第二层：OHASD rootagent 启动：

• CRSD - 管理集群资源的主要后台进程。
• CTSSD - Cluster Time Synchronization Services Daemon
• Diskmon
• ACFS （ASM Cluster File System）驱动

第三层：OHASD oraagent 启动：

• MDNSD - 用来实现 DNS 查询
• GIPCD - 用来做节点间通信
• GPNPD - Grid Plug & Play Profile Daemon
• EVMD - Event Monitor Daemon
• ASM - ASM 资源

第四层：CRSD 启动：

• orarootagent - 负责启动所有 root 用户下的 crsd 资源的 Agent。
• oraagent - 负责启动所有 oracle 用户下的 crsd 资源的 Agent。

第五层：CRSD rootagent 启动：

• Network resource - 监控公共网络
• SCAN VIP(s) - Single Client Access Name Virtual IPs
• Node VIPs - 每个节点1个
• ACFS Registery - 挂载 ASM Cluster File System
• GNS VIP (optional) - VIP for GNS

第六层：CRSD oraagent 启动：

• ASM Resouce - ASM 资源
• Diskgroup - 用来管理/监控 ASM 磁盘组
• DB Resource - 用来管理/监控数据库和实例
• SCAN Listener - SCAN 监听，监听在 SCAN VIP 上
• Listener - 节点监听，监听在 Node VIP 上
• Services - 用来管理/监控 services
• ONS - Oracle Notification Service
• eONS - 加强版 Oracle Notification Service
• GSD - 为了向下兼容 9i
• GNS (optional) - Grid Naming Service - 处理域名解析

10.DG维护

10.1 DG的分类

DG 根据备库（Standby Database）重演日志方式的不同，可以分为物理 DG（PhysicalDG）、逻辑DG（LogicalDG）和快照DG（Snapshot DG），它们对应的数据库分别可以称为Physical Standby、Logical Standby和Snapshot Standby。

10.2 RFS、LNSn、MRP、LSP进程的作用分别是什么？

（1）RFS进程 RFS（Remote File Server）进程主要用来接受从主库传送过来的日志信息。
（2）LNSn（LGWR Network Server process）进程 DG可以使用ARCn、LGWR来传送日志，但它们都是把日志发送给本地的LNSn（如果有多个目标备库，那么会启动相应数量的LNSn 进程，同时发送数据）进程，然后备库的RFS进程接收数据，接收到的数据可以存储在备库的备用Redo日志文件中或备库的归档日志中，然后再应用到备库中。
（3）MRP（Managed Recovery Process）进程 该进程只针对物理备库，作用为应用从主库传递过来的Redo 日志到物理备库，称为 Redo Apply。
（4）LSP（logical standby process）进程 只有逻辑备库才会有该进程。

10.3 DG的3种保护模式

DG 提供了 3 种数据保护模式（Protection Mode）：最大保护（Maximum Protection）、最高性能（Maximum Performance）和最高可用（Maximum Availability）

10.4 Switchover和Failover的区别

1）Switchover是指主库转换成备库，然后将原备库转换成新主库；而Failover是指将备库转换成主库。
2）使用场合不同：Switchover用于有准备的、计划之中的切换，通常是系统升级、数据迁移等常态任务；Failover用于意料之外的突发情况，例如异常断电、自然灾难等。
3）数据丢失程度不同：Switchover表示不会丢失数据，Failover通常意味着有部分数据丢失。
4）善后处理的不同：Switchover之后DG环境不会被破坏，仍然有Primary、Standby两种角色的系统存在，但是Failover之后，DG环境就会被破坏，一般情况下需要重建。

10.5 主库丢失归档，物理DG如何恢复？

可以通过对主库进行基于SCN的增量备份来恢复物理DG。

11.OGG维护

11.1 OGG主要包含Manager进程、Extract进程、Pump进程、Replicat进程

1）Manager进程是OGG的控制进程，运行在源端和目标端上。它的主要作用包含启动、监控、重启 OGG的其他进程；报告错误及事件；分配数据存储空间；发布阀值报告等。
2）Extract进程运行在数据库源端，负责从源端数据表或者日志中捕获数据。在初始数据装载阶段， Extract进程直接从源端的数据表中抽取数据；在初始数据同步完成以后，Extract进程负责捕获源端数据的变化（包括DML和DDL）。
3）Data Pump进程（简称Pump进程）运行在数据库源端。其作用是如果源端使用了本地的trail文件，那么Pump进程就会把trail以数据块的形式通过TCP/IP协议发送到目标端，这通常也是推荐的方式。
4）Replicat 进程也称为应用进程，运行在目标端，是数据传递的最后一站，负责读取目标端 trail文件中的内容，并将其解析为DML或DDL语句，然后应用到目标数据库中。

11.2 OGG-00446 Could not find archived log for sequence错误的处理办法

如果面试官问到维护OGG曾经碰到的一次故障处理过程，那么就可以拿这个错误作为案例来说明。OGG-00446主要是归档文件丢失引起，处理办法就是将缺失的归档日志找回来。如果找不到所需归档日志，那么可以按照如下2种办法来处理：
第一种办法是改变抽取进程的时间，但这可能会导致数据不一致
第二种办法：重新初始化，基于scn重新初始化

12.备份恢复

**RMAN可以用来备份：**① 数据库：包括数据文件、控制文件、SPFILE（Server Parameter File）文件；② 表空间；③ 归档文件；④ 备份集。
**RMAN不能用来备份：**① 联机日志文件（Online Redo Logs）；② 非READ/WRITE状态的可传输表空间；③ PFILE（Parameter File）文件。

12.1 TRUNCATE恢复方法

1）是否有测试库，测试库的表数据和当前数据是否一致，若一致，则可以考虑从测试库把表数据导入到被删除的库中。
2）是否有exp或expdp逻辑备份，若有，则可以导入到被删除的库中。
3）是否有RMAN备份，若有，则可以将数据恢复到其他地方，然后将数据库exp出来，最后导入到被删除的库中。
4）数据库是否开启了闪回，如果开了闪回则可以利用闪回数据库的特性找回数据。
5）利用TSPITR，表空间基于时间点的恢复技术来恢复。
6）是否有归档，若有则可以采用LogMiner进行日志挖掘。
7）若以上这些办法都不能恢复，则可以尝试无备份情况下的恢复，这里推荐两种办法， fy_recover_data包和gdul工具

12.2 Oracle的SCN是什么？

SCN（System Change Number，系统改变号）是一个由系统内部维护的序列号，SCN在数据库全局是唯一的。当系统需要更新的时候自动增加，它是系统中维持数据的一致性和顺序恢复的重要标志，是数据库中非常重要的一种数据结构。在数据库中，SCN作为一种时钟机制来标记数据库动作，比如，当事务发生时，数据库会用一个SCN来标记它。

12.3 实例恢复和介质恢复的区别是什么？

（1）介质恢复 介质恢复是基于物理备份恢复数据，它是Oracle数据库出现介质故障时恢复的重要保障。介质恢复包括块恢复、数据文件恢复、表空间恢复和整个数据库的恢复。介质恢复主要是针对错误类型中的介质失败，如果是少量的块失败，那么可以使用介质恢复中的块恢复来快速修复；但如果是其他情况的丢失，那么需要根据具体情况，可使用数据文件恢复、表空间恢复甚至全库恢复
（2）实例恢复 实例恢复可确保数据库在一个实例失败后仍能回到一致性的状态。Redo日志记录了对实例的所有更改。单实例数据库拥有一个重做线程，而一个RAC数据库拥有多个重做线程，且RAC数据库的每个实例拥有一个重做线程。当事务提交时，LGWR 将内存中的重做条目和事务 SCN 同时写入联机Redo日志。但是，DBWn进程只在最有利的时机将已修改的数据块写入数据文件。所以，未提交的更改可能会暂时存在于数据文件中，而已提交的更改也可能还不在数据文件中。

12.4 DELETE了一条数据并且提交了，该如何找回？

在Oracle中可以通过闪回技术来找回已经删除并且提交了的数据。当然，除了闪回技术外还可以采用LogMiner（使用该工具可以轻松获得Redo日志文件包含归档日志文件中的具体内容）进行日志挖掘，找出其撤销SQL并执行就可以找回DELETE语句删除的数据。

12.5 rm-rf误操作的恢复过程

如果执行了rm-rf操作删除了所有的基于FS的数据文件，但是数据库还处于OPEN状态，那么，在这种情况下如何快速地恢复数据库呢？这里的前提条件是没有任何可用的RMAN 备份、数据库冷备份等，也就是说，没有任何备份。在这种情况下可以通过系统的文件句柄号来恢复数据文件。整个恢复过程可以简单分为如下几步：
（1）找到被删除文件的文件句柄所在的目录 首先通过命令“ps -ef|grep ora_lgwr”找到LGWR的进程号。假设这里的进程号为31863，则被删除的文件句柄在/proc/31863/fd目录下。
（2）采用操作系统 cp 命令复制文件句柄到原数据库文件路径 假设这里看到的是如下的情况，被删除的文件末尾一般都有deleted标识。

12.6 如何处理Oracle中的坏块？

（1）坏块的简介 Oracle数据文件的坏块可以分为物理坏块和逻辑坏块。物理坏块指的是块格式本身已经损坏，块内的数据没有任何意义。逻辑坏块指的是块内的数据在逻辑上存在问题，比如说索引块的索引值没有按顺序排列导致的逻辑坏块。物理坏块一般是由于内存问题、OS问题、I/O子系统问题或硬件引起的，逻辑坏块一般是由Oracle系统Bug等原因引起的。
（2）BMR 恢复坏块 如果数据库只有很少的数据块被破坏，那么使用块介质恢复（Block Media Recovery，BMR）是较好的块恢复方法。BMR只能用于恢复物理损坏（Physical Corruptions），在数据文件联机时即可恢复相关坏块。

12.7 LogMiner是什么？其有哪些用途？

LogMiner 工具的主要用途有：①跟踪数据库的变化：可以离线地跟踪数据库的变化，而不会影响在线系统的性能；②回退数据库的变化：回退特定的变化数据，减少Point-In-Time Recovery的执行；③优化和扩容计划：可通过分析日志文件中的数据以分析数据的增长模式；④确定数据库的逻辑损坏时间：准确定位操作执行的时间和SCN；⑤确定事务级要执行的精细逻辑恢复操作，可以取得相应的Undo操作；⑥执行后续审计。

12.8 什么是BBED？它有哪些作用？

BBED（Block Brower and Editor）是用来直接查看和修改Oracle数据块的一个内部工具，它可以直接修改Oracle数据文件块的内容，在一些极端恢复场景下比较有用。因为该工具不被Oracle服务支持，所以，默认是没有生成可执行文件的，在使用前需要编译生成。

12.9 如果必须利用备份恢复数据库，但是又没有控制文件，如何解决问题？

答案：可以重建控制文件，然后利用带BACKUP CONTROL FILE子句的RECOVER命令恢复数据库。

12.10 归档和非归档模式之间的不同点是什么？它们各自的优缺点是什么？

答案：在Oracle数据库操作中，数据库可以设置为归档模式和非归档模式。归档模式保存所有的事务日志，包括在线日志和归档日志，而非归档模式没有归档日志。归档模式是指可以备份所有的数据库事务并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点但是非归档模式可以带来数据库性能上的少许提高，因为非归档模式没有归档日志。利用 RMAN 备份数据库，若是归档模式则可以在OPEN状态下备份，若是非归档模式则不能在OPEN状态下备份。

12.11 在控制文件丢失且无备份的情况下如何恢复？

答案：重建控制文件，在重建控制文件之前，需要使用包DBMS_BACKUP_RESTORE来抽取数据文件

12.12 在Undo表空间数据文件丢失的情况下如何恢复？

答案：恢复大约可以分为 3 种情况：
①有备份，这种情况下直接采用备份的文件进行恢复即可。
②无备份但是有完整的归档文件存在，这种情况下可以使用命令“ALTER DATABASE CREATE DATAFILE 文件号 AS ‘/u01/app/oracle/oradata/lhrdb/undotbs01.dbf’ size 50m;”来创建丢失的Undo文件，然后使用“RECOVER DATAFILE 文件号;”进行恢复数据库文件即可。
③无备份，归档文件丢失，在这种情况下的恢复比较复杂。首先应该切换Undo表空间到一个新建的Undo表空间中，并设置原有表空间的管理模式为手动管理模式，然后将隐含参数“_OFFLINE_ROLLBACK_SEGMENTS”设置为TRUE

12.13 如果控制文件损坏如何恢复？

答案：如果控制文件有多个，而只损坏了单个控制文件，那么只需要关闭数据库，拷贝其他好的控制文件覆盖掉坏的控制文件即可。也可以修改参数文件，只保留1个控制文件。如果损坏了全部控制文件，那么需要重新创建控制文件或从备份恢复。重新创建控制文件的脚本可以通过命令“ALTER DATABASE BACKUP CONTROLFILE TO TRACE;”获取。

13.ASH/AWR/ADDM

13.1 AWR报告中主要关注哪些方面内容？

（1）DB Time/Elapsed 该部分位于AWR报告的头部
（2）Load Profile 该部分位于AWR报告的总览部分（Report Summary），AWR报告总览部分包括了5个部分：缓存尺寸（Cache Sizes）、负载性能（Load Profile）、数据库效率（Instance Efficiency Percentages）、共享池统计（Shared Pool Statistics）、TOP5事件（Top 5 Timed Events）。
（3）Instance Efficiency Percentages (Target 100%) 该部分包含了Oracle关键指标的内存命中率及其他数据库实例操作的效率。
（4）Top 5 Timed Events 
（5）SQL Statistics SQL Statistics分别从执行时间、物理读、逻辑读、子游标个数、执行次数等方面罗列出TOP语句，从该部分可以迅速获取有性能问题的SQL语句
（6）Segment Statistics 该部分从段（表段、索引段）的角度描述了数据库的繁忙程度，包含了逻辑读、物理读、ITL等方面。

14.审计

14.1 审计如何分类

在Oracle中分别支持以下三种标准审计类型，或者说，可以从3个角度去启用审计：
1）语句审计（Statement Auditing），对某种类型的SQL语句审计，不指定结构或对象。审计SQL语句的成功执行或不成功执行。这里从SQL语句的角度出发，进行指定。审计只关心执行的语句。
2）权限审计（Privilege Auditing），对执行相应动作的系统特权的使用审计，对涉及某些权限的操作进行审计，这里强调“系统权限”
3）对象审计（Object Auditing），对一特殊模式对象上的指定对象的审计。对一个特殊模式对象上的DML语句进行审计。记录作用在指定对象上的操作。

15.Oracle锁相关知识