oracle11g OCP 认证 1Z0-052考试笔记3

1: Examine the following statement that is used to modify the primary key constraint on theSALES table:
SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE;
Which three statements are true regarding the above command? (Choose three.)
A.The constraint remains valid.
B.The index on the constraint is dropped.
C.It allows the loading of data into the table using SQL *Loader.




约束状态--约束一共有四种状态
a、enable validate-要求新旧数据必须同时满足约束规则-在规则正在进行中时是不容许在表上进行任何 DML
操作的
b、enable novalidate-已存在数据可以不满足，但是新数据必须满足
c、disable validate-不容许在表上进行任何 DML 操作，对主键和唯一约束来说，会删除相应的唯一索引，但
约束规则仍然有效
d、disable novalidate-数据不满足约束规则，对主键和唯一约束来说，会删除相应的唯一索引
INITIALLY IMMEDIATE 初始化立即执行--在每条语句执行结束时检验约束
INITALLY DEFERRED 初始化延迟执行,一直等到事务完成后（或者调用 set constraint immediate 语句时）才检验约束


Q2.
2: Note the following points describing various utilities in Oracle Database 11g:
1. It enables the high-speed transfer of data from one database to another.
2. It provides a complete solution for the backup, restoration, and recovery needs of the entire
database.
3. It enables the loading of data from an external file into an Oracle database.
4. It provides a tape backup management for the Oracle ecosystem.
Which point describes Oracle Secure Backup?


D.4


Q3.
3: The instance abnormally terminates because of a power outage. Which statement is true about redo log files during instance recovery?


C.All redo log entries after the last checkpoint are applied from redo log files to data files.


Q4.
4: You execute the following command to change the status of the SALES tablespace:
SQL> ALTER TABLESPACE sales OFFLINE;
Which statements describe the effect of the command? (Choose all that apply.)
表空间离线


B.A checkpoint is taken on all data files that are associated with the SALES tablespace.
产生一个检查点


C.The sessions that subsequently try to access the objects in the SALES tablespace receive an error.
查询返回错信息


Q5.
5: In your database instance, the user sessions are connected to the database server from the
remote machines. You want to achieve the following for these users:
1. The user account must be locked after four unsuccessful login attempts.
2. The user must be prompted to change the password at regular intervals.
3. The user may not have more than three simultaneous sessions.
4. The user session must automatically be logged off if more than 10 minutes elapsed time
used.
How would you accomplish the above?
A.by assigning profiles for the users


Q6.
6: Your database is configured in shared server mode. However, your senior DBA asks you to modify the value of the PRIVATE_SGA limit in the profile of the users. What could be the reason for this?


数据库共享模式  参数PRIVATE_SGA


A.to limit the User Global Area (UGA) memory allocated to a session from the SGA
限制UGA


You can set the PRIVATE_SGA resource limit to restrict the memory used by each client session from the SGA. PRIVATE_SGA defines the number of bytes of memory used from the SGA by a session. However, this parameter is used rarely, because most DBAs do not limit SGA consumption on a user-by-user basis.


Q7: You set the following parameters in the parameter file and restarted the database:
MEMORY_MAX_TARGET=0
MEMORY_TARGET=500M
PGA_AGGREGATE_TARGET=90M
SGA_TARGET=270M
Which two statements are true about these parameters after the database instance is restarted? (Choose
two.)
A.The MEMORY_MAX_TARGET parameter is automatically set to 500 MB. 
B.The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.
C.The value of the MEMORY_MAX_TARGET parameter remains zero until it is changed manually. 
D.The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB
and 270 MB, respectively.
to DBA_ALERT_HISTORY.
E.The threshold alerts related to database metrics are permanently stored in DBA_ALERT_HISTORY but
not the threshold alerts related to instance metrics.
Answer: AD  
参考：http://blog.csdn.net/rlhua/article/details/12493791
只需要设置一个目标内存大小初始化参数( MEMORY_TARGET )  和一个最大内存大小初始化参数( MEMORY_MAX_TARGET)，数据库就会根据处理需求在SGA 与实例 PGA 之间动态交换内存。
自动内存管理是用两个初始化参数进行配置的：
MEMORY_TARGET：动态控制SGA和PGA时，Oracle总共可以使用的共享内存大小，这个参数是动态的，因此提供给Oracle的内存总量是可以动态增大，也可以动态减小的。它不能超过MEMORY_MAX_TARGET参数设置的大小。默认值是0。
 
