数据库程序员面试分模拟题11

数据库程序员面试分模拟题11简答题1.

OS级别删除了数据文件后如何恢复?正确答案：若使用了“ALTERDATABASEDATAFILENOFFLINEDROP;”命令，则并不会删除数（江南博哥）据文件，这个时候可以先ONLINE后再用“ALTERTABLESPACEXXXDROPDATAFILEN;”删除。如果执行“ALTERDATABASEDATAFILENOFFLINEDROP;”后并在OS级别删除了数据文件，那么首先需要使用“ALTERDATABASECREATEDATAFILENAS'/tmp/ts_dd_lhr02.dbf';”来添加一个数据文件，然后再执行RECOVER并ONLINE后再用“ALTERTABLESPACEXXXDROPDATAFILEN:”命令删除。如果产生的日志文件已经丢失，那么目标文件就不能再恢复了，这个时候只能删除表空间了，命令为“DROPTABLESPACEXXXINCLUDINGCONTENTSANDDATAFILES;”。[考点]RMAN

2.

存储过程和函数的区别是什么?正确答案：存储过程和函数都是存储在数据库中的程序，可由用户直接或间接调用，它们都可以有输出参数，都是由一系列的SQL语句组成。

具体而言，存储过程和函数的不同点如下：

1)标识符不同。函数的标识符为FUNCTION，存储过程为PROCEDURE。

2)函数必须有返回值，且只能返回一个值，而存储过程可以有多个返回值。

3)存储过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，在调用函数时，除了用在SELECT语句中，在其他情况下必须将函数的返回值赋给一个变量。

4)函数可以在SELECT语句中直接使用，而存储过程不能。例如，假设已有函数FUN_GETAVG()返回NUMBER类型绝对值，那么，SQL语句“SELECTFUN_GETAVG(COL_A)FROMTABLE”是合法的。[考点]存储过程

3.

在Oracle中，创建视图的语法形式是怎样的?正确答案：在Oracle中创建视图的语法如下：

CREATE[ORREPLACE][FORCE]

VIEW

[SCHEMA.]VIEW_NAME

[(COLUMN1,COLUMN2,...)]AS

SELECT...

[WITHCHECKOPTION][CONSTRAINTCONSTRAINT_NAME]

[WITHREADONLY];[考点]视图

4.

tkprof的作用是什么?正确答案：tkprof是一个调优工具，用于格式化10046事件和SQL_TRACE生成的trace文件。需要注意的是，不能使用tkprof工具来处理10053事件产生的trace文件。在OS命令行中直接输入tkprof就可以获得一个完整的参数列表。tkprof的格式如下：

tkprof

tracefile

outputfile

[options][考点]性能诊断

5.

什么是存储过程?正确答案：存储过程是用户定义的一系列sOL语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值，并且不涉及特定用户表。

存储过程用于执行特定的操作，可以接收输入参数、输出参数，返回单个或多个结果集。在创建存储过程时，既可以指定输入参数(IN)，也可以指定输出参数(OUT)，通过在存储过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行结果传递到应用环境。存储过程可以使对数据库的管理、显示数据库及其用户信息的工作更加容易。[考点]存储过程

6.

NoSQL兴起的原因有哪些?正确答案：大数据技术近几年发展十分迅速，在互联网公司以及传统公司都得到了广泛的应用。传统的关系数据库在应付Web2.0网站，特别是超大规模和高并发的SNS(SocialNetworkSite，社交网)类型的Web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题，而非关系型的数据库NoSQL(NotOnlySQL，泛指非关系型的数据库，意即“不仅仅是SQL”)则由于其本身的特点得到了非常迅速的发展，NoSQL数据库的产生就是为了解决大规模数据集合以及多重数据种类带来的挑战，尤其是大数据应用难题。NoSQL的拥护者们提倡运用非关系型的数据存储，相对于铺天盖地的关系型数据库运用，这一概念无疑是一种全新的思维的注入。NoSQL的兴起原因主要是随着Web2.0时代的到来，关系型数据库越来越不能满足互联网应用的需求，从而产生了NoSQL。这些需求包括：①数据的高并发读写；②数据的高可用性；③海量数据存储；④海量数据的实时分析等。[考点]NoSQL

7.

什么是自连接?正确答案：自连接(SelfJoin)是SQL语句中的一种特殊连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

