Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) (OCP) (OCP) — Questions 226–261

A SQL Tuning Set is a database object holding SQL statements plus their execution context, such as bind values, execution plans and statistics. SQL Tuning Advisor consumes this container to analyse and tune the captured workload.

This command enables automatic backup of the control file and SPFILE whenever a backup or structural change occurs. It ensures that you have a current control file backup, which is essential for recovery. The backup is stored in the fast recovery area or a configured location. This is the correct setting to meet the requirement.

The SAVE STATE clause records the current open mode of the PDB in the CDB's metadata. When the CDB instance restarts, the PDB will be opened to that saved state automatically. This is the standard and supported method to ensure a PDB starts automatically. It is simple, reliable, and does not require custom scripting.

DBA_HIST_WR_CONTROL stores the active Automatic Workload Repository configuration, exposing columns such as RETENTION and SNAP_INTERVAL. Querying it returns the current retention period directly, satisfying the stem's requirement for present settings rather than historical snapshots or parameter defaults.

Dropping the CDB removes the root container that every PDB depends on, so all PDBs are dropped along with it. This satisfies the stem's constraint: a PDB cannot exist independently of its CDB, since the CDB supplies the shared instance, data dictionary and system metadata the PDB requires.

ALTER PLUGGABLE DATABASE OPEN READ ONLY transitions the PDB from MOUNTED to a queryable state while blocking DML, satisfying the READ ONLY requirement. The OPEN clause is mandatory because a PDB must be opened before any read-only access is possible.

V$SQL_PLAN exposes the execution plan rows the optimizer actually chose for a cached SQL statement, keyed by SQL_ID and child number. It satisfies the requirement to see the plan currently in use, unlike EXPLAIN PLAN, which shows a predicted plan.

Oracle GoldenGate provides logical replication with minimal downtime. You can set up the target 23ai database, apply initial load, and start change data capture to keep it synchronized while the source remains online. At cutover, you stop application traffic, allow the target to catch up, and switch over. This reduces downtime to minutes, even for large databases with high transaction rates.

HNSW indexing is a genuine AI Vector Search component in 23ai, providing approximate nearest-neighbour vector indexes for fast similarity queries. It is one of the three components the stem asks for, alongside vector data types and vector distance functions.

BACKUP AS COPY writes a byte-for-byte identical image of each datafile rather than a proprietary backup piece, so the output is usable directly as a datafile. This satisfies the stem's requirement that the output be an exact image copy.

SQL Access Advisor analyses workload from the SQL cache and recommends indexes, materialised views and partitioning for the supplied workload, which directly satisfies the stem's need for new index recommendations. Unlike the SQL Tuning Advisor, which tunes individual statements, it evaluates an entire workload to propose missing indexes.

AUDIT_ADMIN holds the Oracle Database role that governs the Unified Audit trail, granting privileges to create, alter and drop audit policies, and to query and purge the trail itself. This directly satisfies the stem's requirement for the user accountable for managing that trail.

Taking the tablespace offline is mandatory before recovery begins, because Oracle cannot restore and recover datafiles while they remain open to users. Offlining the tablespace quiesces I/O against its datafiles, allowing the restore and subsequent media recovery to proceed without corrupting the online database.

Wide tables store columns in a columnar, compressed format, so queries projecting a small subset read only the relevant column groups rather than full rows. This satisfies the stem by avoiding significant performance degradation, since irrelevant columns are never decompressed or scanned.

ASPM validates a candidate plan by executing the SQL statement with the new plan and comparing its performance metrics against the existing baseline. Only if the measured performance improves does the database accept and evolve the baseline, ensuring the change is verified rather than assumed.

Vector embeddings in 23ai are stored using the VECTOR data type, which holds them as arrays of floating-point numbers. This native binary format supports efficient similarity searches using functions such as VECTOR_DISTANCE rather than text or character storage.

Optimizer hints embedded in the SQL text directly override the optimizer's cost-based decisions, forcing a specific join method, index or access path. This manual method gives the DBA explicit control when statistics or transformations produce a poor plan.