MEMORY_MAX_TARGET：这个参数定义了MEMORY_TARGET最大可以达到而不用重启实例的值，如果没有设置MEMORY_MAX_TARGET值，默认等于MEMORY_TARGET的值。
使用动态内存管理时，SGA_TARGET和PGA_AGGREGATE_TARGET代表它们各自内存区域的最小设置，要让Oracle完全控制内存管理，这两个参数应该设置为0。但并不会因为设置了MEMORY_TARGET，这两个参数就会自动变回0。


Q8: Identify the two situations in which you use the alert log file in your database to check the details. (Choose two.)
使用警告文件检查细节


A.Running a query on a table returns "ORA-600: Internal Error."
内部错误
E.Inserting a row in a table returns "ORA-00060: deadlock detected while waiting for resource."
资源死锁


Q9: You want to configure and schedule offline database backups to run automatically. Which tool or utility would you use to achieve this?


调度离线自动备份，使用哪个工具
D.Enterprise Manager to schedule the backup


11:Which three statements are correct about temporary tables? (Choose three.)
临时表空间
A.Indexes and views can be created on temporary tables.
可以创建索引和视图


C.Temporary tables are always created in a user's temporary tablespace.
通常在用户临时表空间创建
E.Data manipulation language (DML) locks are never acquired on the data of temporary tables.
 临时表的数据只在一个transaction或session中有效，对数据操作不需要DML锁、速度快，对临时表可以创建索引、视图、触发器。
一个用户的临时表就放在当前用户的临时表空间中，创建临时表后并不产生任何segments分配，与普通表不同。


Q12.
SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 100M;
Which statement is true in this scenario?
A.The minimum memory size for the database buffer cache is set to 100 MB.


Q13: In your production database, data manipulation language (DML) operations are executed on
the SALES table. You have noticed some dubious values in the SALES table during the last few
days. You are able to track users, actions taken, and the time of the action for this particular period
but the changes in data are not tracked. You decide to keep track of both the old data and new data
in the table along with the user information.What action would you take to achieve this task?


B.Implement value-based auditing.
基于值得审计




14:Which two statements are true about Shared SQLArea and Private SQLArea? (Choose two.)
A.Shared SQLArea will be allocated in the shared pool.
F.The number of Private SQLArea allocations is dependent on the OPEN_CURSORS parameter.
Shared SQL Area存储着SQL的执行计划等信息，可以被多个session共享，该component存储在SGA中
Private SQL Area 包括绑定变量等信息，每个session的这类信息都不一样，为session所独有（PGA）。
且在Private SQL Area 存储一个指向shared SQL area ( SGA )中共享SQL的指针,与OPEN_CURSORS参数相关


Q15.


15: You are working on a database that must be functioning 24 hours a day, 7 days a week. The
database is configured in ARCHIVELOG mode. Which two options do you have for performing
user-managed backups? (Choose two.)


运行在归档模式下备份数据库。
B.You can perform a complete database backup without shutting down the database instance.
D.You can back up only those data files whose headers are frozen by using ALTER
TABLESPACE BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP commands


归档模式：可以进行一致性备份（冷备）和非一致性备份（热备）。
非归档模式：一致性备份（冷备）
 
一致性备份（冷备份）：在数据库正常关闭情况下做备份，数据库处于一致性状态。
（可以用于归档和非归档），所有SCN号一致。缺点：数据库需要关闭。
非一致性备份（热备份）：数据库在open状态下备份（用于归档模式），
使用alter database/tablespace xxx begin backup开始备份。
优点：数据库不需要关闭，用于7x24事务处理的数据库。
 
一般手工备份主要备份控制文件，数据文件，以及参数文件等。
首先查看控制文件，数据文件，以及参数文件的位置：


Q16.
SQL> CREATE TABLE orders(oid NUMBER(6) PRIMARY KEY, odate DATE, ccode
NUMBER (6), oamt NUMBER(10,2)) TABLESPACE users;
Which two statements are true about the effect of the above command? (Choose two.)
B.A NOT NULL constraint is created on the OID column.
列OID上创建了一个非空约束
D.The ORDERS table and a unique index are created in the USERS tablespace.
在USERS表空间中创建唯一约束


Q17:
Which two statements are true regarding Oracle Data Pump? (Choose two.)
数据泵
A.EXPDP and IMPDP are the client components of Oracle Data Pump.


B.DBMS_DATAPUMP PL/SQL packages can be used independently of the Data Pump clients.


Q18.


18: Note the functionalities of various background processes:
1. Perform recovery at instance startup.
2. Free the resources used by a user process when it fails.
3. Cleanup the database buffer cache when a process fails.
4. Dynamically register database services with listeners.
5. Monitor sessions for idle session timeout.
6. Cleanup unused temporary segments.
7. Record the checkpoint information in control file.
Which option has the correct functionalities listed for a background process?
B.SystemMonitor (SMON): 1, 6
系统监视器进程(SMON) 在实例启动时执行恢复（如果需要）。
SMON 还负责清除不再使用的临时段。如果在实例恢复过程中由于文件读取或脱机错误跳过任何已终止的事务处
理，则SMON 将在表空间或文件重新联机时恢复这些事务处理。
SMON 定期检查以查看是否需要该进程。其它进程在检测到需要SMON 时也可以调用它。
 
