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

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

如何确定MySQL是否处于运行状态?如何开启MySQL服务?正确答案：分为Linux和Windows来讨论。

1)在Linux下启动MySQL服务：

（江南博哥）

在Linux下，也可以通过“netstat-nip|grepmysqld”来查看MySQL服务的状态：

[root@testdb/]#netstat

-nlp|grepmysqld

tcp

0

0:::3306

:::*

LISTEN

13853/mysqld

unix2

[ACC]

STREAM

LISTENING

38511

13853/mysqld

/var/lib/mysq157/mysql.sock

也可以使用mysqld_safe命令启动MySQL数据库，通过mysqladmin来关闭MySQL数据库：

在数据库启动的时候可以加上从指定参数文件进行启动，如下：

mysqld_safe--defaults-file=/etc/f&

2)在Windows下启动MySQL服务：

D:\MySQL\MySQL-advanced-5.6.21-win32\bin＞netstartmysql

MySQL服务正在启动...

MySQL服务已经启动成功。

进入Windows的服务可以看到：

[考点]启动

2.

如何格式化输出结果?正确答案：可以使用column命令。[考点]SHELL脚本系列

3.

差异增量备份和累积增量备份的区别是什么?正确答案：数据库备份可以分为完全备份和增量备份。完全数据文件备份是包含文件中所有已用数据块的备份。RMAN将所有块复制到备份集或映像副本中，仅跳过从未使用的数据文件块。完全映像副本可准确地再现整个文件的内容。完全备份不能成为增量备份策略的一部分，它也不能作为后续增量备份的基础。

增量备份就是将那些与前一次备份相比发生变化的数据块复制到备份集中。通过RMAN可以为单独的数据文件、表空间或者整个数据库进行增量备份。增量备份是0级备份，其中包含数据文件中除从未使用的块之外的所有块；或者是1级备份，其中仅包含自上次备份以来更改过的那些块。0级增量备份在物理上与完全备份完全一样。唯一区别是0级备份可用作1级备份的基础，但完全备份不可用作1级备份的基础。要使用增量备份，必须先执行0级增量备份。

通过BACKUP命令中的INCREMENTAL关键字可指定增量备份，可以指定INCREMENTALLEVEL[0|1]。在RMAN中建立的增量备份可以具有不同的级别，每个级别都使用一个不小于0的整数来标识，也就是在BACKUP命令中使用LEVEL关键字指定的，例如，LEVEL=0表示备份级别为0，LEVEL=1表示备份级别为1。每次进行增量备份仅操作那些发生了“变化”的数据块。RMAN中增量备份有两种：差异增量备份(DIFFERENTIAL)和累计增量备份(CUMULATIVE)，它们的区别见下表。方式关键字默认说明差异增量备份DIFFERENTIAL是将备份上次进行的同级或低级备份以来所有变化的数据块，有同级备份同级，无同级备份低级累积增量备份CUMULATIVE否将备份上次进行的低级备份以来所有变化的数据块

差异增量备份和累计增量备份如下图所示。

1)执行0级增量备份的命令为RMAN＞BACKUPINCREMENTALLEVEL0DATABASE;。

2)执行差异增量备份的命令为RMAN＞BACKUPINCREMENTALLEVEL1DATABASE;。

3)执行累积增量备份的命令为RMAN＞BACKuPINCREMENTALLEVEL1CUMULATIVEDATABASE;。

可以对处于NOARCHIVELOG模式的数据库执行任何类型的备份(完全或增量)。当然，前提条件是数据库处于未打开状态。需要注意的是，数据库只能恢复到上次备份时的状态。只有当数据库处于ARCHIVELOG模式时，才可以将其恢复到上次提交事务处理时的状态。[考点]RMAN

4.

FORUPDATE和FORUPDATEOF的区别是什么?正确答案：SELECT...FORUPDATE语句的语法如下：

SELECT...FORUPDATE[OFcolumn_list][WAITn|NOWAIT][SKIPLOCKED];

其中，这个OF子句在涉及多个表时，具有较大作用。若不使用OF指定锁定的表的列，则所有表的相关行均被锁定。若在OF中指定了需修改的列，则只有与这些列相关的表的行才会被锁定。WAIT子句指定等待其他用户释放锁的秒数，以防止无限期的等待。

“使用FORUPDATEWAIT”子句的优点如下：

1)防止无限期地等待被锁定的行。

2)允许应用程序中对锁的等待时间进行更多的控制。

3)对于交互式应用程序非常有用，因为这些用户不能等待不确定的时间。

