Oracle面试题

1、delete和truncate区别？

1）truncate是DDL语句，delete是DML语句；

2）truncate速度远快于delete；

      原因是：当我们执行delete操作时，所有表数据先被copy到回滚表空间，根据数据量不同花费时间长短不一。而truncate是直接删除数据不进回滚表空间。

3）由于2）中的原因，导致delete数据后我们可以运行rollback进行数据回滚，而truncate则是永久删除不能回滚；

4）truncate操作不会触发表上的delete触发器，而delete会正常触发；（？？？）

5）truncate语句不能带where条件，意味着只能删除全部数据，而delete可以删除部分数据；

6）truncate操作会重置表的高水位线（High Water Mark），而delete不会。（？？？）

ps: 有关高水位线HWM知识清参考文章：http://blog.csdn.net/indexman/article/details/25910255

2、数据库三种设计范式的介绍

1）第一范式（1NF）

      在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

      所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

      简言之，第一范式就是无重复的列。

2）第二范式（2NF）

      第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一的区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或者主键、主码。

      第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

      简言之，第二范式就是非主属性非部分依赖于主关键字。（？？？）

3）第三范式（3NF）

      满足第三范式（3NF）必须先满足第二范式（2NF）。即，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

      简言之，第三范式就是属性不依赖于其它非主属性。

3、集合操作符总结：

1）UNION：由每个查询选择的所有不重复的行组成。并集不包含重复值，默认按第1个查询的第一列升序排列。

2）UNION ALL：由每个查询选择的所有行，完全并集包含重复值，不排序。

3）MINUS：在第一个查询中，不在后面查询中的行。不包含重复，按第1个查询的第1列升序排列。

4）INTERSECT：取每个查询结果的交集，不包含重复行，按第1个查询的第1列升序排列。

4、NVL与NVL2两个函数的用法和区别？

1）NVL（expr,expr2）：expr1为null，返回expr2；不为null，返回expr1.注意两者的类型要一致。

2）NVL2（expr1，expr2，expr3）：expr1不为null，返回expr2；expr1为null，返回expr3。如果expr2和expr3类型不同，expr3会转换成expr2的类型。

5、如何只显示重复数据，或不显示重复数据？

显示重复数据： select * from tablename group by id having count(*) > 1;

不显示重复数据：select * from tablename group by id having count(*) = 1;

6、什么是数据库映射？

就是将数据库的表与字段对应到模型层类名与属性的过程。

7、什么是反射、序列化、反序列化？事务有几种级别？

反射是在程序运行时动态访问DDL的一种方式。

序列化是将对象用二进制、XML等方式直接向文件的存储。

反序列化是指将存储到文件的对象取出的过程。

事务的级别分为三种：页面级、应用程序级、数据库级。

8、什么时候会用到触发器？

A安全管理、B日志管理、C复杂业务逻辑实现

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

      触发器是可以由事件来启动运行的，存在于数据库服务器中的一个过程。

      触发器作用：可以实现一般的约束无法完成的复杂约束，从而实现更为复杂的完整性要求。

      使用触发器并不存在严格的限定，只要用户想在无人工参与的情况下完成一般的定义约束不可以完成的约束，来保证数据库完整性，那么就可以使用触发器。

      由于触发器主要是用来保证数据库的完整性的，所以要创建一个触发器，首先要明确该触发器应该属于哪一种（DML，INSTEAD OF，SYSTEM）因为它们各有各的用途；其次就是要确定触发器被触发以后所涉及到的数据。

      触发器中不可以使用COMMIT。

10、触发器的概念，存储过程的概念

      触发器：是存储在数据库中的过程，当表被修改（增、删、改）时，它隐式地被激发。

      存储过程：是数据库语言SQL的集合，同样也存储在数据库中，但是它是由其它应用程序来启动运行或者也可以直接运行。

11、Oracle中查询系统时间

      select sysdate from dual;

12、Oracle中常用的函数

      Length   长度、lower   小写、upper   大写， to_date   转化日期，to_char   转化字符、

      Ltrim   去左边空格、rtrim   去右边空格，substr   取字符串 、add_month   增加或者减掉月份、to_number   转变为数字

