【面试】【Oracle常见问题总结】【01】

001 数据库分页查询

　　select * from ( 
　　　　select t.*,rownum from table1 t where condition ) 
　　where rownum>(pangeNow-1)*pageSize and rownum<=(pageNow)*pageSize

002 事务概念

　　答案：事务是这样一种机制，它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用： 
　　一致性：同时进行的查询和更新彼此不会发生冲突，其他用户不会看到发生了变化但尚未提交的数据。 
　　可恢复性：一旦系统故障，数据库会自动地完全恢复未完成的事务。

003 触发器的作用，什么时候用触发器,创建触发器的步骤，触发器里是否可以有commit, 为什么？

　　答案：触发器是可以由事件来启动运行的，存在于数据库服务器中的一个过程。他的作用：可以实现一般的约束无法完成的复杂约束，从而实现更为复杂的完整性要求。使用触发器并不存在严格的限定，只要用户想在无人工参与的情况下完成一般的定义约束不可以完成的约束，来保证数据库完整性，那么就可以使用触发器。 
　　由于触发器主要是用来保证数据库的完整性的，所以要创建一个触发器，首先要明确该触发器应该属于那一种（DML，INSTEAD OF，SYSTEM）因为他们各有个的用途；其次就是要确定触发器被触发以后所设计到的数据。 触发器中不可以使用COMMIT。

004 关系数据库系统与文件数据库系统的区别在那里?关系数据库系统一般适用那些方面?

　　答案： 关系数据库系统文件系统的区别在于： 
　　首先，关系性数据库的整体数据是结构化的，采用关系数据模型来描述，这是它与文件系统的根本区别。（数据模型包括：数据结构，数据操作以及完整性约束条件） 
　　其次，关系数据库系统的共享性高，冗余低可以面向整个系统，而文件系统则具有应用范围的局限性，不易扩展。 
　　第三，关系数据库系统采用两级映射机制保证了数据的高独立性，从而使得程序的编写和数据都存在很高的独立性。这方面是文件系统无法达到的，它只能针对于某一个具体的应用。（两级映射：保证逻辑独立性的外模式/模式映射和保证物理独立性的内模式/模式映射。外模式：用户模式，是数据库用户的局部数据的逻辑结构特征的描述。模式：数据库全体数据的逻辑结构特征的描述。内模式：也就是数据最终的物理存储结构的描述。） 
　　第四，就是关系性数据库系统由统一的DBMS进行管理，从而为数据提供了如安全性保护，并发控制，完整性检查和数据库恢复服务。

005 触发器的概念,存储过程的概念.

　　答案： 触发器： 是存储在数据库中的过程，当表被修改（增、删、改）时它隐式地被激发。 存储过程：是数据库语言SQL的集合，同样也存储在数据库中，但是他是由其他应用程序来启动运行或者也可以直接运行。

006 什么是事务一致性 ? 选择熟悉的数据库实现一个事务处理,如信用卡提款. 答案：

　　事务的一致性：是事务原子性的体现，事务所对应的数据库操作要么成功要么失败没有第三种情况。事务不管是提交成功与否都不能影响数据库数据的一致性状态。 
　　事务：用户定义的一个数据库操作序列，这些操作要么全部成功完成要么全部不做，是一个不可分割的整体。定义事务的SQL语句有：BEGIN TRANSACTION，COMMIT，ROLLBACK。 
　　事务的原子性：就是事务所包含的数据库操作要么都做,要么都不做. 
　　事务的隔离性：事务对数据的操作不能够受到其他事务的影响。 
　　事务的持续性：也就是说事务对数据的影响是永久的。 
　　对信用卡提款这一事务而言就是要保证’提取到现金’和’卡帐号余额’的修改要同时成功或失败. 
　　BEGIN TRANSACTION 
　　　　读取A的帐户余额BALANCE 
　　　　BALANCE=BALANCE-AMOUNT转帐金额 
　　　　IF（BALANCE<0） 
　　　　　　THEN ROLLBACK 
　　　　ELSE BEGIN 
　　　　　　将A的新余额写回 
　　　　　　读取B的帐户余额BALANCEB 
　　　　　　BALANCEB=BALANCEB+AMOUNT转帐金额 
　　　　　　将B的新余额写回 
　　　　　　COMMIT 
　　　　END IF 
　　END