The system monitor process (SMON) is in charge of a variety of system-level cleanup duties. The duties assigned to SMON include:
Performing instance recovery, if necessary, at instance startup.In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online.
Cleaning up unused temporary segments. For example, Oracle Database allocates extents when creating an index. If the operation fails, then SMON cleans up the temporary space.
Coalescing contiguous free extents within dictionary-managed tablespaces.
SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.


Q19.
CODE NUMBER(6)
OAMT NUMBER(10,2)
The table has data in the ODATE column for all rows. Many orders are placed in a single day. You need to
ensure that the ODATE column must contain data for every order in future. Which method would serve
the purpose?
A.Modify the column using the ALTER TABLE...MODIFY command.
答案解析：此题考如何添加非空约束
确保ODATE 列在以后都有数据。即添加非空约束，添加非空约束使用alter table modify。
To add a Not Null constraint using the ALTER TABLE statement:
Click the icon SQL Worksheet.
The SQL Worksheet pane appears.
In the SQL Worksheet pane, type this statement:
ALTER TABLE PERFORMANCE_PARTS
MODIFY WEIGHT NOT NULL;
Click the icon Execute Statement.
The statement runs, adding the Not Null constraint to the WEIGHT column of the PERFORMANCE_PARTS table.


Q20: 
Which two statements are true about setting the FAST_START_MTTR_TARGET
initialization parameter to a nonzero value? (Choose two.)
B.Automatic checkpoint tuning will be enabled.
C.The value for the LOG_CHECKPOINT_INTERVAL initialization parameter will be override the value for FAST_START_MTTR_TARGET.


Q21: 
Examine the values for the following initialization parameters:
FAST_START_MTTR_TARGET = 0
LOG_CHECKPOINT_INTERVAL = 0
Which two will be the implications of these values in your database? (Choose two.)


B.The MTTR advisor will be disabled.


C.Automatic checkpoint tuning will be disabled.


Q22:
You have set Tablespace Full Metrics Threshold 完整的指标阈值 values for the USERS tablespace as follows:
Warning (%): 90
Critical (%): 95
Which background process is responsible for issuing alerts when the threshold is crossed?


D.ManageabilityMonitor process (MMON)


可管理性监视器进程(MMON)，可执行各种与可管理性相关的后台任务，例如：
• 每当给定的度量超过其阈值时发出预警
• 通过衍生附加进程（MMON 从属进程）获取快照
• 捕获近期已做修改的SQL 对象的统计值
 
The manageability monitor process (MMON)performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metricviolates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.


Q23.
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 ename FROM emp
2 WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database instance. 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;
独占互斥模式
E.SELECT ename FROM empWHERE job='CLERK' FOR UPDATE OF empno;


Q24.
24:Which statement is true about loading data using the conventional path 常规路径加载of SQL*Loader?
C.No exclusive locks 独占互斥锁 are acquired when the conventional path loads are performed.
常规路径加载通过执行SQL INSERT语句，将表填充到Oracle DB 中。直接路径加载通过格式化Oracle 数据块并将其直接写入数据库文件，消除了大部分Oracle DB 开销。直接加载不与其他用户争用数据库资源，因此其数据加载速度通常与磁盘速度相差无几。常规路径加载使用SQL 处理和数据库COMMIT操作来保存数据。插入记录数组后要执行COMMIT操作。每次数据加载可能涉及多个事务处理。
直接路径加载使用数据保存将数据块写入Oracle 数据文件。这就是为什么直接路径加载比常规路径加载快很多的原因。
Q25:
Which three statements regarding the server parameter file (SPFILE) are true? (Choose three.)
A.An SPFILE is a binary file.
 二进制文件
B.An SPFILE cannot reside on a client.
不能驻留于客户端
D.An SPFILE can store changes persistently across instance restarts.
通过实例重启，可以永久改变


Q26: 
Which three statements are true about logical structures of the Oracle database? (Choose three)
A.Each segment contains one or more extents.
每个段包含一个或者多个区
C.A data block is the smallest unit of input/output (I/O) in data files.
数据块是最小的IO单元
D.It is possible to have tablespaces of different block sizes in a database.
在数据库可以有不同块大小的表空间


