Oracle+10g+OCP+042解析(175题)_甜梦文库
Oracle+10g+OCP+042解析(175题)
QQ:清江石声明：对于答案的相关的说明，是个人对 Oracle 的理解和收集相关资料整理,主要 参考了 tianlesoftware 兄资料,供大家参考学习。 2 跟 154 相同,154 删除 1. Because of a power outage,instance failure has occurred. From what point in the redo log does recovery begin and where does it end? A. Current redo log and inactive redo log B. Checkpoint position to end of redo log C. Beginning of redo log to end of redo log D. All redo logs before the point of last commit E. Beginning of redo log to checkpoint position. 答案 B.Checkpoint 之前的数据已经写入到数据文件。 所以用 restore 就可以恢复。 而 checkpoint 之后的数据 没有写入到数据文件，所以需要进行 recovery。 Recovery 时，对于已经 commit 的数据，前滚写入到数据文件, 没有 commit 的数据，进行回滚。 Oracle 数据库中，对 BUFFER CAHCE 的修改操作是前台进程完成的，但是前台进程只负责将数据块从数据文 件中读到 BUFFER CACHE 中，不负责 BUFFER CACHE 写入数据文件。BUFFER CACHE 写入数据文件的操作是由后台 进程 DBWR 来完成的。DBWR 可以根据系统的负载情况以及数据块是否被其他进程使用来将一部分数据块回写到 数据文件中。这种机制下，某个数据块被写回文件的时间可能具有一定的随机性的，有些先修改的数据块可能 比较晚才被写入数据文件。而 CHECKPOINT 机制就是对这个机制的一个有效的补充，CHECKPOINT 发生的时候， CKPT 进程会要求 DBWR 进程将某个 SCN 以前的所有被修改的块都被写回数据文件。这样一旦这次 CHECKPOINT 完 成后，这个 SCN 前的所有数据变更都已经存盘，如果之后发生了实例故障，那么做实例恢复的时候，只需要从 这次 CHECKPOINT 已经完成后的变化量开始就行了，CHECKPOINT 之前的变化就不需要再去考虑了。Commit 仅仅写日志文件,而不写数据文件 2. Which two operations can be flashed back using the flashback technology? (choose two) A. D B. D C. D D. Alter table sales_rep drop partition p1; E. Alter table employees drop column desig_ 答案：AB。(有疑问?)1. Flashback Database 不能解决 Media Failure， 这种错误 RMAN 恢复仍是唯一选择 2. 如果删除了数据文件或者利用 Shrink 技术缩小数据文件大小，这时不能用 Flashback Database 技术回退到 改变之前的状态，这时候就必须先利用 RMAN 把删除之前或者缩小之前的文件备份 restore 出来， 然后利用 Flashback Database 执行剩下的 Flashback Datbase。 3. 如果控制文件是从备份中恢复出来的，或者是重建的控制文件，也不能使用 Flashback Database。 4. 使用 Flashback Database 所能恢复到的最早的 SCN， 取决与 Flashback Log 中记录的最早 SCN。 答案应该是 A,B,D,EA 可以通过 flashback database 恢复 (OCP Certification All-in-One Exam guide 中文版第 548 页, 28.1.4) B 可用 flashback drop C 这个有点问题.Study Guide 上说 flashback database 无法恢复 drop tablespace,但 All-in-one 那本上说可 以,也是在中文版的第 548 页 (28.1.4) D 可用 flashback database E 可用 flashback database SQL& create table dropcoltest( id number,name varchar2(100)); SQL& select current_scn from v$ 4095011 SQL& alter table dropcoltest drop(name); SQL& ID SQL& select * no rows selected SQL& conn / SQL& SQL& startup mount SQL& flashback database to scn 4095011; SQL& conn /as sysdba SQL& alter dat SQL& conn ganesh/oracle SQL& ID NAME /* Same thing for partition. I could have done both at same time. but for better understanding, doing seperately */ SQL& CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) PARTITION BY RANGE (invoice_date) (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users); SQL& select current_scn from v$ 4095309 SQL& alter table invoices drop partition invoices_q1; SQL& conn /as sysdba SQL& SQL& SQL& flashback database to scn 4095309; SQL& select partition_name from user_tab_partitions where table_name ='INVOICES'; INVOICES_Q1INVOICES_Q2 INVOICES_Q3 INVOICES_Q43. You have to shut down the database instance with the abort option because of a hardware failure. Which statement is true about the subsequent opening of the database? A. The database would open normally. B. The database would not open,and it would stop at mount stage. C. The database would open alter automatically performing instance recovery. D. The database would not open, and have to perform database recovery to open it. 答案： C. 如果实例异常关闭（宕机，shutdown abort），并且数据文件，控制文件，联机日志都没有丢失。 在下次启动时，要利用联机日志的内容进行恢复，这种恢复就是实例恢复（Instance Recovery）。 Instance Recovery 主要包括 3 个阶段： 1） 根据联机日志内容进行 Rollover。(前滚) 2） 打开数据库，提供服务 3） SMON 或者用户进程进行 Rollback。(回滚) 具体参考 Oracle 备份与恢复概述 中的 3.2 节 恢复种类 http://blog.csdn.net/tianlesoftware/archive//5490733.aspx 4. You backed up the control file to trace. Which statement is true about the trace file generated? A. The trace file is in binary format. B. The trace file has a SQL scripts to re-create the control file. C. The trace file is a backup set created during the backup of the control file. D. The trace file contains the instructions to manually re-create the control file. E. The trace file is an image copy of the control file created during the backup of the the control file. 答案： B The trace file has a SQL script. to re-create the control file. Editor?s notes:There are two different ways to backup controlfiles. One is: ALTER DATABASE BACKUP CONTROLFILE TO TRACE (as filename); This statement is used to create a trace file include sql statement for creating controlfile Another is : ALTER DATABASE BACKUP CONTROLFILE TO This statement is used to create binary file.it?s a backup controlfile for current controlfile. 联机文档： An alternative to the CREATE CONTROLFILE statement is ALTER DATABASE BACKUP CONTROL FILE TO TRACE, which generates a SQL script in the trace file to re-create the cont rolfile /docs/cd/B14117_01/server.101/b10759/statements_5003.htm5. While running the Oracle Universal Installer on a Unix platform to install Oracle Database 10g software,you are prompted to run orainstRoot.sh. What does this scripts accomplish? A. It creates the pointer file. B. It creates the base directory. C. It creates the inventory pointer file. D. It creates the Oracle user for installation. E. It modifies the Unix kernel parmeters to match Oracle's requirement. 答案：C 可以查看$ORACLE_BASE/oraInventory/orainstRoot.sh 脚本的内容。该脚本实际上完成了以下工 作： （1）创建 software inventory location pointer file: /etc/oraInst.loc，内容为 inventory_loc=$ORACLE_BASE/oraInventory inst_group=oinstall 修改该文件属性:chmod 644 /etc/oraInst.loc （2）创建 inventory directory: $ORACLE_BASE/oraInventory 修改文件属性： chmod -R 770 $ORACLE_BASE/oraInventory chgrp oinstall $ORACLE_BASE/oraInventory oraInventory 目录是用来存储 oracle 安装的所有软件组件的信息的，每个组件可能占用 150k 的空间. 6. While setting up an Oracle database for one of your critical applications, you want to ensure that the database is backed up at regular intervals without your intervention(介 入). What should you do to achieve the objective? A. Configure the database to run in archivelog mode. B. Configure the Flash recovery area to enable automatic database backup. C. Schedule the database backup using DBMS_JOB package after creating the database. D. Schedule the database backup using recovery manager(RMAN) commands after creating the database. E. Schedule the database backup using Database Configuration Assistant(DBCA) while creating the database. 答案：E. 刚看到这个答案的时候，以为答案有问题。Google 一下，真有。 我们注意看题目，在安装数据 库的时候，确保数据库在没有干预的情况下规则的备份。 只有 E 中的 DBCA 工具合适了。 而且 DBCA 还真有这个功能(玩了几年 Oracle，都没有留意到，杯具中...)7. The application development team has developed PL/SQL procedures and functions for different purposes and calls them as and when required. The loading of individual procedures or functions into memory degrades performance with every call. Also, it causes a security problem for individual subprograms and loss of program units when the whole system is transported into a new location. Which method would you recommend to the application developers to solve this problem? A. Avoiding the use of cursors in the subprograms. B. Using anonymous（匿名） PL/SQL blocks instead of subprograms. C. Referring to views instead of tables inside the subprograms. D. creating PL/SQL packages to include interrelated(相关关联) subprograms. 答案：D. 8. View the Exhibit. Your Oracle 10g database has 6 tablespaces in which: -TEMP is the default temporary tablespace -UNDOTBS1 is the default undo tablespace -USERS is the default permanent tablespace In this database, which three tablespaces can be made offline? (Choose three.)A B C DTEMP PROD USERS SYSAUXE SYSTEM F UNDOTBS1 答案:BCD SYSAUX 表空间是可以被 OFFLINE，不能 read only,但是 system、undo 和 temp 不行,why? SQL& alter table 表空间已更改。 SQL& alter tab 第 1 行出现错误: ORA-03217: 变更 TEMPORARY TABLESPACE 无效的选项 SQL& alter table 第 1 行出现错误: ORA-01541: 系统表空间无法脱机; 如有必要请关闭 9.The junior DBA in your organization has accidentally deleted the alert log file. What will you do to create new alert log file? A. Create the new text file as alert.log B. You have to recover the alert log file from the valid backup. C. Change the value for the BACKGROUND_DUMP_DEST parameter. D. No action required. The file would be created automatically by the instance. 答案 D 10. While planning to manage more than one database in your system,you perform the following activities: （1） organize different categories of files into independent subdirectories. （2） Use consistent naming convention for database files. （3） Separate administrative information pertaining to each database. Which option corresponds(一致) to the type of activities performed by you? A. Oracle Managed Files. B. Oracle Grid Architecture. C. Optimal Flexible architecture. D. Oracle database architecture. 答案：C Flexible 灵活的 architecture 体系结构Oracle Grid Architecture is used for rac Oracle的最佳灵活体系结构（Optimal Flexible Architecture，简称OFA），是指Oracle软件数据库文件及文件 夹的命名约和存储位置规则。 使用最佳灵活体系结构，能够简化数据库系统的管理工作，使数据库管理员更加容易地定位文件或添加文件； 还可以将Oracle系统的执行文件、管理文件、数据文件分别存储到不同的硬盘上，从而有效地使用用户系统中 的所有存储空间、克服某个（些）硬盘存储空间的限制，合理分配存储负担，降低单个硬盘存储速度方面的瓶 颈，提高整个系统的存储效率。 OFA 的核心是一个命名机制 1、linux/unix 下面对于 mount 点的命名采用/pm 的方式 p：字符常量，通常是 u 例如/u01、/u02 m：固定长度，通常是 22、BASE 目录通常是/pm/h/u h：常量，通常是 app 例如/u01/app/oracleu：目录所有者，通常是 oracle3、ORACLE_HOME 目录通常是/pm/h/u/product/V V：版本号，例如 9.2.0、10.2.0 新增的一些组件，例如 asm、db_1 都在这个目录下面 例如/u01/app/oracle/product/10.2.0 4、数据库特定的管理文件，例如启动参数文件、转储文件 /pm/h/u/admin/d/a d 表示数据库的名字 5、关键文件通常是/pm/q/d q 通常是 oradata 例如/u02/oradata/ora10g 控制文件通常是 controln.ctl，n 表示数据位数，通常是 2 例如 control01.ctl、control02.ctl redon.log，n 通常是 2，例如 redo01.log、redo02.log 数据文件 tn.dbf，t 表示表空间的名字，n 通常是 2 例如 users01.dbf、users02.dbf 等 a 表示子目录的名称，例如参数文件是 pfile，用户目录是 udump 例如/u01/app/oracle/admin/ora10g/pfile11.Data files of which three tablespaces can be recovered by performing an open recovery?(Choose three) A. TEMP B. UNDO C. INDEX D. SYSAUX E. SYSTEM 答案：ACD (觉得 A 有问题) Editor?s nots:temp tablespace does not need to recover.when you perform. open recovery, tablespace must be taken offline.So, except undo/system/temp tablespace ,all of other tablepsace can be recovered when database open.12. Your tnsnames.ora file has the following entry for the service alias ORCL: ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.156.24.216) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ) ) ) The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command: SQL& CONNECT scott/tiger@orclWhat could be the reason for this? A) The listener is not running at the server. B) The TNS_ADMIN environmental variable is set to a wrong value. C) The
database service is not registered with the listener. D) The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file. 答案：C.当 SERVICE_NAME 填写不正确时，你可以 tnsping 通数据库实例，但是却无法连接到数据库实例，因为你填写错 的 SERVICE_NAME 并没有注册到 LISTENER 当中 listener 是在 db server 上配置，凡是在 client 端要连接 db 都需要通过 listener，就像非本单位员工要想进 入该单位所在的办公大楼必须去保安那里登记一下自己的信息(亮名自己的身份同时说出来要找谁、办什么事 儿)，之后保安打电话和你要找的人进行确认和求证。host 和 port 很好理解，不多赘述，重要的是这个 SERVICE_NAME 到底是指什么？这里明确说明一下，它就是我们在 listener status 信息中显示的那些服务 &X&, 这里的 X=B,C,a,TEST(看上面的 listener status 信息)，这里如果 SERVICE_NAME=B|C,那么通过这个 tns 连接 所对应的 session 它使用的就是动态注册的 listener 连接 db 的；如果 SERVICE_NAME=a,那么通过这个 tns 连 接所对应的 session 它使用的就是静态注册的 listener 连接 db 的；如果 SERVICE_NAME=test 的话，那么通过 这个 tns 连接所对应的 session 它使用的到底是通过动态还是静态 listener 连接 db 的呢？ SQL& select sid,service_name from v$session where sid=(select sid from v$mystat where rownum=1);153 SYS$USERS，很显然 SERVICE_NAME=SYS$USERS 其对应的 tnsa 使用的 service_name=a 是 静态注册的信息可以通过如下视图查看 db 活动的 service_name:SQL& select name from v$active_ C ,B, TEST,SYS$BACKGROUND --后台进程对应的 session 的 service_name 都看作 SYS$BACKGROUND, SYS$USERS --oracle 统一把静态注册到 listener 的 globaldb_name 信息对应的 service_name 都看作 SYS$USERS,以上 db 是运行在专用模式下，当然我们配置的 tns 使用的都是专用模式在配置 tns 时 service_name 必须是通过动态注册到 listener 中的 service_names 信息，通过静态注册的 listener 信息是不能通过共享模 式连接 db http://www.itpub.net/thread--1.html(warehouse详细讲解)13. View the Exhibit, and examine the alert messages. You added space to the TEST tablespace to bring the space usage below the thres hold value. Which statement is true about the Tablespace Full alert?A) The alert is purged because it is a threshold alert. B) The alert is cleared and transferred to the alert log file. C) The alert is automatically cleared and sent to the alert history. D) The alert appears in Oracle Enterprise Manager Database Control until it is manually cleared. 答案：C在 EM 上的表空间空间不够用的告警信息会在你扩大表空间后，自动被清除，同时移动到 alter history select * from dba_outstanding_alerts 警告记录地方,只记录根据度量产生的 select * from dba_alert_history (警告归档后的地方,记录所有的) 14. Which is the memory area that is created when a dedicated server process is started, and contains data and control information for that server process? A. SGA B. PGA C. Shared Pool D. Streams Pool 答案： B PGA-独立服务进程启动时，里面包括这个进程的数据和控制信息 在 ORACLE9I 中，只需要调整 PGA_AGGREGATE_TARGET。 Oracle 内存 架构 详解 http://blog.csdn.net/tianlesoftware/archive//5594080.aspx SGA 是一组为系统分配的共享的内存结构，可以包含一个数据库实例的数据或控制信息。 PGA（Program Global Area 程序全局区）是一块包含一个服务进程的数据和控制信息的内存区域。 15. Which two statements about Recovery Manager(RMAN) backups are true?(Choose two). A. Online redo log files can be backed up. B. Archived redo log files are backed up. C. Only used data blocks can be backed up as backup sets. D. Only consistent database backups can be performed. E. RMAN backup can be taken only if the database is configured in ARCHIVELOG mode. 答案：BC 归档日志可以使用 rman 备份、只有使用过的数据块可以被备份成备份集 如何 搭建 RMAN 备份平台 http://blog.csdn.net/tianlesoftware/archive//5740896.aspx 对于 E 选项: 在 NOARCHIVELOG 模式下,rman 备份会报 ORA-19602: 无法按 NOARCHIVELOG 模式备份或复制 活动 文件，但是当 SQL& alter tabl或者 SQL& alter tables后就可以执行备份了. 或者在 mount 状态下也可以 rman 备份16. Which three pieces of information are to be manatorily provided while creating a new listener using Enterprise Manager database Control? (Choose three). A. The port used by the listener. B. The protocol used by the listener. C. The server name where the listener runs. D. The log file and trace file destination for the listener. E. The database services to be registered with the listener. 答案：ABC Protocol:协议17. The SCOTT user has an index on the ITEM_DESC column of the ITEM table. As part of the year-ending task, SCOTT updates the ITEM_DESC column for most of the rows in the ITEM table. How does this change to the table affect the index? A. An update in a leaf row takes place. B. The index becomes invalid after the update. C. The leaf block containing the row to be updated is marked as invalid. D. A row in the leaf block of the index for the key value is deleted and inserted. 答案：D 每年用户一个表上的一个列，那么在这个列上的索引，行数据在索引 leaf block 的值会删除 再被插入(估计是便于维护), 索引叶子节点包括 rowid(需实验)18. In the middle of a transaction,a user session was abnormally terminated but the instance is still up and the database is open. Which two statements are true in the scenario （方案）? (Choose two). A. Event viewer gives more details on the failure. B. The alert log file gives detailed information about the failure. C. PMON rolls back the transaction and releases the locks. D. SMON rolls back the transaction and releases the locks. E. The transaction is rolled backup by the next session that refers to any of the blocks updated by the failed transaction. F. Data modified by the transaction up to the last commit before the abnormal termination is retained in the database. 答案：C F 当在一个 transaction 中时，会话不正常结束，这时 PMON 会自动将没有提交的数据 rollback， commited 的脏数据放到 datafile 中 F 的意思如果一个 SESSION,前面有 COMMIT 的部分是不回滚的。只有还未 COMMIT 部分被回滚了 PMON：进程监控进程 1: 进程负责在反常中断的连接之后的清理工作。 2: PMON 监控其他 oracle 后台进程，如果有必要（和有可能）重新启动他们 3: 使用 Oracle TNS 监听器登记 SMON：系统监控, SMON 是负责做所有系统级的工作。相对于 PMON 对单个进程感兴趣，SMON 是一 个系统级别的观点，是一种用于库的“垃圾收集者”。 1.清理临时空间以及临时段 SMON 负责在数据库启动时清理临时表空间中的临时段，或者一些异常操作过程遗留下来的临时 段，例如，当创建索引过程中，创建期间分配给索引的 Segment 被标志为 TEMPORARY，如果 Create Index （或 rebuild Index 等）会话因某些原因异常中断，SMON 负责清理这些临时段。 2.接合空闲空间 在 DMT（字典管理表空间）中，SMON 负责把那些在表空间中空闲的并且互相是邻近的 Extent 接合 成一个较大的空闲扩展区，这需要表空间的 pctincrease 设置为非零值。 3.执行实例恢复（Instance recovery） 在实例恢复过程中，SMON 的工作包括三个环节：应用 Redo 执行前滚、打开数据库提供访问、回 滚未提交数据4.离线（Offline）回滚段 在自动回滚段管理（AUM）中负责 Offline 不再需要的回滚段 5.执行并行恢复。 SMON: Parallel transaction recovery tried 19. The application workload on your database is same bwtween 10 a.m. And 11 a.m. On weekdays. Suddenly you observe poor performance between 10 a.m. And 11 a.m. In the middle of the week. How would you identify the changes in configuration settings, workload profile, and statistics to diagnose the possible causes of the performance degradation? A. By using the SQL access advisor B. By using the automatic workload repository report. C. By running the automatic database diagnostic monitor(ADDM) D. By using the automatic workload repository (AWR) compare period report. E. By analyzing the output of the v$ACTIVE_SESSION_HISTORY view. 答案：D 查看 awr 报告 选择开始快照 和结束快照之间数据库不要重启 While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods.(即可以对比两个不同时间段的 AWR 报告) To generate an AWR Compare Periods report: @$ORACLE_HOME/rdbms/admin/awrddrpt.sql Automatic Database Diagnostic Monitor (ADDM) ADDM 在创建每个 AWR 快照之后自动运行。每 次创建快照后，ADDM 都会分析与最后两个快照对应的时段。多数情况下，ADDM 会为检测到的问 题提供建议解决方案，甚至可以量化这些建议案的 优势。ADDM 分析的结果存放在 AWR 中。 Oracle AWR 介绍 http://blog.csdn.net/tianlesoftware/archive//4682300.aspx 20. Your database is in NOARCHIVELOG mode. After which two operations you should take the backup of the control file? A. Adding a new user to the database. B. Dropping a user from the database. C. Dropping a table from the database. D. Dropping a data file from a tablespace. E. Adding a new tablespace to the database. 答案： D E. 控制文件里保存了数据文件的信息，所以添加删除都会修改控制文件里的内容，所以需要修改。 注意本题是在非归档下,要完全恢复的时候只能用冷备份方式,添加删除都会对当前控制文件有改 动,显然只能用备份的控制文件才能恢复到最近冷备份的那一刻。 Oracle 控制文件 http://blog.csdn.net/tianlesoftware/archive//4974440.aspx21. Which three statements are true regarding the logical structure of the Oracle database? (Choose three). A. Each segment contains one or more extents. B. Multiple tablespace can share single data file. C. A data block is the smallest unit of I/O for data files. D. It is possible to have tablespace of different block sizes in a database. E. Each data block in the database always corresponds to one OS block. 答案：ACD 区间(extent)是 oracle 的最小空间分配单元,而 block 是 oracle 的最小 io 操作单元。 Oracle 以区间为单位将空间分配给对象段,而段内则是以 block 为单位进行空间使用和管理. 表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系 http://blog.csdn.net/tianlesoftware/archive//4962476.aspx 22.You executed the following command to start the database: SQL& STARTUP ORACLE instance started. Total System Global Area
bytes Fixed Size 789000 bytes Variable Size
bytes Database Buffers
bytes Redo Buffers 262144 bytes ORA-00205: error in identifying controlfile, check alert log for more info Which view would you query at this stage to investigate this missing control file? A) V$INSTANCE B) V$CONTROLFILE C) DBA_CONTROL_FILES D) V$DATABASE_PROPERTIES E) V$CONTROLFILE_RECORD_SECTION 答案: B 当控制文件丢失时，你可以通过动态性能视图 V$controlfile 来查看丢失的控制文件 疑问：v 开头视图绝大部门来自控制文件或内存,如果控制文件丢失了，这个视图还能查吗 无 C 选项对应视图 查询 v$controlfile_record_section 可以得出控制文件的使用情况 23. Which two are valid locking levels that are used by transactions in an Oracle database? (Choose two) A.Row level B.Block level C.Object level D.Schema level E.Database level 答案:AC 事务级别的锁定:行, 对象 ORACLE 锁机制http://blog.csdn.net/tianlesoftware/archive//4696896.aspx 24.Your database is started by using the server parameter file (SPFILE). You issued this command to change the value of the LOG_BUFFER initialization parameter: ALTER SYSTEM SET LOG_BUFFER=24M SCOPE=BOTH; What would be the outcome of this command? A) The command would return an error because LOG_BUFFER is a static parameter. B) The parameter value would be changed and it would come into effect immediately. C) You need to restart the database so that parameter changes can come into effect. D) The command would succeed only if initialization parameter LOG_ARCHIVE_MAX_PROCESS is set to value 2. 答案：A LOG_BUFFER 是个静态参数，只能 scope=spfile，修改完后重启数据库才能生效 LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file. The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in the redo granules is given to the log buffer. LOG_BUFFER /docs/cd/E11882_01/server.112/e10820/initparams127.htm#REF RN10094 SQL& select name,issys_modifiable from v$parameter where name='log_buffer'; NAME ISSYS_MOD -------------------- --------log_buffer FALSE 通过这个查询，我们知道，修改这个参数必须重启数据库。 25. Which two statements are true about the roles in the Oracle database? (Choose two) A. A role can be granted to itself. B. Roles are owned by the sys user. C. Roles can be granted to other roles. D. A role can not be assigned external authentication. E. A role can contain both system and object privileges. 答案： C,E external authentication 外部认证 create role role1 i 角色在定义是可以加口令验证，但是使用 identified externally 时用户属于外部认正，书上说 是系统认证，而且定义是不用设置口令，这时我要激活角色该怎么提供口令呢？ role 可以赋予给其它的 role，同时 role 的权限可以包括系统权限和对象权限 查看角色具有的系统权限 select * from dba_sys_privs where grantee='DBA';查看角色具有的对象权限 select * from dba_tab_privs where grantee='role_test'; 查看用户具有的角色 select * from dba_role_privs where grantee='ROLE_TEST'26. You have been recently hired as a database administrator. Your senior manager asks you to study the production database server and submit a report on the settings done by the previous DBA. While observing the server settings, you find that the following parameter has been set in the parameter file of the database: REMOTE_OS_AUTHENT = TRUE What could have been the reason to set this parameter as TRUE? A) to enable operating system authentication for a remote client B) to restrict the scope of administration to identical operating systems C) to allow the start up and shut down of the database from a remote client D) to enable the administration of the operating system from a remote client E) to disable the administration of the operating system from a remote client 答案： A 初始化参数 REMOTE_OS_AUTHENT 用来控制是否允许远端操作系统验证，默认情况下，数据库只 运行本地服务器上的操作系统验证 OSAUTH_PREFIX_DOMAIN SQL& show parameter os_authent_prefix SQL& alter system set os_authent_prefix='' scope= SQL& create user lh identified externally defa SQL& SQL& select username,password from dba_users where username='LH'; SYSDBA,SYSOPER 这两个权限比较特殊而已 ,SYSDBA 权限大些 如果操作系统认证, sqlplus sys as sysdba 后面口令不需要, 类似保险柜的门不加密码,有钥匙 房子都是你的. sqlplus a/b as sysdba 只要 as sysdba 就能连,都挂在 sys 用户下 Win 下,管理--&ora_dba 组,有 administrator 类似 linux 下的 dba 组 只要有权限来操作系统服务器 危险 ,其他都一张白纸 1.1 注册表项：OSAUTH_PREFIX_DOMAIN（字符串键名） OSAUTH_PREFIX_DOMAIN 默认值为 TRUE，该项位于注册表 Oracle9i 版本：HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0 Oracle10g 版本：在 HKEY_LOCAL_MACHINESOFTWAREOracleKEY_OraDb10g_home1 如果注册表中没有 OSAUTH_PREFIX_DOMAIN 这项， 说明 OSAUTH_PREFIX_DOMAIN 为 true， 这时 进行 操作系统验证需要在用户名和 os_authent_prefix 之间增加'主机名'信息；若在注册表中加入此 参数 OSAUTH_PREFIX_DOMAIN， 并设其值为 FALSE， 则其规则和 UNIX 系统中的操作系统验证一致， 即：OS_AUTHENT_PREFIX 的值||操作系统的用户名 1.2 SQLNET.ORA 参数文件里，必须设置 SQLNET.AUTHENTICATION_SERVICES = (NTS) 否则，登录时系统会报： SQL& CONNECT /ORA-01004: default username f logon denied Warning: You are no longer connected to ORACLE. 27. View this parameter setting in your database: DB_CREATE_FILE_DEST='D: \oracle\product\10.2.0\oradata\oracle' You created a tablespace by using this command: CREATE TABLESPACE USERS; Which two statements are true about the USERS tablespace? (Choose two.) A) The tablespace has two data files. B) An error is reported and tablespace creation fails. C) Data files are created with names generated by the instance. D) The tablespace can be extended without specifying the data file. E) Data files belonging to the USERS tablespace cannot be renamed. 答案：C D 当指定了该参数 DB_CREATE_FILE_DEST='D:\oracle\product\10.2.0\oradata\oracle'后，当 你再创建表空间时，数据文件会自动被创建到该文件下，同时数据文件的名字会由实例自动生成。 这就是 Oracle Managed Files (OMF),DB_CREATE_FILE_DEST 定义数据文件和临时文件的默认文 件系统目录的位置,DB_CREATE_ONLINE_LOG_DEST_n 定义重做日志文件和控制文件的创建位置 DB_RECOVERY_FILE_DEST 定义 RMAN 备份位置. 28. View the Exhibit to see the source and target databases.You have created a database link, devdb., between the databases PRODDB and DEVDB. You want to import schema objects of the HR user using Oracle Data Pump f rom the development database, DEVDB, to the production database, PRODDB. You execute the following command on the target database server: $impdp system/manager directory = DB_DATA dumpfile = schemas.dat schemas = hr flashback_time =
09: 00 The command fails, displaying the following error: ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31640: unable to open dump file &/home/oracle/schema/schemas.dat& for read ORA-27037: unable to obtain file status What would you do to overcome the error?A) remove the dumpfile option in the command B) remove the flashback_time option in the command C) add the user, SYSTEM, to the schemas option in the command D) add network_link = devdb. option in the command E) remove the schemas option and add the network_link = devdb. option i n the command F) remove the dumpfile option and add the network_link = devdb. option in the command 答案：F 通过 db_link 导 schema,不需要 dumpfile，但觉得还应该加上 remap_schema 才对! impdp 的使用: 1.导入表: impdp kai/kai directory=test_dir dumpfile=tab.dmp tables=dept,emp (导入到 kai 中) impdp system/manager directory=test_dir dumpfile=tab.dmp tables=kai.dept,kai.emp remap_schema=scott:system (导入到 system 中) 2.导入方案: impdp kai/kai directory=test_dir dumpfile=schema.dmp schemas=kai (导入到 kai 中) impdp system/manager directory=test_dir dumpfile=schema.dmp schemas=kai remap_schema=kai:system (导入到 system 中) 3.导入表空间: impdp system/manager directory=test_dir dumpfile=tablespace.dmp tablespace=tablespace1 4.导入数据库: impdp system/manager directory=test_dir dumpfile=full.dmp full=y29.What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database? A) MTTR Advisor would be disabled. B) Redo Log Advisor would be disabled. C) Automatic tuning of checkpoint would be disabled. D) Checkpoint information would not be written to the alert log file. 答案:C 首先说说 C 的正确性： Automatic Checkpoint Tuning in 10g [ID ] How to enable: Automatic checkpoint tuning is enabled by default. If it is disabled, by setting the parameter to zero explicitly, you can enable it by unsetting FAST_START_MTTR_TARGET or set it to a nonzero value. If you set this parameter to zero this feature will be disabled. Note: this is different from defaulting (i.e. not setting) fast_start_mttr_target to 0. 可以证明 C 是正确的（有待验证，主要不明白怎么查询 Automatic checkpoint tuning ） 关于 A 的正确性，可以看看下面, 每当我设置 fast_start_mttr_target=0 的时候 MTTR advisory 会自动关闭 你可以看看告警日志，alter 里面有这样一段： MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set所以我觉得 A 也是对的 H上,@挡薷 ORACLE 的配置,不竽憷斫獾哪,去增加 CHECKPOINT 的l率,也不 s小 LOG FILE,呵呵,只是在m的r候|l CHECKPOINT 而已 /docs/cd/E11882_01/server.112/e10820/initparams084.htm#REF RN10058 30. Users of HR schema complain about slower-than-normal performance. On investigati on the DBA found that maintenance was recently performed on some of the tables. The DBA traced the query that takes longer than normal to execute. View the Exhibit exhibit_before.After the DBA resolves the problem, the query performs normally. View the Exhibit exhibit_after. What action would the DBA have taken to resolve the performance problem? A) analyzed the EMPLOYEES table to collect the current statistics B) moved the EMPLOYEES table in to a locally managed tablespace C) moved the EMPLOYEES table to another location in the same tablespace D) reorganized the associated indexes for the EMPLOYEES table that were in an un usable stateE) moved the indexes associated with the EMPLOYEES table to the same tablespace where the EMPLOYEES table exists 答案： D 31. Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.) A) You have to shut down the database to perform the backups. B) Archiving information is written to the data files and redo log files. C) You can perform complete database backups without closing the database. D) Online redo log files have to be multiplexed before putting the database in ARCHIVELOG mode. E) All the previous database backups become invalid after you configure the database to ARCHIVELOG mode. 答案：C E 现在恢复必须用 archive 文件了 32.You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays. Which two statements are true about the differential incremental backups? (Choose two.) A) The backup performed on Sundays contains all the blocks that have ever been use in the database. B) The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup. C) The backup performed on each working day contains all the blocks that have changed since the last level 0 or level 1 backup. D) The backup performed on each working day contains all the blocks that have changed since the last level 0 backup. 答案:A C incremental 备份:每次备份上一次 0 级备份以来发生的变化. differential incremental:每次备份是从上一次 0 级或是 1 级备份以来发生变化的内容. 累计备份:同级别的备份可以覆盖,而差异是不能覆盖的33. You require the Oracle server to manage the undo segments and space among various active sessions automatically. You created an undo tablespace, UNDO_TBS1, in your database. Which two additional steps would you perform to achieve this? (Choose two.) A) Set the UNDO_RETENTION parameter to 900 or more. B) Create an initial undo segment in the undo tablespace. C) Enable the retention guarantee for the undo tablespace. D) Set the UNDO_TABLESPACE parameter to UNDO_TBS1. E) Set the UNDO_MANAGEMENT initialization parameter to AUTO. 答案：DE 你 建 立 了 一 个 undo 表 空 间 ， 你 希 望 你 的 oracle 能 自 动 管 理 ， 这 时 你 需 要 设 置 UNDO_TABLESPACE 的参数是你刚才建立表空间的名字， 然后设置 UNDO_MANAGEMENT 为 ANTO Oracle undo 管理 http://blog.csdn.net/tianlesoftware/archive//4901666.aspxOracle undo 表空间管理 http://blog.csdn.net/tianlesoftware/archive//5689558.aspx 34. Which two statements are true regarding a PL/SQL package body? (Choose two.) A) It cannot be created without a package specification. B) It cannot invoke subprograms defined in other packages. C) It can contain only the subprograms defined in the package specification. D) It can be changed and recompiled without making the package specification invalid. 答案：AD Invoke 调用 程序包是由函数和过程组成的集合。每个程序包应由两个对象组成： 程序包说明：这个对象（有时称为程序包头）的对象类型为 PACKAGE，其中只包含程序包中的过 程、函数和变量的定义。 程序包体：这个对象的对象类型为 PACKAGE BODY，包含程序包说明中定义的子程序的实际代码。 只有在编译了程序包说明之后才能编译程序包体。可以在没有程序包体的情况下创建程序包说明， 但不能在没有程序包说明的情况下创建程序包体。与程序包说明是分开的。因此，可以更改并重 新编译程序包体代码，此时不会将与程序包说明相关的其它对象标记为无效。 35. User A executes the following command to update the TRANS table) SQL& UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005'; Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on the TRANS table: SQl& ALTER TABLE trans MODIFY (tr_type VARCHAR2 (3)); What would happen in this scenario? A) The transaction for user A is rolled back. B) The ALTER TABLE command modifies the column successfully. C) The ALTER TABLE command fails due to the resource being busy. D) The ALTER TABLE command waits until user A ends the transaction. 答案：C SQL& alter table wdz2 modify (memo varchar(22)); alter table wdz2 modify (memo varchar(22)) 第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源36. Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.) A) FGA is possible on SELECT statements only. B) The audit trail for FGA is stored in the FGA_LOG$ table. C) The audit trail for FGA is stored in the AUD_LOG$ table. D) FGA enables a SQL predicate to define when to audit an event. E) FGA audits DELETE statements only when audit columns are specified. F) FGA includes the SQL statement used by the user as part of the audit event entry. 答案：B D FPredicate 谓词 Editor?s notes:Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used. 使用FGA策略实现精细审计的时候，不需要激活数据库审计，并且oracle会自动将审计结果放到 数据字典表FGA_LOG$中。 理解的也不是很透彻，参考 About Fine-Grained Auditing 小节： /docs/cd/E11882_01/network.112/e10574/auditing.htm#DBSEG60 681 对 D 选项解释:用 FGA 的时候你当然是可以指定条件的。dbms_fga.add_policy 中有一个参数 audit_condition 就是用来指定审计条件的。Oracle 10g 中对 FGA(Fine Grained Auditing,细粒 度审计）做了极大的增强，这使得 FGA 可以很容易并且很灵活的实现对某个表的 select 和 DML 操作的审计37. Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus? A) The user must be granted the database administrator (DBA) privilege. B) The user must be listed in the password file for the authentication. C) No special setup is needed for the user to connect as SYSDBA in iSQL*Plus. D) Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user. 答案：D 38. In your Oracle 10g database, you have scheduled a job to update the optimizer statistics at 05) 00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? (Choose three.) A) average row size B) last analyzed date C) size of table in bytes D) size of table in database blocks E) number of free blocks in the free list F) number of extents present in the table 答案：ABDOptimizer statistics include table, column, index, and system statistics. Statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution,which the optimizer uses to make decisions on how to access data.The statistics that are collected include: ? Size of the table or index in database blocks? Number of rows ? Average row size and chain count (tables only) ? Height and number of deleted leaf rows (indexes only) As data is inserted, deleted, and modified, these facts change. The performance impact of maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes. Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day. 表大小收集的是块而不是字节数39. Your database is open and users are connected using the LISTENER listener. The new DBA of the system stops the listener by using the following command: LSNRCTL& STOP What would happen to the sessions that are presently connected to the database instance? A) The sessions are able to perform only queries. B) The sessions are not affected and continue to function normally. C) The active transactions are rolled back and the sessions get terminated. D) The sessions are not allowed to perform any operations till the listener is started. 答案：B 已连接的会话，当停止监听时不会中断 40. After being hired as a database administrator, you find that there is only one database that is functional and that is being accessed by the applications. You want to create a replica of the database, to be used for testing purposes. What is the best method to create the replica? A) create a database by using CREATE DATABASE .. command and manually copy the data B) use Database Configuration Assistant (DBCA) to create a template from the existing database to contain the database structure C) use DBCA to create a template from the existing database to contain the database structure and then manually copy the data using Oracle Data Pump D) use DBCA to create a template from the existing database to contain the database structure with data files and then use the same template to create the database in the new location 答案：D 41. You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database? A) The database performance would be enhanced. B) The redo log files would be get filled more frequently. C) The overall database performance would be degraded. D) The mean time to recover (MTTR) would be increased.答案：C Enhance 提高 degrade 降低 fast_start_mttr_target,此参数是在 9i 中引入，它定义了数据块崩溃后所需要的实例恢复的时 间，Oracle 在实际上内在的解释成两个参数:fast_start_io_target 和 log_checkpoint_interval. 如果这两个参数没有显式的指定,计算值将生效.。 fast_start_mttr_target 可以设定的最大值是 3600，即一个小时。它的最小值没有设限，但是并 不是说可以设置一个任意小的值，这个值会受最小 dirty buffer（最小为 1000）的限制，同时还 会受初始化时间以及文件打开时间的限制。 在设置此参数的时候要综合考虑系统的 IO，容量以及 CPU 等信息，要在系统性能和故障恢复时间 之间做好平衡。 将此参数设置成 0 时将禁用 fast-start checkpointing，这样能减小系统负载但同时会增加系统 的恢复时间。如果 fast_start_io_target or log_checkpoint_interval 被指定，他们会自动覆 盖由 fast_start_mttr_target 参数计算出来的值。 如果将 FAST_START_MTTR_TARGET 参数显式设置为 0，则会禁用自动检查点优化。另 外，如果将 FAST_START_MTTR_TARGET 参数显式设置为大于 0 的值，则会启用重做日 志指导（MTTR Advisor）。 42. You are working on an online transaction processing (OLTP) system. You notice that a PL/SQL procedure got executed twice at 2:00 p.m. This has incorrectly updated the EMP_SAL table. How would you revert the table to its state at 2:00 p.m.? A) Perform point-in-time recovery to 2:00 p.m. B) Use Flashback Table feature to revert the changes. C) Restore the entire database from the recent backup and open it. D) Issue the rollback statement with system change number (SCN). 答案：B 索引用的是 rowid 但是闪回表 rowid 要发生变化 alter table t flashback table t to scn 3790152; 应该是比这个 scn 小 select id, rowid 闪回后可能变化，不变是个巧合 Oracle Flashback 技术 总结 http://blog.csdn.net/tianlesoftware/archive//4677378.aspx 43. According to your backup strategy, you performed an incremental level 0 backup of your database. Which statement regarding this backup is true? A) The backup is similar to image copy. B) The backup contains all used data blocks. C) The backup contains only unused data blocks. D) The backup contains all data blocks changed since the last incremental level 1 backup. 答案：B 0 级备份是全量备份 增量备份工作机制 当你设置星期天执行一次 0 及备份，工作日执行 1 级备份 A、差异备份 Differential The backup performed on each working day contains all the blocks that have changed sincethe last level 0 or level 1 backup. B、累积增量备份 Cumulative The backup performed on each working day contains all the blocks that have changed since the last level 0 backup. 累计备份:同级别的备份可以覆盖,而差异是不能覆盖的 44. User SCOTT executes the following command on the EMP table but has not issued COMMIT, ROLLBACK, or any data definition language (DDL) command: SQL& SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF SCOTT has opened another session to work with the database. Which three operations would wait when issued in SCOTT's second session? (Choose three.) A) LOCK TABLE emp IN SHARE MODE; B) LOCK TABLE emp IN EXCLUSIVE MODE; C) DELETE FROM emp WHERE job='MANAGER'; D) INSERT INTO emp(empno,ename) VALUES (1289,'Dick') ; E) SELECT job FROM emp WHERE job='CLERK' FOR UPDATE OF 答案：ABE SHARE：允许并发查询，但禁止更新锁定的表。需要有（并且会自动请求）SHARE 锁定才能创建表 的索引。 EXCLUSIVE：允许查询锁定表，但禁止对锁定表执行任何其它活动。需要有 EXCLUSIVE 锁定才能 删除表。 update, insert ,delete, select ... for update 会 LOCK 相应的 ROW. 只有一个 TRANSACTION 可以 LOCK 相应的行，也就是说如果一个 ROW 已经 LOCKED 了，那就不能被其他 TRANSACTION 所 LOCK 了。LOCK 由 statement 产生,由 TRANSACTION 结尾（commit，rollback），也就是说一个 SQL 完成 后 LOCK 还会存在，只有在 COMMIT/ROLLBACK 后才会 RELEASE。 45. You find that the database performance degrades while you backup the PROD database using Recovery Manager (RMAN). The PROD database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason. Which action would you consider to overcome the performance degradation? A) Configure Java Pool to cache the java objects. B) Configure Streams Pool to enable parallel processing. C) Increase Shared Pool size to cache more PL/SQL objects. D) Increase Database Buffer Cache size to increase cache hits. E) Configure Large Pool to be used by RMAN and shared server. F) Increase the total System Global Area (SGA) size to increase memory hits. 答案：E 使用 RMAN 备份数据库时，由于在共享服务器模式下，共享池已经不够用了，所以使用大池以 供 RAMN 和共享服务使用。 在磁盘上的备份会使用 PGA 内存空间作为备份缓冲区，PGA 内存空间从用于通道进程的内存 空间中分配。如果操作系统没有配置本地异步 I/O，可以利用 DBWR_IO_SLAVES 参数使用 I/O 从属 来填充内存中的输入缓冲区。如果设置 DBWR_IO_SLAVES 参数为任意的非零值，RMAN 会自动分配 4 个 I/O 从属协调输入缓冲区内存中的数据块加载。为了实现这一功能，RMAN 必须利用一个共享内存区域。因此，用于磁盘备份的内存区会被推入共享池，如果存在 large 池，则被推入 large 池。 如果没有使用磁带 I/O 从属，会在 PGA 中分配用于磁带输出缓冲区的内存。设置 init.ora 参 数 BACKUP_TAPE_IO_SLAVES=TRUE，可以使用磁带 I/O 从属，必要时还可以在 spfile 里动态修改。 该参数设为 true 时，rman 会为每个通道创建一个从属进程来帮助备份工作。为了协调这一功能， RMAN 会将内存分配推进 SGA。 如果配置了任一种 I/O 从属选项并且没有配置 large 池，则会在 SGA 的共享池中分配内存。如 果没有配置 large 池又要使用 I/O 从属，建议最好创建一个 large 池，这个 large 池的大小基于 为备份分配的通道总数（加上 1MB 用于开销）。 RMAN 系列（一）---- RMAN 体系结构概述 http://blog.csdn.net/tianlesoftware/archive//5659701.aspx 46. You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective? A) Use the CREATE DATABASE .. command to create the database with Oracle-managed files. B) Use the Database Configuration Assistant (DBCA) to create the database with Oracle-managed files. C) Use Enterprise Manager to create a new database with the Online Transaction Processing (OLTP) option. D) Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template. E) Use the CREATE DATABASE.. command to create the database with Automatic Storage Management (ASM) files system. 答案：D 47. Which two statements are true about the primary key constraint in a table? (Choose two.) A) It is not possible to disable the primary key constraint. B) It is possible to have more than one primary key constraint in a single table. C) The primary key constraint can be referred by only one foreign key constraint. D) The primary key constraint can be imposed by combining more than one column. E) The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed. 答案：DE 当定义主键约束时，默认情况下 oracle 会自动基于唯一约束建立唯一索引，并且索引名与约 束名完全一致，一张表只能有一个主键约束。一个主键约束可以由多个列组成。 48. View the Exhibit to see the structure of the EMPLOYEES and DEPARTMENTS tables.Your organization plans to dissolve the department with department ID 30. You execute the following command to delete rows from the DEPARTMENTS table: SQL&delete from DEPARTMENTS where DEPT_ID = 30; The command fails and displays the following error: ERROR at line 1: ORA-02292: integrity constraint (HR.SYS_C005374) violated - child record found Which two actions would you take to overcome this error? (Choose two.) A) alter the foreign key constraint to include the cascade option B) alter the foreign key constraint to include the on delete cascade option C) first, drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table D) first, drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table E) first, delete all of the rows from EMPLOYEES table and then delete the rows from the DEPARTMENTS table for department id 30 F) first, delete rows from the EMPLOYEES table for department id 30 and then delete the rows from the DEPARTMENTS table for department id 30 答案：BF 49. Which two operations require undo data? (Choose two.) A) committing a transaction B) rolling back a transaction C) recovering from failed transactions D) recording a transaction to redo log files E) rolling forward during instance recovery 答案：BC Undo 里保存的数据前镜像，即数据修改之前，先将原始数据保存在 undo 里。 而 redo 记录的是 事务。因为 undo 含有事务的数据，rollback 的时候用到，事务恢复也会用到 rolling forward during instance recovery 用的是 redo 老白的介质恢复和实例试验: 对于介质恢复和实例恢复来说，第一个步骤都是通过 REDO LOG 的信息进行前滚，在做前滚 的时候，通过 REDO LOG 文件里记录的数据库变化矢量（稍后我们会详细的介绍数据库变化矢量 CV），根据 SCN 的比对，提交到相关的数据文件上，从而使数据文件的状态向前滚动。大家要注意 的是， UNDO 表空间的变化也被记录到 REDO LOG 里了， 因此 UNDO 表空间相关的数据文件也会被前 滚。当前滚到最后一个可用的 REDO LOG 或者归档日志的时候，所有的数据库恢复层面的工作就全 部完成了。这个时候，数据库包含了所有的被记录的变化，这些变化中有些是已经提交，有些是尚未提交的。在最新状态的 UNDO 表空间中，我们也可以看到一些尚未提交的事务。因此数据库下 一步需要做的事情是事务层面的处理，回滚那些尚未提交的事务，以确保数据库的一致性。 首先是在实例故障时，可能某些事物对数据文件的修改并没有完全写入磁盘，可能磁盘文件 中丢失了某些已经提交事务对数据文件的修改信息。其次是可能某些还没有提交的事务对数据文 件的修改已经被写入磁盘文件了。也有可能某个原子变更的部分数据已经被写入文件，而部分数 据还没有被写入磁盘文件。实例恢复就是要通过 ONLINE REDO LOG 文件中记录的信息，自动的完 成上述数据的修复工作。这个过程是完全自动的，不需要人工干预。 ora-600 说过：实例恢复的过程就是 前滚（redo）打开 回滚（undo） 实例恢复: 启动的时候,从某个点把日志文件里内容完全写到数据文件,打开,把没有提交的 rollback50. Which two statements about Automatic Storage Management (ASM) are true? (Choose two.) A) ASM provides mirroring on file by file basis. B) ASM provides automatic load balancing across all ASM disks. C) ASM supports the Oracle database and operating system files. D) ASM can be used to store trace files, alert log files, and the server parameter file (SPFILE). 答案：AB /docs/cd/E11882_01/server.112/e10897/asm.htm#ADMQS12100 自动存储管理 (ASM) ASM 是 Oracle 数据库 10g 中一个非常出色的新特性，它以平台无关的方式提供了文件系统、 逻辑卷管理器以及软件 RAID 等服务。 ASM 可以条带化和镜像磁盘，从而实现了在数据库被加载的情况下添加或移除磁盘以及自动平衡 I/O 以删除“热点”。它还支持直接和异步的 I/O, 并使用 Oracle9i 中引入的 Oracle 数据管 理器 API（简化的 I/O 系统调用接口）。 ASM 不是一个通用的文件系统，并只能用于 Oracle 数据文件、重做日志以及控制文件。ASM 中的文件既可以由数据库自动创建和命名（通过使用 Oracle 管理文件特性），也可以由 DBA 手 动创建和命名。由于操作系统无法访问 ASM 中存储的文件，因此对使用 ASM 文件的 数据库执行备份和恢复操作的唯一途径就是通过恢复管理器 (RMAN)。 ASM 作为单独的 Oracle 实例实施，只有它在运行时其他数据库才能访问它。在 Linux 上， 只有运行 OCSSD 服务（由 Oracle 通用安装程序默认安装）才能使用 ASM。ASM 需要的内存不多： 对大多数系统，只需 64 MB。 asm 支持的是文件系统冗余,不是磁盘级别的51. You executed the following command to back up your control file: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Which initialization parameter is used to specify the location of the trace file? A) USER_DUMP_DEST B) CORE_DUMP_DEST C) TRACE_DUMP_DEST D) BACKUP_DUMP_DEST E) BACKGROUND_DUMP_DEST答案：A 当使用该命令 ALTER DATABASE BACKUP CONTROLFILE TO TRACE 后可以在 USER_DUMP_DEST 中 找到该 trace 文件(指由用户自己跟踪的文件位置) 52. Which three statements are true about the stages of database startup? (Choose three.) A) Data files and redo log files can be renamed at the MOUNT stage. B) Control files are read at the OPEN stage for the location of data files. C) Control files are required to bring the database to the NOMOUNT stage. D) Data files and redo log files are made available to users at the OPEN stage. E) Data files and online redo log files are checked for consistency while opening the database. 答案：ADE 深刻理解 Oracle 数据库的启动和关闭 http://blog.csdn.net/tianlesoftware/archive//4704877.aspx 53. The employee IDs of employees who have made sales in the company are transferred from the EMPLOYEES table to the BONUS table with a default bonus value. Later, the human resources department decides to give bonuses to employees as per the following conditions: 1. Employees with a salary of $8,000 or less should receive a bonus. 2. Employees who have not made sales get a bonus of 1% of their salary. 3. Employees who already made sales get an increase in their bonus equal to 1% of their salary. What would you do to implement these changes in one step? A) Use SQL*Loader utility. B) Use the MERGE statement. C) Use a multitable insert operation. D) Use a correlated UPDATE statement. 答案：B merge into a using (select id,name from b ) c on(a.id=c.id ) when matched then update set a.name=c.name when not matched then insert (a.id,a.name) values (c.id,c.name); 54. Which three pieces of information are considered while deciding the size of the undo tablespace in your database? (Choose three.) A) the size of an undo block B) the size of the redo log files C) undo blocks generated per second D) the size of the database buffer cache E) the value of the UNDO_RETENTION parameter 答案：ACE在决定 undo 表空间的时候，需要考虑 undo block 的大小，每秒产生的 undo 数据多少，UNDO_RETENTION 的参数是多少题目说的是决定 UNDO 表空间大小的三条: 这题定性分析就可以了,UNDO 表空间是用来记录事务数据的. A.每个 UNDO 块的大小 B.不对,与 redo log 无关 C.每秒生成的 UNDO 块的量 , 这个越大 UNDO 表空间就要越大 D.与 buffer cache 无关 E.UNDO_retention : 在事务提交以后,UNDO 数据为一致性读还要保留多长时间．当然保留时间越长 UNDO 表空 间就越大．55. Examine the following commands executed in your database: SQL& ALTER SESSION RECYCLEBIN=ON; Session altered SQL& CREATE TABLE emp TABLESPACE tbsfd AS SELECT * FROM hr. Table created. Further, you executed the following command to drop the table: SQL& DROP TABLE Table dropped. What happens in this scenario? A) The table is moved to the SYSAUX tablespace. B) The table is moved to the SYSTEM tablespace. C) The table is removed from the database permanently. D) The table is renamed and remains in the TBSFD tablespace. 答案：D 打开回收站功能，当你 drop 一个表后，该表会被改名同时还放在原来的表空间里 The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constra ints, nested tables, and the likes are not removed and still occupy space. They cont inue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of t ablespace space constraints. Each user can be thought of as having his own recycle bin, because, unless a use r has the SYSDBA privilege, the only objects that the user has access to in the recy cle bin are those that the user owns. /docs/cd/E11882_01/server.112/e10595/tables011.htm#ADM IN11680 56. You execute the following command to audit the database activities: SQL& AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL; What is the effect of this command? A) One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session. B) One audit record is created for every session when any user successfully drop s a table owned by SCOTT. C) One audit record is created for each successful DROP TABLE command executed b y any user to drop tables owned by SCOTT.D) One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session. E) One audit record is created for each successful DROP TABLE command executed i n the session of SCOTT. 答案：A 如果用户scott在他的会话中成功的drop掉一个或多个表，一条审计信息将被在所有会话中创建。 by session 在每个 session 中发出 command 只记录一次,by access 则每个 command 都记录， 这是 ORACLE 语法规定的。 audit sel(sys 用户除外,by session 是缺省的) 同一个 session 执行相同语句只审计一次 audit select on tt by access
57. You plan to use static database registration for a new listener when you create it. What could be the two reasons for this? (Choose two.) A) More than one database is to be registered with the listener. B) The users will connect the database by using the host naming method. C) The Oracle Enterprise Manager is to be used to monitor an Oracle9i database. D) The database that is to be registered with the listener is configured in shared s erver mode. E)The listener is not configured on the default port of 1521 and the instance is not configured to register with a nondefault port. 答案：CE 你这个考题是 10G 的吧？因为想用监听器连接用户和旧版本的数据库，就必须要静态注册数据 库，而 C 选项是连 9I，所以这个选项对了。 E 选项说监听器不在 1521 端口，而实例默认是在这个端口找监听器。选项又说实例“is not configured to register with a nondefault port”，也就是说没有用动态注册去指定不在默认 端口的监听器，所以这时就用静态注册了，即将实例的信息直接写在 listener.ora 中。 service_names 动态注册 需 1521 端口 show parameter local_listener 一定要配置服务端 tns 让 pmon 自动注册其他端口 a123= ( (address_list=(address=(prottocol=tcp)(host=xys)(port=1522)) ) tnsping a123; tnsping+服务命名 alter system set local_listener=a123; 58. Which two statements regarding archive log destinations are true? (Choose two.) A) A maximum of 10 destinations can be specified. B) The archive logs must be written to all the destinations. C) The archive log files can be written only to local destinations. D) The archiving information can be traced to the alert log file whenever the archiv ing to a destination is successful.E) The number of archiving destinations must be equal to the number of archive proce sses (ARCn). 答案：AD SQL& show parameter archive log_archive_dest_10 可以看到最大 10 个目的地 The number of archiving processed must be relevant to the parameter value of LOG_ARCHIVE_MAX_PROCESSES.The default value of this parameter is 2.The actual number of archiver processes in use may vary subsequently based on archive workload. So,Answer E is worng. 59. Due to media failure you lost one of the data files belonging to the USERS table space, and the tablespace is not available to use. Which statement regarding the sta tus of the database is true? A) The database remains open. B) The database gets dismounted. C) The database becomes read only. D) The database instance gets aborted. E) The database gets shut down automatically. F) The status of the database depends on the status of the USERS tablespace. 答案：A 60. You executed the following command to export the EMPLOYEES table from a remote M achine: $EXPDP hr/hr@data. DUMPFILE=my_dir:exp_hr.log LOGFILE=data_pump_dir:log_hr.log TABLES=employees What would be the outcome of this command? A) The command would execute successfully and the export dump file would be crea ted in the destination of the directory object MY_DIR. B) The command would execute successfully. But log file would not be created as DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege. C) The command fails with an error because DATA_PUMP_DIR directory have higher p recedence over the per-file directory. D) The command fails with an error because no absolute path is specified for log file and dumpfile. 答案：A DUMPFILE=[directory_object:]file_name [,….] Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指 定 directory_object,导出工具会自动使用 DIRECTORY 选项指定的目录对象 LOGFILE=[directory_object:]file_name Directory_object 用于指定目录对象名称,file_name 用于指定导出日志文件名.如果不指定 directory_object.导出作用会自动使用 DIRECTORY 的相应选项值. NETWORK_LINK指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项. 61. View the Exhibit.Which statement regarding the dept and emp tables is true? A) When you delete a row from the emp table, you would receive a constraint violatio n error. B) When you delete a row from the dept table, you would receive a constraint violati on error. C) When you delete a row from the emp table, automatically the corresponding rows ar e deleted from the dept table. D) When you delete a row from the dept table, automatically the corresponding rows a re deleted from the emp table. E) When you delete a row from the dept table, automatically the corresponding rows a re updated with null values in the emp table. F) When you delete a row from the emp table, automatically the corresponding rows ar e updated with null values in the dept table. 答案：D 外键，因为添加了 delete cascade 索引删除主键的同时会自动删除对应外键 62. As a result of performance analysis, you created an index on the prod_name colum n of the prod_det table, which contains about ten thousand rows. Later, you updated a product name in the table. How does this change affect the index? A) A leaf will be marked as invalid. B) An update in a leaf row takes place. C) The index will be updated automatically at commit. D) A leaf row in the index will be deleted and inserted.E) The index becomes invalid when you make any updates. 答案：D 索引分三层，最下面一层是 leaf ,每个 leaf block 包含很多 leaf row， 每个 leaf row 是 由键值和 rowid 组成， 查找索引时先找到键值，再根据对应的 rowid 找到数据块中的数据。更新索引要先删除后插入， 因为这种方式最简洁和规范。不然的话，如果你采用移动的方式会变得非常麻烦和不可控制。 63. You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager. Which statement about the script created by Oracle Enterprise Manager is tr ue? A) It is a SQL script. B) It is a PL/SQL file. C) It is an operating system (OS) file. D) It is a Recovery Manager (RMAN) script. 答案：D 在 em 中使用 backup scheduler 采用的是 RMAN 脚本 64. You executed the STARTUP MOUNT command to start your database. For which databas e operation do you need to start the database in the MOUNT state? A) renaming the control files B) dropping a user in your database C) enabling or disabling redo log archiving D) dropping a tablespace in your database E) re-creating the control files, after you lost all the control files in your d atabase 答案：C 数据库启动顺序，首先是 nomount--读取参数文件，mount--根据参数文件打开控制文件， open=根据控制文件打开数据文件，打开数据库.所以说在 mount 阶段是可以改变 datafile 和 redo log file 的名称的。在 mount 阶段，你可以修改数据文件的名字，同时也可以把数据改为归档或 者非归档模式 65. You are creating a locally managed tablespace to meet the following requirements All the extents should be of the same size. The data should be spread across two data files. A bitmap should be used to record the free space within the allocated extents. Which three options would you choose? (Choose three.) A) set PCTFREE and PCTUSED to 50 B) specify extent allocation as Uniform C) specify extent allocation as Automatic D) create the tablespace as bigfile tablespace E) create the tablespace as smallfile tablespace F) set segment space management to Automatic G) use the RESIZE clause while creating the tablespace 答案：BEF这里的要求是创建统一区的管理表空间,所以是uniform,表空间有2个文件,不可能是bigfile(如 果是 bigfile 只能是一个文件) A bitmap should be used to record the free space within the allocated extents. 这句话我觉得说的有问题,要改成块的管理方式才能选 assm 自动区段空间管理(ASSM)――ASSM 的 tablespace 是通过将 SEGMENT SPACE MANAGEMENT AUTO 子句添加到 tablespace 的定义句法里而实现的。 通过使用位图 bitmap 取代传统单向的链接列表 freelist，ASSM 的 tablespace 会将 freelist 的 管理自动化， 并取消为独立的表格和索引指定 PCTUSED、 FREELISTS 和 FREELIST GROUPS 存储参数 的能力。 create tablespace a