007 实现索引的方式? 索引的原理? 索引的代价? 索引的类型？

　　答案： 实现索引的方式有两种：针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名)；在创建表时为字段建立主键约束或者唯一约束，系统将自动为其建立索引。 
　　索引的原理：根据建立索引的字段建立索引表，存放字段值以及对应记录的物理地址，从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。 
　　引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析. 
　　（oracle）索引的类型有：B树索引，位图索引，函数索引等。

008 view 的概念 ? 何时应用?

答案： view 是对表级数据的多角度的透视,适用于对查询安全性、灵活性有一定要求的环境.

009 使用存储过程访问数据库比直接用SQL语句访问有哪些优点？

　　答案：存储过程是预编译过的，执行时无须编译，执行速度更快；存储过程封装了一批SQL语句，便于维护数据的完整性与一致性；可以实现代码的复用。

010 Oracle中回滚的概念？回滚段有什么作用。

　　回滚就是在事务提交之前将数据库数据恢复到事务修改之前数据库数据状态。 
　　回滚段就是为回滚提供依据，记录的是事务操作数据库之前的数据或者对应于以前操作的操作，这个内容要根据以前的操作而定。比如说以前事务操作如果是UPDATE那么回滚段则存储UPDATE以前的数据，如果事务是DELETE操作那么存储的则是与之相对应的INSERT操作语句，相反如果事务操作是INSERT那么记录相应的则是DELETE操作了。

011 介绍一下oracle的体系结构？

　　逻辑体系结构：块，区，段，表空间 
　　物理体系结构：表空间，三大文件 
　　软件体系结构：SGA,后台进程.

012 简述ORACLE中SGA的组成部分。

　　答：系统全局区包括：共享池、重做日志缓存区、数据高速缓存区，大池，JAVA池。

013 简述ORACLE的启动和关闭各有多少步骤？

　　启动：启动实例、装载数据库数据、打开数据库。 关闭：关闭数据库、卸载数据库数据、关闭实例。

014 ORACLE数据库都有哪些类型的文件？

　　数据文件，控制文件，日志文件，参数文件

015 在Oracle中，你所创建的表空间信息放在哪里？

　　存放在数据字典中，数据字典内容对应于系统表空间SYSTEM表空间。

016 ORACLE的基本数据类型有哪些？

　　Char()存储定长字符，定义的时候可以不为他指定长度但是如若往里插入值则会出错；varchar2()存储变长字符定义的时候必须指定长度，date存储时间日期；Number()数字类型，包括整型，浮点型等；clob()大容量字符串；blob()大二进制对象

017 数据库的几种物理文件？

　　1）数据文件 
　　2）控制文件 
　　3）日志文件

018 控制文件都含有哪些信息？

　　控制文件存放有实例信息（实例名称创建时间等），数据文件和日志文件信息，还有系统运行时记录的系统变更码(SCN)，检查点信息和归档的当前状态信息等。数据库在加载数据库的时候首先要读取控制文件获得和数据库有关的物理结构信息之后才能够正确加载数据文件和日志文件并打开数据库。

019 truncate和delete的区别？

　　1. TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。 
　　2. TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。 
　　3. TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。 
　　4. TRUNCATE不能触发触发器，DELETE会触发触发器。 
　　5. 不能授予任何人清空他人的表的权限。 
　　6. 当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。 
　　7. 不能清空父表。

020 表空间如何扩展？并用语句写出？

　　两种扩展方式： 
　　1. 增加数据文件 alter tablespace tablespace_name add datafile ‘’ xxMB 
　　2. 扩展数据文件大小 alter database datafile ‘’ resize newMB

021 表空间区管理方式？哪种方式现在是推荐使用的？

　　1. 字典管理方式 extent management dictionary;默认方式 
　　2. 本地管理方式 extent management local[autoallocate/uniform xxmb]<推荐>;

022 分区表的应用？

　　1. 一个分区表有一个或多个分区，每个分区通过使用范围分区、散列分区、或组合分区分区的行 
　　2. 分区表中的每一个分区为一个段，可各自位于不同的表空间中 
　　3. 对于同时能够使用几个进程进行查询或操作的大型表分区非常有用

023 谈谈索引的用法及原理？

　　索引是若干数据行的关键字的列表，查询数据时，通过索引中的关键字可以快速定位到要访问的记录所在的数据块，从而大大减少读取数据块的I/O次数，因此可以显著提高性能。