例如，在SCOTT.EMP中的每一个员工都有自己的MGR(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。如果需要将每一个员工自己的名字及其经理的名字都查询出来，SQL语句应该如何写呢?这个时候就可以用到自连接了。最终SQL如下：

SELECTA.ENAMEWORKER,B.ENAMEMANAGERFROMSCOTT.EMPA,SCOTT.EMPBWHEREA.MGR=B.EMPNOORDERBYA.ENAME;

对于非等值连接，理解起来比较简单，即表和表之间是通过非等值运算符来连接的，例如，＜＞、BETWEEN...AND...等，如下：

SELECT

EMP.EMPNO,EMP.ENAME,DEPT.LOCFROMSCOTT.EMPINNERJOINSCOTT.DEPTONEMP.DEPTNO＜＞DEPT.DEPTNO

WHEREEMPNO=7788ORDERBYEMPNO;

8.

MySQL有哪些命令可以查看锁?正确答案：有如下几个命令：

(1)showprocesslist

“showprocesslist;”显示哪些线程正在运行。如果有SUPER权限，那么就可以看到所有线程。如果有线程在UPDATE或者INSERT某个表，此时进程的status为updating或者sendingdata。“showprocesslist;”只列出前100条，如果想全列出，那么可以使用“showfullprocesslist;”。

下面是一些常见的状态。状态含义Checkingtable正在检查数据表(这是自动的)Closingtables正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，那么就应该确认磁盘空间是否已经满了或者磁盘是甭正处于重负中Connectout复制从服务器正在连接主服务器Copyingtotmptableondisk由于临时结果集大于map_table_size，正在将临时表从内存存储转为磁盘存储，以此节省内存Creatingtmptable正在创建临时表以存放部分查询结果Deletingfrommaintable服务器正在执行多表删除中的第一部分，刚删除第一个表Deletingfromreferencetables服务器正在执行多表删除中的第二部分，正在删除其他表的记录Flushingtables正在执行FLUSHTABLES，等待其他线程关闭数据表Kilied发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程被其他线程锁住了，那么kill请求会在锁释放时马上生效Locked被其他查询锁住了Sendingdata正在处理SELECT查询的记录，同时正在把结果发送给客户端Sortingforgroup正在为GROUPBY做排序Sortingfororder正在为ORDERBY做排序Openingtables这个过程应该会很快，除非受到其他因素的干扰。例如，在执行完ALTERTABLE或LOCKTABLE语句以前，数据表无法被其他线程打开。正尝试打开一个表Removingduplicates正在执行一个SELECTDISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端Reopentable获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表Repairbysorting修复指令正在排序以创建索引Repairwithkeycache修复指令正在利用索引缓存一个一个地创建新索引。它会比Repairbysorting慢些Searchingrowsforupdate正在将符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了Sleeping正在等待客户端发送新请求Systemlock正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-extemal-locking参数来禁止外部系统锁UpgradinglockINSERTDELAYED正在尝试取得一个锁表以插入新记录Updating正在搜索匹配的记录，并且修改它们Userlock正在等待GET_LOCK()Waitingfortables该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSHTABLEStb1_name，ALTERTABLE，RENAMETABLE，REPAIRTABLE，ANALYZETABLE或OPTIMIZETABLEWaitingforhandlerinsertINSERTDELAYED已经处理完了所有待处理的插入操作，正在等待新的请求

(2)showopentables

这条命令能够查看当前有哪些表是打开的。in_use列表示有多少线程正在使用某张表，name_locked表示表名是否被锁，这一般发生在DROP或RENAME命令操作这张表时。所以这条命令不能查询到当前某张表是否有死锁，谁拥有表上的这个锁等。常用命令如下：

showopentablefromdb_name;

showopentableswherein_use＞0;

(3)showengineinnodbstatus\G;

这条命令查询innodb引擎的运行时信息。

(4)查看服务器的状态

showstatuslike'%lock%';

(5)查询INFORMATION_SCHEMA用户下的表

通过INFORMATION_SHCEMA下的INNODB_LOCKS、INNODB_LOCK_WAITS和INNODB_TRX这三张表可以更新监控当前事务并且分析存在的锁问题。

查看当前状态产生的innodb锁，仅在有锁等待时有结果输出：

SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;

查看当前状态产生的innodb锁等待，仅在有锁等待时有结果输出：

SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;

当前innodb内核中的活跃(ACTIVE)事务：

SELECT*FROMINFORMATION_SCHEMA.INNODB_TRX;[考点]锁

9.

会话游标有哪些属性?正确答案：会话游标有4个属性，见下表。属性类型简介适用对象适用SQLSQL%FOUND布尔型最近的FETCH是否提取到数据，表示一条SQL语句被执行成功后受其影响而改变的记录数是否大于或等于1，若是则赋值为TRUE，否则为FALSE。在一条DML语句被执行前，SQL%FOUND的值是NULL隐式游标、显式游标INSERT、DELETE、UPDATE、SELECT...INTO...SQL%NOTFOUND布尔型最近的FETCH是否没有提取到数据，表示一条SQL语句被执行成功后受其影响而改变的记录数是否为0，若是则赋值为TRUE，否则为FALSE。在一条DML语句被执行前，SQL%NOTFOUND的值是NULL隐式游标、显式游标SQL%ROWCOUNT数值型表示最近的一条SQL语句成功执行后受其影响而改变的记录的数最，后续执行的SQL会覆盖SQL%ROWCOUNT的值隐式游标、显式游标SQL%ISOPEN布尔型游标是否打开，当游标打开时返回TRUE。对于隐式游标而言，SQL%ISOPEN的值永远是FALSE显式游标

当执行一条DML语句后，DML语句的结果保存在这4个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果。在这些属性中，SQL%FOUND和SOL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。需要注意的是，若游标属于隐式游标，则在PL/SOL中可以直接使用上表中的属性；若游标属于显式游标，则上表中的属性里“SQL%”需要替换为自定义显式游标的名称。上表中的这4个属性对于动态游标依然适用。[考点]游标

10.

什么是数据库系统的型和值?正确答案：型(Type)是指一类数据的结构和属性的说明，值(Value)是型的一个具体赋值。例如：

记录型:(学号,姓名)

记录值:(08450226,李多多)[考点]数据库系统的组成与结构

11.

如何生成日期格式的文件?正确答案：在Linux/Unix上，使用“'date+%y%m%d'或$(date+%y%m%d)”，如：

touchexp_table_name_'date+%y%m%d'.dmp

DATE=$(date+%y%m%d)

或者：

DATE=$(date+%Y%m%d--date'1daysago')#获取昨天或多天前的日期

在Windows上，使用“%date:～4,10%”，其中4是开始字符，10是提取长度，表示从date生成的日期中，提取从4开始、长度是10的串。如果想得到更精确的时间，那么在Windows上面还可以使用time。[考点]SHELL脚本系列

12.

E-R图向关系模型的转换一般遵循哪些原则?正确答案：E-R图向关系模型的转换一般遵循如下原则：

1)一个实体型转换为一个关系模式。实体的属性就是关系的属性。实体的码就是关系的码。