Q27.
Automatic Shared Memory Management is disabled for your database instance. You realize
that there are cases of SQL statements performing poorly because of repeated parsing activity,
resulting in degradation of performance.What would be your next step to improve performance
D.Run the Memory Advisor for the shared pool.
共享池，重复解析SQL语句


Q28:
 You are managing an Oracle Database 11g database. You configured the database to run in ARCHIVELOG mode. Which two statements are true in this scenario? (Choose two.)
 归档模式 
 
 C.You can perform complete database backups without closing the database.
 不用关闭数据库，完成数据库备份
D.All the previous closed database backups including control files become invalid after you configure the database to ARCHIVELOG mode.
先前包括控制文件在内的备份都无效，改为归档模式后


Q29.
simultaneously   
 英 [,sɪml'teɪnɪəslɪ]   美 [saɪməl'tenɪəsli]   全球发音 跟读 口语练习
adv. 同时地
  Which two statements are true regarding listeners? (Choose two.)
  B.Multiple listener processes can run simultaneously on a host.
  多个监听程序能够同时运行在一台主机
C.Multiple database instances can be registered with a single listener.
多个数据库实例能够被注册到一个监听上


30: The HR user creates a stand-alone procedure as follows and grants the EXECUTE privilege on
the procedure to many database users:
CREATE OR REPLACE PROCEDURE create_dept ( v_deptno NUMBER, v_dname
VARCHAR2, v_mgr NUMBER, v_loc NUMBER)
BEGIN
INSERT INTO hr.departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
The users having permission to execute the procedure are able to insert records into the DEPARTMENTS table even though they do not have the INSERT privilege on the table. You want only those users who have privileges on the DEPARTMENTS table to be able to execute the procedure successfully.
What would you suggest to the PL/SQL developers to achieve this?


B.Create the procedure with invoker's right.


用户有执行procedure 的权限以至于能够把记录插入到DEPARTMENTS，即使这些用户没有插入DEPARTMENTS表的权限。
你只想只有能够插入DEPARTMENTS表的数据的那些用户执行procedure 成功。
有什么建议来实现？
 
PL/SQL还提供的数据保护功能。调用方不必知道要读取或处理的数据结构便可进行调用。
另外，调用方不必具有访问这些对象的权限，只需要具有执行PL/SQL程序的权限就足够了。
可以选择使用另一种模式的权限来调用PL/SQL，这种情况下，调用方必须有权执行调用程序运行期间执行的每个语句。


Q31: You perform differential incremental level 1 backups of your database on each working day
and level 0 backup on Sundays, to tape.
Which two statements are true about differential incremental backups? (Choose two.)


A.The backup performed on Sundays contains all the blocks that have ever been used in the database.




D.The backup performed on Monday contains all the blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup.


Q32: View the Exhibit to examine the details for an incident.
Which statement is true regarding the status of the incident?


D.The data collection for the incident is complete and the incident can be packaged and sent to Oracle Support.
Correct Answers: D


Q33:
Which two statements are true regarding a tablespace? (Choose two.)


B.It can consist of multiple data files.


D.It can contain segments of different sizes.
数据库被划分为多个“表空间”，表空间是可用于将相关逻辑结构组合在一起的逻辑存储单元。每个数据库在逻辑上都分为一个或多个表空间。对每个表空间均显式创建一个或多个数据文件，每个数据文件只属于一个表空间，表空间在物理上将所有逻辑结构的数据都存储在表空间中。
注：还可以创建大文件表空间，这种表空间只有一个文件。此文件通常非常大，其大小可达到行ID 体系结构允许的最大大小。此最大大小是表空间的块大小乘以236，即，如果块大小为32 KB，则最大大小为128 TB。传统的小文件表空间（默认值）通常包含多个数据文件，但这些文件不能很大。
从以下可知道，在users表空间中，有很多段，并且段的大小不一样。
BIN$55yRiDitVuDgQwEA 这个表占用两个区，16个数据库，bytes和其他的不一样。


Q34: View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERTS 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 perce 28.125nt]  full
What could be the reason for the elimination of the other rows in the output?
 
C.The threshold alerts 阈值预警conditions are cleared and the alerts are transferred to DBA_ALERT_HISTORY.
阈值预警条件被清空，报警信息传送到 DBA_ALERT_HISTORY.


