前一段时间考了OCP，习了OCP题库，记录部分考题分析及答案理解
2. You execute the following command to enable a session in resumable mode:
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
What is the impact of a timeout on the statements being suspended?
A. The statements remain suspended for at least 60 seconds.
B. The statements are suspended for 60 seconds and then they are executed.
C. C.The suspended statements error out if the problem is not rectified within 60 seconds.
D. The statements are automatically suspended 60 seconds after an error is received, and then attempt to execute normally again.
Answer: C
从9i开始，Oracle提供了一种避免因为space Error而导致事务异常的操作，那就是resumable在会话里打开这个功能后(运行 alter session enable resumable timeout 秒 默认7200).当运行某些sql导致空间不足(表空间用完了)、或已抵达该表/段的 maxextents，或者该用户使用此表空间的配额已经用完（Out of space condition, Maximum extents reached condition, Space quota exceeded condition.），此时Oracle将不会报错，而是把语句挂起，让 dba 在 timeout 所指定的时间范围内解决问题，之后语句又可以继续运行.
[@more@]3. Immediately after adding a new disk to or removing an existing disk from an Automatic Storage Management (ASM) instance, you find that the performance of the database decreases initially, until the addition or removal process is completed. Performance then gradually returns to normal levels.
Which two activities could you perform to maintain a consistent performance of the database while adding or removing disks? (Choose two.)
A. increase the number of checkpoint processes
B. define the POWER option while adding or removing the disks
C. increase the number of DBWR processes by setting up a higher value for DB_WRITER_PROCESSES
D. increase the number of slave database writer processes by setting up a higher value for DBWR_IO_SLAVES
E. increase the number of ASM Rebalance processes by setting up a higher value for
ASM_POWER_LIMIT during the disk addition or removal process
Answer: BE
Note:
ASM ( Automatic Storage Management ) 的一个重要特性是可以进行在线磁盘重配置及动态均衡。当我们向已有磁盘组中增加磁盘后，Oracle会自动进行IO均衡,为了完成这个任务，Oracle引入了一个新的后台进程：
[oracle@danaly ~]$ ps -ef|grep ora_rbal* |
这个进程用来进行IO均衡。IO调整是自动进行的，并且这个平衡速度是可以控制的。
Oracle通过一个新增的参数:asm_power_limit 来控制速度。该参数取值范围为1~11,参数值越大平衡速度越快。
Oracle文档对该参数解释为:
ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.
If the POWER clause of a rebalance operation is not specified, then the default power will be the value of ASM_POWER_LIMIT
5. Manually, you set the consumer group of all of the newly created users to MYDB_GRP. You want the users to be able to change their consumer groups as per the application requirement. What was the first step that was needed in the process to achieve this objective?
A.The user must have been granted the DBA role.
B.The user must have been granted the switch privilege as a part of a role.
C.The user must have been granted the Resource Manager administrator privilege.
D.The user must have been granted the switch privilege by using the
DBMS_RESOURCE_MANAGER_PRIVS package.
Answer: D
Note:
资源管理概述:
资源管理器有三个部件组成：
资源用户组（Resource consumer group ）
资源规划（Resource plan ）
资源分配方法（Resource allocation method）
资源计划目录(Resource plan directives)
它们的功能如下：
资源用户组: 根据数据库资源处理需求，将用户会话分成组资源规划: 指定哪些资源分配给资源用户的命令资源分配方法: 数据库资源管理器分配特殊资源时采用的方法，由资源用户组和资源规划来使用。
资源规划命令: 管理员使用这些命令将资源用户组与特殊规划连接起来，并在资源用户组之间分配资源。
数据库资源管理器可以完成：
.确保某些用户处理少量的资源，不考虑对系统的加载和用户的数量。
.按比例将CPU时间分配给不同的用户和程序，分配有效的处理资源。
.限制一组用户可以使用的并行度。
.对实例进行配置，使其能使用特殊的资源分配方法。例如，DBA不用关闭数据库实例就可以动态地改变这些配置方法。
授予用户“切换权限”:
BEGIN
//撤消用户
dbms_resource_manager_privs.revoke_switch_consumer_group('JOSEN', 'SDL');
//添加用户并授于切换特权选项
dbms_resource_manager_privs.grant_switch_consumer_group('JOSEN', 'SDL', true);
END;
6. You have set the value of the NLS_TIMESTAMP_TZ_FORMAT parameter in the parameter file to YYYYMMDD.The default format of which two data types would be affected by this setting? (Choose two.)
A.DATE
B.TIMESTAMP
C.INTERVAL YEAR TO MONTH
D.INTERVAL DAY TO SECOND
E.TIMESTAMP WITH LOCAL TIME ZONE
Answer: BE
Note:
(ORACLE DOCUMENT)
NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZ functions.
10. You are using Oracle Database 10g. You performed an incomplete recovery of your database and opened the database with the RESETLOGS option.
What is the effect of opening the database with the RESETLOGS option? (Choose two.)
A. This operation resets the SCN for the database.
B. This operation creates a new incarnation of the database.
C. This operation moves all the redo log files to a different location.
D. This operation deletes the old redo log files and creates new redo log files.
E. This operation updates all current d atafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.
Answer: BE
NOTE:
SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 1129345 (在 09/09/2010 11:42:15 生成) 对于线程 1 是必需的
ORA-00289: 建议: G:ORACLEARCHIVELOGARC00037_0718036321.001
ORA-00280: 更改 1129345 (用于线程 1) 在序列 #37 中
指定日志: {=suggested | filename | AUTO | CANCEL}
E:ORACLEORADATALIXIANGREDO03.LOG
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;
数据库已更改。
SQL> select RESETLOGS_CHANGE#,RESETLOGS_TIME from v$datafile_header;
RESETLOGS_CHANGE# RESETLOGS_TIME
----------------- --------------
1129347 09-9月 -10
1129347 09-9月 -10
1129347 09-9月 -10
1129347 09-9月 -10
11. View the Exhibit.
You have more than one table in the recycle bin having the same original name, DEPT2. You do not have any table with the name DEPT2 in your schema. You executed the following command:
PURGE TABLE dept2;
Which statement is correct in this scenario?
A. All the tables having the same original name as DEPT2 will be purged from the recycle bin.
B. The table with dropscn = 1928151 (oldest dropscn) will be purged from the recycle bin.
C. The table with dropscn = 1937123 (most recent dropscn) will be purged from the recycle bin.
D. None of the tables will be purged because there are multiple entries with the same original name in the recycle bin.
Answer: B
NOTE:
SQL> conn lixiang/lixiang
SQL> create table test (id number);
SQL> drop table test;
SQL> create table test (id number);
SQL> drop table test;
SQL> create table test (id number);
SQL> drop table test;
表已删除。
SQL> select OBJECT_NAME,DROPTIME,DROPSCN from dba_recyclebin;
OBJECT_NAME DROPTIME DROPSCN
------------------------------ ------------------- ----------
BIN$T6trE4loSuOImca0KmmeZg==$0 2010-09-09:11:50:47 1129870
BIN$F6FVyCInQjm4MggT9H4nQA==$0 2010-09-09:11:50:52 1129899
BIN$21nrIXZ0SCO5aJ9WNhMDvg==$0 2010-09-09:11:50:56 1129924
SQL> PURGE TABLE TEST;
表已清除。
SQL> select OBJECT_NAME,DROPTIME,DROPSCN from dba_recyclebin;
OBJECT_NAME DROPTIME DROPSCN
------------------------------ ------------------- ----------
BIN$F6FVyCInQjm4MggT9H4nQA==$0 2010-09-09:11:50:52 1129899
BIN$21nrIXZ0SCO5aJ9WNhMDvg==$0 2010-09-09:11:50:56 1129924
12. On Monday, you dropped the DEPT table from your schema and then you recreated
the DEPT table in your schema. On Wednesday, you have a requirement to restore the DEPT table from the recycle bin.
Which statement is correct?
A. You can restore the DEPT table by using the Oracle Flashback Drop feature, provided you use the RENAME TO clause.
B. You can restore the DEPT table by using the Oracle Flashback Drop feature and a system-generated name will be assigned to the restored table.
C. You cannot restore the DEPT table by using the Oracle Flashback Drop feature because a table with the name DEPT already exists in your schema.
D. You cannot restore the DEPT table by using the Oracle Flashback Drop feature because the contents of the recycle bin are purged every 12 hours by default.
Answer: A
Note:
SQL> drop table test;
表已删除。
SQL> create table test (id number);
表已创建。
SQL> flashback table test to before drop;
flashback table test to before drop
*
第 1 行出现错误:
ORA-38312: 原始名称已被现有对象使用
SQL> flashback table test to before drop rename to t1
闪回完成。
13. Which two statements are correct regarding the Oracle Flashback Drop feature? (Choose two.)
A. Recycle bin exists for the tables only in non SYSTEM, locally managed tablespaces.
B.You can flash back a dropped table provided row movement has been enabled on the table.
C. If you drop an index before dropping its associated table, then the recovery of the index is not supported when you flash back the dropped table.
D.When you execute the DROP TABLESPACE INCLUDING CONTENTS command, the objects in the tablespace are placed in the recycle bin.
E.When a dropped table is moved to the recycle bin, only the table is renamed to a systemgenerated name; its associated objects and constraints are not renamed.
F.If you drop a table that is protected by the recycle bin, then associated bitmapjoined
indexes and materialized view logs are also stored in the recycle bin.
Answer: AC
Note:
限制：
.Recycle bin功能只能用于non-system,本地管理的表空间
.对于recycle bin存储，没有时间保证，由系统活动对空间利用的影响决定
.DML和DDL语句不能用于recycle bin对象
.Recycle bin里的名字来查询，不能用原先的名字
.当执行flashback drop后，所有依赖的对象都将被恢复，除了因为space pressure被清除的对象
.出于安全原因，在表上定义的virtual private database 和fine-grained auditing 方针.不受保护
.分区的索引表不会受到recycle bin保护
14. View the Exhibit and examine the Flashback SCNs.
A user has inserted wrong department data in the DEPT3 table in the USERS tablespace. You use the Flashback Table functionality to rectify the erroneous inserts. While performing the recovery, you chose 2004343 as the Flashback SCN.
Which two statements are correct in this scenario? (Choose two.)
A. Only the row with DEPARTMENT_ID 290 would be flashed back.
B. The rows with DEPARTMENT_ID 290 and 300 would be flashed back.
C. The rows with DEPARTMENT_ID 290 and 280 would be flashed back.
D. You would have taken the USERS tablespace offline before starting the Flashback Table operation.
E. You would have enabled row movement for the DEPT3 table before starting the Flashback Table operation.
Answer: BE
Note:
The prerequisites for performing a FLASHBACK TABLE operation are as follows:
.You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
.You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
.Undo information retained in the undo tablespace must go far enough back in time
to satisfy the specified target point in time or SCN for the FLASHBACK TABLE
operation.
.Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:
ALTER TABLE table ENABLE ROW MOVEMENT;
SQL> flashback table test to timestamp('2010-09-09 14:26:49');
flashback table test to timestamp('2010-09-09 14:26:49')
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表
SQL> alter table test enable row movement;
表已更改。
SQL> flashback table test to timestamp to_timestamp('2010-09-09 14:26:49','YYYY-MM-DD HH24:MI:SS');
闪回完成。
16. You executed the following FLASHBACK TABLE command:
FLASHBACK TABLE emp TO TIMESTAMP ('11:45','hh12:mi');
Which two statements are correct? (Choose two.)
A. The FLASHBACK TABLE statement will not be written to the alert log file.
B. The changes made to the EMP table since the specified time will be undone.
C. The EMP table that was dropped by mistake from the database will be restored.
D. The FLASHBACK TABLE statement will be executed within a single transaction.
E. The FLASHBACK TABLE statement will not maintain the existing indexes on the EMP table.
F. The list of transactions that have modified the EMP table since the specified time will be displayed.
Answer: BD
Note:
整个闪回表操作作为单个事务执行。
Flashback table 命令支持同时操作多个表，表名中间以逗号分隔即可，如果执行一条flashback table命令时同时指定了多个表，要记住单个flashback table 是在同一个事务中，因此这些表的恢复操作要么都成功，要么都失败
17. Why would you use the following FLASHBACK TABLE command?
FLASHBACK TABLE emp TO TIMESTAMP ('11:45','hh12:mi');
A. to undo the changes made to the EMP table since the specified time
B. to restore the EMP table that was wrongly dropped from the database
C. to view the transactions that have modified the EMP table since the specified time
D. to view the changes made to the EMP table for one or more rows since the specified time
E. to recover the EMP table to a point in time in the past by restoring the most recent backup.
Answer: A
Note:
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-09-09 14:26:49
SQL> select * from test;
未选定行
SQL> insert into test values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> flashback table test to timestamp to_timestamp('2010-09-09 14:26:49','YYYY-MM-DD HH24:MI:SS');
闪回完成。
SQL> select * from test;
未选定行
19. You are working in an online transaction processing (OLTP) environment. You realize that the salary for an employee, John, has been accidentally modified in the EMPLOYEES table. Two days ago, the data was in the correct state. Flashback logs generated during last two days are available in the flash recovery area.
Which option would you choose to bring the data to the correct state while ensuring that no other data in the same table is affected?
A. perform point-in-time recovery
B. perform a Flashback Table operation to restore the table to the state it was in two days ago
C. perform a Flashback Database operation to restore the database to the state it was in two days ago
D. perform Flashback Versions Query and Flashback Transaction Query to determine all the necessary undo SQL statements, and then use them for recovery
Answer: D
Note:
SQL> insert into test values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> update test set id=2 where id =1;
已更新 1 行。
SQL> commit;
提交完成。
SQL>select versions_starttime,
versions_endtime, versions_xid,
versions_operation,id
from test versions
between timestamp minvalue and maxvalue
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID
------------------------ -------------------------- --------------- - ----
09-9月 -10 02.58.48 下午 5000E00AF010000 U 3
09-9月 -10 02.58.24 下午 9-9月 -10 02.58.48 下午 8002D0098010000 I 2
20. View the Exhibits.
You performed operations on the DEPT4 table as shown in the Exhibit. When you perform the Flashback Versions Query, you find that the first two updates are not listed. What could be the reason?
A.The row movement is not enabled on the table.
B.The first two updates were not explicitly committed.
C.The Flashback Versions Query lists only the most recent update.
D.The Flashback Versions Query stops producing rows after it encounters a time in the past when the table structure was changed.
Answer: D
Note:
SQL>select versions_starttime,
versions_endtime, versions_xid,
versions_operation,id
from test versions
between timestamp minvalue and maxvalue
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID
------------------------ -------------------------- --------------- - ----
09-9月 -10 02.58.48 下午 5000E00AF010000 U 3
09-9月 -10 02.58.24 下午 9-9月 -10 02.58.48 下午 8002D0098010000 I 2
SQL> alter table test add (name varchar2(500));
表已更改。
SQL>select versions_starttime,
versions_endtime, versions_xid,
versions_operation,id
from test versions
between timestamp minvalue and maxvalue
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID
------------------------ -------------------------- --------------- - ----
25. There was media failure and you need to check the data files for any block corruption. Which option would you use to create a report on any corruptions found within the database?
A.the DBNEWID utility
B.the DBVERIFY utility
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7417660/viewspace-1040525/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7417660/viewspace-1040525/
1142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