A physical standby applies redo directly, so during a rolling upgrade the primary runs the new Oracle version while the standby stays on the old one, then roles swap. Logical standby or snapshot standby cannot sustain this cross-version redo apply, making physical standby the minimum configuration.

Buffer busy waits arise when sessions contend for the same data blocks concurrently in the buffer cache. Multiple sessions accessing identical blocks serialise on buffer pins and latches, so the wait event points directly to block-level contention rather than I/O latency or parsing overhead.

Patching the standby first lets it apply the RU while the primary keeps serving users; a switchover then promotes the patched standby, and the old primary is patched afterwards. This sequence satisfies the rolling upgrade constraint of continuous availability, since no single instance is offline during the whole operation.

A SQL Profile supplies auxiliary statistics and corrections to the optimizer for a specific statement, letting it derive a better plan without changing the SQL text. That improved cardinality and cost estimation is what raises optimizer accuracy.

Important for parsing/memory health.

A Proxy PDB presents a remote PDB through a local database link, so applications connect locally while the actual data resides elsewhere. This satisfies the requirement of accessing remote PDB data without duplicating it, since queries are forwarded to the source PDB.

A recovery catalog is a separate schema holding RMAN metadata for many databases, so a single catalogue server can register and manage backups across the whole estate. This satisfies the benefit of centralised metadata, unlike the control file, which is tied to one database.

This reduces IO latency.

V$RMAN_STATUS is the dynamic performance view that records every RMAN job and its state, so querying it lists all current and completed backup operations with their status, satisfying the requirement to check all RMAN backup jobs.

VERIFY is permitted on Blockchain tables, returning a cryptographic proof that rows remain untampered since insertion. It satisfies the stem's requirement by confirming ledger integrity without altering data, unlike DML operations that Blockchain tables restrict. Oracle implements this through hash-chain validation across the table's historical row versions.

Unified auditing captures CREATE TABLE as an auditable system action through a unified audit policy, which defines privileges, actions and objects in one policy applied database-wide. Traditional audit options and fine-grained auditing do not cover this statement type in 23ai.

ADDM (Automatic Database Diagnostic Monitor) analyses Automatic Workload Repository data to detect performance bottlenecks and generate actionable recommendations. This directly satisfies the stem's requirement, distinguishing it from AWR, which merely collects statistics, and SQL Tuning Advisor, which targets individual statements.

JSON Duality Views expose the same underlying relational tables simultaneously as relational rows and as JSON documents, so applications can query either model without duplicating or transforming data. This gives unified JSON and relational access over one schema.

Setting STATISTICS_LEVEL to BASIC disables Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis, since both depend on TYPICAL-level statistics collection. This satisfies the scenario's requirement to stop AWR and ADDM entirely, unlike TYPICAL or ALL, which retain them.

Unified Auditing consolidates all audit records into the AUDSYS schema and is enabled by relinking the Oracle binary, replacing the older split audit trails. It satisfies the stem as a genuine, currently supported Oracle audit type.

V$SQLSTATS provides statistics on SQL statements currently in the shared pool, aggregated by SQL_ID and plan hash value. It includes CPU_TIME, which is the cumulative CPU time consumed by the statement. This view is designed for efficient access to SQL statistics and is ideal for identifying high-CPU SQL in real time. It is updated frequently and is less resource-intensive than querying V$SQL directly for aggregation.

An SQL Profile stores auxiliary information, including optimizer hints and adjusted cardinality estimates, that the optimiser applies when parsing the statement, steering it toward the advisor's recommended plan without editing the SQL text or changing statistics.

By comparing estimated cardinality (from the optimizer) with actual rows (from runtime statistics) using DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST, you can see if the optimizer's estimates are significantly off. If the estimates are inaccurate, it suggests stale or missing statistics. This directly confirms whether statistics are the cause of the suboptimal plan.

V$SQLAREA aggregates statistics per SQL statement across all cursors sharing the same text, including CPU usage, so it ranks the top statements resident in memory by CPU consumption. That matches the requirement to identify the highest-CPU SQL currently in memory.