Oracle 笔试面试题详解

1 概述

1. 笔试、面试常考题（题目来源于网络及工作中的自我总结，欢迎补充）

2. 随时更新，相关知识点，会在本人博客中详细说明（搜索关键字）

3. 最后，愿大家都能找到心仪的工作 ^_^

2 笔试

2.1 选择题

1. 下面哪个用户不是 Oracle 缺省安装后就存在的用户？（'A'）
   A.SYSDBA
   B.SYSTEM
   C.SCOTT
   D.SYS
   
   解析：A：SYSDBA 是 SYS 用户登录时，身份验证的方式
       -- connect system/system@orcl as sysdba
        
2. 带有（'B'）子句的 SELECT 语句可以在表的一行或多行放置排他锁
   A.? FOR INSERT
   B.? FOR UPDATE
   C.? FOR DELETE
   D.? FOR REFRESH
   
   解析：A、C、D 这种语法不存在
     
3. 在 Oracle 中，当 FETCH 语句从游标获取数据是，下面叙述正确的是（'C'）
   A.游标打开
   B.游标关闭
   C.当前记录的数据加载到变量中
   D.创建变量保存当前记录的数据
   
   解析：A：open cursor
        B：close cursor
        C: fetch cursor into 变量
        D: var = 值

4. 以下不属于集合操作符的是（'B'）
   A.UNION、UNION ALL
   B.SUM
   C.MINUS
   D.INTERSET

   解析：A：并集 C：差集 D：交集 
        B: 聚合函数   
           
5. 在 Oracle 中，表分区方式（'C'）建议分区数是 2 的幂（2、4、8等），以获得最平均的数据发布
   A.范围分区
   B.列表分区
   C.散列分区
   D.复合分区

   解析：C.散列分区，又称 hash 分区  
   
6. 不属于游标属性的是（'C'）
   A.%NOTFOUND
   B.%FOUND
   C.%ISCLOSE
   D.%ISOPEN
  
   解析：C.%ISCLOSE 没有这种操作

7. 用户（'A'）拥有所有内置的程序包
   A.SYS
   B.SYSTEM
   C.PUBLIC
   D.DEFAULT
  
   解析：A.SYS：Oracel 最高权限用户
   
8. 事务中使用下列 sql 语句不会引起锁定（'A'）
   A.SELECT
   B.INSERT
   C.UPDATE
   D.DELETE
   
9. 在 Windows 操作系统中， Oracle 的（'A'）服务器监听并接受来自客户端应用程序的连接请求
   A.Oracle HOME_NAME TNSListener
   B.Oracle Service SID
   C.Oracle HOME_NAME Agent
   D.Oracle HOME_NAME HTTPServer

10. 使用下列的语句产生序列：CREATE SEQUENCE seq_id_test，Oracle 服务器会预先开辟内存并维持的序列值有（'C'）个
   A.0
   B.10
   C.20
   D.100

   解析：序列参数 cache 的默认值：20
   
11. 以下（'C'）命令可以删除整个表中的数据，并且无法回滚
   A.drop
   B.delete
   C.truncate
   D.cascade

   解析：A、B 删除数据后，会进入 '回收站'
        C 直接清空数据，且无法回滚
        D 级联删除的选项，如：drop table_name cascade;

---

多选题：

1. 在 SQL 提示符下调用 PL/SQL 过程的方法是（'ABC'）
   A.使用 CALL 语句
   B.使用 EXECUTE 语句
   C.使用 RUN 语句
   D.直接使用过程名

   解析：C:dbms_job.run(:jobno)
   
2. 通过命令（'CD'）可以释放锁
   A.insert
   B.delete
   C.rollback
   D.commit
   
   解析：事务管理

3. 执行特定任务的子程序是（'AB'）
   A.函数
   B.过程
   C.程序包
   D.游标
 
   解析：C.程序包：管理子程序
        D.游标：查询数据
        
 4.在 PL/SQL 块中可以使用下列（'BC'）命令
   A.truncate
   B.delete
   C.savepoint
   D.alter table

   解析：A、B 在 pl/sql 使用时，必须加 execute immediate 'sql 语句';
   
5. 同义词有以下（'ABC'）用途
   A.简化 sql 语句
   B.隐藏对象的名称和所有者
   C.提供对对象的公共访问
   D.显示对象的名称和所有者

2.2 填空题

