1z0-043 lesson 10 Managing Schema Objects

1z0-043 lesson 10

完成
有好多概念都不清楚, IOT, CLuster

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


1.
24. You are designing an application for a database and you have been asked to design a database table to facilitate monthly bill generation. The bill would include details of customer calls, listed in chronological order.Which method would you follow to achieve this objective without increasing the overhead of sorting the rows?
A. create a hash cluster to store the data
B. create an index cluster to store the data
C. create a partitioned table to store the data
D. create a sorted hash cluster to store the data
E. create a heap table with rowid to store the data

D
概念不清

D

其它选项都不行

教材10-17

2.
27. In which scenarios would you rebuild an index? (Choose all that apply.)
A. when you need to disable the index usage
B. when you need to change storage options
C. when you need to enable index monitoring
D. when you need to move the index to another tablespace

BD

BD

教材10-24

A 取消使用不就是no monitoring吗, alter index ... nomonitoring usage;

SQL> select * from v$object_usage;

no rows selected

SQL> alter index ind_t1_oid monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             YES NO
06/07/2009 05:04:56


SQL> select object_id from t1 where object_id=1;

no rows selected

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             YES YES
06/07/2009 05:04:56


SQL> alter index ind_t1_oid nomonitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_T1_OID                     T1                             NO  YES
06/07/2009 05:04:56 06/07/2009 05:05:44


SQL>

C alter index即可

3.
35. While designing your database, you have created the EMPLOYEES table as an index-organized table(IOT). You want to create a bitmap index on the JOD_ID column to make queries faster. Which task must have been completed so that you are able to create the bitmap index?
A. A primary key must have been created.
B. A mapping table must have been created.
C. An overflow tablespace must have been specified.
D. The PCTTHRESHOLD option must have been specified.

B

B

PDF教材13-37

若要在IOT上建位图索引, 必须建映射表(mapping table)
映射表保存IOT的逻辑rowid, 位图索引指向的映射表, 这样建立起位图索引和IOT表的关系(好像是这个意思)

4.
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.

A
得试试

C

看错了

PDF教材13-7

5.
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

PDF教材13-17
"Only segments in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk"

6.
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

不会立即报错

7.
68. You noticed that the index tablespace in your database requires a recovery. However, instead of performing a media recovery, you decided to re-create the indexes in a new tablespace.
Which two options would you use to reduce the time it takes to re-create the indexes? (Choose two.)
A. ONLINE
B. REVERSE
C. PARALLEL
D. COMPRESS
E. NOLOGGING
F. COMPUTE STATISTICS

CE

CE

ALTER INDEX ... REBUILD [ONLINE]也比re-create快, 但是索引是坏的, 只能重建
E. 如果强制打开了日志记录, NOLOGGING也不管用

8.
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.

B

A

不能这样设置

9.
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

教材10-22

10.
97. Users in your production database complain that they are getting the following error message while trying to insert rows into the ORDERS table:
ERROR at line 1:
ORA-01654: unable to extend index USERS.ORDERS_IND by 8 in tablespace INDEXES.While investigating,
you find that the INDEXES tablespace has run out of space and there is no more free space on the disk where the data files are available. Which two actions could you perform to overcome this error without affecting the queries that are currently being executed? (Choose two)
A. Drop and re-create the index.
B. Coalesce the ORDERS_IND index.
C. Coalesce the INDEXES tablespace.
D. Drop and re-create the ORDERS table.
E. Rebuild the index online and move it to another tablespace.

BE
好像做过,OCA的题?

BE

做过

11.
102. While designing the database for one of your online transaction processing (OLTP) applications, you want to achieve the following:
a) high availability of data
b) faster primary key access to the table data
c) compact storage for the table
Which type of tables would you use to achieve these objectives?
A. heap tables
B. object tables
C. partitioned tables
D. index-organized tables (IOTs)

D
索引组织表的高可用性是什么来着?

D

教材10-10
"Have higher availability because table reorganization does not invalidate secondary indexes"

12.
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

PDF教材13-10
"When a resumable statement encounters a correctable error, the system internally generates the AFTER SUSPEND system event."

13.
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

无需解释