“阀（fá）值”是一个错误的用词，其正确用法是“阈（yù）值”。阈值又叫临界值，是指一个效应能够产生的最低值或最高值。
服务器生成预警分为两种类型：阈值预警和非阈值预警。
大多数服务器生成的预警是通过对数据库度量设置警告阈值和严重阈值来配置的。可为120 多个度量定义阈值，这些度量包括：
• 每秒的物理读取次数
• 每秒的用户提交次数
• SQL 服务响应时间
除了表空间的空间占用率度量与数据库相关外，其它度量都与实例相关。阈值预警又称为“有状态预警”，清空预警条件后，会自动清空这些预警。有状态预警显示在
DBA_OUTSTANDING_ALERTS中，清空后会转到DBA_ALERT_HISTORY。
服务器生成的其它预警对应于特定的数据库事件，如ORA-*错误、“快照太旧”错误、“恢复区空闲空间过少”和“可恢复会话被挂起”等。这些都不是基于阈值的预警，又称
为“无状态预警”。无状态预警会直接转到历史记录表。只在Database Control 环境中，清空无状态预警才有意义，因为Database Control 会在自身的资料档案库中存储无状态预警。


Q35: Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)?
(Choose two.)
B.The ADDM runs after each AWR snapshot is collected automatically by MMON.
C.The results of the ADDM analysis are stored in the AutomaticWorkload Repository (AWR).


Q36: View the Exhibit to examine the output for the CROSSCHECK BACKUP command.
Which statement is true about the output of the command?
B.The backup piece is expired because the operating-system file was not found.


Q37: 
Why does performance degrade when many UPDATE, INSERT, or DELETE statements are issued on a table that has an associated Bitmap index?
DML操作性能降低
C.The smallest amount of a bitmap that can be locked is a bitmap segment.
位图的最小数量 锁定 是一个位图段。


If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.


Q38.
 The database instance has the following parameter setting:
OS_AUTHENT_PREFIX = OPS$
OS_ROLES = FALSE
REMOTE_OS_AUTHENT = FALSE
REMOTE_OS_ROLES = FALSE
TIMED_OS_STATISTICS = 0
You have a local operating system user SKD. You create a database user OPS$SKD, and then assign external authentication. The user OPS$SKD has the CREATE SESSION privilege. What would you achieve by the above process?


C.The local operating system user SKD will be able to access the database instance without  specifying the username and password.


本地操作系统用户SKD能都访问数据库实例，不需要制定用户名和密码 操作系统需要OPS$SKD


外部验证：这是使用数据库外部的方法（操作系统、Kerberos 或Radius）执行的验证。Kerberos或Radius 需要使用高级安全选件。用户可以在不指定用户名或口令的情况下连接到Oracle DB。借助于执行严格验证的高级安全选件，系统可以通过使用生物统计学、x509 证书和标记设备来识别用户。使用外部验证时，数据库依赖基础操作系统、网络验证服务或外部验证服务来限制对数据库帐户的访问。对于此类登录不会使用数据库口令。
 
如果操作系统或网络服务允许的话，可以使用外部验证来验证用户。
如果使用操作系统验证，请设置OS_AUTHENT_PREFIX初始化参数，并在Oracle 用户名中使用前缀。OS_AUTHENT_PREFIX参数定义了一个前缀，Oracle DB 会在每个用户的操作系统帐户名之前添加此前缀。为了实现与Oracle 软件早期版本的向后兼容，此参数的默认值为OPS$。
 
sys@TEST0924> show parameter OS_AUTHENT_PREFIX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
 
用户尝试建立连接时，Oracle DB 会将带前缀的用户名与数据库中的Oracle 用户名进行比较。例如，假定OS_AUTHENT_PREFIX设置如下：
OS_AUTHENT_PREFIX=OPS$ ，如果某个用户的操作系统帐户名为tsmith，该用户需要连接到Oracle DB 并且已由操作系统进行验证，则Oracle DB 会检查是否存在对应的数据库用户OPS$tsmith，如果存在这样的用户，则允许该用户建立连接。在对用户（操作系统已对其进行了验证）的所有引用中必须包含OPS$tsmith中显示的前缀。
注：在某些操作系统中，OS_AUTHENT_PREFIX初始化参数的文本是区分大小写的。


Q39.
39: You have two database servers SEMP and SACCT. The database in the SEMP server maintains the employee information and the database in the SACCT server maintains the accounts payable information. The employees submit the expense reports 开销报告to the accounts payable department 应付账款部门. A user of the accounts payable database wants to extract the employee information from the database in the SEMP server for cross-verification.教材验证 Which schema object enables the user
to access the information from the remote database?
你有两个数据库服务器SEMP和SACCT。在SEMP服务器的数据库维护员工信息，在SACCT服务器的数据库维护应付帐款信息。
员工向应付帐款部门提交开销报告，应付账款数据库用户要从SEMP服务器的数据库提取员工信息从而进行交叉验证


B.Database link
作用:将多个oracle数据库逻辑上看成一个数据库,也就是说在一个数据库中可以操作另一个数据库中的对象
SELECT * FROM USER_TABLES@dblink; 
UPDATE jobs@dblink SET min_salary = 3000 WHERE job_id = 'SH_CLERK';


