声明：所有解析均限于本人理解
161. Your database is not configured for session failover. Your tnsnames.ora file contains the following
details:
test.us.oracle.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS= (PROTOCOL=tcp) (HOST=test1-server) (PORT=1521))
(ADDRESS= (PROTOCOL=tcp) (HOST=test2-server) (PORT=1521)))
(CONNECT_DATA= (SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A) Load balancing
B) Instance failover
C) Database failover
D) Connect-time failover
E) Transparent Application Failover (TAF)
答案：D
解析：Connect Time Failover,只是在发起连接时才去感知节点故障，如果发现节点没有响应,则自动尝试地址列表中的下一个地址。(在客户端tnsnames.ora中添加FAILOVER=ON)
TFA(Transparent Application Failover),就是建立连接以后，应用系统运行过程中，如果某个实例发生故障，连接到这个实例的用户会自动迁移到其他健康实例上，透明，不需用户介入。
(在客户端tnsnames.ora中添加FAILOVER_MODE
1:method选项用于定义何时创建到其他实例的连接,有basic和preconnect
2:type用于定义发生故障时对完成的sql语句如何处理，有session和select
3:delay和retries代表重试间隔时间和重试次数)
162. 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
解析：创建一个审计记录整个会话中scott用户成功删除表
163. 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-独立服务进程启动时，里面包括这个进程的数据和控制信息
164. A user complains about getting this error after issuing a certain SQL statement:
ORA-02393: exceeded call limit on CPU usage
Because of the error, the SQL statement gets aborted. What action would you take to increase the CPU usage limit in the subsequent sessions of the user?
A) Modify the resource limit in the profile used by the user.
B) Set the RESOURCE_LIMIT initialization parameter to FALSE.
C) Increase the value of the SESSION_CACHED_CURSORS initialization parameter.
D) Increase the value of the SESSION_MAX_OPEN_FILES initialization parameter.
答案：A
解析：一个用户执行sql语句报错ORA-02393:exceeded call limit on CPU usage这时候可以在该用户的profile 里修给recourse limits
当需要设置资源限制时，必须设置数据库系统启动参数RESOURCE_LIMIT，此参数默认值为FALSE可以使用如下命令来启动当前资源限制：alter system set RESOURCE_LIMIT=true;
165. Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future.
What action would you take to achieve this task?
A) Set the STATISTICS_LEVEL initialization parameter to ALL.
B) Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.
C) Decrease the snapshot interval in the AWR to collect more number of snapshots during the peak-load period.
D) Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid the automatic purging of the snapshots.
答案：B
解析：AWR快照集:快照集是一种机制，使用这种机制可标记重要时段的快照数据集。快照集是根据一对
快照定义的，这对快照用快照序列号 (snap_id) 来标识。每个快照集对应于一对且唯一一对快照。
快照集可用用户提供的名称标识,也可用系统生成的标识符标识。通过执行
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE 过程并指定一个名称和一对快照标识符，便可创建一个快照集。会为新建的快照集指定一个快照集标识符。在数据库生命周期内，快照集标识符是唯一的。快照集用于保留快照数据。因此，属于某个快照集的快照会一直保留，直到删除该快照集。通常，会为过去某些有代表性的时段创建快照集，用于与当前系统行为进行比较。另外，
可在 Database Control 中通过使用快照集来设置基于阈值的预警。
可直接从 DBA_HIST_SNAPSHOT 或 Enterprise Manager Database Control 获取 snap_id。
166. 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.
答案：BDF
解析：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.
使用FGA策略实现精细审计的时候，不需要激活数据库审计，并且oracle会自动将审计结果放到数据字典表FGA_LOG$中。
对D选项解释:用FGA的时候你当然是可以指定条件的。dbms_fga.add_policy中有一个参数audit_condition就是用来指定审计条件的。Oracle 10g中对FGA(Fine Grained Auditing,细粒度审计）做了极大的增强，这使得FGA可以很容易并且很灵活的实现对某个表的select和DML操作的审计
167. 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
解析：ASM 是 Oracle 数据库10g 中一个非常出色的新特性，它以平台无关的方式提供了文件系统、逻辑卷管理器以及软件 RAID 等服务。 ASM 可以条带化和镜像磁盘，从而实现了在数据库被加载的情况下添加或移除磁盘以及自动平衡 I/O 以删除“热点”。ASM不是一个通用的文件系统，并只能用于 Oracle 数据文件、重做日志以及控制文件。ASM 中的文件既可以由数据库自动创建和命名（通过使用 Oracle 管理文件特性），也可以由 DBA 手动创建和命名。由于操作系统无法访问 ASM 中存储的文件，因此对使用 ASM 文件的 数据库执行备份和恢复操作的唯一途径就是通过恢复管理器 (RMAN)。 ASM 作为单独的 Oracle 实例实施，只有它在运行时其他数据库才能访问它。
168. Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)
A) The corresponding indexes for the table are also truncated.
B) Delete triggers on the table are fired during the execution of the TRUNCATE command.
C) Very little or no undo data is generated during the execution of the TRUNCATE command.
D) The child table is truncated when the TRUNCATE command is applied on the parent table.
E) The high-water mark (HWM) is set to point to the first useable data block in the table segment.
答案：ACE
解析：The effects of using this command are as follows:
• The table is marked as empty by setting the high-water mark (HWM) to the
beginning of the table, making its rows unavailable.
• No undo data is generated and the command commits implicitly because TRUNCATE
TABLE is a DDL command.
• Corresponding indexes are also truncated.(索引也会清空)
•A table that is being referenced by a foreign key cannot be truncated.
•The delete triggers do not fire when this command is used.
(delete trigger里面的内容不会执行,但对象仍然存在)
169．You executed the following command to back up the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
What do you find in the trace file?
A. Image of the control file
B. Location of the control file
C.Contents of the control file in text format
D.SQL command to re-create the database
E.SQL command to re-create the control file
F.Contents of the control file in binary format
答案：E
解析：
re-create的command如下
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
CHARACTER SET ZHS16GBK
;
170．Your boss at Supportcenter.cn wants you to clarify Oracle 10g. Which two steps are performed the first time any UPDATE statement is issued after the instance is started? （Choose two）
A. Creating the parse tree of the statement
B. Writing the modified data blocks to the data files
C.Writing the modified data to the archived redo log files
D.Updating the control file to indicate the most recent checkpoint
E.Updating the data file header to indicate the most recent checkpoint
F.Reading the blocks to database buffer cache if they are not already there
答案：CD
解析：ORACLE数据更新时，最初是保存在REDO日志缓存里，再通过LGWR写到REDO日志文件。
当REDO日志文件写满时，就会自动进行日志切换，并触发checkpoint，更新数据文件头和控制文件。
171. You work as a database administrator for Supportcenter.cn. You have been asked to use a centralized
administrative tool to administer your database servers and application servers. In order to achieve the objective,
which component would you configure on each database server?
A. Database Control
B. Management Server
C.Management Repository
D.Application Server Control
E.Oracle Management Agent
答案：E
解析：
Oracle Management Agent (Management Agent) is one of the
integral components of Enterprise Manager Grid Control (Grid
Control) architecture. Management Agent communicates with the
monitored targets, collects information about their health,
transports that information to Oracle Management Service(OMS),
which in turn stores the collected details in the central
repository created in Oracle Database.
172. The data file belonging to the SYSTEM tablespace is corrupted and no backup of the file is available. How do
you recover the data file?
A. The data file cannot be recovered.
B. The date file can be restored from the SYSTEM auto backup.
C.Take thetablespaceoffline,drop,kand re-create it.
D.Recovery Manager (RMAN) is used to recover the data file.
E.The ALTER DATABSE CREATE DATAFILE..commandis used to
recover the data file.
答案:A
解析：
system表空间的数据文件损坏：
1. 以mount方式启动数据库
svrmgrl>startup mount;
2. 从相应的备份结果集中恢复关于这个数据文件的最近的备份。对于没有采用带库备份的点可以直接从磁带上恢复；对于用带库备份的点用相应的rman脚本来恢复。
3. 恢复system表空间：
svrmgrl>alter database recover datafile ‘datafile_name’;
4. 打开数据库：
svrmgrl>alter database open;
5. 用适当的方法进行数据库全备份。
173. You work as a database administrator for Supportcenter.cn. You started the instance using the init.orafile. You
have two control files and three redo log groups in your database. You decided to protect the database against
failures by adding one more control file. Which file is the appropriate way to perform this task?
A. Shut down the instance, copy the control file to the third location and open the database.
B. Abort the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in theinit.orafile
and open the database.
C.Copy the control file to the third location, modify the CONTROL_FILES parameter in theinit.orafile, restart the instance
and open the database.
D.Shut down the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in
theinit.orafile and open the database.
E.Shut down the instance, startup in the mount state, copy the control file to the third location, modify the
CONTROL_FILES parameter ininit.oraand open the database.
答案：D
解析：增加一个控制文件的步骤。关闭数据库，打开至mount状态，复制控制文件，修改init.ora。
174. You work as a database administrator for Supportcenter.cn. You decided to manage client and server
connections using Local Naming method in Supportcenter.cn. When you try to connect to the database you get the following error:
ERROR:
ORA-12154:TNS:couldnot resolve service name
Which network configuration files would you look into to resolve this error? （Choose two）
A. snmp.ora
B. names.ora
C.spfile.ora
D.sqlnet.ora
E.listener.ora
F.tnsnames.ora
答案：DF
解析：
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
ACTIONS:
- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
175. You work as a database administrator for Supportcenter.cn. In your production database, the size of Database
Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The
Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is
production database, you want to accomplish this task with no impact on the user's connections. Which activity must
you have completed before accomplishing this task?
A. You must have started the database instance in restricted mode.
B. You must have started the database instance inNORMALmode.
C.You must have started the database instance with server parameter file.
D.You must have started the database instance but must not have mounted.
E.You must have started the database instance but must not have opened.
答案：C
解析：Spfile可以立刻生效，而不会像Pfile那样，改了以后还要restart instance才能生效
176. Your Supportcenter.cn database is in ARCHIVELOG mode. The data file that belongs to the SYSTEM
tablespace has become corrupted. Up to what point can you recover the database?
A. Until last commit
B. Until the time you perform recovery
C.Until the time the date file got corrupted
D.Until the point where the last transaction begun
E.You cannot recover the SYSTEMtablespaceand must re-create the database.
答案：A
解析：当在一个transaction中时，会话不正常结束，这时PMON会自动将没有提交的数据rollback，commited的脏数据放到datafile中
F的意思如果一个SESSION,前面有COMMIT的部分是不回滚的。只有还未COMMIT部分被回滚了
177. You work as a database administrator for Supportcenter.cn. You find that users with DBA role are using more
CPU resources than what is allocated in their profiles. Which action would you take to ensure that resources limits
are imposed on these users?
A. Assign the DEFAULT profile to the users
B. Set the RESOURCE_LIMIT parameter to TRUE in the parameter file
C.Create a new profile with CPU restrictions and assign it to the users
D.Specify the users as members of the DEFAULT_CONSUMER_GROUP
E.Revoke the DBA role and grant CONNECT and RESOURCE role to the users
答案：B
解析：在oracle中使用概要文件(profile)来设置专门的资源限制。
例如创建一个名为miser的profile
SQL> create profile miser
2 limit
3 idle_time 60
4 connect_time 120
5 sessions_per_user 2;
Profile created
则数据库将允许连接保持最多120秒，指定用户最大的并发会话为2个。然后创建用户时指定最后加上profile 'miser'，也可以alter user test profile miser;
概要文件必须通过alter system set resource_limit = true 来实施概要文件中的资源限制。
178. You work as a database administrator for Supportcenter.cn. In your Oracle database 10g installation you have
set ORACLE_BASE to /u01/app/oracle. Which objective will be achieved by this setting?
A. The Oracle kernel will be placed in this location.
B. The Oracle software will be placed in this location.
C.The server parameter file (SPFILE) will be placed in this location.
D.The database files will be placed in this location, if not specified explicitly.
E.The location will be considered for the base of Oracle Managed Files (OMF).
F.The location will be considered for the base of Optimal Flexible Architecture (OFA).
答案：F
解析：Oralce出于有效性，强烈推荐OFA(最佳灵活体系结构)。OFA设计目的是为了使磁盘争用最小，运行多个数据库而不产生混淆，改善数据库性能。
Oracle基目录一般具有为/u01/app/oracle，主目录/u01/app/oracle/product/10.2.0/db_1
路径末的db_1表示数据库的第一次安装的Oracle主目录。
179. The operating system file cratab in the Linux platform gets updated whenever you create a new database on the
same host machine. What kind of information is stored is stored in this file?
A. OracleSIDsonly
B. Oracle homes only
C.Oracle install timestamp
D.Oracle inventory pointer files
E.Oracle database creation timestamp
F.OracleSIDsand Oracle homes only
G.OracleSIDs, Oracle homes and flag for auto startup
答案：G
解析：cratab记录了oraclesid,oracle homes和oracle flags
180. You work as a database administrator for Supportcenter.cn. Your database is configured for automatic undo
management. UNDO_RETENTION is set to 3 hours. You want to flash back a table that was created last year. How
far back can the flashback query go?
A. 3 hours
B. 6 months
C.until last year
D.until last commit
E.until the point when the undotablespacewas refreshed
F.until the database is shut down and the memory erased
答案：A
解析：3小时内一定保留，commited的数据如果不够用，那就覆盖，如果够用，那就保留3小时
181. In your Supportcenter.cn production database, you find that the database users are able to create and read files
with unstructured data, available in any location on the host machine from an application. You want to restrict the
database users to access files in a specific location on the host machine. What could do to achieve this?
A. Modify the value for the UTL_FILE_DIR parameter in the parameter file
B. Grant read and write privilege on the operating system path to the database users
C.Modify the value for the LDAP_DIRECTORY_ACCESS parameter in the parameter file
D.Modify the value for the PLSQL_NATIVE_LIBRARY_DIR parameter in the parameter file
E.Create a directory object referring to the operating system path, and grant read and write privilege on the directory object
to the database users
答案：A
解析：
第一步：以管理员用户登陆
如：conn sys/password@sid as sysdba
第二步：设置可操作目录
需要指定utl_file包可以操作的目录。在oracle 10g以前，可以用以下方法：
1、alter system set utl_file_dir='e:\utl' scope=spfile;
2、在init.ora文件中，配置如下：
UTL_FILE=E:\utl或者UTL_FILE_DIR=E:\utl
在oracle 10g中建议用以下方法配置：CREATE DIRECTORY utl AS 'E:\utl';
参见oracle online:
In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.
第三步：授权给指定用户，以便执行utl_file
GRANT EXECUTE ON utl_file TO scott;
第四步：conn scott/tiger
就可以正常使用utl_file了。
182. Your boss at Supportcenter.cn wants you to clarify Oracle 10g. What statement about the Shared Server configuration is valid?
A. Program Global Area (PGA) is stored in Shared pool.
B. User session data and Cursor state are stored inLargepool and Stack space is stored Shared pool.
C.User session data is stored in Shared pool and Stack space and Cursor state are stored inLargepool.
D.User session data and Cursor state are stored in Large pool and Stack space is stored outside the System Global (SGA).
E.User session data and Cursor state are stored outside the System Global Area (SGA) and Stack space is stored inside the SGA.
答案：D
解析：
PGA is NEVER shared. Period! There's absolutely no way to share PGA. Otherwise it would not be called PGA. Just think of the name. So A is definitely wrong.
Similarly, stack space or process stack or call stack in a process's address space is always private. No exception. If you have a little system programming experience, you'll know a process's stack cannot be shared. It records what this process has done in the recent past. With this in mind, you can eliminate B, C, and E.
183. You work as a database administrator for Supportcenter.cn. On a Monday morning, you find the database instance aborted. After inspecting the alert log file, you execute the STARTUP command in SQL*Plus to bring the instance up.
What statement is true?
A. PMON coordinates media recovery.
B. SMON coordinates instance recovery.
C.PMON coordinates instance recovery.
D.Undo Advisor would roll back all uncommitted transactions.
E.SQL*PLUS reports an error with the message asking you to perform instance recovery.
答案：B
解析：SMON作用
1.清理临时空间以及临时段
2.接合空闲空间
3.执行实例恢复（Instance recovery）
4.离线（Offline）回滚段
5.执行并行恢复
http://www.eygle.com/archives/2008/05/oracle_smon_process.html
184. In your Supportcenter.cn database server the parameter PLSQL_CODE_TYPE has been set to NATIVE.
Which object would be achieved by the setting?
A. The source PL/SQL code will be stored in native machine code.
B. The source PL/SQL code will be stored in interpreted byte code.
C.The compiled PL/SQL code will be stored in native machine code.
D.The compiled PL/SQL code will be stored in interpreted byte code.
答案：C
解析：Oracle可以将PL/SQL模块编译成共享库中的代码来加速运行，过程被转换成C代码，然后用一个C编译器进行编译，并与Oracle进程动态链接。特点：速度块1-1.5倍，许多Oracle提供的程序包(UTL_File、UTL_RAW)已经用C实现。
plsql_code_type参数，默认值是interpreted，如果想要启动纯编译，则设置为Native。
185. Exhibit:
You work as a database administrator for Supportcenter.cn. You have started the database instance and you want to
manage your database remotely with Enterprise Manager through a Web browser.
Which two URLs would you use to access the Database Control? (Choose two.)
A. http://162.67.17.123:5500/em
B. http://www.162.67.17.123:5500/em
C.http://fubar.europe. Supportcenter.cn:5500/em
D.http:// SupportCenter 13.162.67.17.123:5500/em
E.http:// SupportCenter 13.fubar.europe. Supportcenter.cn:5500
F.http://www. SupportCenter 13.fubar.europe. Supportcenter.cn:5500/em
G.http:// SupportCenter 13.fubar.europe. Supportcenter.cn:5500/em
答案：AC
解析：要么是A一样的IP地址，要么是C一样的域名
186. You work as a database administrator for Supportcenter.cn. While loading data into the SupportCenter STAFF
table using Oracle Enterprise Manager 10g Database Control, you find the status of the job as failed. On further
investigation, you find the following error message in the output log:
ORA-01653 unable to extend table HR. SupportCenter STAFF by 8 intablespaceUSERS
Which task would you perform to load the data successfully without affecting the users who are accessing the table?
A. Restart the database instance and run the job
B. Truncate the SupportCenter STAFF table and run the job
C.Delete all rows from the SupportCenter STAFF table and run the job
D.Increase the size of the USERStablespacethe and run the job
E.Increase the size of the database default permanenttable space and run the job
答案：D
解析：题目中说unable to extend table HR。在不影响使用的情况下，可以采用扩大表空间的方法
187. You work as a database administrator for Supportcenter.cn. Users in the Supportcenter.cn PROD database
complain about the slow response of transactions. While investigating the reason you find that the transactions are
waiting for the undo segments to be available, and undo retention has been set to zero.
What would you do to overcome this problem?
A. Increase the undo return
B. Create more undo segments
C.Create another undotablespace
D.Increase the size of the undo tablespace
答案：D
解析：transactions are
waiting for the undo segments to be available, and undo retention has been set to zero. 这句说明undo表空间已经满了，所以需要增加表空间容量
188. You are working on a test database where instance recovery takes a considerable amount of time.
How can reduce the recovery time? (Choose two)
A. By multiplexing the control files
B. By multiplexing the redo log files
C.By decreasing the size of redo log files
D.By configuring mean time to recover (MTTR) to a lower value
E.By setting the UNDO_RETENTION parameter to a higher value
答案：CD
解析：MTTR可以设置恢复时间
decrease the size of redo log files means that the time of roll forward may decrease.So ,Answer C is write
189. Exhibit #1
Exhibit #2, command
You work as a database administrator for Supportcenter.cn. You have created a database link,
devdb.uk.
Supportcenter.cn, between the database PRODDB and DEVDB. You want to import schema objects of the HR user
using Oracle Data Pump from the development database, DEVDB, to the productiondatagbase, PRODDB.
View Exhibit #1 to see the source and targetdatabase.l You execute the code in Exhibit #2.
The code fails and produces the error displayed in Exhibit #3.
What would you do to overcome the error?
A. Remove thedumpfileoption in the command
B. Remove theflashback_time option in the command
C.Add the user,SYSTEM, to the schemas option in the command
D.Add thenetwork_link= devdb.uk. Supportcenter.cnoption in the command
E.Remove the schemasoptions and add thenetwork_link= devdb.uk. Supportcenter.cnoption in the command
F.Remove the dump file options and add the network_link= devdb.uk. Supportcenter.cnoption in the command
答案：F
解析：通过db_link导schema,不需要dumpfile
示例：http://space.itpub.net/785478/viewspace-570901
190. You work as a database administrator for Supportcenter.cn. The database is open. A media failure has occurred, resulting in loss of all the control files in your database.
Which statement regarding the database instance is true in this scenario?
A. The instance would hang.
B. The instance needs to be shut down.
C.The instance would be in the open state.
D.The instance would abort in such cases.
E.The instance would be in the open and invalid state.
F.The instance would in the open state, but all the background processes will be restarted.
答案：D
解析：当所有的控制文件都丢失时，实例会终止且无法启动
191. You work as a database administrator for Supportcenter.cn. In a production environment, users complain about
the slow response time when accessing the database. You have not optimized the memory usage of the Oracle
instance and you suspect the problem to be with the memory. To which type of object would you refer to determine the cause of the slow response?
A. The trace file
B. The fixed views
C.The data dictionary views
D.The operating system log fields
E.The dynamic performance views.
答案：E
解析：
http://blog.csdn.net/tianlesoftware/article/details/5863191
192. You are working on the SupportCenter database. What is the default name of the alert log file in this database?
A. alert_ SupportCenter .log
B. alertlog_ SupportCenter .log
C.alert_log_ SupportCenter .log
D. SupportCenter _alert_log.log
E.log_alert_: SupportCenter .log
F.trace_alert_ SupportCenter .log
答案：A
解析：警告日志的命名 alert_SID.log
193. You work as a database administrator for Supportcenter.cn. You have set the retention period for Automatic Repository (AWR) statistics to four days and collection interval to 15 minutes. You want to view the statistics collected and stored in AWR snapshot.
Which two methods would you use to view the AWR statistics? (Choose two)
A. use enterprise manager
B. use DBMS_SQL package
C.use DBMS_AWR package
D.use PRVT_WORKLOAD package
E.query the AWR snapshot repository objects
F.use DBMS_WORKLOAD_REPOSITORY package
答案：AF
解析:
Oracle 在10g以前的使用的是 Statspack做性能故障诊断的。Oracle Database 10g 提供了一个显著改进的工具：自动工作负载信息库 (AWR)。AWR 和数据库一起安装。数据库装好后，，快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次（snap）
要查看当前的设置，您可以使用下面的语句：
select snap_interval, retentionfrom dba_hist_wr_control;SNAP_INTERVAL RETENTION------------------- -------------------+00000 01:00:00.0 +00007 00:00:00.0
这些 SQL 语句显示快照每小时采集一次，采集的数据保留 7 天。要修改设置 — 例如，快照时间间隔为 20 分钟，保留时间为两天 — 您可以发出以下命令。参数以分钟为单位。
begin dbms_workload_repository.modify_snapshot_settings ( interval => 20, retention => 2*24*60 );end;
AWR 使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。（您可能已经猜到，H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。）在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上构建的。 AWR 历史表采集的信息比 Statspack 多许多，这些信息包括表空间使用率、文件系统使用率、甚至操作系统统计数据。这些表的完整的列表可以从数据字典中看到。
dba最常用的估计就是这个了
exec dbms_workload_repository.create_snapshot;
应用；
exec dbms_workload_repository.create_snapshot;
@？/rdbms/admin/awrrpt
生成报告，html和text格式都可以
194. Two database users, Jack and Bill, are accessing the SupportCenter STAFF table of the SupportCenter DB
database. When Jack modifies a value in the table, the new value is invisible to Bill.
Which is the modified value invisible to Bill?
A. The modified data are not available on disk.
B. The modified data have been flushed out from memory.
C.The modified rows of the SupportCenter STAFF table have been locked.
D.Jack has not committed the changes after modifying the value.
E.Both users are accessing the database from two different machines.
答案：D
解析：只有commit后才能看到变化的值
195. You work as a database administrator for Supportcenter.cn. You find that reports are running for a long time in
your data warehousing applications. You suspect a lack of indexes is the reason for the performance degradation.
Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisors
B. Segment Advisors
C.SQL Tuning and Access Advisors
D.Automated Workload Repository (AWR)
E.Automatic Database Diagnostic Monitor (ADDM)
答案：C
解析：
select account_no from accounts where old_account_no = 11
该语句调整起来并不难，但是为了更容易说明问题，假定它很难调整。激发顾问的方式有两种：使用 Enterprise Manager 或简单明了的命令行。
我们通过调用提供的包 dbms_sqltune 来调用顾问。
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'ARUP',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'FOLIO_COUNT'
);
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
196. In your database server, the parameter PLSQL_OPTIMIZE_LEVEL has been set to 2. What would this setting achieve?
A. It degrades the run time and compiler performance.
B. It provides better run time and compiler performance.
C.It provides better optimization of the statement during parse time.
D.It forces the rule based optimizer to be used for statement optimization.
E.It provides better run time performance but slightly degraded compiler performance.
F.It provides better run time performance but slightly degraded run time performance.
答案:E
解析：
2 Most aggressive, maximum possible code transformations, biggest impact on compile time. [default]
1 Smaller scale change, less impact on compile times
0 Pre-10g compilation without optimization
197. Initially, for the Automatic Workload Repository (AWR) statistics, the retention period is set to 7 days, the
collection interval is set to 30 minutes and the collection level is set to Typical in your production database. You have
been using the Memory Advisor for the last three months to generate recommendations for tuning memory
components. However, when you observe the Memory Advisor on a Friday, you find that the statistics are available
only for two days, Thursday and Friday, of that week. What would have caused the statistics to be removed?
A. On Wednesday, the statistics have been purged.
B. On Wednesday, the retention period has been set to zero.
C. On Wednesday, the collection interval has been set to zero.
D. On Wednesday, the collection level has been changed to All.
E. On Wednesday, the retention period has been set to one day.
F. On Wednesday, the retention period has been set to two days.
G. On Wednesday, the collection level has been changed to Typical.
H. On Wednesday, the collection interval has been set to 1440 minutes.
答案：F
解析：因为在周三把retention设置为了两天，所以周四和周五的快照覆盖了之前的快照
198. You work as a database administrator for Supportcenter.cn. Users in the SupportCenter PROD database
complain about the slow response when accessing the database. In the Active Session Waiting: Configuration page
you find that the majority of the wait time is for the redo log buffer. Further investigation using the Run Queue
graph and operating system tools reveals that the redo log files are on the same disks, and that the disk I/O for the
redo log files is the reason for the performance degradation.
Which two actions would you take to gain performance?
(Choose two)
A. Increase the size of redo log files
B. Increase the size of redo log buffer
C.Increase the number of redo log files
D.Place the redo log files on a separate disk
E.Decrease the number of redo log groups to two
F.Increate the number Log Writer (LGWR) process
答案：BD
解析：在数据库中，Redo的功能主要通过3个组件来实现：Redo Log Buffer、LGWR后台进程和Redo Log File（在归档模式下，Redo Log File最终会写出为归档日志文件）。
在Oracle的SGA中，存在一块共享内存，称为Redo Log Buffer
Redo Log Buffer位于SGA之中，是一块循环使用的内存区域，其中保存数据库变更的相关信息。这些信息以重做条目（Redo Entries）形式存储（Redo Entries也经常被称为Redo Records）。Redo Entries包含重构、重做数据库变更的重要信息，这些变更包括INSERT、UPDATE、DELETE、CREATE、ALTER或者DROP等。在必要的时候Redo Entries被用于数据库恢复。
Redo Entries的内容被Oracle数据库进程从用户的内存空间复制到SGA中的Redo Log Buffer之中。Redo Entries在内存中占用连续的顺序空间，由于Redo Log Buffer是循环使用的，Oracle通过一个后台进程LGWR不断地把Redo Log Buffer的内容写出到Redo Log File中。
当用户在Buffer Cache中修改数据时，Oracle并不会立即将修改数据写出到数据文件上,因为那样做效率会很低，到目前为止，计算机系统中最繁忙的部分是磁盘的I/O操作，Oracle这样做的目的是为了减少IO的次数，当修改过的数据达到一定数量之后，可以进行高效地批量写出。
大部分传统数据库（当然包括Oracle）在处理数据修改时都遵循no-force-at-commit策略。也就是说，在提交时并不强制写。那么为了保证数据在数据库发生故障时（例如断电）可以恢复，Oracle引入了Redo机制，通过连续的、顺序的日志条目的写出将随机的、分散的数据块的写出推延。这个推延使得数据的写出可以获得批量效应的性能提升。
同Redo Log Buffer类似，Redo Log File也是循环使用的，Oracle允许使用最少两个日志组。缺省情况下，数据库创建时会建立3个日志组。
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 INACTIVE
当一个日志文件写满之后，会切换到另外一个日志文件，这个切换过程称为Log Switch。Log Switch会触发一个检查点，促使DBWR进程将写满的日志文件保护的变更数据写回到数据库。在检查点完成之前，日志文件是不能够被重用的。
由于Redo机制对于数据的保护，当数据库发生故障时，Oracle就可以通过Redo重演进行数据恢复。那么一个非常重要的问题是，恢复应该从何处开始呢？
如果读取的Redo过多，那么必然导致恢复的时间过长，在生产环境中，我们必需保证恢复时间要尽量得短。Oracle通过检查点（Checkpoint）来缩减恢复时间。回顾一下第1章中所提到的内容：检查点只是一个数据库事件，它存在的根本意义在于减少恢复时间。
当检查点发生时（此时的SCN被称为Checkpoint SCN）Oracle会通知DBWR进程，把修改过的数据，也就是此Checkpoint SCN之前的脏数据（Dirty Buffer）从Buffer Cache写入磁盘，在检查点完成后CKPT进程会相应地更新控制文件和数据文件头，记录检查点信息，标识变更。
在检查点完成之后，此检查点之前修改过的数据都已经写回磁盘，重做日志文件中的相应重做记录对于崩溃/实例恢复不再有用。如果此后数据库崩溃，那么恢复只需要从最后一次完成的检查点开始恢复即可。如果数据库运行在归档模式（所有生产数据库，都建议运行在归档模式），日志文件在重用之前必须写出到归档日志文件，归档日志在介质恢复时可以用来恢复数据库故障。
199.Your boss at Supportcenter.cn wants you to clarify Oracle 10g. Which condition in your database forces you to perform only consistent backups?
A. The database is in mount state.
B. One of the tablespaces is offline.
C.One of the tablespaces is read only.
D.The database operates in ARCHIEVELOG mode.
E.The database operates in NOARCHIEVELOG mode.
答案：E
解析：在非归档模式下只能进行一致备份
200. You work as a database administrator for Supportcenter.cn. Your database is in NOARCHIVELOG mode. The
database has been configured with three redo logs groups, and there have been five log switches. You lost one non-system-critical data file from your database.
Which method would you use to recover the data file?
A. Restore all the data files and open the database
B. Create the data file and perform tablespace recovery
C.No need to restore any file, just recover the database
D.Restore only the system data file and recover the database
E.Restore only lost date file and perform tablespace recovery
答案：A
解析：在非归档模式下丢失数据文件只能整库还原，归档模式下可以利用闪回技术恢复
201. You work as a database administrator for Supportcenter.cn. Your database is opened in NOARCHIVEKLOG
mode. You decide to place the database in ARCHIVELOG mode. How would you do this? (Select two)
A. Execute the ALTER DATABASE ARCHIVELOG statement
B. Select ARCHIVELOG option in the database control and restart the database to apply the changes.
C.Shut down and start the instance, and in the NOMOUNT state execute the ALTER DATABASE ARCHIVELOG statement.
D.Shut down and start the instance, mount the database, then execute the ALTER DATABASE ARCHIVELOG statement.
答案：BD
解析：打开归档的两种办法
1.EM中勾选归档后重启
2.SQL*PLUS中启动database至mount，执行ALTER DATABASE ARCHIVELOG 语句
202. You work as a database administrator for Supportcenter.cn. You are using three database, SupportCenter DB01, SupportCenter DB02, and SupportCenter DB03, on different host machines in your development environment. The database server configuration, such as IP address and listener port number, change frequently due to development requirements, and you have the task of notifying the developers of the changes.
Which connection method would you use to overcome this overhead?
A. Host naming
B. Local naming
C.Easy Connect
D.External naming
E.directory naming
答案：E
解析：OID(Oracle Internet Directory)是一个符合LDAP(轻便目录访问协议)的目录服务，存储连接标识符。用户连接到OID，它是运行在Oracle数据库上的应用。用户提供一个Oracle服务标识符(数据库名)给OID。目录返回完整的连接信息（主机名、连接协议、端口号和数据库实例名）
203. Exhibit
You work as a database administrator for Supportcenter.cn. While creating a new user or changing the password for
existing users, the password must adhere to the restrictions specified in the exhibit.
Which option would you use to achieve this objective?
A. Use a trigger to validate the password
B. Use a user defined PL/SQL block to validate the password
C.Use a profile to include the password verify function to validate the password
D.Use Oracle's default password verification procedure to validate the password
E.Use a procedure name in the CREATE USER command to validate the password
答案：C
解析：Profile has a parameter “PASSWORD_VERIFY_FUNCTION” to check the user passwd.The verify function must be created by yourself.
204. Exhibit, HR Database properties
You work as a database administrator for Supportcenter.cn. You have two database, SALES and HR; created on the
same machine using Oracle database 10g. For the SALES database, the initialization parameter COMPTIBLE is
set to 9.2.0 and for the HR database, COMPATIBLE is set to 10.0.0. The sales_tbs tablespace is create in the SALES
database as follows:
CREATE TABLESPACE sales_tbs DATAFILE '/ SupportCenter / SupportCenter data/sales_tbs.dbf'
SIZE50M EXTENT MANAGEMENT LOCAL;
The hr_tbs tablespace created in the HR database is as follows:
CREATIVE TABLESPACEhr_tbsDATAFILE'/ SupportCenter / SupportCenter data/hr_tbs.dbf' SIZE 50M
Which two statements are correct in this scenario?
A. Hr_tbs is a bigfile tablespace.
B. A bigfile tablespace cannot be created in the SALES database
C.All tablespaces in HR database will be locally managed by default.
D.Sales_tbs and hr_tbs tablespaces can share the same data file.
E.All the tablespaces in the SALES database will be dictionary managed by default.
答案：BE
解析：
Extent management local; 使用BFT有一些限制，只有在使用了具有自动段空间管理的表空间才能使用BFT，具有自动段空间管理的本地管理表空间是Oracle 10g版本2的默认表空间。
With Oracle 10g, the value of this parameter must be set at least to 9.2; and once it was set to 10 it cannot be lowered afterwards.
The value of this parameter can be determined with dbms_utility.db_version.
The COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format on disk.
For example, if you create an Oracle Database 10g database, but specify COMPATIBLE = 9.2.0.2 in the initialization parameter file, then features that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2 compatibility level.
You can advance the compatibility level of your database. If you do advance the compatibility of your database with the COMPATIBLE initialization parameter, there is no way to start the database using a lower compatibility level setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.
The default value for the COMPATIBLE parameter is the release number of the most recent major release.
Note:For Oracle Database 10g Release 2 (10.2), the default value of the COMPATIBLE parameter is 10.2.0. The minimum value is 9.2.0. If you create an Oracle Database using the default value, you can immediately use all the new features in this release, and you can never downgrade the database.
205. You work as a database administrator for Supportcenter.cn. In your development environment, the developers
are responsible for modifying the table structure according to the application requirements. However, you want to
keep track of the ALTER TABLE commands being executed by developers, so you enable auditing to achieve this
objective. Which two views would you refer to find out the audit information? Choose two
A. DBA_AUDIT_TRAIL
B. DBA_AUDIT_SESSION
C.DBA_FGA_AUDIT_TRAIL
D.DBA_COMMON_AUDIT_TRAIL
答案：AC
解析：A是标准审计视图。C是FGA审计视图
206. You work as a database administrator for Supportcenter.cn. Users complain that their query is taking a long
time to complete. You suspect the database buffer cache size to be the reason, and you define an alert to be generated
when the buffer cache hit metric goes below 85%. When you observe the dynamic performance views, you find that
the buffer cache hit ratio is 81% but no alerts are being generated. What could be the reason for this problem?
A. The advisory memory has been turned off.
B. The DB_CACHE_ADVICE parameter has been set to OFF in the parameter file.
C.The STATISTICS_LEVEL parameter has been set to BASIC in the parameter file.
D.The TIMED_STATISTICS parameter has been set to FALSE in the parameter file.
E.The STATISTICS_LEVEL parameter has been set to TYPICAL in the parameter file.
答案：C
解析：STATISTICS_LEVEL 参数控制数据库中所有主要统计信息的收集或指导，并设置数据库的统计信息收集级别。
Basic: 不会自动收集数据库的计时系统统计信息
Typical和all：会自动收集数据库的计时系统统计信息
207. You work as a database administrator for Supportcenter.cn. You want to be notified when the number of disk
I/O increases by 10% of what it was yesterday. You create a baseline metric for yesterday's disk I/O performance.
How do you use the baseline metrics to achieve your objective?
A. Save the baseline in the database
B. Save the baseline in the Database Control Home Page
C.Create a procedure to read and apply the baseline to disk I/O metrics
D.Apply the baseline to the disk I/O metrics in the Mange Metrics page
E.Alter the disk I/O metrics to include baseline by using the ALTER METRICScommnd
答案：D
解析：这道题的意思是在哪里对该设定值进行操作 ，基线是在度量管理页
208. You work as a database administrator for Supportcenter.cn. You enabled tracing while creating a listener to
your database. What additional information is available to you when you enable tracing?
A. Information about each Oracle Net connection.
B. Information about each entry in theLinstener.orafile
C.Information about the changed done in the database
D.Information about the different listeners in your system
E.Information about all thenondatabasefiles required to operate the database
答案：A
解析：在创建监听的时候启动tracing可以查看详细的oracle网络连接信息
209. You work as a database administrator for Supportcenter.cn. Your database is in NOARCHIVELOG mode and
you want to perform a backup. Which type of backup would be appropriate in this case?
A. Hot backup
B. Online backup
C.consistent backup
D.incremental backup
E.inconsistent backup
答案：C
解析：在无归档下执行的备份为一致性备份
210. You work as a database administrator for Supportcenter.cn. Because of space constraints, you decided to
manually shrink the table. You executed the ALTER TABLE ....SHRINK SPACE statement to shrink the space and
you receive an error as follows:
ERROR at Line 1:
ORA-10635: Invalid segment or tablespace type
What could be the reason?
A. The table is partitioned
B. The table name is wrong.
C.It is an index-Organized table (IOT)
D.The table is stored in locally managed tablespace.
E.The table is stored in at ablespace where segment space is managed manually.
答案：E
解析：
ORA-10635: Invalid segment or tablespace type
Cause: Cannot shrink the segment because it is not in auto segment space managed tablespace or it is not a data, index or lob segment.
Action: Check the tablespace and segment type and reissue the statement
211.You work as a database administrator for Supportcenter.cn. You want to create your database with the automatic undo
management feature enable.Which two tasks must you perform to achieve this? Choose two.
A. Set the UNDO_RETENTION parameter to AUTO.
B. Create an initial undo segment in undotablespace.
C.Set the UNDO_RETENTION parameter to 900 or more
D.Set the initialization parameter UNDO_MANAGEMENT to AUTO E.Set the UNDO_MANAGEMENT parameter to the name of an undotablespace
答案：DE
解析：D是设置初始化参数，E是将某一个undo表空间设置为自动管理
212. You work as a database administrator for Supportcenter.cn. In your organization, you have four databases,
SupportCenter DB01, SupportCenter DB02, SupportCenter DB03, and SupportCenter DB04, which are located in
Berlin, Frankfurt,Rammstein, and Munich respectively, and which were created using Database Configuration
Assistant (DBCA). You want to perform administrative tasks on these databases that include startup and shutdown,
taking regular backups, and so on, fromFrankfurtusing Oracle tools. Which Oracle file would you enable to perform
this task?
A. Control file
B. Password file
C.Parameter file
D.Online redo log file
E.Listener controller file
F.Server parameter file
答案：B
解析：password file中有个参数是remote_login_passwordfile，
remote_login_passwordfile=none
控制此时停用口令文件验证，Oracle数据库不允许远程SYSDBA/SYSOPER身份登录
无法通过远程进行数据库起停等操作管理
213. You work as a database administrator for Supportcenter.cn. Your database is functional. One of the disks
containing a mirrored redo log member has bad sectors in it. You are unable to fix the damaged file. How does the
loss of a redo log member affect the instance of the database that is currently running?
A. The instance would normally abort.
B. The instance would not be affected.
C.The instance would be invalid, so would have to be aborted.
D.The instance would remain open, but the LGWR process would fail.
E.The instance would remain open and would automatically create the missing file
答案：B
解析：单个redo log文件丢失不会对实例产生影响
214. You work as a database administrator for Supportcenter.cn. After being hired, you find that there is only one
database that is functional and that is being access by the applications. You want to create a replicate of the database,
to be used for testing purposes. What is the best method to create a replica?
A. Create a database by using CREATE DATABASE...command and manually copy the data
B. Use Database ConfigurationAssitant(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 and then use the same template to create the database in the new location.
答案：D
解析：因为该数据库正在被使用，无法关闭。所以使用DBCA创建一个模板，并使用该模板创建数据库
215. Exhibit, error
You work as a database administrator for Supportcenter.cn. When you try to connect to the database, you get an
error (See exhibit). What could be the possible reason for getting such an error?
A. Database is not open
B. Listener is not running
C.Service name is not correct
D.Wrong port number used in URL
E.Usersysdoes have themediaprivilege
答案：C
解析：
异常如下：
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Io 异常: The Network Adapter could not establish the connection)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
Caused by: java.sql.SQLException: Io 异常: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:335)
主要原因：
1.可能是server.xml文件的DataSource配置出错。
(1).如果用到的是本机上的数据，本地数据源里的部分配置为： username="pcisv62" password="11" url="jdbc:oracle:thin:@localhost:1521:orcl"
(2).如果用到的是服务器上的数据，则数据源里的部分配置为： username="pcisv62" password="11" url="jdbc:oracle:thin:@dbserver:1521:corev6"
（dbserver为服务器的名字，corev6为服务器上Oracle的SID）
2.可能是oracle的tnsnames.ora文件配置出错。
oracle的安装目录E:\oracle\ora92\network\admin下的tnsnames.ora：
本地配置：
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主机名)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
或服务器配置：
COREV6_DBSERVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
(SID = COREV6)
(SERVER = DEDICATED)
)
)
216. You work as a database administrator for Supportcenter.cn. While granting a role,
SELECT_CATALOG_ROLE, to a user, SupportCenter, using Oracle Enterprise Manager 10g Database Control,
you have cleared the DEFAULT check box. What will be the result?
A. The user cannot use the role at all.
B. The role would not be granted to the user.
C.The user needs to enable the role explicitly.
D.The user can start using the role immediately.
E.The user would have the admin option for the role.
答案：C
解析：那个check box的意思是该用户在登陆时是否默认具有对其赋予的角色的权限，如果不勾上的话，在使用该角色的权限时，需要显式的（explicitly）使用该角色，比如：select * from *** as SELECT_CATALOG_ROLE
217. You work as a database administrator for Supportcenter.cn. You are working on Oracle Database 10g. To forward client connections to an instance, the listener must know information such as the name of the instance and
where the instance's ORACLE_HOME is located. How does the listener get this information? Choose two.
A. It gets it from the parameter file.
B. The listener prompts for details on startup.
C.The listener gathers information from the control file.
D.The instance automatically registers with the default listener.
E.The listener gets this information through thelistener.orafile.
答案：DE
解析：
1、Dynamic service registration: Oracle8i, Oracle9i, and Oracle Database 10g instances automatically register with the default listener on database startup. No additional listener configuration is required for the default listener.
2、Static service registration: The earlier releases of the Oracle database do not automatically register with the listener and, therefore, require that the listener configuration file contain a list of all database services that the listener will serve. You may still choose to use static service registration with newer releases if:
- Your listener is not on the default port of 1521, and you do not want to configure your instance to register with a nondefault port.
- Your application requires static service registration.
218.
You work as a database administrator for Supportcenter.cn. While setting up the database for your production
environment, you want to create a user according to the requirements in the exhibit. Which three options of the
CREATE USER command would you use to achieve this objective? Choose three
A. Profile
B. Account lock
C.Account unlock
D.Password expire
E.Quota 10MB on SupportCenter TBS1
F.Password never expire
G.Defaulttablespace SupportCenter TBS1
H.Quota unlimited on SupportCenter TBS1
I.TemporarytablespaceTEMP
J.TemporarytablespaceSYSTEM
答案：DEG
219. You work as a database administrator for Supportcenter.cn. You want to configure database backups to be run automatically.
Which tool or utility would you use to achieve this?
A. The DBMS_JOB package
B. The BACKUP_JOB package
C.The DBMS_BACKUP_RESTORE package
D.The calendar tool in the Operating System (OS)
E.The backup scheduler in Enterprise Manager (EM)
F.The DBMS_BACKUP_SCHEDULER packager inline mode
答案：E
解析：run automatically 自动备份，使用
backup scheduler
220. You work as a database administrator for Supportcenter.cn. You want to administer your database servers,
which are running on different host machines, using the Web-enabled grid control interface. You have configured the
agent on the host
machines. You have started the Oracle Database 10g Grid Control utility on your machine. Which two additional
components would you configure to achieve this objective? Choose two
A. Application Server Control
B. Oracle Management Service
C. Oracle Enterprise Manager Repository
D. Oracle Enterprise Manager Database Control
答案：CD
解析：Grid Control有三个组成元素：答案C/D和Agent。
221. You work as a database administrator for Supportcenter.cn. You are using Recovery Manager (RMAN) to
perform backups in your databases. Which four backup operations can be performed using RMAN? Choose four.
A. A backup of the target database when it is mounted
B. A backup of online redo log files when the database is open
C. A backup of only used data blocks in the file beingbeingbackup up
D. A backup of online, read/write data files when the database is open
E. A backup of only those data blocks that have changed since a previous backup
F. A backup of online, read/write data files when the database is in the NOMOUNT state.
答案：ACDE
解析：无归档mount状态下可以备份数据库，归档mount或open可以备份
CD是不完全备份
E是增量备份
222. Your boss at Supportcenter.cn wants you to clarify Oracle 10g. Which statement regarding the usage of redo log
files during instance recovery is true?
A. Transactions are rolled back using redo log files.
B. Transactions are rolled forward using redo log files.
C.Transactions are rolled back using redo log files and archived log files.
D.Transactions are rolled forward using redo log files and rolled back using redo log files.
答案：B
解析：实例恢复过程中redo log进行前滚
223. You execute the following command in your Supportcenter.cn production database to change the width of the
CUST_NAME column of the CUSTOMERS table:
ALTER TABLE customers
MODIFY (cust_nameVARCHAR2 (40))
/
When you execute the command, it displays the following error message:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
What could be the reason for the error message?
A. The database instance is not available.
B. The ALTER TABLE command does not have WAIT option
C.The table or a row in the table is currently locked by another user session.
D.The database instance is busy processing other user sessions commands.
E.The CUSTOMERS table has no long running query active at the time when this request is made.
F.The server process executing the ALTER TABLE command is busy with another command execution
答案：C
解析：
ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy. 所以选C
Action: Retry if necessary.
224. You work as a database administrator for Supportcenter.cn. When you tried to connect to the database, you
received the following error:
ERROR:
ORA-12541:TNS:no_listener
You found that the listener is not running on the server, so you issued the following command in the Listener Control
utility to start the listener.
lsnrctl>START
What statement is true?
A. It would attempt to start the default listener
B. itwould start the most recently created listener.
C.It would display an error with a message asking for the listener name to be specified.
D.It would show you a list of listeners and prompts for the listener name that you want to start.
答案：A
解析：启动监听
Lsnrctl>start <name>
不指定名字则启动默认监听
225. You work as a database administrator for Supportcenter.cn. One of the database users accidentally deleted all
the rows of a critical table and committed the delete at 1:30 p.m. You senior database administrator, SupportCenter,
asks you to retrieve the lost data. Which two actions would you use to ensure that there is no impact on other
connected users while retrieving the deleted rows? Choose two.
A. Use a flashback query to retrieve the rows that were deleted
B. Use convential incremental export and then import the affected object
C.Shutdown the database inNORMALmode and restart the database instance
D.Use DBMS_META package to reconstruct the object using undo segments
E.Use TablespacePoint in Time Recovery (TSPITR) method to recover the table and the data
F.Use Oracle DataPumptwithflashback_timeoption toentableflashback export and then import the affected object.
答案：BF
解析：.备份策略和综合应用举例：
(1).做全数据库增量备份和恢复的方法：
全数据库Export至dmp文件（如sidfull0701.dmp）：
$ exp sys/manager file= sidfull0701.dmp Full=y inctype=complete
第一天增量备份Export至dmp文件（如sidincr1.dmp）：
$ exp sys/manager file= sidincr1.dmp inctype=incremental
第二天增量备份Export至dmp文件（如sidincr2.dmp）：
$ exp sys/manager file= sidincr2.dmp inctype=incremental
第三天增量备份Export至dmp文件（如sidincr3.dmp）：
$ exp sys/manager file= sidincr3.dmp inctype=incremental
现假设Oracle数据库在第三天被破坏了重新建一个Oracle数据库，先把最后的一个dmp文件imp至数据库中：
$ imp sys/manager file= sidincr3.dmp full=y ignore=y inctype=system
把整个数据库备份的dmp文件imp至数据库中：
$ imp sys/manager file= sidfull0701.dmp ignore=y full=y inctype=restore
把第一天增量备份的dmp文件imp至数据库中：
$ imp sys/manager file= sidincr1.dmp ignore=y full=y inctype=restore
把第二天增量备份的dmp文件imp至数据库中：
$ imp sys/manager file= sidincr2.dmp ignore=y full=y inctype=restore
把第三天增量备份的dmp文件imp至数据库中
$ imp sys/manager file= sidincr3.dmp ignore=y full=y inctype=restore
(2).数据库逻辑备份策略的制定:
数据库管理员可以排定一个备份日程表，结合数据导出的三个不同方式合理、高效、可靠地完成。比如数据库的备份任务可作如下安排：
星期一：完全导出（A）
星期二：增量导出（B）
星期三：增量导出（C）
星期四：增量导出（D）
星期五：累计导出（E）
星期六：增量导出（F）
星期日：增量导出（G）
如果在星期日，数据库遭到意外破坏，数据库管理员可按以下步骤来恢复数据库：
第一步：用命令CREATE DATABASE重新生成数据库结构；
第二步：创建一个足够大的附加回滚段。
第三步：完全增量导入A：
$ imp system/manager inctype=RECTORE FULL=Y FILE=A
第四步：累计增量导入E：
$ imp system/manager inctype=RECTORE FULL=Y FILE =E
第五步：最近增量导入F：
$ imp system/manager inctype=RESTORE FULL=Y FILE=F
注：备份文件的命名最好加上日期，这样便于以后的恢复工作。
操作如下：
$ tar -cvf full'date +%Y-%m-%d-%H-%M'.dmp abc
$ tar -cvf full'date +%Y-%m-%d-%H-%M-%S'.dmp abc
226. You work as a database administrator for Supportcenter.cn. You need to rename a data file of a tablespace. How
would you perform this?
A. Bring the database to the NOMOUNT state, rename the data file using the operating system, and then execute the
ALTER TABLESPACE... RENAME DATAFILE... statement.
B. Take thetablespacethat contains the data file offline, rename the data file using the operating system, and then execute the
ALTER DATABASE... RENAME DATAFILE... statement.
C.Take the tablespace that contains the data file offline, rename the data file using the operating system, and then execute the
ALTER TABLESPACE... RENAME DATAFILE... statement.
D.Make thetablespacethat contains the data file read only, rename the data file using the operating system, and then execute
the ALTER TABLESPACE... RENAME DATAFILE... statement.
答案：C
解析：在联机状态下无法进行重命名
227. Exhibit
You work as a database administrator for Supportcenter.cn. While executing the command line to create a table, the
user gets the following error message and the CREATE TABLE... command fails.
ERROR at line 1:
ORA-01950: no privileges on tablespace
What could be the possible reason for this error message?
A. Thetablespace SupportCenter tbs7 is full.
B. The user is not the owner of the SYSTEMtablespace.
C.The user does have quota on the SupportCenter 7tablespace.
D.The user does not have sufficient system privileges to create table in the SupportCenter 7 tablespace.
E.The user does not have sufficient privileges to create table on the default permanent tablespace
答案：C
解析：
ORA-01950: no privileges on tablespace "string"
Cause: User does not have privileges to allocate an extent in the specified tablespace.
Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.
228. You work as a database administrator for Supportcenter.cn. You are using Recovery Manager (RMAN) for
backup and recovery of your database. You executed the delete command in RMAN to delete a backup of a
tablespace. Which statement is true in this scenario?
A. The backup status will be marked expired in RMAN recovery catalog.
B. The backup status will be marked deleted in RMAN recovery catalog after you execute the crosscheck command.
C.The backup will be deleted physically from the OS (Operating system)and the backup information is removed from the recovery catalogl
D.The backup status will be marked deleted and you must delete the backup file manually from the OS (Operating system).
E.The backup status will be marked deleted and you must delete the backup file manually from the OS (Operating system), if you are not using Oracle Managed Files (OMF).
答案：C
解析：在RMAN中使用delete语句会使物理上和逻辑上的备份都删除
229. You work as a database administrator for Supportcenter.cn. Your listener process, L1, is currently listening to
three databases: CertK INDB04, SupportCenter DB05 and SupportCenter DB06. Using Database Configuration
Assistant (DBCA) you create one more database, SupportCenter DB07, and enable dynamic registration. Which two
commands of Listener Control Utility (lsnrct1) would you use to determine whether the dynamic registration
occurred for the database, SupportCenter DB07, with the L1 listener process? Choose two
A. SHOW L1
B. SPAWN L1
C.STATUS L1
D.SERVICES L1
E.STATUS LISTENER
F.SERVICES LISTENER
G.SHOWcurrent_listener
答案：CE
230. Your boss at Supportcenter.cn wants you to clarify Oracle10g. Which statement is true about the analysis of the Automatic Database Diagnostic Monitor (ADDM)?
A. The analysis does not provide the output for the performance of SQL statements.
B. The results of analysis are stored in tables, belonging to SYS user, in the SYSAUX tablespace.
C.The results of analysis are stored in tables, belonging to SYS user, in the SYSTEM tablespace.
D.The analysis runs automatically after each Automatic Workload Repository (AWR) snapshots.
E.The ADDM analysis is done manually after each Automatic Workload Repository (AWR) snapshots.
F.The results of analysis are stored in tables, belonging to SYSMAN user, in the SYSTEM tablespace.
答案：D
解析：why answer B is wrong?because addm belong to sysman.
ADDM并不是核心的东西，所以不可能交给“总裁”去管理（SYS, SYSTEM）,所以是由“副总”来管（AUX就是辅助的意思）
