13.8. Review Questions

  1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?

    1. DBA_ADVISOR_FINDINGS

    2. DBA_ADVISOR_RECOMMENDATIONS

    3. DBA_ADVISOR_ACTIONS

    4. DBA_ADVISOR_RATIONALE

  2. Which of the following statements is not true about sorted hash clusters?

    1. The new access path is used regardless of the type of predicate in the WHERE clause.

    2. You are allowed to create indexes on sorted hash clusters.

    3. The cost-based optimizer must be used to take advantage of the new access path.

    4. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.

    5. More than one table can be stored in a sorted hash cluster.

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

    1. The DBA receives another alert indicating that the undo tablespace is still undersized.

    2. The user SCOTT calls the DBA to report that the query is still failing.

    3. The second datafile autoextends so that future queries will have enough undo space to complete when there is concurrent DML activity.

    4. Resumable Space Allocation suspends the query until the DBA adds another datafile to the undo tablespace, and then the query runs to completion.

  4. The background process _________ checks for tablespace threshold violation or clearance every _______ minutes.

    1. MMON, 10

    2. SMON, 10

    3. TMON, 30

    4. PMON, 15

    5. MMON, 30

  5. Which of the following initialization parameters influences the recommended redo log file size provided by the Redo Logfile Size Advisor?

    1. LOG_CHECKPOINT_INTERVAL

    2. OPTIMAL_LOGFILE_SIZE

    3. FAST_START_IO_TARGET

    4. FAST_START_MTTR_TARGET

    5. None of the above

  6. Which of the following is not a benefit of segment shrink?

    1. Full table scans will take less time.

    2. Better index access takes place because of a smaller b-tree.

    3. Space is freed up for other database objects.

    4. All chained rows are fixed.

    5. Space below the HWM is released, and the HWM is moved down.

  7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?

    1. Column datatypes

    2. PCTUSED

    3. PCTFREE

    4. Column sizes

    5. Estimated number of rows

  8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)

    1. Rows within a given cluster key value are sorted by the sort key(s).

    2. The ORDER BY clause is not required to retrieve rows in ascending or descending order of the sort key(s).

    3. Cluster key values are hashed.

    4. Rows selected by a cluster key value using an equality operator are returned in ascending or descending order.

  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;

    1. 3, 4, 1, 5, 2

    2. 4, 1, 3, 2, 5

    3. 5, 2, 1, 3, 4

    4. 4, 1, 2, 3, 5

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

    1. 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);

    2. 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'),

    3. 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'),

    4. 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'),

  11. Which of the following statements is not true about segment shrink operations? (Choose the best answer.)

    1. Tables with ROWID-based materialized views are maintained.

    2. Segment shrink is allowed only on segments whose space is automatically managed.

    3. Heap-organized and index-organized tables can be shrunk.

    4. ROW MOVEMENT must be enabled for heap-organized segments.

    5. Chained rows may be repaired during a segment shrink operation.

    6. Triggers are not fired during a segment shrink operation.

  12. Which of the following is not a feature of the Segment Advisor within EM Database Control?

    1. Growth trend analysis

    2. Segment Resource Estimation

    3. Finding candidates for segment shrink

    4. Finding table segments with chained rows

  13. Which of the following conditions will trigger an additional sort on a sorted hash cluster? (Choose two.)

    1. The ORDER BY clause specifies non-sort columns that are not indexed.

    2. An ORDER BY clause is used in the query, although the sort may still fit in memory.

    3. The cost-based optimizer is in effect.

    4. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.

    5. The ORDER BY clause specifies trailing sort columns.

  14. Which of the following statements is not true about segment shrink operations in tablespaces with automatic segment space management?

    1. Clustered tables cannot be shrunk.

    2. LOB segments can be shrunk.

    3. IOT mapping tables and overflow segments cannot be shrunk.

    4. Tables with function-based indexes cannot be shrunk.

    5. ROW MOVEMENT must be enabled for heap-based segments.

  15. Which of the following is a disadvantage of rebuilding an index instead of coalescing an index?

    1. You temporarily need twice as much disk space to rebuild the index.

    2. You cannot move the index to another tablespace.

    3. The storage characteristics of the index cannot be changed.

    4. The rebuild operation only coalesces index leaf blocks within each branch instead of re-creating the entire tree.

  16. Which of the following commands adds a member /logs/redo22.log to redo log file group 2?

    1. ALTER DATABASE ADD LOGFILE '/logs/redo22.log' TO GROUP 2;

    2. ALTER DATABASE ADD LOGFILE MEMBER '/logs/redo22.log' TO GROUP 2;

    3. ALTER DATABASE ADD MEMBER '/logs/redo22.log' TO GROUP 2;

    4. ALTER DATABASE ADD LOGFILE '/logs/redo22.log';

  17. Which of the following is not a benefit of index clusters?

    1. The tables in the cluster are always queried together.

    2. Queries with an equivalence operator will perform better.

    3. The tables in the cluster have little or no DML activity.

    4. The child tables have roughly the same number of rows for each parent key in the parent table.

  18. Which of the following scenarios will never trigger Resumable Space Allocation? (Choose all that apply.)

    1. SELECT * FROM HR.EMPLOYEES;

    2. DELETE FROM HR.EMPLOYEES;

    3. DROP TABLE HR.EMPLOYEES;

    4. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;

    5. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;

    6. ALTER INDEX HR.EMP_NAME_IX REBUILD;

  19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)

    1. Instance

    2. Schema

    3. Table

    4. Session

    5. Database

  20. Which of the following statements is not true about index-organized tables?

    1. An index-organized table can have additional indexes defined on other columns in the table.

    2. An index-organized table has both physical and logical ROWIDs.

    3. An index-organized table without secondary indexes is stored in a single segment.

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset