Oracle程序员面试分类模拟36

Oracle程序员面试分类模拟36简答题1.

如果执行了rm-rf操作删除了所有的基于FS的数据文件，但是数据库还处于OPEN状态，那么，在这种情况下如何快速地恢复数据库呢?正确答案：这里的前提条（江南博哥）件是没有任何可用的RMAN备份、数据库冷备份等，也就是说，没有任何备份。在这种情况下可以通过系统的文件句柄号来恢复数据文件。整个恢复过程可以简单分为如下几步：

(1)找到被删除文件的文件句柄所在的目录首先通过命令“ps-ef|grepora_lgwr”找到LGWR的进程号。假设这里的进程号为31863，则被删除的文件句柄在/proc/31863/fd目录下。

(2)采用操作系统cp命令复制文件句柄到原数据库文件路径假设这里看到的是如下的情况，被删除的文件末尾一般都有deleted标识。

执行cp命令，复制数据文件到原路径：

需要注意的是，最好使用Oracle用户去执行cp命令。如果使用root用户执行cp命令的话，那么Oracle进程是没有权限操作的。当然也可以在使用root用户复制完数据文件后，再执行赋权操作，命令如下：

但是一定要注意，必须要等全部数据文件恢复后才可以执行chown操作。因为一旦执行了该操作，原来的ora进程就会停止，那么就不能恢复所有的数据文件了。

(3)其他检查工作，例如数据检查、备份等最后需要特别注意的是，当执行操作系统命令rm的时候，切记不可随意加-rf参数，就算一定要用，也要再三确定后才能执行，否则对于数据库而言，可以说是灾难性的。由于rm操作是在数据库OPEN状态下直接进行了破坏性操作，对于RedoBuffer还来不及写入联机Redo日志文件的那部分操作，肯定是会丢失的。因为通过文件句柄号恢复出来的日志文件中，并不一定包含数据库的最新变更。即便如此，本小节对于rm-rf误操作的恢复，还是有一定意义的，至少可以在没有任何备份的情况下，多了一项保障来拯救数据库。最后再次强调一下，执行rm-rf后，千万不要着急地关闭数据库重启，否则在没有任何备份的情况下基本上是很难恢复数据文件的。

2.

Oracle如何判定实例是否运行?正确答案：在启动Oracle实例之前，必须定义ORACLE_SID，Oracle根据SID的HASH值来唯一确定一个实例的地址。当打开SQL*Plus工具，输入“sqlplus/assysdba”以后，系统根据SID进行HASH，查找在共享内存中是否有相应的共享内存段(SHMID)存在，如果有，那么返回connected，否则返回connecttoanidleinstance，这个实例名是存放在SGA中的variablesize中。

通过ORADEBUGIPC来得到variable所存放的SHM的SHMID号，在OS下使用ipcrm-mSHMID可以删掉这一段共享内存。另外，在OS级别也可以使用sysresv命令来获取SHMID号。

ORADEBUGIPC命令如下：

sysresv命令如下：

3.

什么是内存碎片?什么是内碎片?什么是外碎片?正确答案：内存碎片是由于多次进行内存分配造成的，当进行内存分配时，内存格式一般为：(用户使用段)(空白段)(用户使用段)，当空白段很小的时候可能不能提供给用户足够多的空间，比如夹在中间的空白段的大小为5，而用户需要的内存大小6，这样会产生很多的间隙造成使用效率的下降，这些很小的空隙称为碎片。

内碎片：分配给程序的存储空间没有用完，有一部分是程序不使用，但其他程序也没法用的空间。内碎片是处于区域内部或页面内部的存储块，占有这些区域或页面的进程并不使用这个存储块，而在进程占有这块存储块时，系统无法利用它，直到进程释放它，或进程结束时，系统才有可能利用这个存储块。

外碎片：由于空间太小，小到无法给任何程序分配(不属于任何进程)的存储空间。外部碎片是出于任何已分配区域或页面外部的空闲存储块，这些存储块的总和可以满足当前申请的长度要求，但是由于它们的地址不连续或其他原因，使得系统无法满足当前申请。

内碎片和外碎片是一对矛盾体，一种特定的内存分配算法，很难同时解决好内碎片和外碎片的问题，只能根据应用特点进行取舍。

4.

如何彻底杀掉(结束)会话?V$SESSION的STATUS为KILLED的情况下如何找到相关的后台OS进程?正确答案：一般情况下，可以通过执行SQL语句“ALTERSYSTEMKILLSESSION'SID,SERIAL#'”直接杀掉(结束)会话，当SESSION是ACTIVE的时候，ALTERSYSTEMKILLSESSION只是将SESSION的状态标识为KILLED,SERVER变为PSEUDO状态，但并不会释放SESSION持有的资源，所以，在执行完ALTERSYSTEMKILLSESSION后，会话还是一直存在。执行命令KILLSESSION的时候，后边加上IMMEDIATE，这样在没有事务的情况下，相关会话就会立即释放而不会变为KILLED的状态，当有事务存在的情况下，会先进行回滚相关的事务，然后释放会话所占有的资源。

另外，由于变为KILLED状态的回话的PADDR列都变成了另外一个值，因此，通过平常的连接方式就没有办法关联到后台进程，在Oracle11g下提供了CREATOR_ADDR列，该列可以关联到后台进程，对于Oracle10g可以通过特殊的SQL找到后台的进程号。

Oracle10g可以使用如下的脚本：

Oracle11g可以使用如下的脚本：

5.

在RMAN的备份中如何列出1430到1437之间的归档日志?正确答案：可以使用LIST命令，如下：

若要列出当前数据库的所有归档日志，可以使用如下命令：

6.

可以从dmp文件获取哪些信息?正确答案：在开发中常常碰到，需要导入dmp文件到现有数据库。这里的dmp文件可能来自于其他系统，所以，一般情况下是不知道导出程序(exp)的版本、导出时间或者导出模式等信息的。那么如何从现有的dmp文件中获取到这些信息呢?下面作者将一一讲解。

(1)获取基本信息：导出的版本、时间、导出的用户

下面的示例中，exp_ddl_lhr_02.dmp是生成的dmp文件：

(2)获取dmp文件中的表信息下面的示例中，exp_ddl_lhr_02.dmp是生成的dmp文件：

(3)解析dmp文件生成parfile文件下面的示例中，exp_ddl_lhr_03.dmp是生成的dmp文件：

(4)如何查看dmp文件的字符集有两种办法可以查看dmp文件的字符集，第一种办法为imp导入命令查看，示例如下：

如果NLS_LANG的值和当前数据库的字符集相同，那么将不显示“serveruses”和“importserveruses”行。如果没有显示“exportclient”行，那么说明当前dmp文件的字符集和当前的NLS_LANG环境变量的值相同。无论是使用exp还是imp工具都会显示当前的NLS_LANG环境变量的值(表现为“Exportdone”、“importdone”)。

第二种查看dmp文件字符集的办法是，以十六进制的方式打开dmp文件，然后查看第2和第3个字节。如下：

然后在数据库中可以查到十六进制0369代表的字符集：

以上结果说明dmp文件的字符集是UTF8。常用的US7ASCII、ZHS16GBK和AL32UTF8对应的字符集ID如下：

若dmp文件在Windows平台下，则可以使用软件UltraEdit(UE)、EditPlus或Pilotedit等文本编辑工具以十六进制的方式打开dmp文件查看。其中，软件Pilotedit可以轻松打开GB以上大小的文件。示例如下：

需要注意的是，十六进制在Linux和Windows下顺序不同。

如果将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中，那么还需要根据文件修改第4行的第3～4个字节(即07D0之前的2个字节)，如下：

修改前(字符集为US7ASCII)：

修改后：

其实，也可以把第一行的第2和第3字节，第4行的第1～4字节(即07DO之前的4个字节)全部修改掉，也可以成功导入，如下：

7.

DELETE了一条数据并且提交了，该如何找回?正确答案：在Oracle中可以通过闪回技术来找回已经删除并且提交了的数据。当然，除了闪回技术外还可以采用LogMiner(使用该工具可以轻松获得Redo日志文件包含归档日志文件中的具体内容)进行日志挖掘，找出其撤销SQL并执行就可以找回DELETE语句删除的数据。

8.

怎么从备份片(BACKUPPIECE)中恢复(RESTORE)控制文件与数据文件?正确答案：可以使用如下方法，在RMAN中恢复备份片的控制文件：

如果是自动备份，可以采用如下的方法：

但是，如果控制文件全部丢失，需要指定DBID，命令为“SETDBID=?”。自动备份控制文件的默认格式是%F，这个格式的形式为“c-IIIIIIIIII-YYYYMMDD-QQ”，其中IIIIIIIIII就是DBID。可以采用包DBMS_BACKUP_RESTORE来恢复(RESTORE)数据文件，当然该包也可以用来恢复备份片中的控制文件。该包在NOMOUNT状态下就可以执行，如下：

9.

用户的权限分为哪几类?如何导出用户的权限?正确答案：权限是指执行特定类型SQL命令或访问其他用户对象的权利，包括系统权限(SystemPrivilege)、对象权限(ObjectPrivilege)、角色权限(RolePrivilege)、列权限(ColumnPr]vilege)。

(1)系统权限

系统权限是指执行特定类型SQL命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。通过查询系统表SYSTEM_PRIVILEGE_MAP可以获取所有系统权限，查询视图DBA_SYS_PRIVS可以获取每个用户拥有的系统权限。以下示例显示了SCOTT用户具有的系统权限：

一般情况，授予系统权限是由DBA完成的，如果用其他用户来授予系统权限，那么要求该用户必须具有GRANTANYPRIVILEGE的系统权限。在授予系统权限时，可以带有WITHADMINOPTION选项，这样，被授予权限的用户或角色还可以将该系统权限授予其他的用户或角色。

(2)对象权限

对象权限是指访问其他用户(SCHEMA)对象的权利，用户可以直接访问自己用户的对象，但是如果要访问别的用户的对象，那么必须具有该对象的相应权限。常用的对象权限有：ALTER、DELETE、SELECT、INSERT、UPDATE等。通过数据字段视图DBA_TAB_PRIVS可以查询用户或角色所具有的对象权限。下列示例列举出了所有的对象权限的种类：

可以单独赋权，也可以多个权限用逗号隔开：

也可以使用ALL来赋权：

(3)列权限

可以基于列进行赋权，只不过只能赋予INSERT、REFERENCES和UPDATE的权限，举例如下：

基于列的权限可以查询DBA_COL_PRIVS视图。

(4)角色权限

角色即用户权限的集合，可以对用户直接赋予某一个角色，这样，该用户就拥有了角色的所有权限。如果想查询角色所拥有的权限，那么可以通过视图DBA_SYS_PRIVS来查询；如果想查询某个用户拥有哪些角色，那么可以通过视图DBA_ROLE_PRIVS来查询。

角色权限需要注意默认角色(DefaultRole)的问题。一个用户一旦被赋予某个角色之后，其默认角色为YES，即角色权限处于激活状态，该角色拥有的权限是生效的；若默认角色为NO，则代表目标用户被赋予了某个角色，但是该角色拥有的权限并没有生效。可以使用如下SQL语句让角色生效：

(5)如何获取用户的权限

若要获取一个用户的角色、系统权限、对象权限以及列权限，则可以通过以上介绍的数据字典视图来获取也可以通过DBMS_METADATA.GET_GRANTED_DDL来获取。

若使用SYS用户创建了如下的用户LHRSYS并赋予相应的权限：

既可以通过数据字典来编写程序，也可以创建视图来获取一个用户的所有权限。通过视图VW_USER_PRIVS_LHR(视图内容参考随书提供的pdf文档)来直接查询某个用户的权限：

通过系统包DBMS_METADATA.GET_DDL也可以获取用户的权限信息，如下：

将结果拷贝出来简单地用文本编辑工具编辑后即可运行。

另外还可以通过exp或expdp来获取用户的权限，这里不再演示。

10.

什么是EZCONNECT方式连接数据库?正确答案：如果对tnsnames.ora文件没有做相应的配置，那么也可以使用Oracle的EZCONNECT(EasyConnectNarrungMethod)方式来连接到数据库，其语法形式如下：

需要注意的是，如果想要使用EZCONNECT方式连接到数据库，那么需要在客户端的sqlnet.ora文件中增加“NAMES.DIRECTORY_PATH=(EZCONNECT)”信息，否则该功能将无法使用，默认是有该配置的。一般配置如下：

例如：若listner配置TCP协议，使用主机名，端口为1521,service_names=，则：

1)连接到数据库：sqlplusyinfu/yinfu@30:1521/。

2)测试连通性：tnsping30:1521/。

11.

在控制文件丢失且无备份的情况下如何恢复?正确答案：重建控制文件，在重建控制文件之前，需要使用包DBMS_BACKUP_RESTORE来抽取数据文件，其实面试官主要想看求职者是否知道有这个包的存在而已。

12.

什么是外部表?正确答案：外部表是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。外部表只能在Oracle9i之后的版本来使用。

Oracle外部表用来存取数据库以外的文本文件(TextFile)或Oracle专属格式文件。因此，建立外部表时不会产生段、区、数据块等存储结构，只有与表相关的定义放在数据字典中。外部表仅供查询，不能对外部表的内容进行修改(例如INSERT、UPDATE、DELETE等操作)。不能在外部表上建立索引。因为创建索引就意味着要存在对应的索引记录，而其实外部表的数据没有存储在数据库中，故在外部表上是无法建立索引的。

