Upgrade the hardware/memory to accommodate
B) Load the data into your database by using the PARALLEL
C) Give analysts DBA privilege, so that they can query
DBA_EXTERNAL_TABLES.
D) Use an external table so you can have the metadata
available in your database, but leave the data in the operating system files.
解析：外部表注意事项：
数据在数据库的外部组织，是操作系统文件。　
操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。　　　　
数据是只读的。（外部表相当于一个只读的虚表）　&
不可以在上面运行任何DML操作，不可以创建索引。　　
可以查询操作和连接，可以并行操作。
52. A user receives the following error while
executing a query:
ORA-01555: snapshot too old
Which two options can be the solutions to
avoid such errors in future? (Choose two.)
A) increase the size of redo log files
B) increase the size of the undo tablespace
C) increase the size of the Database Buffer Cache
D) increase the size of the default temporary tablespace
E) enable the retention guarantee for the undo tablespace
解析：If in
Automatic Undo Management mode, increase undo_retention setting（E）. Otherwise, use
larger rollback segments（B）
53. You executed the following command to
export the EMPLOYEES table from a remote Machine:
$ EXPDP hr/hr@data.
DUMPFILE=my_direxp_hr.log
LOGFILE=data_pump_dirlog_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.
Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象
LOGFILE=[directory_object:]file_name
Directory_object用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY的相应选项值.
NETWORK_LINK
指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项.
54. 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.
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.
Table created.
Further, you executed the following command
to drop the table:
SQL& DROP TABLE
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.
解析：打开回收站功能，当你drop一个表后，该表会被改名同时还放在原来的表空间里
56. Which two statements are true about a bitmap index? (Choose two.)
A) It is recommended for the columns that have unique
B) It can be converted to a B-tree index by using the
ALTER INDEX command.
C) It has a bitmap segment for each distinct value in the
key column, containing a string of bits in which each bit represents the
presence or absence of a key column value.
D) Updating the key column locks the whole bitmap segment
that contains the bit for the key value to be updated.
解析：在关键列上，每一个不同值都有位图段，更新关键列，会锁住位图段，包含主要要被更新的段
1：bitmap 索引是分段存储的，也就是说很多条记录可能是分做了N段来存储，也就是有N个begin/end
,当新的记录 insert 而使用以前未曾使用过的物理地址的时候，会产生一个bitmap 段来存储，就算只有一条记录
2： 当删除一条记录的时候,在bitmap 索引上做了一个delete 的标记并用一新的记录来标记了
3: 当 dml发生的时候，会lock住某个值的存储bit的那一rowid所在的记录，参考下面的 row 中 lock ,这样显然会影响并发
57. Your database is configured with the
following parameters related to SGA)
SGA_TARGET=256MB
SHARED_POOL_SIZE=32MB
DB_CACHE_SIZE=100MB
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
STREAMS_POOL_SIZE=0
Which two statements are true about the
configuration? (Choose two.)
A) The SGA_TARGET value cannot be sized smaller than 100
B) The shared pool and the default buffer pool will not
be sized bigger than 32 MB and 100 MB, respectively.
C) The shared pool and the default buffer pool will not
be sized smaller than 32 MB and 100 MB, respectively.
D) 124 MB (256 minus 132) of memory is available for use
by all the manually sized components.
E) 124 MB (256 minus 132) of memory is available for use
by all the manually and automatically sized components.
解析：在自动内存管理下shared pool如果指定了初始大小是不会减小的,即最低值不会低于设定的值
58. The application workload on your database
is same between 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
D) by using the Automatic Workload Repository Compare
Period report
E) by analyzing the output of the
V$ACTIVE_SESSION_HISTORY view
解析：题目要求的是从配置设置，工作量概要文件和状态来诊断可能的性能问题，所以选择D，AWR报告
1.自动工作量资料档案库 (AWR) 是 SYS 拥有的永久性系统性能统计信息的集合。
2.AWR&驻留在
SYSAUX 表空间中。
3.快照是在特定时间捕获并存储在 AWR 中的一组性能统计信息。快照用于计算统计信息的更改率
4.每个快照都用一个快照序列号 (snap_id) 进行标识，该序列号在 AWR 中是唯一的。
5.默认情况下，快照每 60 分钟生成一次。通过更改快照INTERVAL 参数可以调整此频率。
59. 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
解析：注意是solve this problem
60. Which three pieces of information are to
be mandatorily provided while creating a new listener using
Enterprise Manager Database Control? (Choose
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
E) the database services to be registered with the
解析：使用EM创建监听时必须提供端口，协议，监听对应的服务器名
61. What are the consequences of executing
the SHUTDOWN ABORT command? (Choose two.)
A) The database files are synchronized.
B) Uncommitted changes are not rolled back.
C) The database is closed, but the instance is still
D) Database buffers and redo buffers are not written to
E) The database undergoes automatic media recovery during
the next startup
解析：没有commited的数据不会rollback，Database buffers and redo buffers
are not written to the disk.
关于E选项：
当数据库执行了SHUTDOWN ABORT或者由于操作系统、主机等原因宕机重启后，在ALTER DATABASE OPEN的时候，就会自动做实例恢复.
启动的时候 从某个点把日志文件里内容 完全写到数据文件,打开,把没有提交的rollback
62. User SCOTT wants to
export his objects using Oracle Data Pump and executes the following command:
$ expdp scott/tiger
directory = EXPORT_DIR
dumpfile = scott.dmp
include = table
include = view: "like
'%DEPARTMENTS%'"
content = DATA_ONLY
Which task would the command accomplish?
A) Oracle Data Pump would export only the data of all of
the tables and views.
B) Oracle Data Pump would export all of the table
structures along with data and all the views.
C) Oracle Data Pump would export the table data and the
view definitions where the view name contains a string named DEPARTMENTS.
D) Oracle Data Pump would export the table data and the
view definitions with data where view name contains a string named DEPARTMENTS.
E) Oracle Data Pump would export all of the table
structures and the view definitions with data where view name contains a string
named DEPARTMENTS.
导出表中的数据以及包含DEPARTMENTS的视图定义
63. View the Exhibit, and examine the alert
You added space to the TEST tablespace to
bring the space usage below the threshold value.
Which statement is true about the Tablespace
Full alert?&
解析：在EM上的表空间空间不够用的告警信息会在你扩大表空间后，自动被清除，同时移动到alter&history&
select&*&from&dba_outstanding_alerts&警告记录地方,只记录根据度量产生的&
select&*&from&dba_alert_history&(警告归档后的地方,记录所有的)
解析：v$controlfile&lists&the&names&of&the&control&files.You&can&confirm&whether&the&control&files&have&broken&by&v$controlfile.status.
66. Which statement is true about the loss of
system-critical data files in ARCHIVELOG mode?
A) The data files can be recovered till the last
committed transaction.
B) The data files can be recovered without shutting down
the database.
C) The data file is taken o the
database remains open.
D) The data files can be recovered to the time of the
most recent backup.
解析：在归档模式下，只要归档文件存在，数据库可以恢复到任意时刻，最近可恢复到最后次commit
67. Which two statements about bigfile
tablespaces are true? (Choose two.)
A) The bigfile tablespace have only one data file.
B) The segment space management is automatic.
C) The extent management is dictionary managed.
D) The database can have only one bigfile tablespace.
E) The bigfile tablespace can be converted to a smallfile
tablespace.
解析： BIGFILE有且只有一个数据文件，BIGFILE的段空间是自动管理的
68. 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.
alter table wdz2 modify (memo varchar(22))
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
69. 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 script 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 control file.
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
This statement is used to create
binary file.it&s a backup controlfile for
current controlfile.
70. You created a response file and want to
check it before starting installations in silent mode. You started&installation of Oracle software in
interactive mode by providing the response file. In the middle of the&installation, you realize that the behavior
of the Oracle Universal Installer (OUI) is not&consistent with the&response file.
What action would you take to
detect the cause of this behavior?
A) Compare the contents of the install.log file with the
response file.
B) Refer to the contents of the oraInst.loc file to
verify the steps performed by OUI.
C) Compare the contents of the installActions.log file
with that of the response file.
D) Refer to the alert log file for information regarding
the actions performed by OUI during installation.
解析：安装数据库时OUI的行为跟响应文件不一致，这时应该比较installActions.log和response file查看原因
解析：因为添加了delete&cascade索引删除主键的同时会自动删除对应外键
解析：已连接的会话，当停止监听时不会中断
解析：初始化参数REMOTE_OS_AUTHENT用来控制是否允许远端操作系统验证，默认情况下，数据库只运行本地服务器上的操作系统验证
解析：SQL&&select&*&from&database_properties&Where&property_name='DEFAULT_TBS_TYPE';&
PROPERTY_NAME&&&&&&&&&PROPERTY_VALUE&&&&DESCRIPTION&
--------------------&---------------&------------------------&
DEFAULT_TBS_TYPE&&&&&&SMALLFILE&Default&tablespace&type&
SQL&CREATE&BIGFILE&TABLESPACE&bigtbs&
DATAFILE&&/u01/oradata/big_tbs_data_01.dbf&&SIZE&1024&M;&
SQL&&ALTER&DATABASE&SET&DEFAULT&bigfile&TABLESPACE;&
SQL&&CREATE&TABLESPACE&bigtbs&DATAFILE&'/us01/oradata/bigtbs_02.dbf'&size&1M;&
SQL&&select&TABLESPACE_NAME,&BIGFILE&from&DBA_TABLESPACES;&
TABLESPACE_NAME&&&&&&&&&&&&&&&&BIGFILE&
------------------------------&---------&
USERS&SMALLFILE&&&&&&&&&&&&&&&&BIGTBS&BIGFILE&
并不一定需要在创建的时候指明类型,使用缺省指定的类型来创建,E错，也可以在创建的时候指定创建不同类型的表空间,D错
解析：备份分为完全备份和增量备份
完全备份：创建所备份的文件中包含数据的所有数据块的副本
增量备份：创建一个包含自以前某次备份以来更改过的所有数据块的副本（分为级别0和级别1两种）
级别0：等同于完全备份
累积备份：备份自上次级别&0&备份以来的所有更改
差异备份：备份自上次增量备份以来的所有更改（可以采用级别&0&或级别1）
解析：从第一张图看到同样的查询走了一个全表扫描。所以我们有理由相信，索引无效了导致执行计划有问题了。第二张图应该就是dba重建了该索引后，同样的sql走了索引。an&unusable&state表示索引的状态。在user_indexes的status字段中体现。
解析：所有的控制文件正常才可以打开数据库
解析：在FLASHBACK&DATABASE的时候,是可以恢复删除的非系统用户的&B是flash&drop&table
logging&clause&
这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。&
FORCE&LOGGING&
使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。&
注意：设置这个参数在临时表空间和回滚表空间中不能使用这个选项。
D选项是说在表空间级别的logging选项没有在表、视图等的级别高
解析：Automatic&Database&Diagnostic&Monitor&(ADDM)&ADDM&在创建每个AWR&快照之后自动运行。每次创建快照后，ADDM&都会分析与最后两个快照对应的时段。多数情况下，ADDM&会为检测到的问题提供建议解决方案，甚至可以量化这些建议案的优势。ADDM分析的结果存放在AWR中。
BFILE&二进制文件,存储在数据库外的操作系统文件，只读的。把此文件当二进制处理。&
BLOB&二进制大对象。存储在数据库里的大对象，一般是图像声音等文件。&
CLOB&字符型大对象。一般存储大数量文本信息。存储单字节，固定宽度的数据。&NCLOB&字节字符大对象。存储单字节大块，多字节固定宽度，多字节变宽度数据。
解析：license_max_users&限制被授权使用oracle的人数&到这个数后不能建用户license_max_sessions达到会话连接的最大数&，到了后只有&拥有restricted&session&权限用户能连接license_max_warning&就是警告&查v$license&~~~~~可以&&设为0&就是无限
解析：STATISTICS_LEVEL&参数控制数据库中所有主要统计信息的收集或指导，并设置数据库的统计信息收集级别。
Basic:&不会自动收集数据库的计时系统统计信息
Typical和all：会自动收集数据库的计时系统统计信息
你正在使用一个简单的文件作为你的应用的数据源，你打算将这些数据从文件里导入到数据库里，以此优化应用程序的性能。你已经收集好了库表。在迁移数据的时候，你想尽可能不影响数据库的性能。你会使用哪种方法将数据导入到库里？
SQL*LOADER的基本特点：&
1）能装入不同数据类型文件及多个数据文件的数据（）
2）可装入固定格式，自由定界以及可度长格式的数据
3）可以装入二进制，压缩十进制数据
4）一次可对多个表装入数据
5）连接多个物理记录装到一个记录中
6）对一单记录分解再装入到表中
7）可以用数对制定列生成唯一的KEY&
8）可对磁盘或磁带数据文件装入制表中
9）提供装入错误报告
10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
解析：外部表注意事项：
数据在数据库的外部组织，是操作系统文件。　操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。　　　　数据是只读的。（外部表相当于一个只读的虚表）　&不可以在上面运行任何DML操作，不可以创建索引。　　可以查询操作和连接，可以并行操作。
解析:&可以综合多个字段建立联合主键，所以答案D正确。主键可以理解为唯一索引+非空约束，答案E正确。
关于约束的延迟（deferrable）的含义，有两种，一种是defered（延迟检查，即Update或Insert语句执行后不立即进行约束检查，而是在发出Commit命令时采取检测是否符合约束。）另一种是Immediate（立即检查，即Update或Insert语句执行后立即进行约束检查，发现不满足约束，立即报错）
A&&以下语句可以无效化主键，所以选项A错误。&&&&ALTER&TABLE&policies&DISABLE&PRIMARY&KEY;B&&一个表最多只能有一个主键。C&&主键与参照外键的关系为1：N
解析：Enhance&提高&degrade降低
fast_start_mttr_target，定义了数据块崩溃后所需要的实例恢复的时间
设置此参数的时候要综合考虑系统的IO，容量以及CPU等信息，要在系统性能和故障恢复时间之间做好平衡（成反比）
将此参数设置成0时将禁用&fast-start&checkpointing，这样能减小系统负载但同时会增加系统的恢复时间。如果fast_start_io_target&or&log_checkpoint_interval被指定，他们会自动覆盖由fast_start_mttr_target参数计算出来的值。
解析：open&recovery是指单个数据文件损坏的回复，至少得表空间可以offline或者datafile可以offline的才可能在db处于open模式下回复.&
解析：SYSAUX表空间是可以被OFFLINE，不能read&only,但是system、undo和temp不行,why?&
SQL&&alter&tablespace&sysaux&&
表空间已更改。&
SQL&&alter&tablespace&temp&&
第&1&行出现错误:&
ORA-03217:&变更&TEMPORARY&TABLESPACE&无效的选项&
SQL&&alter&tablespace&system&&
第&1&行出现错误:&
ORA-01541:&系统表空间无法脱机;&如有必要请关闭
解析：HR用户根据scott用户的emp表建立了一个视图，他想把该视图的select权限赋予JIM用户，他需要有emp表的with&GRANT&OPTION才能这么做&
With&admin&option&&系统权限
With&grant&&option&&对象权限
解析：当使用该命令ALTER&DATABASE&BACKUP&CONTROLFILE&TO&TRACE后可以在USER_DUMP_DEST中找到该trace文件(指由用户自己跟踪的文件位置)
解析：An&alert&file&in&the&directory&specified&by&BACKGROUND_DUMP_DEST&logs&significant&database&events&and&messages.&
解析：不小心删除alert文件没有关系，数据库会自己自动重建
解析：LOG_BUFFER是个静态参数，只能scope=spfile，修改完后重启数据库才能生效
解析：SQL&&show&parameter&archive&
log_archive_dest_10&可以看到最大10个目的地
在使用LOG_ARCHIVE_DEST_n&参数时，可以将目标指定为强制(mandatory)&或可选(optional)&&MANDATORY&表示必须成功完成归档到该目标的操作才可以覆盖联机重做日志文件。&&OPTIONAL&表示即使联机重做日志文件尚未成功地归档到该目标，也可以重新使用。这是缺省设置。
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.
解析：rman可以在datafile&、tablespaces和整个数据库做增量备份
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.
正常grant了resource之后，unlimited&tablespace系统权限就被grant了，如果没有unlimited&tablespace权限，就需要通过tbs&quota来控制
解析：在一个update执行后，如果没有Committed，那么会在undo&缓存中建立一个原数据的前镜像，如果这时候查询还会是原来的数据
段空间是自动管理，就意味着段空间是有位图管理的本地管理的表空间：在表空间中通过位图管理区。位图中的每一位对应一个块或一组块。分配区或为重新利用空间而释放区后，Oracle&服务器通过更改位图值来显示块的新状态。
Flashback&Query可以帮助用户解决行级别的错误。是根据undo&data来闪回的，undo&retention=900s,这要看有没有retention&guarantee，如果有，15分钟内不会被覆盖。如果没有打开，那得看UNTO有没有可用空间，如果没有就会被覆盖&
Flashback&database相当一次不完全恢复,&此时必需alter&database&open&&即截断多余的scn,本质还是select&file#,checkpoint_change#&from&v$&
select&file#,checkpoint_change#&from&v$datafile_header,两个结果不一致造成的.&
10g支持穿越resetlogs了,即用当前数据库的控制文件(resetlog后的控制文件)加上resetlog之前数据文件的备份,其实只要scn连续就行&
但一旦resetlogs后,无法再flashback到之前的时间点了