4)若使用了SKIPLOCKED，则可以越过锁定的行，不会报告由waitn引发的“资源忙”异常报告。

在Oracle10g之前，SELECT...FORUPDATE获取的是2级TM锁，而从Oracle10g开始，SELECT...FORUPDATE获取的是3级TM锁。[考点]锁

5.

如何删除表中重复的记录?正确答案：在平时工作中可能会遇到这种情况，当试图对表中的某一列或几列创建唯一索引时，系统提示ORA-01452：不能创建唯一索引，发现重复记录。这个时候只能创建普通索引或者删除重复记录后再创建唯一索引。

重复的数据可能有这样两种情况：第一种是表中只有某些字段一样，第二种是两行记录完全一样。删除重复记录后的结果也分为两种，第一种是重复的记录全部删除，第二种是重复的记录只保留最新的一条记录。在一般业务中，第二种的情况较多。

1.删除重复记录的方法原理

在Oracle中，每一条记录都有一个ROWID，ROWID在整个数据库中是唯一的，ROWID确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列上的内容都相同，但ROWID不会相同，所以，只要保留重复记录中那些具有最大的ROWID的记录，其余的全部删除。

2.删除重复记录的方法

若想要删除部分字段重复的数据，则使用下面语句进行删除，下面的语句是删除表中字段1和字段2重复的数据：

DELETEFROM表名WHERE(字段1,字段2)IN(SELECT字段1,字段2FROM表名GROUPBY字段1,字段2HAVINGCOUNT(1)＞1);

也可以利用临时表的方式，先将查询到的重复的数据插入一个临时表中，然后进行删除，这样，执行删除的时候就不用再进行一次查询了。代码如下：

CREATETABLE临时表AS(SELECT字段1,字段2,COUNT(*)FROM表名GROUPBY字段1,字段2HAVINGCOUNT(*)＞1);

上面这句话的功能是建立临时表，并将查询到的数据插入其中。有了上面的执行结果，下面就可以进行删除操作了：

DELETEFROM表名AWHERE(字段1,字段2)IN(SELECT字段1,字段2FROM临时表);

假如想保留重复数据中最新的一条记录，应该怎么做呢?可以利用ROWID，保留重复数据中ROWID最大的一条记录即可：

DELETEFROMTABLE_NAMEWHEREROWIDNOTIN(SELECTMAX(ROWID)FROMTABLE_NAMED

GROUPBYD.COL1,D.COL2);

重复数据删除技术可以提供更大的备份容量，实现更长时间的数据保留，还能实现备份数据的持续验证，提高数据恢复服务水平，方便实现数据容灾等。[考点]高级操作

6.

如何填充V$SESSION的CLIENT_INTO列和CLIENT_IDENTIFIER列?正确答案：V$SESSION的CLIENT_INFO列和CLIENT_IDENTIFIER列往往为空，所以需要写登录触发器，然后在触发器中使用如下的存储过程记录这2列的值：

BEGIN

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV','IF_ADDRESS'));

DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('USERENV','HOST'));

END;[考点]系统包

7.

在Undo表空间数据文件丢失的情况下如何恢复?正确答案：恢复大约可以分为3种情况：①有备份，这种情况下直接采用备份的文件进行恢复即可；②无备份但是有完整的归档文件存在，这种情况下可以使用命令“ALTERDATABASECREATEDATAFILE文件号AS'/u01/app/oracle/oradata/lhrdb/undotbs01.dbf'size50m;”来创建丢失的Undo文件，然后使用“RECOVERDATAFILE文件号;”进行数据库文件恢复即可；③无备份，归档文件丢失，在这种情况下的恢复比较复杂。首先应该切换Undo表空间到一个新建的Undo表空间中，并设置原有表空间的管理模式为手动管理模式，然后将隐含参数“_OFFLINE_ROLLBACK_SEGMENTS”设置为TRUE，一些关键性的命令如下：

ALTERSYSTEMSETUNDO_TABLESPACE=UNDOTBS2SCOPE=SPFILE;

ALTERSYSTEMSETUNDO_MANAGEMENT=MANUALSCOPE=SPFILE;

ALTERSYSTEMSET"_OFFLINE_ROLLBACK_SEGMENTS"=TRUESCOPD=SPFILE;

ALTERSYSTEMSET"_CORRUPTED_ROLLBACK_SEGMENTS"='_SYSSMU1$','_SYSSMU2$'SCOPE=SPFILE;

CREATEUNDOTABLESPACEUNDOTBS1DATAFILE'/u03/app/oracte/oradata/ora1024g/undotbs01.dbf'SIZE50MAUTOEXTENDON;