例如，学生实体可以转换为如下关系模式，其中，学号为学生关系的码：

学生(学号，姓名，出生日期，所在系，年级，平均成绩)。同样，性别、宿舍、班级、档案材料、教师、课程、教室、教科书都分别转换为一个关系模式。

2)一个联系转化为一个关系模式，与该联系相连的各实体的码以及联系的属性转化为关系的属性。该关系的码有如下3种情况：

①若联系为1:1，则每个实体的码均是该关系的候选码。

②若联系为1:n，则关系的码为n端实体的码。

③若联系为m:n，则关系的码为诸实体码的组合。

下面分别来讲解这3种情况。

①联系为1:1

一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。

a)如果转换为一个独立的关系模式，那么与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。

b)如果与某一端对应的关系模式合并，那么需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。

②联系为1:n

一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

a)如果转换为一个独立的关系模式，那么与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。

b)如果与n端对应的关系模式合并，那么在n端实体对应模式中加入1端实体所对应关系模式的码，以及联系本身的属性。而关系的码为n端实体的码。

③联系为m:n

一个m:n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。

3)同一实体集的实体间的联系，即自联系，也可按上述1:1、1:n和m:n三种情况分别处理。

例如，如果教师实体集内部存在领导与被领导的1:n自联系，那么可以将该联系与教师实体合并，这时主键职工号将多次出现，但作用不同，可用不同的属性名加以区分。例如，在合并后的关系模式中，主键仍为职工号，再增设一个“系主任”属性，存放相应系主任的职工号。

4)具有相同码的关系模式可合并。