Q40: 
While observing the index statistics观察索引统计信息, you find that an index is highly fragmented 有很多碎片, there by
resulting in poor database performance降低性能. Which option would you use to reduce fragmentation 减少碎片不影响当前使用索引
without affecting the users who are currently using the index?




B.Rebuild the index using the ALTER INDEX..REBUILD ONLINE command.
在线重建索引


Q41:
 Your database instance is running with full workload after database creation. You have decided
to use a fixed-size undo tablespace. You want to use the Undo Advisor to estimate the capacity of
the undo tablespace. Which two factors must you consider before using the Undo Advisor to
estimate the capacity of the undo tablespace? (Choose two.)
选项
A.the retention period to support flashback
B.the expected length of the longest-running query
评估undo容量


Q42.
42: View the Exhibit to observe the roles assigned to the SL_REP user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SL_REP user?
 
 A.The user must enable the role explicitly明确的.
 
Q43:Which statements listed below describe the data dictionary views?
1) These are stored in the SYSTEM tablespace.
2) These are the based on the virtual tables.
3) These are owned by the SYS user.
4) These can be queried by a normal


A.1 and 3




Q44:
 In a new installation of Oracle Database 11g, you perform these activities:
1. Organize software and data on different subdirectories to avoid poor performance. 区分不同的文件
2. Use consistent naming conventions for database files. 统一命名
3. Separate administrative information pertaining to the database in different directories. 区分不同目录
Which option corresponds to the type of activities you performed?


C.Optimal Flexible Architecture


Oracle的最佳灵活体系结构（Optimal Flexible Architecture，简称OFA），是指Oracle软件和数据库文件及文件夹的命名约定和存储位置规则。可以将它作为一组好的习惯，它使用户可以很容易地找到与Oracle数据库相关的文件结构。




Q45: The database instance is currently using SPFILE. View the Exhibit and examine the error that
you received while running the DB Structure Integrity check.
Given below are the steps to recover from the error in random order:
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the
new location of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database. Identify the correct sequence of steps?
A.1, 2, 4, 3, 5, 6


46: You configured the Flash Recovery Area (FRA) for your database. The database instance is running in ARCHIVELOG mode. The default location for the archived redo log files is the Flash Recovery Area. Which two files are removed automatically if the space is required in the FRA as
per the retention policy? (Choose two.) 


数据库运行在归档模式，默认归档位置为FRA， 有空间需求，哪两种文件会自动移除，


A.flashback log files  
闪回日志文件
B.backups that have become obsolete
废弃的备份文件


Q47.
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582;
System altered.
To verify the change in size, you executed this command:
SQL> SHOW PARAMETER DB_CACHE_SIZE
NAME TYPE VALUE
------------------- ----------- ------------------
db_cache_size big integer 4194304
Why is the value set to 4194304 and not to 2516582?


A.because 4194304 is the granule size  粒度大小


Q48.
SQL> ALTER SYSTEM SETAUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
Which statement is true regarding the audit record generated when auditing starts after restarting the database?
 审计数据库 外部审计
B.It contains the SQL text executed by the user and the bind variables used with it.
执行成功的SQL文本 和 绑定的变量会记录
??NONE：不开启审计功能。?
?DB：开启审计功能，并将审计记录保存在数据库系统提供的审计视图，要求用于审计功能的视图已创建。?
DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；
??OS：审计记录写入一个操作系统文件。?


Q49


SHUTDOWN ABORT  过程
1. SGA is allocated.
2. Control file is read.
3. Redo log files are read.
4. Instance recovery is started.
5. Background processes are started.
6. Data files are checked for consistency.
7. Server parameter file or the initialization parameter file is read.
Which option has the correct order in which these steps occur?
A.7, 1, 5, 2, 3, 6, 4




Q50:
 All the database users are presently connected to the database instance and working. The HR
user has opened three database sessions and executed the following command in one of his
sessions:
SQL> UPDATE persons SET ccode='U031'WHERE ccode='U029';
123 rows updated.
SQL> DELETE FROM personsWHERE exp='Y';
3 rows deleted.
The SYS user opens a new session after HR executed the above commands. Which sessions can
see the effect of the UPDATE and DELETE commands?


DML操作没有COMMIT
 只有自己能看到
 
 C.the session of the HR user that executed the commands
 执行这些命令的HR用户能看到


 
 
 Q51.
 View the Exhibit and examine the privileges granted to the SL_REP user.