ALTERSYSTEMSETUNDO_TABLESPACE=UNDOTES1

SCOPE=SPFILE;

ALTERSYSTEMSETUNDO_MANAGEMENT=AUTO

SCOPE=SPFILE;

ALTERSYSTEMRESET"_OFFLINE_ROLLBACK_SEGMENTS"SCOPE=SPFILESID='*';

ALTERSYSTEMRESET"_CORRUPTED_ROLLBACK_SEGMENTS"SCOPE=SPFILESID='*';[考点]RMAN

8.

若在使用“PLSQLDeveloper”软件编译存储过程、函数等对象时无响应，则可能的原因是什么?正确答案：在编译某个存储过程的时候，Oracle会自动给这个对象加上DDL锁，同时也会对这个存储过程所引用的对象加锁。在数据库的开发过程中，经常碰到包、存储过程、函数无法编译或使用“PLSQLDeveloper”这款软件进行编译时会导致该软件无法响应的问题，这个时候可以通过查询DBA_DDL_LOCKS或V$ACCESS来获取锁的相关信息。[考点]锁

9.

MySQL的分区表是什么?如何查询MySQL是否支持分区?正确答案：表分区是指根据一定规则，将数据库中的一张表分解成多个更小的、容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成，每个分区都是一个独立的对象。分区有利于管理大表，体现了“分而治之”的理念。一个表最多支持1024个分区。

在MySQL5.6.1之前可以通过命令“showvariableslike'%have_partitioning%'”来查看MySQL是否支持分区。若have_partintioning的值为YES，则表示支持分区。从MySQL5.6.1开始，该参数已经被去掉了，而是用SHOWPLUGINS来代替。若有partition行且STATUS列的值为ACTIVE，则表示支持分区，如下：

此外，也可以使用表INFORMATION_SCHEMA.PLUGINS来查询，如下：

10.

什么是物理读和逻辑读?正确答案：当会话所需要的数据在内存的BufferCache中找不到，此时就要去磁盘上的数据文件中读取，这样就产生了物理读(PhysicalReads)，即物理读是从磁盘文件把需要的数据读入内存(SGA中的BufferCache)。

逻辑读(LogicalReads)表示CPU需要的数据在内存中被找到，数据被直接从内存中传入CPU执行，即逻辑读是从内存中读取。在Oracle中，逻辑读=即时读(又叫当前读，CurrentRead)+一致性读(ConsistentRead)；在“SETAUTOTRACEON”中，逻辑读=dbblockgets(当前读)+consistentgets(一致性读)。即时读就是读取数据块当前的最新数据。任何时候在BufferCache中都只有一份当前数据块。即时读通常发生在对数据进行修改、删除操作时。这时，后台进程会给相关数据加上行级锁，并且标识数据为“脏数据”。

需要注意的是，物理读过大表现为磁盘I/O较高，逻辑读过大表现为CPU使用率过高。[考点]SQL优化

11.

SESSIONS和PROCESSES有什么关系?正确答案：在数据库安装完成后，常常需要设置SESSIONS和PROCESSES的大小。其中，SESSIONS指定了一个实例中允许的会话数，即能同时登录到数据库的并发用户数；PROCESSES指定了一个实例在操作系统级别能同时运行的进程数，包括后台进程与服务器进程。由于一个后台进程可能同时对应多个会话，所以，通常SESSIONS的值大于PROCESSES的值。

通过查找官方文档，可以知道SESSIONS参数的值在Oracle10g和11g中是不同的，见下表。属性Oracle10gOracle11gR2参数值的类型IntegerInteger默认值Derived：(1.1*PROCESSES)+5Derived：(1.5*PROCESSES)+22是否可以动态修改否否范围1～2311～216(即1～65536)注：在Oracle11gR1中，该参数值和10g足一样的，修改语句为“ALTERSYSTEMSETPROCESSES|SESSIONS=200SCOPE=SPFILE;”。

由于SESSIONS的值是根据PROCESSES的值计算得到的，所以，一般情况下只需要设置PROCESSES的值即可。在Oracle11gR2以下版本中，SESSIONS大小的计算公式为(1.1*PROCESSES)+5；在Oracle11gR2中，SESSIONS大小的计算公式为(1.5*PROCESSES)+22。若SESSIONS的当前值比计算值大，则SESSIONS的值可能保持不变；若SESSIONS的当前值比计算值小，则SESSIONS取计算值，即SESSIONS的值总是取MAX(当前值，计算值)，但是这个也不是绝对的。