为了减少系统中的关系个数，如果两个关系模式具有相同的主键，那么可以考虑将它们合并为一个关系模式。合并方法是将其中一个关系模式的全部属性加入另一个关系模式中，然后去掉其中的同义属性(可能同名也可能不同名)，并适当调整属性的次序。[考点]E-R模型

13.

MySQL和Oracle如何修改命令提示符?正确答案：MySQL的默认提示符为“mysql”，可以使用prompt命令来修改，如下：

全局:exportMYSQL_PS1="(\u@\h)[\d]＞"

兰前会话:prompt(\u@\h)[\d]\R:\m:\s＞\_

其中，“\u”代表用户名，“\h”代表服务器地址，“\d”代表当前数据库，“\R:\m:\s”代表时分秒，例如，23:10:10。

Oracle的默认命令提示符为“SQL”，可以使用“SETSQLPROMPT”命令来修改，如下：

SQL＞SHOWSQLPROMPT

sqlprompt"SQL＞"

SQL＞SETSQLPROMPT"_USEW'@'_CONNECT_IDENTIFIER＞"

SYS@lhrdb＞

SYS@lhrdb＞SHOWSQLPROMPT

sqlprompt"_user'@'_connect_identifier＞"

在以上结果中，SYS表示用户，lhrdb表示数据库。注意，以上提示符的“＞”后有一个空格。

如果想全局生效，那么可以修改文件：$ORACLE_HOME/sqlplus/admin/glogin.sql。在glogin.sql文件中添加如下的内容：

SETSQLPROMPT"_USER'@'_CONNECT_IDENTIFIER＞"

这样，每次登录SQL*Plus的时候，SQL提示符就会变为设置的内容。

14.

如何查询表和索引的历史统计信息?正确答案：从Oracle10g开始，当收集表的统计信息的时候，旧的统计数据被保留，如果因为新的统计信息而出现性能问题，旧的统计信息就可以被恢复。历史统计信息保存在以下几张表中：

1)WRI$_OPTSTAT_TAB_HISTORY表的统计信息。

2)WRI$_OPTSTAT_IND_HISTORY索引的统计信息。

3)WRI$_OPTSTAT_HISTHEAD_HISTORY列的统计信息。

4)WRI$_OPTSTAT_HISTGRM_HISTORY直方图的信息。

从视图DBA_TAB_STATS_HISTORY可以查询历史收集统计信息的时间，但是不能查询到行数，所以需要结合基表来查询，查询的SQL语句如下：

SELECTB.OWNER,B,OBJECT_NAMETABLE_NAME,TO_CHAR(D.ANALYZETIME,'YYYY-MM-DDHH24:MI:SS')LAST_ANALYZETIME,

TO_CHAR(D.SAVTIME,'YYYY-MM-DDHH24:MI:SS')CURR_ANALYZETIME,D.ROWCNT

FROMSYS.WRI$_OPTSTAT_TAB_HISTORYD,DBA_OBJECTSB

WHERE

D.OBJ#=B.OBJECT_IDANDB.OBJECTNAMEIN('TEST_STAT','TPCCBOKBAL_TMP','TPCCBOKBALJN','PK_TPCCBOKBAL')

ORDERBYD.OBJ#,D.SAVTIME;

查询索引的历史统计信息的SQL语句如下：

SELECTB.OWNER,B.OBJECT_NAMEINDEX_NAME,TO_CHAR(D.ANALYZETIME,'YYYY-MM-DDHH24:MI:SS')LAST_ANALYZEIIME,

TO_CHAR(D.SAVTIME,'YYYY-MM-DDHH24:MI:SS')CURR_ANALYZETIME,D.ROWCNT,D.BLEVEL,D.LEAFCNT,D.DISTKEY,D.CLUFAC

FROMSYS.WRIS_OPTSTAT_IND_HISTORYD,DBA_OBJECTSB

WHERED.OBJ#=B.OBJECT_IDANDB.OBJECT_NAMEIN('IND_TEST)

ORDERBYD.OBJ#,D.SAVTIME;

默认情况下统计信息将被保留31天，可以使用下面的命令修改：

EXECUTEDBMS_STATS.ALTER_STATSHISTORY_RETENTION(XX);

--xx是保留的天数

注意：这些统计信息在SYSAUX表空间中占有额外的存储开销，所以应该注意并防止统计信息将表空间填满。

SELECTDBMS_STATS.GET_STATS_HISTORY_RETENTIONFROMDUAL;--查询统计信息当前保留的天数。[考点]统计信息