14.
109. In one of your online transaction processing (OLTP) applications, most users frequently modify the values, including the key values, of the application tables. Some users generate application reports by using multiple application tables. What is the best table structure that you can use to gain optimal performance?
A. Heap table
B. Object table
C. External table
D. Clustered table
E. Global temporary table
F. Index-organized table (IOT)

A

A

B 这个好像没什么关系
C 外部表不能被修改
D,F 聚簇表和索引组织表都不适用于关键字段频繁修改的情况.
E 临时表的作用范围只对当前会话

15.
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

PDF教材13-22
"During the compaction phase, locks are held on individual rows containing the data. This causes concurrent DML operations such as updates and deletes to serialize on these locks."

16.
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

太容易了反而觉得有什么蹊跷

17.
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
不知道

A

答案错了,应该是B
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;
会锁表吗?

映射表也不能被收缩(shrinking), PDF教材13-17

18.
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

重复了

19.
149. You lost the index tablespace in your database. You decided to re-create the index tablespace and the indexes in the tablespace. What methods can you use to re-create the indexes? (Choose all that apply.)
A. SQL scripts
B. Recovery Manager (RMAN) script
C. Data Pump
D. SQL*Loader
E. Flashback database

ABC
没看懂

AC

破题, B为什么不行
A 肯定行
B 可以恢复表空间, 包括了其中的索引
C 可以从导出文件中导出建索引的SQL语句

20.
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

ABCDE
不会做.图不对题.

CD

PDF教材13-21
"If CASCADE is specified, the shrink behavior is cascaded to all the dependent segments that support a shrink operation, except materialized views, LOB indexes, IOT mapping tables, and overflow segments."

21.
182. In which two conditions are resumable statements suspended? (Choose two.)
A. when a user exceeds the space quota
B. when a user session is terminated
C. when a table that is being accessed by the current transaction is not found
D. when a user manually suspends the statement
E. when the maximum extents of a segment is reached

AE

AE

PDF教材13-7
无法手工挂起一个会话

=====


22.
28. You create a new table, populate it, and build several indexes on the table. When you issue a query against the table, the optimizer does not choose to use the indexes. Why might this be?
A. The indexed columns are not used in the where clause of the query.
B. There are no statistics available for the table.
C. There is a small number of rows in the table.
D. The query contains a hint instructing the optimizer not to use the indexes.
E. All of the above.

E

28. E. Because you aren’t given enough information to determine the availability of statistics, nor are you privy to the query itself, you can’t rule out any valid possibilities. All of the choices listed are valid reasons why the optimizer might choose to not use an available index. See Chapter 8 for more information.

A. 没查询这个字段
B. 没有统计信息
C. 记录数很少, 无需使用索引
D. 提示强制改变了执行计划

23.
29. You are concerned about keeping statistics up-to-date for the NOODLE table, a table that is the target of frequent DML operations. In response, another DBA issues the command ALTER TABLE NOODLE MONITORING. What is the result of this action? (Choose the best answer.)
A. The action resolves the issue.
B. The action has no effect.
C. The action only partially resolves the issue.
D. The action has a detrimental effect regarding the issue.
E. None of the above.

D

29. B. The MONITORING option of the ALTER TABLE command is deprecated in Oracle Database 10g. While it will not produce an error, it is treated as no operation by Oracle. Automatic DML monitoring is used instead. See Chapter 8 for more information.

C 我记着我选C来着, 怎么成D了. 10G不用monitoring, 设不设置都一样

SQL> select monitoring from user_tables where table_name='T1';

MON
---
YES

SQL> alter table t1 nomonitoring;

Table altered.

SQL> select monitoring from user_tables where table_name='T1';

MON
---
YES

SQL>

见Automatically Collecting Statistics on Tables(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2270)
Formerly, you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. Starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

D detrimental是有害的意思

24.
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;
D. ALTER INDEX HR.IDX_PK_EMP REBUILD;

D
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.

D为啥不对? 是因为不是online的吗?

25.
48. Which type of queue is supported by sorted hash clusters?
A. DEQUE
B. LIFO
C. FIFO
D. A queue represented by a two-way linked list

C
不会

48. C. Sorted hash clusters are similar to standard hash clusters, except that they store data sorted by non-primary key columns and make access by applications that use the rows in a first in, first out (FIFO) manner very efficiently; no sorting is required. Chapter 13 describes how sorted hash clusters are created and used.

