1z0-043 lesson 11 Managing Storage

1z0-043 lesson 11

大部分题和前一章重复了, 再做一遍

是否显示我的答案
是否显示书上的答案和我的注释


1.
31. You have specified the warning and critical threshold values of an application tablespace to be 60% and 70% respectively. From the tablespace space usage metrics, you find that the actual space usage has reached the specified warning threshold value, but no alerts have been generated. What could be the reason for this?
A. The EVENT parameter was not set.
B. The SQL_TRACE parameter is set to FALSE.
C. The Enterprise Manager Grid Control is not used.
D. The STATISTICS_LEVEL parameter is set to BASIC.
E. The TIMED_STATISTICS parameter is set to FALSE.

D

D

见STATISTICS_LEVEL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref640)

2.
49. 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. 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.

C

C

教材11-21
"A resumable statement that is suspended for the time-out interval (the default is 7,200 seconds (2 hours)) reactivates itself and returns the exception to the user."

3.
58. Exhibit: Using Database Control, you have scheduled a job to shrink the TRANS table residing on the TT tablespace. The job would run at 5:00 p.m. every Friday. When you examine the space usage of the table after the completion of the job, you find that the table has not been shrunk. What could have been the reason for this?

A. The tablespace that contains the TRANS table is online.
B. The tablespace that contains the TRANS table is permanent.
C. The tablespace that contains the TRANS table is locally managed.
D. The segment space management of the tablespace that contains the TRANS table is manual in nature.

D

D

教材11-14
"You cannot execute a shrink operation on segments managed by free lists. Segments in automatic segment-space managed tablespaces can be shrunk."

4.
59. The warning and critical threshold values have been set to 85% and 97%, respectively, for one of the tablespaces. The current tablespace space usage is 54%. You modify the warning threshold to be 50 % and critical threshold to be 53% in Database Control. Which statement is true?
A. The new setting would be applied but no alerts would be raised immediately.
B. The new setting would be applied and an alert would be raised immediately.
C. The new setting would be ignored because the tablespace space usage is more than the specified threshold value.
D. The new setting would cause an error because the tablespace space usage is more than the specified threshold value.

A

A

教材11-11
"This information is aggregated every 10 minutes by the MMON process. An alert is triggered when the threshold for a tablespace has been reached or cleared. "

5.
74. While setting up the threshold for tablespace space usage metrics for one of your tablespaces, you define the critical threshold as 60% and warning threshold as 75%. What would be the result of this setting?
A. It would result in an error because the warning threshold cannot be more than the critical threshold.
B. The warning threshold is ignored and alerts would be generated when space usage exceeds the critical threshold.
C. The critical threshold is ignored and alerts would be generated when space usage exceeds the warning threshold.
D. Both threshold values are used and alerts are generated when space usage exceeds the respective threshold values.

A

A

没找到

6.
84. Exhibit: View the Exhibit and examine the characteristic of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation?

A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

B

B

教材11-19

7.
106. Which type of PL/SQL construct would you use to automatically correct the error resulting from a statement that was suspended due to a space-related problem?
A. functions
B. package
C. procedure
D. database trigger
E. anonymous PL/SQL block

D

D

教材11-25

8.
107. In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)

C

C

教材11-9
"Information gathered is stored in the Automatic Workload Repository (AWR) and is used to perform growth trend analysis and capacity planning of the database."

9.
116. In one of your online transaction processing (OLTP) applications, users are manipulating and querying a database table simultaneously. From the Segment Advisor, you find that one of the tables is highly fragmented and you want to shrink the table immediately without affecting the currently active queries. Which option would you use with the ALTER TABLE command to achieve this objective?
A. REBUILD
B. CASCADE
C. TRUNCATE
D. ROW MOVEMENT
E. SHRINK SPACE COMPACT
F. SHRINK SPACE CASCADE

E

E

教材11-12
"The first phase does the compaction. During this phase, rows are moved to the left part of the segment as much as possible. Internally, rows are moved by packets to avoid locking issues."