如果外部表采用PARALLEL的方式加载的话，那么加载的数据是无序的。所以，这种情况需要综合考虑，尤其是在使用该方式来查看告警日志文件内容的时候需要特别注意。

1)外部表有如下几点特性：

①外部表的数据位于文件系统之中，并按一定格式分割。文本文件或者其他类型的表可以作为外部表。操作系统文件在数据库中的标志是通过一个逻辑目录来映射的，所以外部表需要在Oracle数据库“服务端”创建目录，这些OS文件必须放在这些目录中。

②对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。

③外部表是只读的，因此，只能对外部表进行SELECT操作，不能对外部表执行DML(DELETE、UPDATE和INSERT等)操作，也不能创建索引，但是可以创建视图，也可以创建同义词。

④ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。

⑤可以对外部表执行查询、连接和并行操作。

⑥外部表不支持LOB对象。

2)与外部表相关的几个视图如下：

13.

什么是SQL实时监控?正确答案：在Oracle11g中，V$SESSION视图增加了一些新的字段，这其中包括SQL_EXEC_START和SQL_EXEC_ID，这两个字段实际上代表了Oracle11g的一个新特性：实时的SQL监控(RealTimeSQLMonitoring)。

在Oracle11g中，当SQL并行执行时，会立即被实时监控到，或者当SQL单进程运行时，若消耗超过5s的CPU或I/O时间，则它也会被监控到。另外，若使用/*+monitor*/提示的SQL语句，则也会被强制监控到。若使用/*+no_monitor*/提示的SQL语句则不会被监控到。监控数据被记录在V$SQL_MONITOR视图中，当然也可以通过Oracle11g新增的包DBMS_MONITOR来主动对SQL执行监控部署。

V$SQL_MONITOR这个视图还记录了SQL的CPU_TIME以及BUFFER_GETS等重要信息，对于诊断SQL性能问题具有极大的帮助。结合V$SQL_MONITOR视图与V$SQL_PLAN_MONITOR视图可以进一步查询SQL的执行计划等信息。联合一些其他视图，如V$ACTIVE_SESSION_HISTORY、V$SESSION、V$SESSION_LONGOPS、V$SQL、V$SQL_PLAN等，可以获得关于SQL的更多信息。

V$SQL_MONITOR收集的信息每秒刷新一次，接近实时。当SQL执行完毕，信息并不会立即从V$SQL_MONITOR中删除，至少会保留1min。V$SQL_PLAN_MONITOR视图中的执行计划信息也是每秒更新一次，当SQL执行完毕，它们同样至少被保留1min。

实时SQL监控需要STATISTICS_LEVEL初始化参数设置为TYPICAL或ALL。同时CONTROL_MANAGEMENT_PACK_ACCESS参数必须是DIAGNOSTIC+TUNING(默认设置)。

可以使用如下的SQL获取文本格式的SQL报告：

在有外网的情况下，可以通过如下的脚本获取HTML格式的报告：

实时SQL监视通过HTML查看其峪视报告时，具有更好的图形化的展示效果，它是基于Flash的基础进行展现，动态、图形画，让SQL的执行过程直观地展示出来。如果监视的SQL语句发现具有全表扫描等执行计划的特征，或者CPU时间和I/O时间比较长，那么可以与SQL调优顾问接合起来，不但能获知性能瓶颈，而且能获得Oracle推荐的优化策略。

14.

有哪些角色可以管理ASM实例，它们之间的区别有哪些?正确答案：能够管理ASM实例的角色有三种，见下表。

能够管理ASM实例的角色

通常oracle用户和grid用户必须是asmdba组成员，此外，grid用户还必须是asmadmin、asmoper组的成员。安装ASM或RAC时默认创建ASMSNMP用户，该用户为OPEN状态并被赋予了SYSDBA权限。OEM需要该用户来监控ASM实例并从ASM实例相关的数据字典中获取数据。在ASM实例中，通过视图V$PWFILE_USERS可以查看该用户的信息。

15.

并发操作可能会产生哪几类数据不一致?用什么方法能避免各种不一致的情况?正确答案：并发操作带来的数据不一致性包括三类：丢失修改、不可重复读和读“脏”数据。①丢失修改(LostUpdate)：两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了(覆盖了)T1提交的结果，导致T1的修改被丢失。②不可重复读(NonrepeatableRead)：不可重复读是指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。③读“脏”数据(DinyRead)：读“脏”数据是指事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据。