024 存储过程的应用，如何既有输入又有输出？

　　Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type; ) 
　　is/as zzzz type; 
　　begin 
　　　　sqlpro; 
　　　　exception exceptionxxxxx; 
　　　　commit; 
　　end;

025 Oracle中的异常有哪几类？

　　oracle中有三种类型的异常。预定义的异常 非预定义的异常 用户定义的异常 第二种非预定义的异常是与特定的oracle错误关联。

026 Oracle的优化策略（对MySQL同样适用）

　　优化的策略一般包括: 内存优化、操作系统优化、数据存储的优化、网络优化等方法 
　　具体到不同的数据库涉及到要调整不同的数据库配置文件、不同的操作系统参数、网络参数等等, 不同的数据库不同。

027 使用索引查询一定能提高查询的性能吗？为什么？

　　通常，通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况: 
　　基于一个范围的检索,一般查询返回结果集小于表中记录数的30%宜采用; 
　　基于非唯一性索引的检索，索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引,或者讲是场合不同

028 绑定变量是什么？绑定变量有什么优缺点？

　　绑定变量是指在SQL语句中使用变量，改变变量的值来改变SQL语句的执行结果。 
　　优点：使用绑定变量，可以减少SQL语句的解析，能减少数据库引擎消耗在SQL语句解析上的资源。提高了编程效率和可靠性。减少访问数据库的次数, 就能实际上减少ORACLE的工作量。 
　　缺点：经常需要使用动态SQL的写法，由于参数的不同，可能SQL的执行效率不同； 
　　绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件， 这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，查询bind value在运行时传递，然后绑定执行。 
　　优点是减少硬解析,降低CPU的争用,节省shared_pool 
　　缺点是不能使用histogram,sql优化比较困难

029 简单描述tablespace / segment / extent / block之间的关系

　　tablespace: 一个数据库划分为一个或多个逻辑单位，该逻辑单位成为表空间;每一个表空间可能包含一个或多个 Segment; 
　　Segments: Segment指在tablespace中为特定逻辑存储结构分配的空间。每一个段是由一个或多个extent组成。包括数据段、索引段、回滚段和临时段。 
　　Extents: 一个 extent 由一系列连续的 Oracle blocks组成.ORACLE为通过extent 来给segment分配空间。 
　　Data Blocks：Oracle 数据库最小的I/O存储单位，一个data block对应一个或多个分配给data file的操作系统块。 
　　table创建时,默认创建了一个data segment,每个data segment含有min extents指定的extents数,每个extent据据表空间的存储参数分配一定数量的blocks

030 回滚段的作用是什么？

　　回滚段用于保存数据修改前的映象，这些信息用于生成读一致性数据库信息、在数据库恢复和Rollback时使用。一个事务只能使用一个回滚段。 
　　事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。 
　　事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。 
　　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。 当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性） 当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN） 来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时， 若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图

031 日志的作用是什么

　　日志文件（Log File）记录所有对数据库数据的修改，主要是保护数据库以防止故障,以及恢复数据时使用。其特点如下： 
　　1. 每一个数据库至少包含两个日志文件组。每个日志文件组至少包含两个日志文件成员。 
　　2. 日志文件组以循环方式进行写操作。 
　　3. 每一个日志文件成员对应一个物理文件。 
　　记录数据库事务,最大限度地保证数据的一致性与安全性 
　　重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复,一个数据库至少需要两个重做日志文件 
　　归档日志文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

031 SGA主要有那些部分，主要作用是什么

　　系统全局区（SGA）:是ORACLE为实例分配的一组共享缓冲存储区，用于存放数据库数据和控制信息，以实现对数据库数据的管理和操作。 SGA主要包括: 
　　1. 共享池(shared pool) ：用来存储最近执行的SQL语句和最近使用的数据字典的数据。 
　　2. 数据缓冲区 (database buffer cache)：用来存储最近从数据文件中读写过的数据。 
　　3. 重作日志缓冲区（redo log buffer）：用来记录服务或后台进程对数据库的操作。 另外在SGA中还有两个可选的内存结构： 
　　4. Java pool: 用来存储Java代码。 
　　5. Large pool: 用来存储不与SQL直接相关的大型内存结构。备份、恢复使用。

