Oracle面试问题
什么是学习一门技术的最佳方法，我觉得是通过考试来学习，无论是笔试、面试、还是认证考试，这种带着目的学习的模式，要比没有任何压力自己阅读文档的效率高得多。所以很多时候人都是需要一种压力让自己保持学习下去的。专门找了两份面试题以及简单的答案过来，参阅一下。
第一份是阿里巴巴的DBA面试题以及答案，这个我还去过一次，答得太烂直接没有消息了。这份答卷自己稍微改了一下：
******************************************************************************************
一、SQL tuning类
1：列举几种表连接方式
答：sort merge join：先分别排序再进行合并、查找，适用于已经排过序的情况；
hash join：将一个表在内存中建hash表，然后在对应大表在hash中查找。适用于没有索引、1大1小(2张大表)的连接；
nested loop：以驱动表为基准，用来查找大表的对应信息，适用于需要快速找到第一批记录的情况。
2：不借助第三方工具，怎样查看sql的执行计划？
答：sqlplus：set autotrace on (utlxplan.sql创建plan_table表)
explain plan for select ... ;
select * from table(dbms_xplan.display);
tkprof算第三方工具么？
3：如何使用CBO、CBO与RULE的区别？
答：在初始化参数里面设置optimizer_mode=choose/all_rows/first_row等可以使用CBO。Oracle默认即为all_rows。
rbo直接根据既定原则进行查询，有可能会选择不合适的索引，cbo需要统计信息来判断cost大小后选择是否使用索引。
4：如何定位重要(消耗资源多)的SQL？
答：根据v$sqlarea 中的逻辑读(disk_read)。以及寻找CPU使用过量的session，查出当前session的当前SQL语句，或者：监控WIN平台Oracle的运行
5：如何跟踪某个session的SQL？
答：先找出对应的'sid,serial'，然后调用dbms_system.set_sql_trace_in_session(sid,serial,true)；
参考：跟踪某个会话(这个包需要安装rdbms\admin\prvtutil.plb，可以用dbms_monitor.session_trace_enable代替)
6：SQL调整最关注的是什么？
答：逻辑读、IO量
7：说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)
答：默认的索引是b-tree。
对insert的影响：分裂，要保证tree的平衡。
对delete的影响：删除行的时候要标记该节点为删除。
对update的影响：如果更新表中的索引字段，则要相应的更新索引中的键值。
查询中包含索引字段的键值和行的物理地址。
--注：这个问题太大了，讲讲太麻烦。
8：使用索引查询一定能提高查询的性能吗？为什么？
答：不能。如果返回的行数目较大，使用全表扫描的性能较好。
另外记录很少的时候，也还是不走索引的效率比较高。
9：绑定变量是什么？绑定变量有什么优缺点？
答：通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的语句的解析。
10：如何稳定(固定)执行计划？
答：使用stored outline。 or hint？
11：和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么？
答：8i：使用sort_area_size，hash_area_size，每个session分配相同的值，不管有无使用。
9i：使用pga_aggregate来统一管理。
临时表空间的作用：
在sort_area_size中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临时表等都要用到。
还有hash join不能完成的也在临时表空间中做。
12：存在表T(a,b,c,d)，要根据字段c排序后取第21-30条记录显示，请给出sql
select a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;
或者：
select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;
二、数据库基本概念类
1：pctused and pctfree 表示什么含义有什么作用？
答：表示数据块什么时候移入和移出freelist。
pctused：如果数据块的使用率小于pctused的值，则该数据块重新加入到fresslist中。
pctfree：如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。
2：简单描述table / segment / extent / block之间的关系
答：一个table至少是一个segment，如果分区表，则每个分区是一个segment，table可以看成是一个逻辑上的概念，segment可以看成是这个逻辑概念的物理实现；
segment由一个或多个extents组成，segment不可以跨表空间但可以跨数据文件；
extent由多个连续的blocks组成，不可以跨数据文件；
block由1-多个os块组成，是oracle i/o的最小存储单位。
3：描述tablespace和datafile之间的关系
答：tablespace是逻辑上的概念，datafile是物理上的概念。
一个tablespace可以由多个datafile组成，一个datafile不能跨越多个tablespace。
4：本地管理表空间和字典管理表空间的特点，ASSM有什么特点？
答：一个使用freelist管理，一个使用位图管理。
5：回滚段的作用是什么？
答：保存数据的前像，保证数据读取的时间点一致性。Oracle里数据的多版本特性就是通过回滚段来实现的，正因为此，Oracle数据库实现了读写不竞争的性能优势！
6：日志的作用是什么？
答：记录对数据库的操作，便于恢复。
7：SGA主要有那些部分，主要作用是什么？
答：db_cache(缓存数据块)，shared_pool(缓存sql，执行计划，数据字典信息等)，large_pool(MTS模式、parallel 、rman等要用到)，java pool(java程序如SQLJ存储过程运行时要用到)。
8：Oracle系统进程主要有哪些，作用是什么？
答：smon（合并空间，实例恢复），pmon（清理失败的进程），归档进程（负责在日志切换的时候归档日志文件），lgmr（日志书写器进程，负责写日志），ckpt（检查点进程，触发检查点），dbwr（数据库写入器，负责把数据写入导 datafile）。
三、备份恢复类
1：备份如何分类？
答：逻辑备份(exp)与物理备份。或者冷备份与热备份。
2：归档是什么含义？
答：把日志文件放到另一个地方。
3：如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复
答：拷贝备份，
recover database until time 2004-08-04 10:30:00
alter database open resetlogs;
4：rman是什么，有何特点？
答：rman叫恢复管理器。
特点很多。可以在线备份，到少目标数据库是mount状态。
1）热备份。
2）可以存储脚本。
3）可以增量备份。
4）自动管理备份集。
5：standby的特点
答：利用传输重做日志来达到同步的目的。可以设定多个保护级别，9i后支持逻辑standbyDB。
6：对于一个要求恢复时间比较短的系统(数据库50G，每天归档5G)，你如何设计备份策略
答：
每天一个全备份。
四、系统管理类
1：对于一个存在系统性能的系统，说出你的诊断处理思路
答：做一个statspack，根据top 5，system load，top sql等来做相应的调整。
2：列举几种诊断IO、CPU、性能状况的方法
答：hp-unix:iostat -x 1 5；
top/vmstat/glance
3：对statspack有何认识？
答：一个性能诊断工具而已，其本质就是在两个时间点采样两个系统数据。(动态性能视图)，然后根据两个snapshot，产生一个报告。
4：如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
答：1)增大sort_area_size(8i)/pga_aggregate_target(9i)值。
2)如果表有分区（一般大表都要用到分区的），按分区逐个建索引，如果是本地索引的话。
3)系统空闲的时候建。
5：对raid10 和raid5有何认识？
答：raid10是先镜像后条带，适合对写入速度要求较高的数据库系统，特别是online redolog文件，raid5适合大部分的数据库系统和数据仓库系统，读性能优于写性能。
******************************************************************************************
下面这份不知道是哪里的面试题了，在网上也流传得比较广了，虽然答案都简单了点，但是涉及的面还是比较广的：
******************************************************************************************
1.解释冷备份和热备份的不同点以及各自的优点
解答：热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）
2.你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题呢？
解答：重建控制文件，用带backup control file子句的recover命令恢复数据库。
3.如何转换init.ora到spfile？
解答：使用create spfile from pfile命令
4.解释data block,extent和segment的区别
解答：data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent. 一个数据库对象拥有的所有extents被称为该对象的segment.
5.给出两个检查表结构的方法
解答：1、DESCRIBE命令
2、DBMS_METADATA.GET_DDL包
6.怎样查看数据库引擎的报错
解答：alert log
7.比较truncate和delete命令
解答：两者都可以用来删除表中所有的记录。
区别在于：truncate是DDL操作，它移动HWK，不需要 rollback segment；而Delete是DML操作,需要rollback segment且花费较长时间
8.使用索引的理由
解答：快速访问表中的data block
9.给出在STAR SCHEMA中的两种表及它们分别含有的数据
解答：Fact tables 和 Dimension Tables。
Fact tables 包含大量的主要的信息而 Dimension Tables 存放对 Fact Table 某些属性描述的信息
10.Fact Table上需要建立何种索引？
解答：位图索引(bitmap index)
11.给出两种相关约束?
解答：主键和外键
12.如何在不影响子表的前提下，重建一个母表
解答：子表的外键强制失效，重建母表，激活外键
13.解释归档和非归档模式之间的不同和它们各自的优缺点
解答：归档模式是指你可以备份所有的数据库transactions并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高
14.如何建立一个备份控制文件？
解答：Alter database backup control file to trace.
15.给出数据库正常启动所经历的几种状态?
解答： STARTUP NOMOUNT – 数据库实例启动
STARTUP MOUNT - 数据库装载
STARTUP OPEN – 数据库打开
16.哪个column可以用来区别V$视图和GV$视图?
解答：INST_ID指明集群环境中具体的某个instance
17.如何生成explain plan?
解答：运行utlxplan.sql. 建立plan 表
针对特定SQL语句，使用 explain plan set statement_id = 'tst1' into plan_table
运行utlxplp.sql 或 utlxpls.sql察看explain plan
18.如何增加buffer cache的命中率？
解答：在数据库较繁忙时，适用buffer cache advisory工具，查询v$db_cache_advice
如果有必要更改，可以使用 alter system set db_cache_size 命令
19.ORA-01555的应对方法？
解答：具体的出错信息是snapshot too old within rollback seg , 通常可以通过增大rollback seg来解决问题。
当然也需要察看一下具体造成错误的SQL文本
20.解释$ORACLE_HOME和$ORACLE_BASE的区别？
解答：ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。
21.如何判断数据库的时区？
解答：SELECT DBTIMEZONE FROM DUAL;
22.解释GLOBAL_NAMES设为TRUE的用途
解答：GLOBAL_NAMES指明联接数据库的方式。如果这个参数设置为TRUE, 在建立数据库链接时就必须用相同的名字连结远程数据库
23.如何加密PL/SQL程序？
解答：WRAP
24.解释FUNCTION,PROCEDURE和PACKAGE区别
解答：function和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure不需要返回任何值而function将返回一个值在另一方面
Package是为了完成一个商业功能的一组function和proceudre的集合
25.解释TABLE Function的用途
解答：TABLE Function是通过PL/SQL逻辑返回一组纪录，用于普通的表/视图。他们也用于pipeline和ETL过程。
26.举出3种可以收集three advisory statistics
解答：Buffer Cache Advice, Segment Level Statistics, Timed Statistics
27.Audit trace 存放在哪个oracle目录结构中?
解答：unix $ORACLE_HOME/rdbms/audit
Windows the event viewer
28.解释materialized views的作用
解答：Materialized views 用于减少那些汇总，集合和分组的信息的集合数量。它们通常适合于数据仓库和DSS系统。
29.当用户进程出错，哪个后台进程负责清理它
解答：PMON
30.哪个后台进程刷新materialized views?
解答：The Job Queue Processes.
31.如何判断哪个session正在连结以及它们等待的资源？
解答：V$SESSION / V$SESSION_WAIT
32.描述什么是redo logs
解答：Redo Logs 是用于存放数据库数据改动状况的物理和逻辑结构，可以用来修复数据库
33.如何进行强制LOG SWITCH?
解答：ALTER SYSTEM SWITCH LOGFILE;
34.举出两个判断DDL改动的方法？
解答：你可以使用 Logminer 或 Streams
35.Coalescing做了什么？
解答：Coalescing针对于字典管理的tablespace进行碎片整理，将临近的小extents合并成单个的大extent.
36.TEMPORARY tablespace和PERMANENT tablespace 的区别是？
解答：A temporary tablespace 用于临时对象例如排序结构而 permanent tablespaces用来存储那些'真实'的对象(例如表，回滚段等）
37.创建数据库时自动建立的tablespace名称？
解答：SYSTEM tablespace.
38.创建用户时，需要赋予新用户什么权限才能使它联上数据库。
解答：CONNECT
39.如何在tablespace里增加数据文件？
解答：ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
40.如何变动数据文件的大小？
解答：ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
41.哪个VIEW用来检查数据文件的大小？
解答：DBA_DATA_FILES
42.哪个VIEW用来判断tablespace的剩余空间
解答：DBA_FREE_SPACE
43.如何判断谁往表里增加了一条纪录？
解答：auditing
44.如何重构索引？
解答：ALTER INDEX <index_name> REBUILD;
45.解释什么是Partitioning（分区）以及它的优点
解答：Partition将大表和索引分割成更小，易于管理的分区。
46.你刚刚编译了一个PL/SQL Package但是有错误报道，如何显示出错信息？
解答：SHOW ERRORS
47.如何搜集表的各种状态数据？
解答：The ANALYZE command.
48.如何启动SESSION级别的TRACE
解答: DBMS_SESSION.SET_SQL_TRACE
ALTER SESSION SET SQL_TRACE = TRUE;
49.IMPORT和SQL*LOADER 这2个工具的不同点
解答：这两个ORACLE工具都是用来将数据导入数据库的。
区别是：IMPORT工具只能处理由另一个ORACLE工具EXPORT生成的数据。而SQL*LOADER可以导入不同的ASCII格式的数据源
50. 用于网络连接的2个文件？
解答：TNSNAMES.ORA and SQLNET.ORA
******************************************************************************************