The EMP table is owned by the SCOTT user. The SL_REP user executes the following command:
SQL> GRANT SELECT ON scott.emp TO hr;
Which statement describes the outcome of the command?
A.The command executes successfully.


 本题主要考的是权限问题，主要是理解GRANT ANY OBJECT PRIVILEGE的系统权限。
这个系统权限可以把任何用户下的表授权给其他用户。
SQL> CREATE USER SL_REP IDENTIFIED BY SL_REP ACOUNT UNLCOK;
SQL> GRANT connect,resource TO SL_REP;
SQL> GRANT grant any object privilege TO SL_REP;
另一个回话中
SQL> conn SL_REP/SL_REP
SQL >  GRANT SELECT ON scott.emp TO hr;
可以成功授权。


Q52.
View the Exhibit and examine the attributes of an undo tablespace.
In an OLTP system, the user SCOTT has started a query on a large table in the peak transactional hour that performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives the following error:
ORA-01555: snapshot too old  快照太旧
What could be the reason for this error


A.The query is unable to get a read-consistent image.
“快照太旧”错误是因为没有足够的还原数据来实现读取一致性。所以选A。
ORA-01555 快照过旧，是数据库中很常见的一个错误，比如当我们的事务需要使用undo来构建CR块的时候，而此时对应的undo已经不存在了，这个时候就会报ORA-01555的错误。
一般是由于，SQL语句执行时间太长，或者undo表空间过小，或者事务量过大，或者过于频繁的提交，导致执行SQL过程中进行一致性读时，SQL执行后修改的前镜像(既UNDO数据)在UNDO表空间中已经被覆盖，不能构造一致性读块(CR blocks)


A.正确。B.错误，闪回恢复区的空间跟快照过旧没有关系。C.错误，闪回归档的空间大小跟快照过旧没有关系。D.错误，query产出的事务大量在undo段中产生undo数据，覆盖了已有的undo数据，才会造成快照过旧。所以D错误。


Q53: 
The user HR receives the following error while inserting data into the TTK table:
ERROR at line 1:
ORA-01653: unable to extend table HR.TTK by 128 in tablespace 
SMD Upon investigation, you
find that SMD is a small file tablespace.
Which three action would allow the user to insert data? (Choose three.)
表空间不能扩展
A.Add a data file to the SMD tablespace.
 增加数据文件
C.Resize the data file associated with the SMD tablespace to make it larger.
重置数据文件大小
D.Alter the data file associated with the SMD tablespace to grow automatically.
更改为自动增加


Q54: In which situation may the UNDO_RETENTION parameter be ignored, even if it is set to a value?
UNDO_RETENTION 参数被忽略
C.when the undo tablespace is of a fixed size and retention guarantee is not enabled
undo表空间有固定大小，retention guarantee保留保证没设置




Q55:Which two operations can be performed on an external table? (Choose two.)
外部表
A.Create a view on the table.  
视图
C.Create a synonym on the table.
同义词


Q56: After performing a clean shut down of the database instance for maintenance, you mount the database and then execute a command to open the database:
SQL> ALTER DATABASE OPEN;
Which two statements are true? (Choose two.)
为维护 干净的关闭数据库实例 挂载数据库然后打开
A.The online redo log files and online data files are opened.
打开联机redo日志文件和在线数据文件
B.All the online data file headers are checked for consistency.
Q57.
  For which database operation would you need the database to be in the MOUNT state?
  挂载模式下更改数据库为归档模式
  E.configuring the database instance to operate in the ARCHIVELOG or NOARCHIVELOG modes


58:Which two statements are true about checkpointing? (Choose two.)  
检查点的介绍
  B.It ensures that all committed data is written to the data files during normal shutdown.
  确保所有提交的数据写入数据文件
  C.The frequent full checkpoint in a database can cause the overall degradation of the database performance.
  频繁的全部检查点能导致数据库性能下降
  
 Q59: In which of the scenarios will the DBA perform recovery? (Choose all that apply.)
 哪个场景需要DBA执行恢复
 B.A tablespace is accidentally dropped.
 表空间突然掉了
 E.The hard disk on which the data file is stored is corrupted.
 存有数据文件的硬盘坏掉了
 
 
Q60: A user, who is authenticated externally, logs in to a remote machine and connects to the database instance. What action would you take to ensure that a user cannot connect to the database instance by merely仅仅 logging in to a remote machine?


外部验证用户，登录到一台远程主机，连接到数据库实例 。那种措施确保用户不能连接到数据库实例，仅仅登录远程主机。


C.Set the REMOTE_OS_AUTHENT parameter to FALSE


限制远程数据库验证：默认情况下，REMOTE_OS_AUTHENT参数设置为FALSE。一定不要更改这个设置，除非可确保所有客户机都能适当地验证用户。在引入了安全外部口令存储（在Oracle Database 10g发行版2 中提供）后，允许远程操作系统验证就有了令人信服的理由。
在远程验证过程中：
• 数据库用户通过外部方式来进行验证
• 远程系统验证用户
• 用户登录数据库，不必接受另外的验证
注：如果撤销了一些权限，务必全面测试应用程序。
REMOTE_OS_AUTHENT FALSE 
Specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter.


61: Which two statements are true regarding the Oracle Data Pump export and import operations? (Choose two.)
数据泵导出和导入操作
B.You can rename tables during an import operation.
在导入操作中可以重命名表
C.You can overwrite existing dump files during an export operation.
在导出操作中可以重写已经存在的dump文件


Q62.


63: View the Exhibit. 
You are creating an additional database by using the Database Configuration Assistant (DBCA).
You opted to create a General Purpose database and during the database creation realize that the
Block Size field is not enabled.What could be the reason for this?


块带下不能更改


D.You are using General Purpose template and not the Custom Database template to create the  database.
你使用 了通用的模板 而不是客户的数据库模板 创建数据库


Q64.
  Your database is functional with a peak load for the last one hour. 
  数据库峰值负载最后的一个小时
  You want to preserve保存 the performance statistics collected 性能统计信息during this period 在这个时间里to be used for comparison 用来做比较when you analyze the
performance of the database in the future. 当你在将来进行数据库性能分析的时候
What action would you take to achieve this task? 
B.Create a baseline on a pair of snapshots spanning the peak-load period.
在一对峰值快照之间创建一个基线
题问：你想保留数据库最后一个小时的峰值负载，此期间的性能统计分析，以便在将来用户数据库的性能比较，采取什么措施来实现？
A,针对以后ADDM任务出入寻找指令；
B,创建一个基线，一对在峰值负载期间快照。
C,在高峰负荷期间，减少在AWR快照时间间隔，以收集更多的快照。
D,自动负载信息库（AWR）快照保留期限设置为零，以避免自动清除快照


• 自动工作量资料档案库(AWR)：是用于数据搜集、分析和提供解决方案建议的基础结构，包括数据库统计信息和度量、应用程序统计信息（事务处理量和响应时间）、操作系统统计信息及其它度量。自动工作量资料档案库(AWR) 通过为内部Oracle 服务器组件提供服务来收集、处理、维护和使用性能统计信息，以便进行问题检测和自优化。
• AWR 基线：是在一段时间内收集的一组AWR 快照。基线用于性能比较，这既可以是当前性能与基线的比较，也可以是一个基线与另一基线的比较。
从题中可知，可在峰值负载期间建立一对快照来对比性能。


Q65:Which is the correct description of a pinned buffer in the database buffer cache?
A.The buffer is currently being accessed.


Q66.


66: You have statistics collected for some selected tables. 从选择的表中收集统计信息Your requirement is that the statistics for the tables and all dependent indexes must not be overwritten by further statistics collection 统计信息不能被重写 直到一个合适的时间until a certain point of time. How would you achieve this?
A.Lock statistics for the tables.
You can maintain optimizer statistics manually using the DBMS_STATS package. For example, you can save and restore copies of statistics. You can export statistics from one database and import those statistics into another database. For example, you can export statistics from a production system to a test system. You can also lock statistics to prevent them from changing.




Q67: Which two kinds of failures make the Data Recovery Advisor 数据恢复顾问(DRA) generate a manual 手工的checklist?检查表 (Choose two.)
B.failure because a data file is renamed accidentally
数据文件意外的重命名
D.failure due to loss of connectivity-for example, an unplugged disk cable
由于丢失连接失败，未插入磁盘
在“View and Manage Failures（查看和管理故障）”页上单击“Advise（建议）”按钮后，数据恢复指导会生成一个手动核对清单。可显示两种类型的故障。
? 需要人工干预的故障：例如，未插入磁盘电缆的连接故障。
? 可通过撤消以前的错误操作快速修复的故障：例如，如果错误地重命名了数据文件，则与从备份启动RMAN 还原相比，将该文件重命名回以前的名称可以更快地进行修复。


Q68.
 View the Exhibit to examine the error that occurred during the database startup.
You opened an RMAN session for the database. To repair the failure, you executed the following
command as the first RMAN command:
RMAN>REPAIR FAILURE;
Which statement describes the consequence of this command?


D.The command produces an error because the ADVISE FAILURE command was not executed before the REPAIR FAILURE command.




Q69.
 Which two files in the database can be configured for automatic backups by using the autobackup feature in Recovery Manager (RMAN)? (Choose two.)
 A.data files
B.control files
C.parameter file
D.online redo log files
E.server parameter file
Correct Answers: B E