OCM Database Administration & SQL Flashcards

Which Oracle feature partitions a table by automatically routing rows to sub-partitions based on hash values when list partitioning is the top level?

Answer: List-Hash Composite Partitioning

List-Hash composite partitioning first divides data by a list of discrete values, then applies hash sub-partitioning within each list partition for even data distribution.

What is the purpose of the ROWID pseudocolumn in Oracle?

Answer: Unique identifier for each row that encodes its physical storage location

ROWID is a unique address for each row in a table that encodes the data object number, data file, data block, and row slot position, allowing direct block-level access.

Which Oracle Data Dictionary view shows the dependencies between stored PL/SQL objects?

Answer: DBA_DEPENDENCIES

DBA_DEPENDENCIES lists all referencing and referenced objects so you can trace dependency chains between packages, procedures, triggers, and views.

In Oracle, what happens when you issue COMMIT while a DDL statement is in progress?

Answer: DDL issues an implicit COMMIT before and after execution; no explicit COMMIT needed

Oracle DDL statements automatically issue an implicit COMMIT before they execute and another implicit COMMIT upon successful completion, making them auto-transactional.

Which clause in a SELECT statement enables you to perform aggregations at multiple levels of a hierarchy in one query?

Answer: ROLLUP

The ROLLUP extension to GROUP BY computes subtotals and grand totals at each level of the specified column hierarchy within a single SQL statement.

What is the function of Oracle's Automatic Workload Repository (AWR)?

Answer: Captures and retains database performance statistics snapshots for analysis

AWR automatically collects and stores database performance statistics in periodic snapshots (default every 60 minutes) retained for 8 days for historical analysis and tuning.