13、解释冷备份和热备份的不同点以及各自的优点？

      冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种方法。

      热备份是在数据库运行的情况下，采用归档方式备份数据的方法。

      冷备份的优点：

      1）是非常快速的备份方法（只需要拷贝文件）

      2）容易归档（简单拷贝即可）

      3）容易恢复到某个时间点上（只需要把文件再拷贝回去）

      4）能与归档方法相结合，作数据库“最新状态”的恢复

      5）低度维护，高度安全

      冷备份的不足：

      1）单独使用时，只能提供到“某一时间点上”的恢复

      2）在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

      3）若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度回很慢。

      4）不能按表或按用户恢复。

      热备份的优点：

      1）可在表空间或数据文件级备份，备份时间短。

      2）备份时数据库扔可以使用。

      3）可达到秒级恢复（恢复到某一时间点上）。

      4）可对几乎所有数据库实体作恢复。

      5）恢复是快速的，在大多数情况下在数据库仍工作时恢复。

      热备份的不足：

      1）不能出错，否则后果严重。

      2）若热备份不成功，所得结果不可用于时间点的恢复。

      3）因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

14、解释data block，extent和segment的区别？

      1）data block：数据块，是Oracle最小的逻辑单位，通常Oracle从磁盘读写的就是块。

      2）extent：区，是由若干个相邻的block组成。

      3）tablespace：表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件。

15、Oracle的导入导出有几种方式？有何区别？

      1）使用Oracle工具   exp/imp

      2）使用plsql相关工具

      方法一：导入/导出的是二进制的数据；方法二：plsql导入/导出的是sql语句的文本文件。

16、Oracle中字符串用什么符号链接？

      Oracle中使用   ||   这个符号链接字符串。例如：‘abc’   ||   ‘d’

17、Oracle分区是怎样优化数据库的？

      Oracle的分区可以分为：列表分区、范围分区、散列分区、符合分区。

      1）增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用；

      2）减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，可能比整个大表修复花的时间更少；

      3）维护轻松：如果需要建表，独立管理每个公区比管理单个大表要轻松得多；

      4）均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；

      5）改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；

      6）分区对用户透明，最终用户感觉不到分区的存在。

18、Oracle是怎样分页的？

      Oracle中使用rounum来进行分页，这个是效率最好的分页方法，Hibernate也是使用rownum来进行Oracle分页的。

      select * from

            (select rownum r,  a from tablename where rownum <= 20)

      where r > 10;

19、Oracle怎样存储文件？能够存储哪些文件？

      Oracle 能存储 clob 、nclob、blob、bfile

      clob：可变长度的字符串数据，也就是其它数据库中提到的文本型数据类型

      nclob：可变字符类型数据，不过其存储的是Unicode字符集的字符数据

      blob：可变长度的二进制数据

      bfile：数据库外面存储的可变二进制数据

20、数据库怎样实现每隔30分钟备份一次？

      通过操作系统的定时任务调用脚本导出数据库。

21、怎样优化Oracle数据库？有几种方式？（转载）

      影响数据库性能的最关键因素在于IO，因为操作内存是很快的，但是读写磁盘的速度很慢，所以优化数据库的关键在于减少磁盘的IO。可以分为物理优化和逻辑优化，物理优化是指Oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化。

      物理优化的一些原则：

      1）Oracle的运行环境（网络，硬件等）

      2）使用合适的优化器

      3）合理配置Oracle实例参数

      4）建立合适的索引（减少IO）

      5）将索引数据和表数据分开在不同的表空间上（降低IO冲突）

      6）建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO）

      逻辑优化：

      1）可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用；

      2）sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如：全部大写，全部小写等）

      3）数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能。

      4）尽量不适用*符号，如：select * from Emp，因为要转化为具体的列名是要查数据字典的，所有比较耗时

      5）Where字句 规则（？？？）

      6）使用Exits  Not Exits替代In  Not in

      7）合理使用事务，合理设置事务隔离性

            数据库的数据操作比较消耗数据库资源，尽量使用批量处理，以降低事务操作次数

22、如何使用Oracle的游标？

      1）Oracle中的游标分为显式游标和隐式游标；

      2）显式游标是用cursor……is命令定义的游标，它可以对查询语句（select）返回的多条记录进行处理；隐式游标是在执行插入（insert）、删除（delete）、修改（update）和返回单条记录的查询（select）语句时由PL/SQL自动定义的

      3）显式游标的操作：打开游标、操作游标、关闭游标；PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它

23、Oracle中function和procedure的区别？

      1）可以理解为函数时存储过程的一种；

      2）函数可以没有参数，但是一定需要一个返回值，存储过程可以没有参数，不需要返回值；

      3）函数return返回值没有返回参数模式，存储过程通过out参数返回值，如果需要返回多个参数则建议使用存储过程；

      4）在sql数据操作语句中只能调用函数而不能调用存储过程。