避免不一致性的方法和技术就是并发控制。最常用的技术是封锁技术。也可以用其他技术，例如在分布式数据库系统中可以采用时间戳方法来进行并发控制。

16.

事务的概念及其4个特性是什么?正确答案：事务是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。事务通常以BEGINTRANSACTION开始，以COMMIT或ROLLBACK操作结束，COMMIT即提交，提交事务中所有的操作、事务正常结束。ROLLBACK即回滚，撤销己做的所有操作，回滚到事务开始时的状态。

事务有4个特性，一般都称之为ACID特性，见表。

17.

PMON清理失败进程的频率是多长?正确答案：PMON(ProcessMonitorProcess)主要的作用有3点：①在进程失败后执行清除工作：回滚事务、释放锁、释放其他资源。②注册数据库。③检测会话的空闲连接时间。PMON负责处理异常结束的进程相关资源的释放。PMON周期性地被唤醒，这个间隔主要由隐含参数“_PKT_PMON_INTERVAL”进行控制，默认为50cs(cs即centisecond，表示百分之一秒，1s=100cs)。例如，可以通过命令“altersystemset"_PKT_PMON_INTERVAL"=5;”将该参数设置为5厘秒(cs)。另外，也可以通过查找出PMON进程的PID号，然后执行命令“oradebugwakeuporapid”来手动唤醒PMON进程。需要注意的是，这里的orapid是Oracle进程的PID，不是OS的PID。PMON进程的orapid一般为2，可以使用命令“SELECTPID，SPIDFROMV$PROCESSPWHERERPROGRAMLIKE'%PMON%';”来查询。可以用事件“altersystemsetevents'10246tracenamecontextforever,level4';”来查看PMON的相关操作。

18.

什么是谓词越界?正确答案：谓词越界即如果对目标列指定的WHERE查询条件不在该列的最大值和最小值之间，CBO就无法判断出针对该列的查询条件的可选择率，所以只能用一个估算值来作为针对该目标列的查询条件的可选择率。如果这个估算的可选择率与实际情况严重不符的话，那么就有可能导致CBO评估出来的Cardinality(基数)出现严重偏差，进而使得CBO选错执行计划。

19.

基表COL_USAGE$的作用是什么?正确答案：从Oracle9i开始引入了SYS.COL_USAGES表用来跟踪列的使用情况，该功能通过隐含参数“COLUMN_TRACKING_LEVEL”来控制。若隐含参数“_COLUMN_TRACKING_LEVEL”的值为0则取消该功能，若隐含参数“_COLUMN_TRACKING_LEVEL”的值为1则表示该功能生效。缺省情况下，该功能是生效的，并且CBO负责将SQL语句中WHERE条件的查询谓词信息保存在该表中，数据库在执行SHUTDOWNNORMAL或者SHUTDOWNIMMEDIATE会自动将该表中的数据清空。

20.

绑定变量是什么?绑定变量有什么优缺点?正确答案：(1)绑定变量的含义及优缺点。通常在高并发的OLTP系统中，可能会出现这样的现象，单个SQL的写法、执行计划、性能都是没问题的，但整个系统的性能就是很差，这表现在当系统并发的数量增加时，整个系统负载很高，CPU占用率接近100%。其实，这种系统性能随着并发量的递增而显著降低的现象，往往是因为这些系统没有使用绑定变量而产生了大量的硬解析所致。因为同一条SQL语句仅仅由于谓词部分变量的不同而在执行的时候就需要重新进行一次硬解析，造成SQL执行计划不能共享，这极大地耗费了系统时间和系统CPU资源。那么怎样才能降低OLTP应用系统的硬解析的数量呢?答案就是使用绑定变量。高并发的OLTP系统若没有使用绑定变量则会导致硬解析很大，这在AWR中的LoadProfile部分可以很容易地看出来。

使用绑定变量能够有效降低系统硬解析的数量。对于同一类型的SQL语句若使用了绑定变量，则SQL文本就变得完全相同了，据此计算出来的哈希值也就完全相同，这就具备了可以重用解析树和执行计划的基础条件。这里的同一类型的SQL语句指的是除SQL文本中对应的输入值不同外其他部分都一模一样的SQL语句。例如，银行的查询余额的SQL语句，在成千上万次查询中都只是账户名不同，而SQL语句的其他部分都一样。若没有使用绑定变量，则每查询一次都必须进行一次硬解析。如果使用了绑定变量，假设每次可以节省0.001s，那么在高并发下上千万次查询节省下来的时间将是非常大的，这在无形中就提高了系统的响应时间。

绑定变量(BindVariable)其实质是变量，