10.
118. Users are performing a large volume of inserts and deletes on the application tables in the APPS tablespace. You observe that there are several warning alerts being generated for the APPS tablespace space usage metrics. Currently, the warning threshold for the tablespace usage metrics is set to 70%. To make the generated alerts more useful as a problem identification tool, you want to reduce the frequency of alert generation for the tablespace usage metrics for the APPS tablespace. What should you do?
A. Disable SQL tracing for the APPS tablespace.
B. Disable logging attributes for the APPS tablespace.
C. Modify the tablespace to be a dictionary-managed tablespace.
D. Increase the critical threshold value for the tablespace space usage metrics for the APPS tablespace.
E. Increase the warning threshold value for the tablespace space usage metric for the APPS tablespace.

E

E

教材11-10
"When the tablespace limits are reached, an appropriate alert is raised."

11.
127. In your test database, you have created the ORDERS table as an index-organized table (IOT). To facilitate faster querying, you have created a mapping table and a bitmap index on the ORDER_FILLED column. You observe that the query performance degrees when users perform a large volume of transactions. While investigating the reason, you find that the mapping table segment is fragmented, leading to poor performance. Which option would you use to defragment the mapping table without affecting the original table data?
A. Export and import the mapping table.
B. Drop and re-create the mapping table.
C. Truncate the mapping table and reinsert the values.
D. Use the ALTER TABLE .. REBUILD command to defragment the mapping table.

B
忘了, C?

A

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

见第9课(http://mrxiesdatabase.blogspot.com/2009/05/1z0-043-lesson-9.html)
----
PDF教材13-38
"To rebuild the mapping table, it must be dropped and re-created."
问题:
alter table xxx move nomapping;
alter table xxx move mapping table;
会锁表吗?
----

12.
142. View the Exhibit and examine the characteristics of the USERS tablespace. You observe that a large volume of inserts and deletes are happening on the TRANS table in the USERS tablespace and you suspect that the TRANS table is fragmented. Which advisory component would you refer to, in order to find information about table fragmentation? Exhibit:

A. Segment Advisor
B. SQL Access Advisor
C. SQL Tuning Advisor
D. Memory Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

A

A

教材11-15

13.
154. Exhibit: View the Exhibit and examine the properties of the USERS tablespace. You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)

A. the materialized views based on the TRANS table
B. the materialized views log of the TRANS table
C. the TRANS table
D. the B*Tree indexes on the TRANS table
E. the large object (LOB) segments of the TRANS table

CD
又忘了

CD

ABE为何不对?

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

教材11-20
Use the ALTER command to invoke segment shrink on an object. The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log.

===


14.
47. Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:
ALTER INDEX HR.IDX_PK_EMP COALESCE;
Which of the following commands accomplishes the same task as this command? (Choose the best answer.)
A. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
B. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
C. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;

A

47. A. Using the CASCADE keyword in any segment shrink operation will shrink the free space in any dependent objects such as indexes. Chapter 13 discusses segment shrink functionality.

教材11-20
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, and IOT (index-organized tables) mapping tables."

15.
50. Which of the following statements is not true about segment shrink operations?
A. The compaction phase of segment shrink is done online.
B. During the compaction phase, the entire segment is locked but only for a very short period of time.
C. When the second phase of segment shrink occurs, the high watermark (HWM) is adjusted.
D. User DML can block the progress of the compaction phase until the DML is committed or rolled back.
E. Using the COMPACT keyword, the movement of the HWM can occur later during non-peak hours by running the command without the COMPACT keyword.

B

50. B. During the compaction phase, locks are held only on individual rows, causing some minor serialization with concurrent DML operations. For more information about segment shrink, see Chapter 13.

教材11-12

===


16.
1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE

C

1. C. The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.

见第10课(http://mrxiesdatabase.blogspot.com/2009/06/1z0-043-lesson-10.html)
---
见
Viewing Segment Advisor Results(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2119)
DBA_ADVISOR_RECOMMENDATIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3014.htm)
DBA_ADVISOR_ACTIONS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3003.htm)
---

17.
3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M. that receives an ORA-01555: Snapshot too old error after running for 15 minutes. An alert is sent to the DBA that the undo tablespace is incorrectly sized. At 10:15 A.M., the DBA checks the initialization parameter UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The DBA doubles the size of the undo tablespace by adding a second datafile. At 1:15 P.M., the user SCOTT runs the same query and once again receives an ORA-01555: Snapshot too old error. What happens next? (Choose the best answer.)
A. The DBA receives another alert indicating that the undo tablespace is still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.

