ocp 1Z0-042 61-120题解析

191145686

　　61. View the Exhibit.
Which statement regarding the dept and emp tables is true?
A) When you delete a row from the emp table, you would receive a constraint violation error.
B) When you delete a row from the dept table, you would receive a constraint violation error.
C) When you delete a row from the emp table, automatically the corresponding rows are deleted from the dept table.
D) When you delete a row from the dept table, automatically the corresponding rows are deleted from the emp table.
E) When you delete a row from the dept table, automatically the corresponding rows are updated with null values in the emp table.
F) When you delete a row from the emp table, automatically the corresponding rows are updated with null values in the dept table.
答案：D
外键，因为添加了delete cascade索引删除主键的同时会自动删除对应外键
62. As a result of performance analysis, you created an index on the prod_name column of the prod_det table, which contains about ten thousand rows. Later, you updated a product name in the table. How does this change affect the index?
A) A leaf will be marked as invalid.
B) An update in a leaf row takes place.
C) The index will be updated automatically at commit.
D) A leaf row in the index will be deleted and inserted.
E) The index becomes invalid when you make any updates.
答案：D
索引分三层，最下面一层是 leaf ,每个leaf block 包含很多 leaf row， 每个leaf row 是由键值和rowid组成， 查找索引时先找到键值，再根据对应的rowid找到数据块中的数据。更新索引要先删除后插入，因为这种方式最简洁和规范。不然的话，如果你采用移动的方式会变得非常麻烦和不可控制。
63. You scheduled a backup by using the Schedule Backup option in Oracle Enterprise Manager. Which statement about the script created by Oracle Enterprise Manager is true?
A) It is a SQL script.
B) It is a PL/SQL file.
C) It is an operating system (OS) file.
D) It is a Recovery Manager (RMAN) script.
答案：D
在em中使用backup scheduler采用的是RMAN脚本
64. You executed the STARTUP MOUNT command to start your database. For which database operation do you need to start the database in the MOUNT state?
A) renaming the control files
B) dropping a user in your database
C) enabling or disabling redo log archiving
D) dropping a tablespace in your database
E) re-creating the control files, after you lost all the control files in your database
答案：C
数据库启动顺序，首先是nomount--读取参数文件，mount--根据参数文件打开控制文件，open=根据控制文件打开数据文件，打开数据库.所以说在mount阶段是可以改变datafile和redo log file的名称的。在mount阶段，你可以修改数据文件的名字，同时也可以把数据改为归档或者非归档模式
65. You are creating a locally managed tablespace to meet the following requirements
All the extents should be of the same size.
The data should be spread across two data files.
A bitmap should be used to record the free space within the allocated extents.
Which three options would you choose? (Choose three.)
A) set PCTFREE and PCTUSED to 50
B) specify extent allocation as Uniform
C) specify extent allocation as Automatic
D) create the tablespace as bigfile tablespace
E) create the tablespace as smallfile tablespace
F) set segment space management to Automatic
G) use the RESIZE clause while creating the tablespace
答案：BEF
这里的要求是创建统一区的管理表空间,所以是uniform,表空间有2个文件,不可能是bigfile(如果是bigfile只能是一个文件) A bitmap should be used to record the free space within the allocated extents. 这句话我觉得说的有问题,要改成块的管理方式才能选assm
自动区段空间管理(ASSM)——ASSM的tablespace是通过将SEGMENT SPACE MANAGEMENT AUTO子句添加到tablespace的定义句法里而实现的。 通过使用位图bitmap取代传统单向的链接列表freelist，ASSM的tablespace会将freelist的管理自动化，并取消为独立的表格和索引指定PCTUSED、FREELISTS和FREELIST GROUPS存储参数的能力。
create tablespace asm_lmt_ts datafile 'c:\oracle\oradata\diogenes\asm_lmt.dbf' size 5m EXTENT MANAGEMENT LOCAL -- Turn on LMT （本地管理） SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM ;
66. You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file.
What action would you take to detect the cause of this behavior?
A) Compare the contents of the install.log file with the response file.
B)Refer to the contents of the oraInst.loc file to verify the steps performed by OUI
C)Compare the contents of the installActions.log file with that of the response file
D)Refer to the alert log file for information regarding the actions performed by OUI during installation.
答案：C
你安装数据库时OUI的行为跟响应文件不一致，这时应该比较installActions.log和response file查看原因
67.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 MB.
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.
答案：CE
在自动内存管理下shared pool如果指定了初始大小是不会减小的,即最低值不会低于设定的值
68. You suspect unauthorized data manipulation language (DML) operations on a particular table. You want to track users who are performing the transactions and the values used in the transactions. Also, you plan to transfer these values to another table for analysis.
How would you achieve this?
A) by using triggers
B) by using external tables
C) by using anonymous PL/SQL blocks
D) by auditing all DML operations on the table
答案：A
69. Which two statements are true about the Automatic Database Diagnostic Monitor (ADDM)? (Choose two.)
A) The ADDM runs after each AWR snapshot is collected.
B) The ADDM requires at least four AWR snapshots for analysis.
C) The ADDM analysis provides only the diagnostic information but does not provide recommendations.
D) The results of the ADDM analysis are stored in the Automatic Workload Repository (AWR).
E) The ADDM calls other advisors if required, but does not provide recommendations about the advisors.
答案：AD
The AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots).
Automatic Database Diagnostic Monitor (ADDM) ADDM 在创建每个AWR 快照之后自动运行。每次创建快照后，ADDM 都会分析与最后两个快照对应的时段。多数情况下，ADDM 会为检测到的问题提供建议解决方案，甚至可以量化这些建议案的优势。ADDM分析的结果存放在AWR中。
Oracle ADDM 自动诊断监视工具 介绍
http://blog.iyunv.com/tianlesoftware/archive/2010/05/29/5630942.aspx
70. 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 offline automatically; the database remains open.
D) The data files can be recovered to the time of the most recent backup.
答案：A
在归档模式下，只要归档文件存在，数据库可以恢复到任意时刻,本题意在考归档下最近可以恢复的什么时刻 critical:危机的,临界的
71. Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file?
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.
A) 5,2,3,4
B) 1,2,4,3,5
C) 2,4,3,4,5
D) 4,5,6,2,3
答案：B
用排除法：必须先关闭数据库,才能复制控制文件
72. The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a bank. As part of the year-end tax and interest calculation process, all the rows in the table need to be accessed. The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator. Which method would you suggest to the bank for achieving this task?
A) implementing value-based auditing by using triggers
B) implementing fine-grained auditing with audit condition and event handler
C) performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
D) performing standard database auditing to audit SQL statements with granularity level set to ACCESS
答案：B
(warehouse)值的审计是通过数据库trigger
dbma_fga(fine-grained auditing) 精细粒度审计 能控制到列和行
trigger不能track select，但FGA可以, fga可以將使用者的操作具體操作記錄下來.
FGA精细审计，精确到行列的审计就要使用精细审计。FGA的审计信息，存放在$FGA_LOG中
show parameter audit
AUDIT_FILE_DEST =指示出审计的文件存放的路径信息
audit_sys_operations 默认值是FALSE，如果开启审计功能，这个参数需要修改为TRUE。
audit_syslog_level 语句：指定审计语句或特定类型的语句组
权限：使用审计语句指定系统权限，象AUDIT CREATE ANY TRIGGER 对象：在指定对象上指定审计语句，象ALTER TABLE on the emp table
AUDIT_TRAIL = NONE|DB|OS DB--审计信息记录到数据库中 OS--审计信息记录到操作系统文件中 NONE--关闭审计（默认值）
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db scope=spfile;
一个有趣的效果，就是所有sysdba权限下的操作都会被记录到这个/oracle/app/oracle/admin/ora10g/adump审计目录下。这也是为什么开启了审计功能后会存在一些开销和风险。
查看审计设置可以通过查询dba_obj_audit_opts视图来完成 select OWNER,OBJECT_NAME,OBJECT_TYPE,DEL,INS,SEL,UPD from dba_obj_audit_opts;
通过查询dba_audit_trail视图或者sys.aud$视图得到详细的审计信息，这种审计方法可以得到操作的时间，操作用户等较粗的信息
FGA:可以通过FGA得到操作的SQL语句级别的信息
exec dbms_fga.add_policy(object_schema=>'SEC', object_name=> 't_audit', policy_name=> 'check_t_audit',statement_types => 'INSERT, UPDATE, DELETE, SELECT');
对t_audit表增删改查操作一番,查看审计结果
select db_user,sql_text from dba_fga_audit_trail;
对象的审计object(object privilege)
select * from dba_obj_audit_opts (注意这个地方是obj)
audit select on tt by session;(sys用户除外,by session是缺省的)
同一个session执行相同语句只审计一次
audit select on tt by access whenever successfull; (whenever successfull好像不起作用)
sql的审计statement(sql)
audit table by test;
truncate table aud$ (aud$唯一一个sys用户下的表可以修改)
audit select on tt by session whenever not successfull;
select * from dba_stmt_audit_opts (权限的审计都在里面,但表不在权限里)
dbma_fga(fine-grained auditing) 精细粒度审计 能控制到列和行
select * from dba_common_audti_trail 包含标准和精细审计(标准审计,不会捕获真实的值)
(需实验)
73. The user SCOTT executes the following command successfully to increase the salary values in one of his sessions:
SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20;
Before SCOTT ends the transaction, user HR who has the privileges on EMP table executes a query to fetch the salary details but finds the old salary values instead of the increased values.
Why does HR still see the old data?
A) because of redo data from redo log file
B) because of data from database buffer cache
C) because of data from a temporary tablespace
D) because of undo data from the undo tablespace
答案：D
在一个update执行后，如果没有Committed，那么会在undo 缓存中建立一个原数据的前镜像，如果这时候查询还会是原来的数据
74. View the Exhibit and examine the output.
Which statement describes the conclusion?
A) The users should use bind variables instead of literals in the query.
B) The dictionary cache is consuming more space than the library cache.
C) The shared pool size should be increased to accommodate the SQL statements.
D) Preparing indexes on the tables used in the SQL statements would improve the library cache performance.
答案：A
唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
http://blog.iyunv.com/tianlesoftware/archive/2009/10/17/4678335.aspx
75. In your database, the Log Writer (LGWR) process is unable to write to a member of a current redo log group due to read/write failure. Which two effects would you see in your database? (Choose two.)
A) The database instance aborts.
B) The database operation temporarily halts till the member becomes available.
C) Writing proceeds as normal. LGWR writes to the available members of a group and ignores the unavailable members.
D) The member would be marked as STALE and an error message would be written to the alert log file and LGWR trace file.
E) The status of the group changes to INACTIVE and an error message would be written to the alert log file and LGWR trace file.
答案：CD
日志组中的某成员损坏并不会影响数据库的正常使用，同时该成员将被标记为stale(陈旧)状态，错误信息会被写到alert日志里
一个数据库可以有多个日志组，组内也可以有多少日志成员，成员之间成镜像关系
增加日志成员用 alter database add logfile member '路径+名字' to group 3 这样组中的两个成员就成了镜像关系
Oracle 10g文档原文 （Your database's redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group. Each group is defined by a number, such as Group 1.）
76. Your database is not configured for session failover. Your tnsnames.ora file contains the following details:
test.us.oracle.com=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=off)
(FAILOVER=ON)
(ADDRESS= (PROTOCOL=tcp) (HOST=test1-server) (PORT=1521))
(ADDRESS= (PROTOCOL=tcp) (HOST=test2-server) (PORT=1521)))
(CONNECT_DATA= (SERVICE_NAME=test.us.oracle.com)))
Which feature is enabled in this case?
A) Load balancing
B) Instance failover
C) Database failover
D) Connect-time failover
E) Transparent Application Failover (TAF)
答案：D
Connect Time Failover,只是在发起连接时才去感知节点故障，如果发现节点没有响应,则自动尝试地址列表中的下一个地址。(在客户端tnsnames.ora中添加FAILOVER=ON)
TFA(Transparent Application Failover),就是建立连接以后，应用系统运行过程中，如果某个实例发生故障，连接到这个实例的用户会自动迁移到其他健康实例上，透明，不需用户介入。
(在客户端tnsnames.ora中添加FAILOVER_MODE
1:method选项用于定义何时创建到其他实例的连接,有basic和preconnect
2:type用于定义发生故障时对完成的sql语句如何处理，有session和select
3:delay和retries代表重试间隔时间和重试次数)
77. On which three can you use Recovery Manager (RMAN) to perform incremental backup? (Choose three.)
A) data files
B) control files
C) tablespaces
D) password file
E) parameter file
F) whole database
G) flashback log file
H) archived log files
I) change tracking file
答案：ACF
rman可以在datafile 、tablespaces和整个数据库做增量备份
78. You define an alert to be raised when the USERS tablespace usage has reached 80% of the total space. Which area would you refer to, in order to confirm that the alert has been raised due the event?
A) the alert.log file
B) the DBA_ALERTS view
C) the Database Control Home page
D) the Database Control performance page
E) the Database Control Maintenance page
答案：C
置疑
79. A user complains that he gets the following error message repeatedly after executing some SQL statements. The error message forces the user to log off from and log on to the database to continue his work.
ORA-02392: exceeded session limit on CPU usage, you are being logged off
Which action would you take to increase the session limit on CPU usage?
A) Modify the profile assigned to the user.
B) Modify the roles assigned to the users.
C) Modify the object privileges assigned to the user.
D) Modify the system privileges assigned to the users.
E) Modify the value for the RESOURCE_LIMIT parameter in the parameter file.
答案：A
一个用户执行sql语句报错ORA-02393:exceeded call limit on CPU usage这时候可以在该用户的profile 里修给recourse limits
当需要设置资源限制时，必须设置数据库系统启动参数RESOURCE_LIMIT，此参数默认值为FALSE可以使用如下命令来启动当前资源限制：alter system set RESOURCE_LIMIT=true;
create user test7 identified by test7 default profile;
口令10次锁定 password验证函数 idle time cpu空闲
建立新的profile sys: @..\rdbms\admin\utlpwdmg.sql;可以修改这段脚本
password验证函数 verify_function
80. 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
81.Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)
A) The corresponding indexes for the table are also truncated.
B) Delete triggers on the table are fired during the execution of the TRUNCATE command.
C) Very little or no undo data is generated during the execution of the TRUNCATE command.
D) The child table is truncated when the TRUNCATE command is applied on the parent table.
E) The high-water mark (HWM) is set to point to the first useable data block in the table segment.
答案：ACE
The effects of using this command are as follows:
· The table is marked as empty by setting the high-water mark (HWM) to the
beginning of the table, making its rows unavailable.
· No undo data is generated and the command commits implicitly because TRUNCATE
TABLE is a DDL command.
· Corresponding indexes are also truncated.(索引也会清空)
· A table that is being referenced by a foreign key cannot be truncated.
· The delete triggers do not fire when this command is used.
(delete trigger里面的内容不会执行,但对象仍然存在)
truncate--相应的index也会被truncate，执行truncate会产生非常少量的undo信息或者不产生，HWM（高水位标记）会恢复到第一个使用段，使用区、使用块，HWM这时应该是第二个块
82. Which is the correct description of the significance of the ORACLE_HOME environmental variable?
A) It specifies the directory containing the Oracle software.
B) It specifies the directory containing the Oracle-Managed Files.
C) It specifies the directory for database files, if not specified explicitly.
D) It specifies the base directory of Optimal Flexible Architecture (OFA) .
答案：A
ORACLE_HOME＝$ORACLE_BASE/product/version
ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。
83. Your test database is running in NOARCHIVELOG mode. What are the implications of this?
A) You can perform open database backups.
B) You can perform closed database backups.
C) You cannot perform schema-level logical backups.
D) You can perform the backup of only the SYSTEM tablespace when the database is open.
答案：B
非归档模式下，只能在关闭数据库后备份
84. View the Exhibit to observe the roles assigned to the SCOTT user.
Which statement is true about the assignment of the SELECT_CATALOG_ROLE role to the SCOTT user?
A) The user cannot use the role at all.
B) The user can grant the role to other users.
C) The user needs to enable the role explicitly.
D) The user can start using the role immediately.
答案: C explicit清楚的
Editor’s notes:
Oracle® Database SQL Reference10gRelease 1 (10.1)Part Number B10759-01SET ROLE
Specifying Default Roles
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles.
A user's list of default roles can be set and altered using the ALTER USER statement. The ALTER USER statement allows you to specify roles that are to be enabled when a user connects to the database, without requiring the user to specify the roles' passwords. The user must have already been directly granted the roles with a GRANT statement. You cannot specify as a default role any role managed by an external service including a directory service (external roles or global roles).
The following example establishes default roles for user jane:
ALTER USER jane DEFAULT ROLE payclerk, pettycash;
You cannot set a user's default roles in the CREATE USER statement. When you first create a user, the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the user's default roles.
If you create a role,but you does grant the role to user whih no default role.When the user log on,it can not ues the privilege of the role except the user enable the role after it log on;
This is my test:
1)All system privileges and schema object privileges that permit a user to perform. a DDL operation are usable when received through a role.(通过角色得到的DDL权限在DDL操作中是可用的)
2)All system privileges and object privileges that allow a user to perform. a DML operation that is required to issuse a DDL statement are not usable when received through a role.(通过角色得到的DML权限在DDL操作中不可用). And this may explain why you grant select on table to role. And then you grant role to ueer.But when you create procedure included the statement ”select count(*) from table ”, it get error.
85. You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
A) use the external table population
B) use the Oracle Data Pump export and import utility
C) use the conventional path data load of SQL*Loader utility
D) use the direct path data load of Oracle export and import utility
答案：C
SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。
你正在使用一个简单的文件作为你的应用的数据源，你打算将这些数据从文件里导入到数据库里，以此优化应用程序的性能。你已经收集好了库表。在迁移数据的时候，你想尽可能不影响数据库的性能。 你会使用哪种方法将数据导入到库里？
Because the source is "flat file" ,so should consider to use SQL*LOADER And the target table is "clustered table",so must use conventional path to load data.
86. You are working on a database created with the Oracle Database 10g software in which the initialization parameter COMPATIBLE is set to 10.0.0. The hr_tbs tablespace in the database is created as follows:
CREATE TABLESPACE hr_tbs DATAFILE '/oracle/oradata/hr_tbs.dbf' SIZE 50M;
View the Exhibit to see the properties of the database.
Which statement is correct in this scenario?
A) hr_tbs is a bigfile tablespace.
B) hr_tbs is a dictionary-managed tablespace.
C) All the tablespaces in the database will be locally managed by default.
D) All the tablespaces in the database must be of the smallfile tablespace type.
E) The tablespace type bigfile or smallfile must be mentioned in the command.
答案：C
Eg: 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错
87. View the Exhibit and analyze the CREATE TABLE statements used to create the ITEMS and ORDERS tables. The ITEMS table has 50 rows and ORDERS table has 500 rows. Because you decide to not deal with the item code 188 in future, you execute the following command to remove the item:
SQL> DELETE FROM items WHERE item_code=188;
What is the effect of this command?
A) The command fails with integrity violation error.
B) The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table, and the foreign key constraint is disabled in the ORDERS table.
C) The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table, and the dependent rows in the ORDERS table are also deleted automatically.
D) The row containing the value 188 corresponding to the ITEM_CODE column is deleted from the ITEMS table, and the dependent rows in the ORDERS table are populated with NULL values in their ITEM_CODE columns.
答案： C
因为添加了delete cascade索引删除主键的同时会自动删除对应外键
88. 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
答案：BE
ORA-01555: snapshot too old 解决这个问题，一、增加undo表空间大小，二、打开retention guarantee
Oracle ORA-01555快照过旧
http://blog.iyunv.com/tianlesoftware/archive/2009/10/31/4745898.aspx
89. You have a text file that maintains information on thousands of items. The end-user application requires the transfer of that information into a table in the database. What would you use to achieve this task? (Choose two.)
A) Oracle Text
B) Data Pump
C) SQL*Loader
D) Oracle Import
E) External table
答案：CE
90. Redo log files are not multiplexed in your database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk.
Which command would you execute to proceed further?
A) RECOVER LOGFILE BLOCK GROUP 2;
B) ALTER DATABASE DROP LOGFILE GROUP 2;
C) ALTER DATABASE CLEAR LOGFILE GROUP 2;
D) ALTER DATABASE RECOVER LOGFILE GROUP 2;
E) ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
答案：E
没有归档的日志直接clear是不能清除掉的，须加上unarchived,而且该日志组应该也不能是current和active的
91. Your database is open and you plan to perform Recovery Manager (RMAN) backups. Which three statements are true about these backups? (Choose three.)
A) The backups would be consistent.
B) The backups would be inconsistent.
C) The backups need to be restored and database has to be recovered in case of a media failure.
D) The backups need not be restored during recovery in case of a media failure.
E) The backups would be possible only if the database is running in ARCHIVELOG mode.
F) The backups would be possible only if the database is running in NOARCHIVELOG mode.
答案：BCE
RMAN-备份不是连续的-可以在数据库打开的情况下备份-恢复时需要使用restore命令，如果meida failure那么首先要restore转储备份 然后再recover命令将数据文件恢复到失败点状态
非归档模式只能在mount下备份
92. You want the user APP_DBA to administer the Oracle database from a remote machine. APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database.
Which file is used by the Oracle database server to authenticate APP_DBA?
A) control file
B) password file
C) listener controller file
D) control file and password file
答案：B
Sysdba的密码是保存在密码文件中的。
93. Your database is having two control files, three redo log file groups with two members in each group. Failure of which file would cause an instance to shut down?
A) any control file
B) any archive log file
C) one of the redo log members
D) loss of the initialization parameter file
E) any data file belonging to the default permanent tablespace
答案：A
是在从关闭的数据库打开数据库，打开数据库要做检查的，检查不到参数中所有的控制文件就关闭实例,如果运行中的数据库丢失一个控制文件也会关闭实例吗?需试验
94. The UNDO_RETENTION parameter in your database is set to 1000 and undo retention is not guaranteed.
Which statement regarding retention of undo data is correct?
A) Undo data becomes obsolete after 1,000 seconds.
B) Undo data gets refreshed after every 1,000 seconds.
C) Undo data will be stored permanently after 1,000 seconds.
D) Committed undo data would be retained for 1,000 seconds if free undo space is available.
E) Undo data will be retained in the UNDO tablespace for 1,000 seconds, then it gets moved to the TEMPORARY tablespace to provide read consistency.
答案：D
UNDO_RETENTION=1000s undo retention is not guaranteed Committed的undo数据会在undo表空间中保留1000s，只要undo 表空间有空闲undo表空间够用
ora_01555 错误：
1：查询时间太长 2：undo空间太小 3：undo_retention保留时间太短
Oracle undo 表空间管理
http://blog.iyunv.com/tianlesoftware/archive/2010/07/11/5689558.aspx
95. Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future. What action would you take to achieve this task?
A) Set the STATISTICS_LEVEL initialization parameter to ALL.
B) Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.
C) Decrease the snapshot interval in the AWR to collect more number of snapshots during the peak-load period.
D) Set the snapshot retention period in the Automatic Workload Repository (AWR) to zero to avoid the automatic purging of the snapshots.
答案：B
AWR快照集:快照集是一种机制，使用这种机制可标记重要时段的快照数据集。快照集是根据一对
快照定义的，这对快照用快照序列号 (snap_id) 来标识。每个快照集对应于一对且唯一一对快照。
快照集可用用户提供的名称标识,也可用系统生成的标识符标识。通过执行
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE 过程并指定一个名称和一对快照标识符，便可创建一个快照集。会为新建的快照集指定一个快照集标识符。在数据库生命周期内，快照集标识符是唯一的。快照集用于保留快照数据。因此，属于某个快照集的快照会一直保留，直到删除该快照集。通常，会为过去某些有代表性的时段创建快照集，用于与当前系统行为进行比较。另外，
可在 Database Control 中通过使用快照集来设置基于阈值的预警。
可直接从 DBA_HIST_SNAPSHOT 或 Enterprise Manager Database Control 获取 snap_id。
Editor’s notes:what is the baseline?
Abaseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
awr baseline在我看来就是保留下一段时间的snapshot不被删除，比如说每天peak time，然后在将来run awrrpt.sql来分析这段snapshots。(把这些快照保留下来,作为基准参考)
How to create and drop baseline?
Create baseline:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270,
end_snap_id => 280, baseline_name => 'peak baseline',
dbid => 3310949047, expiration => 30);
END;
/
Drop baseline:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/
After you create baseline,you can see the information by querying the view “dba_hist_baseline”.
96. Which statement is true when you run the SQL Tuning Advisor with limited scope?
A) Access path analysis is not performed for the SQL statements.
B) SQL structure analysis is not performed for the SQL statements.
C) SQL Profile recommendations are not generated for the SQL statements.
D) The staleness and absence of the statistics are not checked for the objects in the query supplied to the SQL Tuning Advisor.
答案：C
如果选中“Limited（有限制）”选项，SQL 优化指导会根据统计信息检查、访问路径分析和 SQL 结构分析来生成建议案。
使用“Limited（限制）”选项不会生成SQL 概要文件建议（SQL Profile recommendations）。
SQL 概要文件：ATO 会验证它自身的估计值并收集辅助信息以消除估计错误。它使用辅助信息构建SQL 概要文件并提出创建SQL 概要文件的建议。创建 SQL 概要文件后，查询优化程序可使用这个文件生成合理优化的计划。
Editor’s notes:You can see the detail explaination of sql tuning advisor inb12411- 12 Automatic SQL Tuning
SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.
If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.
If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.
dbms_sqltune.create_tuning_task (
sql_text => l_sql,user_name => 'SYS',scope => 'COMPREHENSIVE',
time_limit => 120,task_name => 'TEST');
97. You specified segment space management as automatic for a tablespace. What effect would this have on space management?
A) Extents would be managed by the freelists.
B) The segment would be managed by bitmaps.
C) The segment would be managed by the freelists.
D) Free space would be managed by the data dictionary.
答案：B
段空间是自动管理，就意味着段空间是有位图管理的本地管理的表空间：在表空间中通过位图管理区。位图中的每一位对应一个块或一组块。分配区或为重新利用空间而释放区后，Oracle 服务器通过更改位图值来显示块的新状态。
98. 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.
答案：AB
BIGFILE--BIGFILE有且只有一个数据文件(why?多个没必要还是不便于管理?)
--BIGFILE的段空间是自动管理的
99. You want to set up the Oracle Database 10g installation to follow the French language, with France as the territory, and the date to be displayed in French for all users' sessions. Which environmental variable would you set to achieve this objective?
A) NLS_LANG
B) NLS_LANGUAGE
C) NLS_TERRITORY
D) NLS_CHARACTERSET
E) NLS_DATE_LANGUAGE
F) NLS_LENGTH_SEMANTICS
G) NLS_NCHAR_CHARACTERSET
答案：A
(warehouse讲课)
客户端:nls_lang(在注册表里) 语言.版图.客户端字符集
select * from v$nls_valid_values 里面的东西都可以写到nls_lang里
select sysdate from dual;
16 -1月 -10 有中文(客户端)
修改nls_lang 为 american_CHINA.ZHS16GBK
现在是英文显示了
alter session set nls_langure=....只是改变当前session,作用域不一样
select sysdate from dual;
现在变成 16-JAN-10 (好像日期格式也改了,语言只影响月份)
语言主要影响提示
修改nls_lang 为 american_japan.ZHS16GBK
重新登陆
select sysdate from dual
现在变成 10-01-16 自己去查文档
客户端字符集影响往数据库写时的编码
试验:create table t(id int,name varchar2(10))
insert into t values (1,'中国')
select * from t;
修改nls_lang 为 american_CHINA.WE8ISO8859P1
重新登陆
select * from t;
1 靠
其实数据库里存的是正确的,只是客户端没有对应的编码
insert into t values (1,'我们')
commit;
select * from t; (现在存进去就是乱码了)
1 靠
1 框靠
又修改nls_lang 为 american_japan.ZHS16GBK
select * from t; (现在 ‘我们’ 就是乱码了)
1 中国
1 ????
控制面板 -->日期,时间,语言和区域设置 区域选简体中文 GBK 决定能不能输入汉字,决定建库的时候缺省字符集
select * from database_properator (建库的时候选的字符集)
现在3条线 操作系统 客户端 服务端 只有3个完全相同一直,才不会乱码,正确保存的数据库里
客户端字符集起中间转换作用,最后一直,不需要转换
中文字符集完全能包括日文,但不能包括韩文等,这个时候要选UTF8,一个汉字3个字节
编码原理很复杂的
create table tt(a nvarchar2(10))
insert into tt values('中国');
insert into tt values(N'中国');
select dump(a,16) from tt;
现在还都是4个字节
二医院 的数据库服务端字符集是w开头的,客户端改为w的就能显示中文,奇怪,迷惑!
客户端 NLS_LANG 的设置方法
Windows:
# 常用中文字符集set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# 常用unicode字符集 set NLS_LANG=american_america.AL32UTF8
可以通过修改注册表键值永久设置HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\NLS_LANG
Unix:
# 常用unicode字符集 export NLS_LANG=american_america.AL32UTF8
# 常用中文字符集 export NLS_LANG="Simplified Chinese_china".ZHS16GBK
可以编辑 bash_profile 文件进行永久设置
vi .bash_profile
NLS_LANG="Simplified Chinese_china".ZHS16GBK export NLS_LANG
# 使 bash_profile 设置生效 source .bash_profile
100. You want to refer the employee personal information stored in operating system(OS) files in EMPLOYEE table. You plan to add a new column to EMPLOYEE table to achieve this.
Which data type would you use for the new column?
A) BLOB
B) CLOB
C) BFILE
D) LONG RAW
答案： C
Oracle将lob分类为两种： 1.存储在数据库里的，参与数据库的事务。BLOB,CLOB,NCCLOB。 2.存储在数据库外的BFILE，不参与数据库的事务，也就是不能rollback或commit等，它依赖于文件系统的数据完整性。
BFILE 二进制文件,存储在数据库外的操作系统文件，只读的。把此文件当二进制处理。 BLOB 二进制大对象。存储在数据库里的大对象，一般是图像声音等文件。 CLOB 字符型大对象。一般存储大数量文本信息。存储单字节，固定宽度的数据。 NCLOB 字节字符大对象。存储单字节大块，多字节固定宽度，多字节变宽度数据。
The BFILE data type enables access to binary file LOBs that are stored in file systems outside Oracle Database. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server file system. The locator maintains the directory name and the filename.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements001.htm#SQLRF50996
101. View the Exhibit.
Which statement causes more undo generation?
A) SELECT * FROM emp;
B) DELETE FROM emp WHERE empno=7934;
C) UPDATE emp SET comm=400 WHERE empno=7844;
D) INSERT INTO emp VALUES (7999,'JHON','CLERK', 7782,'10-MAY-83', 1500, NULL, 10);
答案:B
一般来讲，INSERT生成的undo最少，因为Oracle为此需记录的只是要“删除”的一个rowid（行ID）。UPDATE一般排名第二（在大多数情况下）。对于UPDATE，只需记录修改的字节。你可能只更新（UPDATE）了整个数据行中很少的一部分，这种情况最常见。因此，必须在undo中记录行的一小部分。前面的许多例子都与这条经验相左，不过这是因为那些列更新的行很大（有固定大小），而且它们更新了整个行。更常见的是UPDATE一行，并修改整行中的一小部分。一般来讲，DELETE生成的undo最多。对于DELETE Oracle必须把整行的前映像记录到undo段中。
Tom> 第九章有完整实验
102. In which situation would you use the Oracle Shared Server configuration?
A) when performing export and import using Oracle Data Pump
B) when performing backup and recovery operations using Oracle Recovery Manager
C) when performing batch processing and bulk loading operation in a data warehouse environment
D) in an online transaction processing (OLTP) system where large number of client sessions are idle most of the time
答案：D
OLTP数据库，有大量的用户会话需要连接，这个时候使用共享服务。OLAP 数据仓库
103. The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table:
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A) It is terminated after receiving the error and JIM can continue with his transaction.
B) The transaction in it is terminated after receiving the error and JIM can continue with his transaction.
C) SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
D) SCOTT has to reexecute the last command in the transaction after he commits the transaction.
答案：CD
死锁是锁定冲突的特殊示例。两个或多个会话等待由对方锁定的数据时，就会发生死锁。
因为每个会话都在等待另一个会话释放锁定，所以任何一个会话都不能完成事务处理，也不能解决冲突。Oracle 数据库会自动检测死锁并终止语句，同时显示错误消息。更正这种错误的适当做法是执行提交或回退，这样做会在一个会话中释放任何其它锁定，以便其它会话可继续完成其事务处理。
104: A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.)
A) It fails if any existing row violates the constraint.
B) It does not validate the existing data in the table.
C) It enables the constraint to be enforced at the end of each transaction.
D) It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
答案：A,D
约束状态--约束一共有四种状态
a、enable validate-要求新旧数据必须同时满足约束规则-在规则正在进行中时是不容许在表上进行任何DML操作的
b、enable novalidate-已存在数据可以不满足，但是新数据必须满足
c、disable validate-不容许在表上进行任何DML操作，对主键和唯一约束来说，会删除相应的唯一索引，但约束规则仍然有效
d、disable novalidate-数据不满足约束规则，对主键和唯一约束来说，会删除相应的唯一索引
初始化立即执行--在每条语句执行结束时检验约束
初始化延迟执行,一直等到事务完成后（或者调用set constraint immediate语句时）才检验约束
SQL> create table t( x int constraint check_x check ( x > 0 ) deferrable initially immediate,
y int constraint check_y check ( y > 0 ) deferrable initially deferred ) SQL> insert into t values ( -1,1);
insert into t values ( -1,1);0ERROR at line 1: ORA-02290: check constraint (OPS$TKYTE.CHECK_X) violated
由于CHECK_X是可延迟但初始化为立即执行的约束，所以这一行立刻被拒绝了。而CHECK_Y则不同，它不仅是可延迟的，而且初始化为延迟执行，这就意味着直到我用COMMIT命令提交事务或将约束状态设置为立即执行时才检验约束。
SQL> insert into t values ( 1,-1); 现在它是成功的（总之到目前为止是成功的）。我将约束检验延迟到了执行COMMIT的时候：
SQL> commit;
0ERROR at line 1: ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.CHECK_Y) violated
此时数据库将事务回滚，因为违反约束导致了COMMIT语句的失败。这些语句说明了初始化立即执行与初始化延迟执行约束之间的区别。initially（初始化）部分指定Oracle什么时候会进行默认的约束检验--是在语句结束时［immediate（立即执行）］，还是在事务结束时［deferred（延迟执行）］。我还要说明deferred（可延迟）子句有什么用。我可以发出命令，让所有可延迟的约束变为延迟执行的。注意，你也可以对一个约束使用该命令,你不必让所有可延迟的约束都变为延迟执行的：
SQL>set constraints all deferred; 或者 SQL> set constraints all immediate;
延迟约束有哪些实际用处呢？ 有很多。它主要用于物化视图（快照）。这些视图会使用延迟约束来进行视图刷新。在刷新物化视图的过程中，可能会破坏完整性，而且将不能逐句检验约束。但到执行COMMIT时，数据完整性就没问题了，而且能满足约束。没有延迟约束，物化视图的约束可能会使刷新过程不能成功进行。使用延迟约束的另一个普遍原因是，当预测是否需要更新父/子关系中的主键时，它有助于级联更新。如果你将外键设为可延迟、但初始化为立即执行，那么你就可以将所有约束设置为可延迟。 将父键更新为一个新值--至此子关系的完整性约束不会被验证。将子外键更新为这个新值。 COMMIT--只要所有受更新影响的子记录都指向现有的父记录，这条命令就能成功执行。
105. Which statement regarding the contents of the V$PARAMETER view is true?
A) displays only the list of default values
B) displays only the list of all basic parameters
C) displays the currently in effect parameter values
D) displays only the list of all advanced parameters
E) displays the list of all the parameter files of a database
F) displays the current contents of the server parameter file
答案：C
V$PARAMETER只是显示现在起作用的参数（currently in effect parameter values）
Editor’s note:V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
So,what’s the difference between v$parameter and v$system_parameter. If you execute the sql like that ‘alter session set parameter_name value.’ . You will see the difference with them by querying these two views.
106. The HR user owns the EMP table. The HR user grants privileges to the SCOTT user by using this command:
SQL> GRANT SELECT, INSERT, UPDATE ON emp TO scott WITH GRANT OPTION;
The SCOTT user executes this command to grant privileges to the JIM user:
SQL> GRANT SELECT, INSERT, UPDATE ON hr.emp TO jim;
Now the HR user decides to revoke privileges from JIM by using this command:
SQL> REVOKE SELECT, INSERT, UPDATE ON emp FROM jim;
Which statement is true after HR issues the REVOKE command?
A) The command succeeds and privileges are revoked from JIM.
B) The command fails because SCOTT still has privileges with him.
C) The command fails because HR cannot revoke the privileges from JIM.
D) Only HR can perform SELECT, INSERT, and UPDATE operations on the EMP table.
答案:C
A、B、C三个用户，A用户把自己表的权限赋给了B、B又赋给了C，这时侯A用户是不能直接revoke C用户关于该表的权限。
授予他人的对象权限在被授予者向第三人授予该权限时，最初授权者无法取消第三人的对象权限。但是如果最初授予的是系统权限，则最初授权人可以取消第三人的系统权限。
107. Your database is running under automatic undo management and the UNDO_RETENTION parameter is set to 900 sec. You executed the following command to enable retention guarantee:
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
What effect would this statement have on the database?
A) The extents in undo tablespace retain data until the next full database backup.
B) The extents containing committed data in the undo tablespace are never overwritten.
C) The extents which no longer contain uncommitted data in the undo tablespace are not overwritten for at least 15 minutes.
D) The extents containing committed data in the undo tablespace are not overwritten until the instance is shut down.
答案：C
UNDO_RETENTION=900s 在undo表空间里的extent不包括没有committed的数据，将会被保留15分钟（为什么不说只有committed的数据，因为除此以外应该还有其它数据,我也不知道）
108. In your database, the snapshot interval is set to 10 minutes for the Automatic Workload Repository (AWR). The database instance is running for the past 30 days
View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) task.
The ADDM task is performed every 10 minutes,except the last task.What could be the reason for this?
A. The snapshot retention period for the AWR was increased before the last task. B. The optimizer statistics for few objects in the database were locked before the last task. C. The STATISTICS_LEVEL initialization parameter was changed to BASIC and reset to T
YPICAL before the last task. D. The optimizer statistics was collected manually by using the DBMS_STATS package before the last task.
答案:C
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够诊断99%的性能问题了.
alter system set statistics_level=basic;
alter system set statistics_level=typical;
alter system set statistics_level=all;
or
alter session set statistics_level=basic;
alter session set statistics_level=typical;
alter session set statistics_level=all;
statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;statistics_level=typical的时候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;如果当statistics_level=ALL,系统收集所有的统计信息.
109. Which two statements about Flashback Query are true? (Choose two.)
A) It is generated by using the redo log files.
B) It helps in row-level recovery from user errors.
C) It can be performed to recover ALTER TABLE statements
D) It fails when undo data pertaining to the transaction is overwritten.
E) The database has to be opened with the resetlogs option after performing Flashback Query.
答案：BD
Flashback Query可以帮助用户解决行级别的错误。是根据undo data来闪回的，undo retention=900s,这要看有没有retention guarantee，如果有，15分钟内不会被覆盖。如果没有打开，那得看UNTO有没有可用空间，如果没有就会被覆盖
Flashback database相当一次不完全恢复, 此时必需alter database open resetlogs; 即截断多余的scn,本质还是select file#,checkpoint_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header,两个结果不一致造成的.
10g支持穿越resetlogs了,即用当前数据库的控制文件(resetlog后的控制文件)加上resetlog之前数据文件的备份,其实只要scn连续就行
但一旦resetlogs后,无法再flashback到之前的时间点了(需实验)
110. 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 started.
D) Database buffers and redo buffers are not written to the disk.
E) The database undergoes automatic media recovery during the next startup.
答案：BD
undergo：经历，遭受
shutdown abort后--没有commited的数据不会rollback，Database buffers and redo buffers are not written to the disk.
当数据库执行了SHUTDOWN ABORT或者由于操作系统、主机等原因宕机重启后，在ALTER DATABASE OPEN的时候，就会自动做实例恢复.
实例恢复: 启动的时候 从某个点把日志文件里内容 完全写到数据文件,打开,把没有提交的rollback
111. Which three statements are correct about temporary tables? (Choose three.)
A) Indexes and views can be created on temporary tables.
B) Both the data and the structure of temporary tables can be exported.
C) Temporary tables are always created in a user's temporary tablespace.
D) The data inserted into a temporary table in a session is available to other sessions.
E) Data manipulation language (DML) locks are never acquired on the data of temporary tables.
答案：ACE
可以对临时表创建索引，视图，触发器，可以用export和import工具导入导出表的定义，但是不能导出数据。表的定义对所有的会话可见。建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.
尽管对临时表的DML操作速度比较快，但同样也是要产生 Redo Log
D,E都一个道理,不存在并发,就不存在锁,每个人看到的都是自己的东西
临时表的数据只在一个transaction或session中有效，对数据操作不需要DML锁、速度快，对临时表可以创建索引、视图、触发器。 一个用户的临时表就放在当前用户的临时表空间中，创建临时表后并不产生任何segments分配，与普通表不同。
112. A user wants to connect to the database instance from an application that is running on a remote machine. Which tools should the DBA use to establish the required configuration to ensure that the user is able to connect to the database instance? (Choose two.)
A) Data Pump
B) Oracle Net Manager
C) Oracle Enterprise Manager
D) Oracle Universal Installer (OUI)
E) Database Configuration Assistant (DBCA)
答案：BC
可以使用EM和ORACLE NET MANAGER是用户从远程连接到数据库实例(排除法)
113. 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
OFA是oracle数据库中的一个概念
Installation and configuration on all supported platforms complies with Optimal Flexible Architecture (OFA).OFA organizes database files by type and usage. Binary files, control files, xj files, and administrative files can be spread across multiple disks.
优化结构OFA。使用这种结构进行设计会大大简化物理设计中的数据管理。
优化自由结构OFA,简单地讲就是在数据库中可以高效自由地分布逻辑数据对象,
因此首先要对数据库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类,这种分类包括将系统数据和用户数据分开、一般数据和索引数据分开、低活动表和高活动表分开等等。
The Optimal Flexible Architecture standard helps you to organize database software and configure databases to allow multiple databases, of different versions, owned by different users to coexist. Optimal Flexible Architecture assists in identification of ORACLE_BASE with its Automatic Diagnostic Repository (ADR) diagnostic data to properly collect incidents.
All Oracle components on the installation media are compliant with Optimal Flexible Architecture. This means, Oracle Universal Installer places Oracle Database components in directory locations, assigning the default permissions that follow Optimal Flexible Architecture guidelines.
Oracle recommends that you use Optimal Flexible Architecture, specially if the database will grow in size, or if you plan to have multiple databases.
http://download.oracle.com/docs/cd/E11882_01/install.112/e10848/appendix_ofa.htm#sthref698
114. You specified extent management as local for a tablespace. How will it affect space management in the tablespace?
A) All the extents will be of the same size.
B) Bitmap will be used to record free and allocated extents.
C) Free extents will be managed by the data dictionary tables.
D) The tablespace will be system managed and the users cannot specify the extent size.
答案：B
extent是本地管理，位图将用来记录空闲和占用区域
Editor’s notes:
Answer A:lmt makes the extents auto allocate.the minimum value is 8K,and the largest is 64K
Answer D:when you create tablesapce,you can configure the uniform. extent by using this statement:
CREATE TABLESPACE lmtbsb DATAFILE 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\TEMP2.DBF' SIZE25M
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 128K;
The above statement use lmt and assm.So, as you see, users can specify the extent size.
extent management 有两种方式 extent management local (LMT); extent management dictionary (DMT)默认的是local
做题的时候容易记错
115. View the Exhibit to examine the output of the DBA_OUTSTANDING_ALERT view.
After 30 minutes, you executed the following command:
SQL> SELECT reason,metric_value FROM dba_outstanding_alerts;
REASON METRIC_VALUE
------------------------------------------------------- ----------------------
Tablespace [TEST] is [28 percent] full 28.125
What could be the two reasons for the elimination of the other rows in the output? (Choose two.)
A) The threshold alert conditions are cleared.
B) The threshold alerts are transferred to DBA_ALERT_HISTORY.
C) The non-threshold-based alerts are transferred to DBA_ALERT_HISTORY.
D) The threshold alerts related to database metrics are permanently stored in DBA_ALERT_HISTORY but not the threshold alerts related to instance metrics.
答案：AB
Oracle 10g通过DBA_OUTSTANDING_ALERTS视图记录了数据库活动警报信息,这些信息直到警告清除或复位才会从这个视图消失,而进入DBA_ALERT_HISTORY
搞清 ：DBA_OUTSTANDING_ALERTS ： 描述了oracle数据库认为是突出的警告。当向 Flash Recovery中添加或删除文件等变化都将记录在数据库的 alert 日志中，Oracle 10g 也针对该新特性提供了一个新的视图 ，通过该视图可以得到相关的信息。
dba_alert_history：代表了不在突出的有时间限制的历史
threshold_alert ：是超过备份阈值时引发的警报
ORACLE的一些内部组件可以周期性进行监控的动作，对发现的问题产生相应的alert信息。
alert信息的产生是基于一些threshold值或者特定事件。 基于threshold的alert信息会自DBA_OUTSTANDING_ALERTS视图中找到，当被clear的时候，这些状态的警告会进入DBA_ALERT_HISTORY
select * from dba_outstanding_alerts 警告记录地方 只记录根据度量产生的
select * from dba_alert_history (警告归档后地方,所有的)
116. Which two statements are true regarding the SGA_TARGET initialization parameter? (Choose two.)
A) It can be increased up to the value of the SGA_MAX_SIZE parameter.
B) Increasing the value of SGA_TARGET up to the value of SGA_MAX_SIZE disables the automatic shared memory management feature.
C) Reducing the value of the SGA_TARGET parameter takes away memory from both autotuned and manually sized components.
D) Increasing the value of the SGA_TARGET parameter distributes the increased memory among all the autotuned components.
答案：AD
SGA_TARGET最大能增加到SGA_MAX_SIZE规定的大小，当增大SGA_TARGET的大小，同时会自动增加其他组件的大小.
Editor’s notes:Dynamic Modification of SGA_TARGET
The SGA_TARGET parameter can be increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
When SGA_TARGET is resized, the only components affected are the automatically tuned components for which you have not set a minimum value in their corresponding initialization parameter. Any manually configured components remain unaffected.
117. You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache holds the ideal workload for analysis.
You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance?
A) Run the SQL Access Advisor.
B) Run the SQL Tuning Advisor (STA).
C) Run the Automatic Workload Repository (AWR) report.
D) Run the Automatic Database Diagnostic Monitor (ADDM).
答案：A
Intensive 精深的
SQL 优化指导（SQL Tuning Advisor）--可以使用 SQL 优化指导分析 SQL 语句，并获得性能建议案。通常，会将此指导作为ADDM 性能判断工具来运行。
SQL 访问指导（SQL Access Advisor）--可以使用 SQL 访问指导来优化方案并提高查询性能。这个指导要求用户确定SQL 工作量，即访问方案的一组 SQL 语句。可以选择不同来源的工作量，包括当前最近的 SQL 活动、SQL 资料档案库或用户定义的工作量，如开发环境的工作量。
SQL Tuning Advisor，它可以提供有关查询调整以及在流程中延长整个优化过程的建议。但请考虑以下调整案例：假设一个索引确实有助于某个查询，但该查询只执行一次。这样，即使该查询可以得益于此索引，但创建索引的成本也会超出其带来的好处。要按这种方式分析案例，您需要了解查询的访问频率和原因。
另一个顾问程序 (SQL Access Advisor) 可执行这种类型的分析。除了像在 Oracle 数据库 10g中一样可以分析索引、物化视图等，Oracle 数据库 11g中的 SQL Access Advisor 还可以分析表和查询以识别可能的分区策略 — 这在设计最佳模式时可以提供很大帮助。在 Oracle 数据库 11g中，SQL Access Advisor 现在可以提供与整个负载相关的建议，包括考虑创建成本和维护访问结构。
这些建议仅与单个语句（而非整个负载）相关。因此，只能将 SQL Tuning Advisor 偶尔用于高负载或关键业务查询。注意，与 SQL Access Advisor 相比（其标准更加宽松），该顾问程序只建议能够提供重大性能改进的索引。当然，SQL Tuning Advisor 没有分区顾问程序。
SQL Access Advisor 对于调整模式（而不仅仅是查询）很有用。作为一个最佳实践，您可以使用该策略来开发高效的 SQL 调整计划：
1搜索高成本 SQL 语句，或者（更好的是）评估整个负载。
2将可疑语句放入 SQL 调整工具集。
3使用 SQL Tuning Advisor 和 SQL Access Advisor 对其进行分析。
4得到分析结果；记录建议。
5将建议插入 SQL Performance Analyzer（参见本文）。
6在 SQL Performance Analyzer 中检查更改前后的情况，并得出最佳解决方案。
7重复上述操作，直到获得最佳模式设计。
8获得最佳模式设计之后，您可能希望使用 SQL 计划管理基准锁定该计划（如本文所述）
SQL Tuning Advisor 提出的建议只对应以下四个目标之一：
1为统计信息丢失或失效的对象收集统计信息
2考虑优化器的任何数据偏差、复杂谓词或失效的统计信息
3重新构建 SQL 以优化性能
4提出新索引建议
调整数据库结构是最费时费力的棘手任务之一，同时也是最有成效的任务之一。同样，分区是一个非常有效的调整工具，但分区的选择很难轻松决定。SQL Access Advisor 在这些过程中提供了一个非常有用的帮助。
http://blog.ixpub.net/html/32/9544432-281641.html
118. You notice this warning in the alert log file:
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
What would you do to reclaim the used space in the Flash Recovery Area? (choose two)
A) Back up the Flash Recovery Area.
B) Increase the retention time for the files.
C) Decrease the retention time for the files.
D) Manually delete all the archived log files from the Flash Recovery Area by using
operating system (OS) commands.
答案：AC
Editor’s note:you can see the deail description of this error:
Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
Action: One of the following:
1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
2. Backup files to tertiary device using RMAN.
3. Consider changing RMAN retention policy.
4. Consider changing RMAN archivelog deletion policy.
5. Delete files from recovery area using RMAN.
6. 将归档设置到其他目录，修改alter system set log_archive_dest = 其他路径 可事先用show parameter log_archive_dest查看
When you see the step 3,you will know why answer C is right.
如果是归档到闪回区的，你在os上手工删除后，rman仍然认为是available，rman还是认为那文件存在，只有执行了crosscheck才会将available转成expired，delete expired后，rman就不会觉得那空间还没占用的
crosscheck archivelog all; delete expired archivelog all;
119. View the Exhibit to observe the message received while trying to drop the SL_REP user in Oracle Enterprise Manager. The SL_REP user owns objects and is currently connected to the database instance.
What would happen if you click YES in the Exhibit?
A) An error is returned, and the user is not dropped.
B) The drop operation waits until the session started by the user ends.
C) The user is dropped successfully, and the session started by the user is killed.
D) The user is dropped, and the objects owned by the user are transferred to the recycle bin.
答案：A
数据库用户正连接数据时，这时是不能删除该用户的
120. In the PROD database you have granted the RESUMABLE system privilege to the CONNECT role. Resumable space operation has been enabled for all user sessions. You want users NOT to be aware of any kind of space-related problems while performing transactions. Instead, you want the problem to be resolved by a database trigger automatically. Which combination of triggering time and event would you use to achieve this objective?
A) AFTER CREATE
B) AFTER SUSPEND
C) BEFORE CREATE
D) AFTER TRUNCATE
E) BEFORE SUSPEND
F) AFTER INSERT OR DELETE OR UPDATE
G) BEFORE INSERT OR DELETE OR UPDATE
答案：B
Resumable 可恢复的
Oracle提供了一种 方法，当对数据库执行操作时，出现分配存储空间失败的错误时，Oracle不是简单的返回错误信息，并回滚整个事务，而是将执行的语句置于悬挂状态，等待一段时间，在等待时间内，如果问题得到解决，则语句会继续执行下去，如果问题一直无法解决，则会报错并回滚。产生SUSPEND的前提是当前的session处于ENABLE RESUMABLE状态。而且发出的语句遇到下面三种错误：空闲空间不足、达到最大的MAXEXTENTS和达到用户的空间QUOTA限制。
下列语句可能被SUSPEND：
SELECT语句（使用临时空间超过空间限制）；DML语句；IMP/EXP；DDL语句。包括CREATE TABLE AS SELECT、CREATE INDEX、ALTER INDEX和CREATE MATERIALIZED等几种语句。
ALTER SESSION ENABLE RESUMABLE和ALER SESSION DISABLE RESUMABLE分别用来启用和禁用RESUMABLE模式。在ENABLE RESUMABLE时还可以通过使用TIMEOUT语句指明SUSPEND的时间。
Oracle提供了 DBMS_RESUMABLE包和AFTER SUSPEND触发器来管理并解决SUSPEND状态。当语句进行SUSPEND状态，不会把错误信息返回给执行语句的SESSION，但是会在警告日志 alert.log中记录错误，并会触发AFTER SUSPEND触发器（如果建立了的话）。这时也可以通过查询视图USER_RESUMABLE和DBA_RESUMABLE查询到处于SUSPEND状 态的语句。
试验如下
session1： SQL> CREATE TABLESPACE lh DATAFILE 'E:\ORACLE\PRODUCT\DATA\lh01.DBF' SIZE 3M;
SQL> create or replace trigger trigg_system after suspend on database begin dbms_resumable.set_timeout(10); end;
Sql> create table t_resumable tablespace lh as select *from dba_objects;
SQL> insert into t_resumable select *from t_resumable;
ORA-01653: 表 SYS.T_RESUMABLE 无法通过 128 (在表空间 LH 中) 扩展
SQL> alter session enable resumable;
注意到过了10秒之后才出错
--修改一下timeout时间（10秒有些短，来不及修改lh01.DBF的autoextend属性），单位是秒 SQL> create or replace trigger trigg_system after suspend on database begin dbms_resumable.set_timeout(40); end;
SQL> insert into t_resumable select *from t_resumable;
这时不报错了！
在另外一个session里修改
SQL> alter database datafile 2 autoextend on;
Sql>drop trigger trigg_system;
If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.说明E错(没有试验)