Oracle面试题

1.Oracle中 dml、ddl、dcl的使用

DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。

DML包括：

SELECT：用于查询检索数据；

INSERT：用于插入数据到数据库；

UPDATE：用于更新数据到现存的数据

DELETE：用于从数据库中删除数据。

DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。

DDL包括：DDL语句可以用于创建用户和重建数据库对象。

CREATE 创建数据库表

ALTER 修改数据库表

DROP 删除数据库表

DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。

DCL包括：

GRANT 授权

REVOKE 回收权限

ROLLBACK 回滚

2、数据库的三大范式是什么

第一范式：原子件，要求每一列的值不能再拆分了。

第二范式： 一张表只描述一个实体（若列中有冗余数据，则不满足）

第三范式： 所有列与主键值直接相关。

3.oracle中的经常使用到得函数

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

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

4.Oracle中字符串用什么符号链接?

 Oracle中使用 || 这个符号连接字符串 如 ‘abc’ || ‘d’

5.Mysql数据库与Oracle 数据库有什么区别？

1，Mysql是中小型应用的数据库。一般用于个人和中小型企业。Oracle 属于大型数据库，一般用于具有相当规模的企业应用。

2，自动增长的数据类型方面：MySQL有自动增长的数据类型。Oracle没有自动增长的数据类型。需要用触发器建立一个自增序列。

3，group by 用法：Mysql中group by 在SELECT语句中可以随意使用，但在ORACLE中如果查询语句中有组函数，那么其他列必须是组函数处理过的或者是group by子句中的列，否则会报错。

4,引导方面：MySQL中可以用单引号、双引号包起字符串，Oracle中只可以用单引号包起字符串。

6.事务的特性（ACID）是指什么

1）原子性（Atomic）： 事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败。

2) 一致性（Consistent）： 事务结束后系统状态是一样的。

3）隔离性（Isolated）: 并发执行的事务彼此无法看到对方的中间状态。

4） 持久性（Durable）:事务完成后，即使发生灾难性故障，通过日志和同步备份可以在故障发生后重建数据。

7.rowid,rownum的定义

1. rowid和rownum都是虚列

2. rowid是物理地址，用于定位oracle中具体数据的物理存储位置

3. rownum则是sql的输出结果排序

8.索引的作用?和它的优点缺点是什么?

 索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。索引可以是唯一的,创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小。

• 索引使用原则：

1.索引字段建议建立NOT NULL约束

2.经常与其他表进行连接的表，在连接字段上应该建立索引；

3.经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；

4.如果有两个或者以上的索引，其中有一个唯一性索引，而其它是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

5.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

6.小表不要建立索引

7.对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引

8.列中有很多空值，但经常查询该列上非空记录时应该建立索引

9.经常进行连接查询的列应该创建索引

10.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。

9.delete与Truncate区别？

1）Truncate 是DDL 语句，DELETE 是DML语句。

2）Truncate 的速度远快于DELETE；

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

3）delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。

4) Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。

5) Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。

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

7）DELETE可以操作视图，TRUNCATE不能操作视图

10.Oracle中的游标

游标是用来操作数据库中的一组查询结果集。

游标作用：

游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下（返回多行记录时），需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。(大体意思就是：游标（cursor）能够根据查询条件从数据表中提取一组记录，将其作为一个临时表置于数据缓冲区中，利用指针逐行对记录数据进行操作。)

为什么避免使用游标？

游标使用时会对行加锁，可能会影响其他业务的正常进行。而且，数据量大时其效率也较低效。另外，内存也是其中一个限制。因为游标其实是相当于把磁盘数据整体放入了内存中，如果游标数据量大则会造成内存不足。

11.Oracle中function和procedure的区别？

可以理解函数是存储过程的一种

存储过程：

（1）一般用于在数据库中完成特定的业务或任务

（2）可以定义返回类型，也可以不定义返回类型

（3）可返回多个参数

函数：

（1）一般用于特定的数据查询或数据转转换处理

（2）申请时必须要定义返回类型，且程序体中必须定义 return 语句

（3）最多返回一个值

（4）不能独立执行，必须作为表达式的一部分调用

注意：sql数据操纵语句中只能调用函数而不能调用存储过程

12.什么是锁、死锁，如何解决Oracle中的死锁？

锁是数据库用来控制共享资源并发访问的机制。

锁用于保护正在被修改的数据。