B

3. B. Even if the size of the undo tablespace is adjusted after an undo space problem, only one alert is sent for each 24-hour period. Therefore, the only way that the problem will be resolved promptly is for SCOTT to call the DBA, because the DBA will not receive another alert until the next day when another query fails.

见第10课(http://mrxiesdatabase.blogspot.com/2009/06/1z0-043-lesson-10.html)
---
...24小时内重复的报警只发一次, 不太合理呀. 只有undo报警才这么处理?

见
Viewing Information About Undo(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1510)
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.---

18.
4. The background process __________ checks for tablespace threshold violation or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30

E
猜的

4. A. The new background process MMON checks for threshold violations every 10 minutes. An alert is triggered when the threshold is reached or is cleared.

教材11-11
"This information is aggregated every 10 minutes by the MMON process."

19.
6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.

D

6. D. While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in a segment shrink operation.

不是很理解

见第10课
---
D 见Space Reclamation(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240)
Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
Note: The Segment Advisor does not detect chained rows created by inserts.
---

20.
9. In the following scenario, the DBA wants to reclaim a lot of wasted space in the HR.EMPLOYEES table by using the segment shrink functionality. Which of the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;
A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5

A

9. A. While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a subsequent operation.

见教材11-12,11-20

21.
10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent? (Choose the best answer.)
A.

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  null);

B.

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_le, 60,
  dbms_server_alert.operator_le, 90,
  1, 1, null,
  dbms_server_alert.object_type_datafile,
  'UNDOTBS1');

C.

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  'UNDOTBS1');

D.

dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_ge, 60,
  dbms_server_alert.operator_ge, 90,
  1, 1, null,
  dbms_server_alert.object_type_tablespace,
  'UNDOTBS1');

D

10. D. The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the tablespace name itself. Specifying NULL for the tablespace name will set the threshold for all tablespaces, not just the UNDOTBS1 tablespace.

见第10课
---
见
94 DBMS_SERVER_ALERT(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm)
---

22.
11. Which of the following statements is not true about segment shrink operations? (Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.

C
忘了 A?

11. A. Because the ROWIDs are changed with a segment shrink operation, tables with ROWID-based materialized views cannot be shrunk unless the materialized views are dropped and re-created after the segment shrink operation.

教材11-14
However, the following objects stored in ASSM tablespaces cannot be shrunk:
Tables in clusters
Tables with LONG columns
Tables with on-commit materialized views
Tables with ROWID-based materialized views
IOT mapping tables
Tables with function-based indexes

23.
12. Which of the following is not a feature of the Segment Advisor within EM Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows

D
?

12. D. The Segment Advisor is not used to find tables with chained rows, but instead is used for finding segments that are good candidates for segment shrink or may be growing too fast.

有疑问

见第10课
---
segment advisor可以发现chained rows呀, 见上一道题
---

24.
14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.

C

14. B. For segments in tablespaces with automatic segment space management, LOB segments cannot be shrunk. In addition, tables with LONG columns, on-commit materialized views, and ROWID-based materialized view cannot be shrunk. In all cases, shrink operations cannot be performed on segments managed by freelists.

有疑问, 可以收缩LOB类型的呀
教材11-20
"The object’s type can be one of the following: table (heap- or index-organized), partition, subpartition, LOB (data and index segment), index, materialized view, or materialized view log."

25.
15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.

A

15. A. Whether you rebuild the index offline or online, you temporarily need twice as much disk space. If you rebuild the index online, you also need disk space to support a journal table to hold the intermediate changes to the index while the index is being rebuilt.

没找到

26.
18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;

AC

18. A, C. Unless an ORDER BY clause forces a sort operation and uses up temporary space, a SELECT command will not otherwise trigger Resumable Space Allocation. Dropping a table does not use any additional disk space in a tablespace; it frees up disk space. Therefore, it will not trigger Resumable Space Allocation.

见第10课
---
A 查询可能发生ora-1555错误, 无法恢复
---

27.
19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database

BE

19. B, E. The AFTER SUSPEND system-level trigger can be defined only at the database level or at the schema level.

教材11-25
"Users can register triggers for this event at both the database and schema level."

-fin-