關於OCP考題學習

前一段時間考了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* oracle 3815 1 0 Jan23 ? 00:00:01 ora_rbal_danaly oracle 31601 23407 0 14:33 pts/2 00:00:00 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:

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/，如需轉載，請註明出處，否則將追究法律責任。