直到提交或回滚了事务之后，其他用户才可以更新数据。

当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错，就形成了死锁。

解决办法：

1). 查找出被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode

from v$locked_object a,dba_objects b

where b.object_id = a.object_id;

select b.username,b.sid,b.serial#,logon_time

from v$locked_object a,v$session b

where a.session_id = b.sid order by b.logon_time;

2). 杀进程中的会话

alter system kill session "sid,serial#";

13.sql优化

（1）减少访问数据库的次数

（2）在执行SELECT子句时尽量避免使用 *，因为oracle在解析的过程中，会将*依次转换成列名, 这是通过查询数据字典完成的, 这将耗费更长的时间。

（3）可以好好利用DECODE函数,使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

（4）尽量用TRUNCATE语句替代DELETE语句。当执行TRUNCATE命令时, 回滚段不会存放任何可被恢复的信息，所有数据不能被恢复。因此很少资源被调用，整个执行时间就会很短。

（5）在使用oracle时，尽量多使用COMMIT命令。该命令可以明显释放运行资源，因此程序的性能得到明显提高。

（6）可以通过用索引提高效率。oracle使用了树形的数据结构。通过合理的索引，数据查询时明显快于全表扫描，因此可以显著提高检索数据的效率。

（7）SQL语句尽量用大写的

因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

（8）WHERE子句中的连接顺序：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

(9)根据需要用UNION ALL替换UNION，UNION ALL的执行效率更高

(10)用EXISTS替换DISTINCT：

当SQL包含一对多表查询时,避免在SELECT子句中使用DISTINCT,一般用EXISTS替换,EXISTS查询更为迅速

(11)避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。用Where子句替换HAVING子句

(12)用EXISTS替代IN、用NOT EXISTS替代 NOT IN：在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN改写成外连接(Outer Joins)或NOT EXISTS。

(13)避免在索引列上使用NOT：

NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT,就避免在索引列上使用计算：

(14)WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。会停止使用索引转而执行全表扫描。

(15)用UNION替换OR(适用于索引列):

用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。

(16)总是使用索引的第一个列：

如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

(17)避免改变索引列的类型:

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换

(18)使用表的别名：当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。

(19)避免在索引列上使用 IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 。

（20）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

（21）在FROM 子句中包含多个表的情况下，选择记录条数最少的表作为

基础表。

14.什么是视图？视图有什么作用？如何更改视图？

视图可以理解为数据库中一张虚拟的表。它是建立在已有表的基础上,创建视图所依据的表称为“基表”。通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。

视图的作用？

（1）隐藏数据的逻辑复杂性并简化查询语句

（2） 可以提高数据访问的安全性，通过视图设定允许用户访问的列和数据行

（3）可以将复杂的查询保存为视图

视图上的DML语句有如下限制：

只能修改一个底层的基表

如果修改违反了基表的约束条件，则无法更新视图

如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图。

如果视图包含伪列或表达式，则将无法更新视图。

复杂视图中，可以更新键保留表的列，不可以更新非键保留表的列。

15.CHAR类型和VARCHAR类型有何区别？

char的长度是固定的，最长2000个字符，当输入的字符小于你定义的字符数目时，它会自动补空值，所以它占用空间较大但是效率高；varchar的长度是可变的，用它可以节省存储空间，但是效率没有char高。

16.sql语句执行顺序

1、最先执行from 表名

2、where语句是对条件加以限定

3、分组语句【group by…… having】

4、聚合函数

5、select语句

6、order by排序语句

17.冷备份和热备份的不同点以及各自的优点

热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。

冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。(因为不必将archive log写入硬盘)

18.如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响

在系统比较空闲时;

大的sort_ared_size或pga_aggregate_target较大

19.Oracle的导入导出有几种方式，有何区别？

1). 使用oracle工具 exp/imp

2). 使用plsql相关工具

方法：1.导入/导出的是二进制的数据

2.plsql导入/导出的是sql语句的文本文件

20.Oracle 悲观锁和乐观锁

悲观锁是对数据的冲突采取一种悲观的态度，假设数据肯定会冲突，在数据开始读取的时候就把数据锁定住。乐观锁就是认为数据一般情况下不会造成冲突，只有在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。悲观锁是通过在sql语句上加入 for update，乐观锁可以通过增加一列version或者timestamp在应用程序中实现,Hibernate采用乐观锁的版本戳。