Oracle 10g OCP 042 题库 31-70 题 共168题
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.
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
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
Oracle undo 管理
http://blog.csdn.net/tianlesoftware/archive/2009/11/30/4901666.aspx
Oracle undo 表空间管理
http://blog.csdn.net/tianlesoftware/archive/2010/07/11/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
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
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
理解的也不是很透彻，参考 About Fine-Grained Auditing 小节：
http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/auditing.htm#DBSEG60681
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
答案：ABD
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94712
16.5.1 Verifying Optimizer Statistics
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_overview.htm#PFGRF94815
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
10.5.3 Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/instance_tune.htm#PFGRF13015
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
Oracle Flashback 技术 总结
http://blog.csdn.net/tianlesoftware/archive/2009/10/16/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
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 empno;
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 empno;
答案：ABE
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
在磁盘上的备份会使用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/2010/06/09/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
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 记录的是事务。
50. 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
http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/asm.htm#ADMQS12100
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
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/2009/10/21/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
Oracle Merge Into 的用法详解实例
http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4714921.aspx
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
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.employees;
Table created.
Further, you executed the following command to drop the table:
SQL> DROP TABLE emp;
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
The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue 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 tablespace space constraints.
Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tables011.htm#ADMIN11680
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 drops a table owned by SCOTT.
C) One audit record is created for each successful DROP TABLE command executed by 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 in the session of SCOTT.
答案：A
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 server 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
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 archiving to a destination is successful.
E) The number of archiving destinations must be equal to the number of archive processes (ARCn).
答案：AD
59. Due to media failure you lost one of the data files belonging to the USERS tablespace, and the tablespace
is not available to use. Which statement regarding the status 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 Machine:
$ EXPDP hr/hr@data.us.oracle.com
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 created 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 precedence over the per-file directory.
D) The command fails with an error because no absolute path is specified for log file and dumpfile.
答案：A
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 violation error.
B) When you delete a row from the dept table, you would receive a constraint violation error.
C) When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
D) When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
E) When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
F) When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
答案：D
62. As a result of performance analysis, you created an index on the prod_name column 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
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 true?
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
64. You executed the STARTUP MOUNT command to start your database. For which database 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 database
答案：C
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
66. You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file.
What action would you take to detect the cause of this behavior?
A) Compare the contents of the install.log file with the response file.
B) Refer to the contents of the oraInst.loc file to verify the steps performed by OUI.
C) Compare the contents of the installActions.log file with that of the response file.
D) Refer to the alert log file for information regarding the actions performed by OUI during installation.
答案：C
67. Your database is configured with the following parameters related to SGA)
SGA_TARGET=256MB
SHARED_POOL_SIZE=32MB
DB_CACHE_SIZE=100MB
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
STREAMS_POOL_SIZE=0
Which two statements are true about the configuration? (Choose two.)
A) The SGA_TARGET value cannot be sized smaller than 100 MB.
B) The shared pool and the default buffer pool will not be sized bigger than 32 MB and 100 MB, respectively.
C) The shared pool and the default buffer pool will not be sized smaller than 32 MB and 100 MB, respectively.
D) 124 MB (256 minus 132) of memory is available for use by all the manually sized components.
E) 124 MB (256 minus 132) of memory is available for use by all the manually and automatically sized components.
答案：CE
68. You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who are performing the transactions and the values used in the transactions. Also, you plan to transfer these values to another table for analysis.
How would you achieve this?
A) by using triggers
B) by using external tables
C) by using anonymous PL/SQL blocks
D) by auditing all DML operations on the table
答案：A
69. Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A) The ADDM runs after each AWR snapshot is collected.
B) The ADDM requires at least four AWR snapshots for analysis.
C) The ADDM analysis provides only the diagnostic information but does not provide recommendations.
D) The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
E) The ADDM calls other advisors if required, but does not provide recommendations about the advisors.
答案：AD
Oracle ADDM 自动诊断监视工具 介绍
http://blog.csdn.net/tianlesoftware/archive/2010/05/29/5630942.aspx
70. Which statement is true about the loss of system-critical data files in ARCHIVELOG mode?
A) The data files can be recovered till the last committed transaction.
B) The data files can be recovered without shutting down the database.
C) The data file is taken offline automatically; the database remains open.
D) The data files can be recovered to the time of the most recent backup.
答案：A
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977
DBA3 群：63306533; 聊天 群：40132017
