131. Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.)
A) It can be increased up to the value of the SGA_MAX_SIZE parameter.
B) Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the automatic shared memory management feature.
C) Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned and manually sized components.
D) Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components.
答案：AD
解析：SGA_TARGET最大能增加到SGA_MAX_SIZE规定的大小，当增大SGA_TARGET的大小，同时会自动增加其他组件的大小.
Editor’s notes:Dynamic Modification of SGA_TARGET
The SGA_TARGET parameter can be increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
When SGA_TARGET is resized, the only components affected are the automatically tuned components for which you have not set a minimum value in their corresponding initialization parameter. Any manually configured components remain unaffected.
132. 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 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；
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);
133. 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
解析：因为该数据库正在被使用，无法关闭。所以使用DBCA创建一个模板，并使用该模板创建数据库
134. 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
解析：安装数据库时OUI的行为跟响应文件不一致，这时应该比较installActions.log和response file查看原因
135. Your database is in NOARCHIVELOG mode. After which two operations you should take the backup of control file? (Choose two.)
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/2009/12/13/4974440.aspx
136. 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
解析：isql*plus不能登录DBA身份，按D的步骤所做就可以了
137. You want to create a tablespace with the following specifications:
1. The tablespace extends automatically.
2. Used and free extents should be managed by bitmaps.
3. Default PCTUSED attribute is set to 60.
4. All the extents would be of size 1 MB.
Which three options would you choose to create the tablespace? (Choose three.)
A) tablespace with AUTOEXTEND enabled
B) tablespace with dictionary-managed extents
C) tablespace with a uniform extent allocation of 1 MB
D) tablespace with segment space management as manual
E) tablespace with segment space management as automatic
答案：ACD
解析：这里的要求是创建个本地管理的表空间,而且表空间是自动扩展的,区的大小统一为1m,pctused为60,说明不能是assm的,
在ASSM管理下,insert通过扫描位图来查找可用的block即使block的可用空间低于pctfree,也不会从位图中摘除,因此pctused参数不再需要,而pctfree参数,仍需要它来指示需要保留多少空间给后续的update导致的行数据增长使用.
138. 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
解析：Flashback Table单独恢复某个表,不影响别个表数据,即别的 业务可以正常进行
139. Your database is open and you plan to perform Recovery Manager (RMAN) backups. Which three
statements are true about these backups? (Choose three.)
A) The backups would be consistent.
B) The backups would be inconsistent.
C) The backups need to be restored and database has to be recovered in case of a media failure.
D) The backups need not be restored during recovery in case of a media failure.
E) The backups would be possible only if the database is running in ARCHIVELOG mode.
F) The backups would be possible only if the database is running in NOARCHIVELOG mode.
答案：BCE
解析：RMAN-备份不是连续的-可以在数据库打开的情况下备份-恢复时需要使用restore命令，如果meida failure那么首先要restore转储备份 然后再recover命令将数据文件恢复到失败点状态
非归档模式只能在mount下备份
140. Which two database operations can be performed at the MOUNT stage of database startup? (Choose two.)
A) renaming of data files
B) dropping the database user
C) renaming of control files
D) multiplexing of control files
E) configuring the database in ARCHIVELOG mode
答案：AE
解析：mount状态下通过读取控制文件来获取数据文件和联机重做日志文件的名称和状态。但是，此时不必执行任何检查便可验证数据文件和联机重做日志文件是否存在
Mount或open下可以打开归档
141. You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis.
You want to analyze only few most resource-intensive statements. What would be your suggestion to receive
recommendations on the efficient use of indexes and materialized views to improve query performance?
A) Run the SQL Access Advisor.
B) Run the SQL Tuning Advisor (STA).
C) Run the Automatic Workload Repository (AWR) report.
D) Run the Automatic Database Diagnostic Monitor (ADDM).
答案：A
解析：sql tuing advisor将一条或多条SQL语句作为输入,并且研究这些语句的结构与执行方式.这些SQL语句称为SQL TUNING SET,标识负载较高的SQL语句与建议改进措施.
SQL ACCESS ADVISOR 也将SQL TUNING SET 作为其输入.这个顾问程序通过学习添加索引或物化视图来改进SQL执行性能.
142. 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，包含程序包说明中定义的子程序的实际代码。
只有在编译了程序包说明之后才能编译程序包体。可以在没有程序包体的情况下创建程序包说明，但不能在没有程序包说明的情况下创建程序包体。与程序包说明是分开的。因此，可以更改并重新编译程序包体代码，此时不会将与程序包说明相关的其它对象标记为无效。
143. The database users are connecting to the PROD database from different applications, thereby degrading
the database performance. The senior database administrator suspects the large number of concurrent
connections to be the reason for low performance and asks you to restrict the number concurrent connections
per database user to one. Which action would you take to achieve this objective?
A. set the SESSIONS to 1 in the parameter file
B. grant SINGLE SESSION privilege to all of the users
C. set the SESSIONS_PER_USER to 1 in the users' profile
D. grant RESTRICTED SESSION privilege to all of the database users
E. create a role with SINGLE SESSION privilege and assign the role to the users
答案：C
解析：
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
144. You want to protect your database from single point of failure by multiplexing the control file. Your database is started using SPFILE. You have to perform the following tasks:
1. Shut down the database instance.
2. Copy the control file to new location.
3. Configure the CONTROL_FILES parameter.
4. Restart the instance.
Select the correct order in which these tasks need to be performed.
A) 1,2,3,4
B) 3,2,1,4
C) 3,1,2,4
D) 2,3,1,4
答案：C
用排除法:必须先关闭数据库,才能拷贝控制文件
修改CONTROL_FILES 实验
1.OCP教材上修改控制文件的方法
a.configure the control_files parameter
b.shutdown the database instance
c.copy the control file to new location
d.restart the instance
2.官方文档的方法
a.shutdown the database
b.copy an existing control file to a new location
c.edit the control_files parameter.
d.restart the database
145. 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
解析：
Select num_rows,blocks,empty_blocks,avg_space,chaint_cnt,avg_row_len,sample_size, last_analyzed
from dba_tables
where table_name='your table'
146. View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SCOTT user?
A) The user cannot use the role at all.
B) The user can grant the role to other users.
C) The user needs to enable the role explicitly.
D) The user can start using the role immediately.
答案：C
解析：
Explicitly（清楚的）
If you create a role,but you does grant the role to user whih no default role.When the user log on,it can not ues the privilege of the role except the user enable the role after it log on;
147. View the Exhibit.
Which statement causes more undo generation?
A) SELECT * FROM emp;
B) DELETE FROM emp WHERE empno=7934;
C) UPDATE emp SET comm=400 WHERE empno=7844;
D) INSERT INTO emp VALUES (7999,'JHON','CLERK', 7782,'10-MAY-83', 1500, NULL, 10);
答案：B
解析：一般来讲，INSERT生成的undo最少，因为Oracle为此需记录的只是要“删除”的一个rowid（行ID）。UPDATE一般排名第二（在大多数情况下）。对于UPDATE，只需记录修改的字节。你可能只更新（UPDATE）了整个数据行中很少的一部分，这种情况最常见。因此，必须在undo中记录行的一小部分。前面的许多例子都与这条经验相左，不过这是因为那些列更新的行很大（有固定大小），而且它们更新了整个行。更常见的是UPDATE一行，并修改整行中的一小部分。一般来讲，DELETE生成的undo最多。对于DELETE Oracle必须把整行的前映像记录到undo段中。
148. In which situation would you use the Oracle Shared Server configuration?
A) when performing export and import using Oracle Data Pump
B) when performing backup and recovery operations using Oracle Recovery Manager
C) when performing batch processing and bulk loading operation in a data warehouse environment
D) in an online transaction processing (OLTP) system where large number of client sessions are idle most of the time
答案：D
解析：OLTP数据库，有大量的用户会话需要连接，这个时候使用共享服务。OLAP 数据仓库
149. 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
解析：A选项online redo log 不可以备份
D选项RMAN可以执行 inconsistent database backups
E选项无归档模式下在mount下备份
150. The session of user SCOTT receives the following error after executing an UPDATE command on the EMP
table:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A) It is terminated after receiving the error and JIM can continue with his transaction.
B) The transaction in it is terminated after receiving the error and JIM can continue with his transaction.
C) SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
D) SCOTT has to reexecute the last command in the transaction after he commits the transaction.
答案：CD
解析：死锁是锁定冲突的特殊示例。两个或多个会话等待由对方锁定的数据时，就会发生死锁。
因为每个会话都在等待另一个会话释放锁定，所以任何一个会话都不能完成事务处理，也不能解决冲突。Oracle 数据库会自动检测死锁并终止语句，同时显示错误消息。更正这种错误的适当做法是执行提交或回退，这样做会在一个会话中释放任何其它锁定，以便其它会话可继续完成其事务处理。
151. View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE
------------------------------------------------------- ----------------------
Tablespace [TEST] is [28 percent] full 28.125
What could be the two reasons for the elimination of the other rows in the output? (Choose two.)
A) The threshold alert conditions are cleared.
B) The threshold alerts are transferred to DBA_ALERT_HISTORY.
C) The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
D) The threshold alerts related to database metrics are permanently stored in
DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
答案：AB
解析：Oracle 10g通过DBA_OUTSTANDING_ALERTS视图记录了数据库活动警报信息,这些信息直到警告清除或复位才会从这个视图消失,而进入DBA_ALERT_HISTORY
搞清 ：DBA_OUTSTANDING_ALERTS ： 描述了oracle数据库认为是突出的警告。当向 Flash Recovery中添加或删除文件等变化都将记录在数据库的 alert 日志中，Oracle 10g 也针对该新特性提供了一个新的视图 ，通过该视图可以得到相关的信息。
dba_alert_history：代表了不在突出的有时间限制的历史
threshold_alert ：是超过备份阈值时引发的警报
ORACLE的一些内部组件可以周期性进行监控的动作，对发现的问题产生相应的alert信息。
alert信息的产生是基于一些threshold值或者特定事件。 基于threshold的alert信息会自DBA_OUTSTANDING_ALERTS视图中找到，当被clear的时候，这些状态的警告会进入DBA_ALERT_HISTORY
select * from dba_outstanding_alerts 警告记录地方 只记录根据度量产生的
select * from dba_alert_history (警告归档后地方,所有的)
152. 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
解析：打开回收站功能，当你drop一个表后，该表会被改名同时还放在原来的表空间里
153. 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 = orcl.oracle.com)
)
)
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 orcl.oracle.com 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的
154. 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
155. When you try to start the Database Control by using the emctl start dbconsole command the following error is displayed:
TZ set to America/New_york
OC4J Configuration issue.
/u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found.
Which two environment variables do you need to set appropriately to avoid such errors and start the Database
Control successfully? (Choose two.)
A) NLS_LANG
B) ORACLE_SID
C) ORACLE_HOME
D) ORACLE_BASE
E) LD_LIBRARY_PATH
答案：BC
解析：ORACLE_HOME＝$ORACLE_BASE/product/version
ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录
156.You want to be notified when the space usage of an existing critical tablespace has reached 75% of the allocated space. Which option would you use to achieve this?
A. run a procedure to check the tablespace space usage
B. define the warning threshold for the tablespace at 75% in Database Control
C. create a trigger to be executed when tablespace space usage reaches 75%
D. submit a job by using DBMS_JOB package to check the free space in the tablespace at regular intervals
E. define the warning threshold to be 75% for the tablespace by ALTER TABLESPACE .. ADD THRESHOLD command
答案：B
解析：
system trigger contains:
System events such as startup, shutdown, and error messages
User events such as logon and logoff
157. 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 theDEPARTMENTS 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
解析：on delete cascade 级联删除 主表dept删除一条记录，则emp表相关联的记录也被删除
158. Exhibit:View the Exhibit to see the information on existing tablespaces of the PROD database. You
observe that a large volume of INSERTs and DELETEs are happening on the TRANS table residing in the
USERS tablespace, and you suspect that the TRANS table is fragmented. Which advisory component would
you refer to, in order to find the information about table fragmentation?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning and Access Advisors
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)
答案：B
解析：fragmented 成碎片状
witch advisor that oracle can use is display by DBA_ADVISOR_DEFINITIONS. Segment advisor is belonged to it. You can use dbms_advisor package to create a task for segment advisor
159. View the Exhibit.
The DBA creates a new user as follows:
SQL> CREATE USER manfus IDENTIFIED BY manfus TEMPORARY TABLESPACE temp;
User manfus creates a table as follows:
SQL> CREATE TABLE material (id NUMBER (3));
In which tablespace will the material table be created?
A) TEMP
B) PROD
C) USERS
D) SYSAUX
E) SYSTEM
答案：C
解析：建ACCOUNT时没有指定表空间,所以当创建表时会放在默认的表空间Users下面
160. You want to move all the objects of the APPS user in the test database to the DB_USER schema of the
production database. Which option of IMPDP would you use to accomplish this task?
A) FULL
B) SCHEMAS
C) REMAP_SCHEMA
D) REMAP_DATAFILES
E) REMAP_TABLESPACE
答案：C
解析：
1. REMAP_DATAFILE 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项. REMAP_DATAFIEL=source_datafie:target_datafile
2. REMAP_SCHEMA 该选项用于将源方案的所有对象装载到目标方案中