033 Oracle系统进程主要有哪些，作用是什么

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

034 备份如何分类

　　逻辑备份：exp/imp 指定表的逻辑备份 物理备份： 
　　热备份:alter tablespace begin/end backup; 
　　冷备份:脱机备份(database shutdown) 
　　RMAN备份 
　　full backup/incremental backup(累积/差异) 物理备份 
　　物理备份是最主要的备份方式。用于保证数据库在最小的数据库丢失或没有数据丢失的情况下得到恢复。 
　　冷物理 
　　　　冷物理备份提供了最简单和最直接的方法保护数据库因物理损坏丢失。建议在以下几种情况中使用。 
　　　　对一个已经存在大最数据量的数据库，在晚间数据库可以关闭，此时应用冷物理备份。 对需对数据库服务器进行升级，（如更换硬盘），此时需要备份数据库信息，并在新的硬盘中恢复这些数据信息，建议采用冷物理备份。 
　　热物理 
　　　　主要是指备份过程在数据库打开并且用户可以使用的情况下进行。需要执行热物理备份的情况有： 
由于数据库性质要求不间断工作，因而此时只能采用热物理备份。 由于备份的要求的时间过长，而数据库只能短时间关闭时。 
　　逻辑备份 (EXP/IMP) 
　　　　逻辑备份用于实现数据库对象的恢复。但不是基于时间点可完全恢复的备份策略。只能作为联机备份和脱机备份的一种补充。 
　　完全逻辑备份 
　　　　完全逻辑备份是将整个数据库导出到一个数据库的格式文件中，该文件可以在不同的数据库版本、操作系统和硬件平台之间进行移植。 
指定表的逻辑备份 
　　通过备份工具，可以将指定的数据库表备份出来，这可以避免完全逻辑备份所带来的时间和财力上的浪费。

035 事物务是什么?

　　事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID(原子性、一致性、隔离性和持久性)属性，只有这样才能成为一个事务: 
　　原子性：事务必须是原子工作单元;对于其数据修改，要么全都执行，要么全都不执行。 
　　一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。 
　　隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。 
　　持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

036 数据库死锁的预防与解除

　　死锁发生的条件： 
　　　　1、资源不能共享，需要只能由一个进程或者线程使用 
　　　　2、请求且保持，已经锁定的资源自已保持着不释放 
　　　　3、不剥夺，自给申请到的资源不能被别人剥夺 
　　　　4、循环等待 
　　想预防死锁，把上面四个条件破坏一个就可以了。 
　　防止死锁的途径就是避免满足死锁条件的情况发生，为此用户需要遵循以下原则。 
　　　　（1）尽量避免并发地执行涉及到修改数据的语句。 
　　　　（2）要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行。 
　　　　（3）预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保持一致。 
　　　　（4）每个事务的执行时间不可太长，在业务允许的情况下可以考虑将事务分割成为几个小事务来执行。【比如说把复杂的多表查询分散成多次单表查询】 
　　　　（5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或列分解为若干小表，或者按不同的用户群分解两种方法实现。这种方法类似分散“数据热点”，但是确实，如果数据不是太经常被访问，那么死锁就不会太经常发生。 
　　　　（6）还是类似（1）的，比如有一个修改上百条记录的update语句，我们可以修改成每10条一个update语句，或者干脆就每条记录一个update语句。 
　　　　（7）将经常更新的数据库和查询数据库分开

037 ORACLE中的控制文件什么时候读取？

　　ORACLE服务器启动时，先启动实例然后再读取数据库的各个文件当然也包括控制文件。也就是说在数据库服务器启动的第二步时读取。

038 Oracle索引分为哪几类，说出唯一索引和位图索引的概念。

　　Oracle索引有B树索引，位图索引，函数索引，簇索引等。 
　　唯一索引也是B树索引的一种，它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。 
　　位图索引并不是采用像唯一索引那样存储（索引字段值，记录ROWID）来创建索引段的，而是为每一个唯一的字段值创建一个位图，位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。

039 描述tablespace和datafile之间的关系

　　一个表空间可包含一个或多个数据文件。表空间利用增加或扩展数据文件扩大表空间，表空间的大小为组成该表空间的数据文件大小的和。一个datafile只能属于一个表空间; 一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内, table中的数据,通过hash算法分布在tablespace中的各个datafile中,tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象