1. 一个用户拥有的所有数据库对象统称为 '模式(schema)'

2. 打开游标用 'open'，游标取值用 'fetch'，关闭游标用 'close'

3. 字符串转换成日期函数的是：'to_date()'，
   日期转换为字符串函数是  : 'to_char()'
   获取当前系统时间的函数是: 'sysdate'

4. 在 Oracle 数据备份中，用 'exp' 命令来导出，用 'imp' 命令来导入 

5. 数据库的约束有那些？ -- 5 种
   (1) '主键约束'：非空且唯一
   (2) '唯一约束'：唯一
   (3) '非空约束'：非空
   (4) '检查约束'：检查列值是否合法
   (5) '外键约束'：外键值必须是参照表的主键或者空值

6. 数据库的三大范式及BC范式？ -- 目的：减少数据冗余
   (1) '1NF'：每一列都不可再分
   (2) '2NF'：在 1NF 基础之上，消除非主属性对主键的 "部分函数依赖"
   (3) '3NF'：在 2NF 基础之上，消除非主属性对主键的 "传递函数依赖"
   (4) 'BCNF'：在 3NF 基础之上，消除主属性对主键的的 "传递函数依赖"
   
7. 事务的概念和四种特性？
   概念：事务是一个操作序列，这些操作要么都做，要么都不做。
   (1) '原子性 A'：在逻辑上是不可分割的操作单元 
   (2) '一致性 C'：事务完成一项工作，将数据库从一种一致性状态换到另一种一致性状态
   (3) '隔离性 I'：多个事务，互不影响
   (4) '持久性 D'：事务一旦提交成功，对数据的修改时永久性的

8. 事务的四种隔离级别？
   概念：每一种级别都规定了事务所作的修改，较低级别的事务通常可以执行更高
        的并发，系统的开销也更低，但也较危险
   (1) Read Uncommitted 未提交读  -- 实际很少用到
   (2) Read Committed   提交读    -- 解决了 "脏读" 的问题
   (3) Repeatable Read  可重复读  -- 解决了 "不可重复读" 的问题
   (4) Serializable     可序列化  -- 解决了 "幻读" 的问题，最高级别的隔离，代价较大

9. sql 语句常见分类
   (1) DDL 数据定义语言: create、alter、drop、truncate
   (2) DML 数据操作语言: insert、update、delete、merge、select ... for update
   (3) DCL 数据控制语言: grant、revoke
   (4) DQL 数据查询语言: select
   (5) TCL 事务控制语言: commit、rollback、savepoint、...

2.3 简答题

1. 创建一个备份控制文件
   alter database backup controlfile to trace;
   
2. 给出数据库正常启动所经历的几种状态
   startup nomount  -- 数据库实例启动
   startup mount    -- 数据库装载
   startup open     -- 数据库打开
   
3. 如何判断一个存储过程是否正在运行？
   select *
     from v$db_object_cache
    where type = 'PROCEDURE'
      and locks > 0
      and pins > 0;

2.4 编码题

1. 创建表空间 'newspace'，数据文件命名为 'newdata.dbf'，存放在 'd:\data' 目录下，
   文件大小为 200MB，设为自动增长，增量 5MB，文件最大 500MB
  
   create tablespace nawspace
   datafile 'd:\data\newdata.dbf'
   size 200m
   auto extend on next 5m maxsize 500m;


2. 假设表空间 'newspace' 已用尽 500MB，现要求增加一个数据文件，存放在
   'e:\appdata' 目录下，文件名为 'appnewspace.dbf'，大小为 500MB，不自动增长
   
   alter tablespace newspace 
   add datafile 'e:\appdata\appnewspace.dbf' 
   size 500m;


3. 以系统管理员身份登录，创建账号 'tom'，设置 tom 的默认表空间 'newspace'。
   为 'tom' 分配 connect 和 resource 系统角色，获取基本的系统权限，
   然后为 'tom' 分配对用户 'scoot' 的表 emp 的 select 权限 和 
   属性 mgr、hiredate 的 update 权限
   
   create user tom identified by tom 
   default tablespace newspace;

   grant connect, resource to tom;
   grant select, update(salary, mgr) on scott.emp to tom;


4. 按如下要求创建表 class 和 student
   属性		 类型		 默认值		约束		备注
   class_no  数值(2)                主键		'班级号'
   cname     可变字符(10)  		   非空     班级名称
   
   stu_no	 数值(8)		     		主键	    '学号'
   sname	 可变字符(12)            非空     姓名
   sex       字符(2)      男        	        性别
   birthday  日期	                        生日	  
   email	 可变字符(20)            唯一    电子邮件
   score     数值(5, 2)             检查     成绩        
   class_no  数值(2)                外键     "班级号"

   create table class (
	 class_no number(2) constraint pk_class_class_no primary key,
     cname    varchar2(10) not null
   );
   comment on table class is '班级信息表';
   comment on column class.class_no is '班级号';
   comment on column class.cname is '班级名称';
  
  
5. 在表 student 的 sname 属性上创建索引 'idx_student_sname'
   create index idx_student_sname on student(sname); 


6. 创建序列 seq_student_stu_no，最小值为 1，最大值为 999，从 1 开始，
   增量为 1，缓存为 30，不循环，不排序
   
   create sequence seq_student_stu_no
   minvalue 1
   maxvalue 999
   start with 1
   increment by 1
   cache 30
   cycle
   noorder; 


7. 创建视图 vw_student，要求视图中包含 1 班学生的 stu_no，sname，score，class_no 4 个属性，
   并且具有 with check option 限制

   create or replace view vw_student as
   select s.stu_no,
          s.sname,
          s.score,
          s.class_no
     from student s
    where s.class_no = 1
   with check option;


8. 怎么将相邻记录合并到一条？
   select t.*,
          lag(t.loc, 1, null) over(order by t.deptno) lag_name,
          lead(t.loc, 1, null) over(order by t.deptno) lead_name
    from scott.dept t;
  
   "考点"：分析函数 lag()：提取前 N 条记录，lead()：提起后 N 条记录
    
    
9. 如何查询列中的特殊字符，如通配符 "%" 和 "_"？
   select t.* 
     from scott.dept t
    where t.dname like 'A\%' escape '\';

   "考点"：escape 代表将后面的字符用于转义

3 面试

1. 不借助第三方工具，怎么查看 sql 的执行计划
   alter session set statistics_level = 'ALL';
   select sql_id from v$sql t where t.text like '%查询语句%';
   select * from table(dbms_xplan.display_cursor('sql_id ',null,'allstats last')); 

2. 什么是索引
   Q：就是为 '表中某列' 建立一个查找 '目录'
   如果没有目录，词典就要一页一页的翻
   有了目录直接翻目录，可以快速定位到要查找的位置	

3. 索引类型
   (1) B-Tree 索引  -- 默认
   (2) 位图索引
   (3) 函数索引

4. 解释 '热备份' 和 '冷备份' 的不同点以及各自的优点
   不同点：
       '热备份' 针对 '归档模式' 的数据库，在数据库仍旧处于 '工作状态时' 进行备份。
       '冷备份' 指在 '数据库关闭' 后，进行备份，适用于所有模式的数据库。
   
   优点：
       '热备份' 的优点在于备份时，数据库仍旧可以被使用，并且可以将数据库恢复到任意一个时间点。
       '冷备份' 的优点在于它的备份和恢复操作相当简单，并且适用所有模式的数据库，数据库性能比归档模式好（因为不必将 archive log 写入硬盘） 

5. 你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题
   Q：重建控制文件，适用 recover 命令

6. 解释 data block，extent，segment 的区别（建议使用专业术语）
   数据块 -> 区 -> 段 -> 表空间
   data block -> extent -> segment -> tablespace
   最小存储逻辑单元
   
7. 给出两个检查表结构的方法
   (1) desc 命令，如： desc scott.emp
   (2) 选中表，右键"描述" 或 "查看"
   (3) dbms_metadata.get_ddl 包，较麻烦，不推荐

8. 比较 truncate 和 delete 命令
   truncate: 一次性清空表记录、DDL 命令、不支持 rollback、不支持 flashback
     delete: 一条条删除表记录、DML 命令、  支持 rollback、 支持 flashback
     
9. 使用索引的理由
   快速查找表记录（data block）

10. 什么是 OLTP 和 OLAP？
    数据处理可以大致分为两类
    OLTP（联机事务处理）：Online transaction processing
    OLAP（联机分析处理）：Online analytical processing

    OLTP：传统关系型数据库的主要应用，实时 DML 操作频繁，日常操作处理。
    OLAP：数据仓库的主要应用，支持复杂的数据分析，侧重决策支持。