当数据库连接的并发用户已经达到SESSIONS的值时，又有新会话连接进来，就会报错：“ORA-00018,"maximumnumberofsessionsexceeded"”。

当Oracle需要启动新的PROCESS，而当前的进程数又已经达到PROCESSES参数时，就会报错：“ORA-00020:maximumnumberofprocesses(2048)exceeded”。

如果数据库上连接被占用完，当新的连接过来时，那么就会在客户端产生“ORA-12519,TNS:noappropriateservicehandlerfound”的报错信息。[考点]会话

12.

RAC环境中的SCANIP的作用是什么?正确答案：从Oracle11gR2RAC开始引入SCAN(SingleClientAccessName，集群的单客户端访问名称)IP的概念，相当于在客户端和数据库之间增加一层虚拟的网络服务层，即是SCANIP和SCAPIPLISTENER。在客户端的tnsnames.ora配置文件中，只需要配置SCANIP，然后用户即可访问数据库。客户端通过SCANIP、SCANIPLISTENER来负载均衡地连接到RAC数据库。同之前各版本的RAC相比，使用SCANIP的好处就是，当后台RAC数据库添加、删除节点时，客户端配置信息无须修改。SCAN提供一个域名来访问RAC，域名可以解析1～3个(注意，最多3个)SCANIP，可以通过DNS、GNS或/etc/hosts文件来解析实现。需要注意的是，SCANIP、VIP和PublicIP必须属于同一子网。[考点]RAC

13.

如何在存储过程中暂停指定时间?正确答案：DBMS_LOCK包的SLEEP过程。例如，“DBMS_LOCK.SLEEP(5);”表示暂停5s。[考点]系统包

14.

Oracle数据库在物理上是由哪些文件组成的?正确答案：Oracle数据库物理结构如下图所示。

Oracle数据库的物理结构由控制文件(ControlFiles)、数据文件(DataFiles)、联机Redo日志文件(OnlineRedoLogFiles)、参数文件(ParameterFile)、归档日志文件(ArchiveLogFiles)和密码文件(PasswordFile)组成。

1)控制文件：包含维护和验证数据库完整性的必要信息，其中记录了数据库的物理结构。例如，控制文件用于识别数据文件和Redo日志文件。每个Oracle数据库都有相应的控制文件，一个数据库至少需要一个控制文件，控制文件属于二进制文件。控制文件的命名格式通常为ctr*.ctl。

2)数据文件：存储数据的文件。

3)联机Redo日志文件：包含对数据库所做的更改记录，一个数据库至少需要两组联机Redo日志文件。联机Redo日志文件也叫在线重做日志文件或联机重做日志文件。

4)参数文件：定义Oracle实例的特性，分为SPFILE和PFILE两种类型的参数文件。

5)归档文件：归档文件是联机Redo日志文件的脱机副本，这些归档文件对于介质恢复很重要。

6)密码文件：认证哪些用户有权限启动和关闭Oracle实例。

Oracle中逻辑结构包括表空间(TABLESPACE)、段(SEGMENT)、区(EXTENT)和块(BLOCK)。数据库由表空间构成，而表空间又由段构成，段又由区构成，区又由Oracle块构成，即块→区→段→表空间→数据库。[考点]物理结构

15.

在MySQL中，如何查看表的详细信息，例如，存储引擎、行数、更新时间等?正确答案：可以使用SHOWTABLESTATUS获取表的详细信息，语法为

SHOWTABLESTATUS

[{FROM|IN}db_name]

[LIKE'pattern'|WHEREexpr]

例如：

(1)showtablestatusfromdb_name查询db_name数据库里所有表的信息

(2)showtablestatusfromdb_namelike'lhruse'\G;查询db_name里lhruse表的信息

(3)showtablestatusfromdb_namelike'uc%'查询db_name数据库里表名以uc开头的表的信息

下面的SQL语句查询了mysql数据库中的user表的详细信息：

其中，每列的含义见下表。列名解释Name表名Engine表的存储引擎，在MySQL4.1.2之前，该列的名字为TypeVersion表的.frm文件的版本号Row_format行存储格式(Fixed，Dynamic，Compressed，Redundant，Compact)。对于MyISAM引擎，可以是Dynalmc、Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段Rows行的数目。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。例如MyISAM，存储精确的数目。对于其他存储引擎，比如InnoDB，本值是一个大约的数，与实际值相差可达40～50%。在这些情况下，使用SELECTCOUNT(*)来获得准确的数目。对于在INFORMATION_SCHEMA数据库中的表，Rows值为NULLAvg_row_length平均每行包括的字节数Data_length表数据的大小(和存储引擎有关)Max_data_length表可以容纳的最大数据量(和存储引擎有关)Index_length索引的大小(和存储引擎有关)Data_free对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间Auto_increment下一个Auto_increment的值Create_time表的创建时间Update_time表的最近更新时间Check_time使用checktable或myisamchk工具检查表的最近时间Collation表的默认字符集和字符排序规则Checksum如果启用，则对整个表的内容计算时的校验和Create_options指表创建时的其他所有选项Comment包含了其他额外信息，对于MyISAM引擎，包含了注释。对于InnoDB引擎，则保存着InnoDB表空间的剩余空间信息。如果是一个视图，那么注释里面包含了VIEW字样

