ocp 1Z0-042 1-60题解析

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