教材10-18
Calls are stored as they are made and processed later in a “first-in, first-out” order when bills are generated for each originating telephone number.

26.
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.

PDF教材13-22

27.
51. What is the purpose of the overflow area for an index-organized table (IOT)?
A. The overflow area helps to reduce row chaining in the IOT.
B. The overflow area allows you to store some or all of the non-primary key data in an IOT row in another tablespace, improving performance.
C. The overflow area is used when there is a duplicate value for the primary key in the IOT.
D. The overflow area stores the index information for indexed columns that are not part of the primary key.

B

51. B. If an IOT row’s data exceeds the threshold of available space in a block, the row’s data will be dynamically and automatically moved to the overflow area. For more information about index-organized tables, see Chapter 13.

PDF教材13-33

=====


28.
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

B
不确定, 或者是A?

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.

见
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)

29.
2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.

A
不懂

2. A. The new access path in a sorted hash cluster is used only if an equality predicate is used.

教材10-17
见
15.9 Using Hash Clusters for Performance(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i7690)

30.
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.

D
什么意思

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.

考的不是resumable啊, 考的是OEM报警
像答案说的那样, 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.

31.
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

A
不知道

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.

见Other Background Processes(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1626)

32.
5. Which of the following initialization parameters influences the recommended redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above

D

5. D. FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.

PDF教材13-5
This column shows the redo log file size (in megabytes) that is considered to be optimal based on the current FAST_START_MTTR_TARGET setting. It is recommended that you set all online redo log files to at least this value.

33.
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.

B
不确定. 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.

B 也对. shrink索引
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.

34.
7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows

B

7. B. Only PCTFREE is used in the calculation, because it is the amount of space to leave free in the block for updates to existing rows. PCTUSED is not needed unless the segment space management is not AUTO. In addition, extent sizes calculated by this feature help assess the impact on the tablespace where this segment will be stored.

教材10-21
For example, the estimation of a table size is based on the following data: column data types, column sizes, and PCTFREE. Extent information is also used to calculate the space allocation impact on the currently selected tablespace.

35.
8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are returned in ascending or descending order.

B
不知道

8. C. While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.

B 教材10-17
Within each list, the rows are stored in the order specified by the sort key columns defined by the corresponding sorted hash cluster. This is also the default return order when querying the table by using the hash key columns in the predicate.

36.
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
第5步有疑问

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.

第5步cascade的时候会不会锁索引?

37.
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_ CT_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.

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

38.
11. Which of the following statements is not true about segment shrink operations? (Choose the best sanswer.)
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.

E
不知道

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.

PDF教材13-17
E 见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.

Using the Segment Advisor(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2102)
Note: The Segment Advisor flags only the type of row chaining that results from updates that increase row length.

39.
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.

segment advisor可以发现chained rows呀, 见上一道题

40.
13. Which of the following conditions will trigger an additional sort on a sorted hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.

A
不知道

13. A, E. If a query on a sorted hash cluster retrieves rows and an ORDER BY clause specifies either non-sort columns or a suffix of the sort columns, additional sorting is required, assuming that indexes are not defined on the columns in the ORDER BY clause.

E是说, ORDER BY字段的顺序跟sorted hash cluster指定的顺序不一样?

41.
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.

A
不知道

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.

PDF教材13-17

42.
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.

PDF教材13-27

43.
16. Which of the following commands adds a member /logs/redo22.log to redo log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;

B

16. B. When adding log file members, specify the group number or specify all the existing group members.

嗯

44.
17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.

D
就这句最看不懂

17. B. Hash clusters, not index clusters, use a hashing function to find a row in a cluster and perform best for equivalence queries.

不懂. index cluster指的是cluster上建的索引?
都不懂

45.
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
A应该不行吧

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.

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

46.
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

E

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

多选
见Notifying Users: The AFTER SUSPEND System Event and Trigger(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2075)
When a resumable statement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.

47.
20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part because the data is stored in the same segment as the index, and therefore no physical ROWID is required.

C

20. B. An index-organized table (IOT) does not have a physical ROWID; instead, a logical ROWID is constructed based on the value of the primary key.

C overflow就是另一个segment

=====



-fin-