也可以使用information_schema.tables表来查询，如下：

SELECTtable_name,Engine,Version,Row_format,table_rows,Avg_row_length,

Data_length,Max_data_length,Index_length,Data_free,Auto_increment,

Create_time,Update_time,Check_time,table_collation,Checksum,

Create_options,table_comment

FROMinformation_schema.tables

WHERETable_Sehema='mysql'andtable_name='user\G;

16.

使用裸设备作为Oracle数据文件有什么需要注意的?正确答案：使用裸设备作为Oracle的数据文件必须注意以下几点：

1)一个裸设备只能放置一个数据文件。

2)数据文件的大小不能超过裸设备的大小，为了简单起见，对所有的文件设置成比裸设备小1MB即可。

3)数据文件最好不要设置成自动扩展，如果设置成自动扩展，一定要把MAXSIZE设置为比裸设备小。[考点]裸设备(RAW)

17.

Linux下启动和关闭网络的命令有哪些?正确答案：Linux下启动和关闭网络的命令如下：

chkconfigNetworkManageroff

chkconfignetworkon

serviceNetworkManagerstop

servicenetworkstart[考点]网络

18.

Oracle10g和11g的自动统计信息收集机制有哪些不同?正确答案：在Oracle10g之前并没有自动收集统计信息的机制，从Oracle10g开始引入了自动收集统计信息的功能，这个功能在Oracle10g中被称为自动统计信息收集(AutomaticStatisticsGathering)，在Oracle11g中被称为自动优化器统计信息收集(AutomaticOptimizerStatisticsCollection)。自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集x$系列表的内部对象统计信息。Oracle的初始化参数STATISTICS_LEVEL控制收集统计信息的级别，有三个参数值：

1)BASIC：收集基本的统计信息。

2)TYPICAL：收集大部分统计信息(数据库的默认设置)。

3)ALL：收集全部统计信息。

当使用Oracle自动收集统计信息时，必须要确保Oracle的参数STATISTICS_LEVEL的值为TYPICAL或者ALL。默认值为TYPICAL，该值可以确保数据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值能胜任大多数的环境，并且Oracle不推荐去修改该值。

DBA可以根据Oracle提供的脚本$ORACLE_HOME/rdbms/admin/catmwin.sql查看统计信息收集作业的整体搭建流程。有兴趣的读者可以研究下此脚本的内容。

Oracle10g和11g的自动统计信息收集机制有所不同，详见下表。

在Oracle11g中对统计信息自动收集的功能进行了加强。在Oracle10g中，如果表中变更的行数(字典表SYS.MON_MODS_ALL$中记录的INSERT+UPDATE+DELETE的总数)超过表的总行数(SYS.TABS中记录的目标表总记录数)的10%时或自上次自动统计信息收集作业完成之后目标表被执行过TRUNCATE操作，那么Oracle就会在指定时间段自动收集统计信息。在Oracle10g中，这个10%(STALE_PERCENT)是无法修改的，如果表非常大，那么10%其实是非常多的数据，这就造成统计信息不准确。在Oracle11g中，这个10%(STALE_PERCENT)是可以修改的，分为全局(DBMS_STATS.SET_GLOBAL_PREFS)、数据库级别(DBMS_STATS.SET_DATABASE_PREFS)、用户级别(DBMS_STATS.SET_SCHEMA_PREFS)和表级别(DBMS_STATS.SET_TABLE_PREFS)。其中，数据库级别和用户级别都是调用表级别的存储过程DBMS_STATS.SET_TABLE_PREFS来对表进行设置的。

表级别的设定如下：

1)修改为5%(范围为1～100)：EXECDBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME',''STALL_PERCENT',5);

2)恢复为10%：EXECDBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);

3)查询表百分比：SELECTDBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME')FROMDUAL;

4)查询全局百分比：SELECTDBMS_STATS.GET_PREFS('STALE_PERCENT')FROMDUAL;

Oracle10g的自动统计信息