Optimizer Statistics drive the cost-based optimizer’s query plans. Let’s dissect each option:

A . Optimizer Statistics are collected automatically by an automatic maintenance job that runs during

predefined maintenance windows.

True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance

windows (e.g., nightly 10 PM–2 AM). The GATHER_STATS_PROG job, managed by

DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.

Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the

DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.

Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.

Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via

DBMS_AUTO_TASK_ADMIN.DISABLE.

B . Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.

False. Stats aren’t updated in real-time; this would be too resource-intensive. Instead, Oracle tracks

changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or

manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with

REAL_TIME_STATS), but it’s not the default.

Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting

Oracle’s batch approach.

C . Optimizer Statistics can be manually collected at multiple levels using

DBMS_STATS.GATHER_*_STATS PL/SQL procedures.

True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS,

GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables,

schemas, or the entire database.

Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options

like ESTIMATE_PERCENT and DEGREE fine-tune the process.

Practical Use:Used for immediate stats updates post-DML or for custom schedules outside

maintenance windows.

Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.

D . Optimizer Statistics are collected by the Statistics Advisor.

False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn’t

collect them. Collection is still via DBMS_STATS or AutoTask.

Why Incorrect:It’s a diagnostic tool, not an executor.

E . Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR)

Snapshot.

False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats

collection is a separate process via AutoTask or manual commands.

Why Incorrect:AWR and stats collection serve distinct purposes—monitoring vs. optimization.

Reference:Oracle Database SQL Tuning Guide 23ai, "Managing Optimizer Statistics"; Oracle Database

PL/SQL Packages and Types Reference 23ai, "DBMS_STATS Package."

A .Incorrect syntax and view (pdb_spfile$ is not a valid view; PDB_SPFILE$ exists but lacks value$).

B .From CDB root, V$PARAMETER shows all parameters, not PDB-specific ones.

C .pdb_spfiles is not a valid view; PDB_SPFILE$ exists but requires scoping to a PDB.

D .True. From the PDB, V$PARAMETER shows parameters specific to that PDB, including inherited

and PDB-modified values, with ISPDB_MODIFIABLE indicating alterability.

Reference:Oracle Multitenant Administrator’s Guide 23ai, "Managing PDB Initialization Parameters."

To change a PDB_MODIFIABLE parameter, the PDB must be in READ WRITE mode, allowing ALTER

SYSTEM commands to modify parameters stored in the PDB’s SPFILE or memory. READONLY modes

prevent modifications, and no RESTRICTED WRITE ONLY mode exists.

Reference:Oracle Multitenant Administrator’s Guide 23ai, "Modifying PDB Parameters."

A . Dependent indexes become UNUSABLE.False. SHRINK SPACE COMPACT does not affect index

usability; only SHRINK SPACE without COMPACT may require index maintenance if CASCADE is

omitted.

B . ... starting toward the end of the ORDERS segment.False. Rows are compacted toward the

beginning of the segment, not the end.

C . Only queries are allowed ...False. Both queries and DML are allowed during SHRINK SPACE

COMPACT as it’s an online operation.

D . The high-water mark (HWM) of ORDERS is adjusted.False. COMPACT moves rows but doesn’t

adjust the HWM; the full SHRINK SPACE command is needed for HWM adjustment.

E . Queries and DML statements are allowed ...True. This is an online operation in 23ai, allowing

concurrent access.

F . ... starting from the beginning of the ORDERS segment.True. Rows are moved to fill free space

from the segment’s start.

Reference:Oracle Database SQL Language Reference 23ai, "ALTER TABLE ... SHRINK SPACE."

Logical structures (e.g., segments, extents) map to physical structures (e.g., data files, blocks). Let’s

dissect each option:

A . A segment can span multiple data files in some tablespaces.

True. A segment (e.g., a table or index) is a logical entity stored in a tablespace. In a smallfile

tablespace (default in Oracle), a segment’s extents can span multiple data files if the tablespace has

multiple files and space allocation requires it. This is common in large tables or when autoextend

adds new files.

Mechanics:Oracle allocates extents across available data files in a round-robin fashion (with ASSM) or

as needed, ensuring the segment’s data is distributed. This doesn’t apply to bigfile tablespaces,

which use a single data file.

Example:A 10GB table in a tablespace with two 5GB data files will span both.

B . Segments can span multiple tablespaces.

False. A segment is confined to a single tablespace. Oracle enforces this to maintain logical

separation (e.g., a table’s data stays in its assigned tablespace). Partitioned tables can have partitions

in different tablespaces, but each partition is a separate segment.

Why Not:The segment header and extent map reside in one tablespace, preventing cross-tablespace

spanning for a single segment.

C . A segment might have only one extent.

True. A segment starts with one extent upon creation (e.g., a small table or index). If no further

growth occurs, it remains a single-extent segment. This is common with small objects or when

INITIAL extent size suffices.

Mechanics:In locally managed tablespaces (default), the initial extent is allocated based on INITIAL or

tablespace defaults (e.g., 64KB), and additional extents are added only as needed.

D . All tablespaces may have one or more data files.

False. Bigfile tablespaces are restricted to one data file (up to 128TB). Smallfile tablespaces

(traditional) can have multiple data files (up to 1022), but the “all” phrasing makes this false due to

bigfile exceptions.

Clarification:The question’s intent may assume smallfile tablespaces, but Oracle 23ai supports both

types.

E. A segment’s blocks can be of different sizes.

False. All blocks in a segment use the tablespace’s block size (e.g., 8KB). While a database can have

tablespaces with different block sizes (e.g., 8KB, 32KB), a single segment’s blocks are uniform, as it

resides in one tablespace.

Reference:Oracle Database Concepts 23ai, "Logical Storage Structures"; Oracle Database

Administrator’s Guide 23ai, "Managing Tablespaces."