15.

哪个命令可以查看所有数据库?正确答案：运行命令：showdatabases;。[考点]连接数据库

16.

视图的作用有哪些?正确答案：同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表而言，视图的作用类似于筛选。定义视图可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。如果有几台不同的服务器分别存储不同地区的数据，那么当需要将这些服务器上相似结构的数据组合起来的时候，这种方式就非常有用。

数据库视图的作用有以下几点：

1)隐藏了数据的复杂性，可以作为外模式，提供了一定程度的逻辑独立性。

2)有利于控制用户对表中某些列或某些机密数据的访问，提高了数据的安全性。

3)能够简化结构，执行复杂查询操作。

4)使用户能以多种角度、更灵活地观察和共享同一数据。[考点]视图

17.

数据独立性包括哪几个方面?正确答案：数据独立性包括物理独立性和逻辑独立性两个方面。物理独立性是指用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的；逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的。[考点]数据库安全

18.

如何捕获存储过程中出现异常的行号?正确答案：使用函数DBMS_UTILITY.FORMAT_ERROR_BACKTRACE可以获取到出现异常时候的程序的行号，而函数DBMS_UTILITY.FORMAT_ERROR_STACK可以获取到出现异常时的错误信息，作用和SQLERRM一样。[考点]程序处理

19.

什么是ORA-01555?正确答案：在告警日志中记录的ORA-01555(snapshottooold，快照过旧)报错信息类似：

ORA-01555:snapshottooold:rollbacksegmentnumber107withname"_SYSSMU107_1253191395$"toosmall

默认情况，ORA-01555错误发生时不会自动生成跟踪日志文件，但是可以在系统里设置下面的事件，让它在错误发生的同时生成跟踪日志文件：

altersystemsetevents'1555tracenameerrorstacklevel3';

由于回滚段是循环使用的，当事务提交以后，该事务占用的回滚段事务会被标记为非活动，此时的回滚段空间可以被覆盖重用。那么问题就出现了，如果一个查询需要使用被覆盖的回滚段构造前镜像(BeforeImage)实现一致性读，那么此时就会出现Oracle著名的ORA-01555(snapshottooold，快照过旧)错误。需要注意的是，ORA-01555错误是一个安全的错误，它不会造成数据丢失或者损坏，只是会让收到该错误的查询无法继续。

ORA-01555错误的另外一个原因是延迟块清除(DelayedBlockCleanout)。当一个查询触发延迟块清除时，Oracle需要去查询回滚段获得该事务的提交SCN。如果事务的前镜像信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小提交SCN，那么Oracle将无从判断查询SCN和事务提交SCN的大小，此时出现延迟块清除会导致ORA-01555错误。

还有一种导致ORA-01555错误的情况出现在使用SQL*Loader直接方式加载(direct=true)数据时，由于不产生Redo和Undo信息，Oracle直接指定CACHEDCOMMITSCN进行加载数据，在访问这些数据时，有时会产生ORA-01555错误。

知道了ORA-01555错误产生的原因就可以总结出以下方法来解决ORA-01555错误问题：

1)扩大回滚段。因为回滚段是循环使用的，如果回滚段足够大，那么被提交的数据信息就能保存足够长的时间，而那些大事务就可以完成一致性读取。

2)增加UNDO_RETENTION时间。在UNDO_RETENTION规定的时间内，任何其他事务都不能覆盖这些数据。

3)优化相关查询语句，减少一致性读。减少查询语句的一致性读，也可以降低读取不到回滚段数据的风险。

4)减少不必要的事务提交。提交的事务越少，产生的回滚段信息就越少。

5)对大事务指定回滚段。通过以下语句可以指定事务的回滚段：

SETTRANSACTIONUSEROLLBACKSEGMENTorllback_segment;

给大事务指定回滚段，即降低大事务回滚信息覆盖其他事务的回滚信息的概率，又降低了它自身的回滚信息被覆盖的概率。大事务的存在，往往是ORA-01555错误产生的诱因。

6)使用游标时尽量使用显式游标，并且只在需要的时候打开游标，同时将所有可以在游标外做的操作从游标循环中拿出。当游标打开时，查询就开始了，直到游标关闭。减少游标的打开时间，就减少了ORA-01555错误发生的概率。

7)使用回滚表空间自动管理。回滚表空间自动管理是Oracle9i后的特性，Oracle自动管理回滚段的创建和回收，并且Oracle1