OCP Practice Test 1 — 10 Questions

Question 1

You observe a significant performance regression after a database upgrade. You suspect that the optimizer has chosen a suboptimal execution plan. Which component of SQL Plan Management should you use to ensure the database only uses known, verified execution plans?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

SQL Plan Baselines. SQL Plan Baselines are used to capture and freeze known good execution plans for SQL statements. When the optimizer considers a new plan, it compares it to the baseline; if the new plan is not verified as better, the baseline plan is used. This ensures that only verified plans are used, preventing performance regressions after an upgrade.

Answer

Optimizer Statistics Advisor

Answer

SQL Tuning Sets

Answer

Automatic SQL Tuning Advisor

Question 2

Which of the following is true regarding SQL Plan Baselines?

Accepted Answer

They provide stability for SQL execution plans. They allow the database to maintain a stable execution plan even if the environment (stats, indexes) changes.

Answer

They automatically gather statistics

Answer

They store the actual table data

Answer

They require the Oracle Enterprise Manager

Question 3

You need to perform a full database restore and recovery. The control file is lost. What is the correct sequence of operations?

Accepted Answer

STARTUP NOMOUNT; RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS;. After losing the control file, you must restore it from an autobackup before you can restore the datafiles and perform recovery.

Answer

RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS;

Answer

RESTORE SPFILE; STARTUP MOUNT; RECOVER DATABASE; ALTER DATABASE OPEN;

Answer

FLASHBACK DATABASE TO RESTORE POINT; ALTER DATABASE OPEN;

Question 4

What happens if a Lock-Free Reservation numeric value reaches its maximum allowed limit?

Accepted Answer

It raises an error. Lock-Free Reservation columns in Oracle use a bounded numeric range, and when the accumulated value hits the maximum allowed limit, Oracle raises an error rather than silently wrapping or continuing. This is by design to preserve data integrity — the reservation mechanism cannot represent values beyond its defined ceiling. The error forces the application or DBA to intervene (e.g., reset, archive, or widen the column) before further reservations can be made.

Answer

It wraps around to 0

Answer

It allows further updates anyway

Answer

It switches to locking mode

Question 5

Which file needs to be updated when you move the wallet location in 23ai?

Accepted Answer

sqlnet.ora. The sqlnet.ora file contains the WALLET_LOCATION parameter that must be updated.

Answer

init.ora

Answer

tnsnames.ora

Answer

listener.ora

Question 6

When using 'Snapshot Carousel', how many snapshots can a single PDB retain?

Accepted Answer

Defined by MAX_PDB_SNAPSHOTS. The number is managed by the MAX_PDB_SNAPSHOTS parameter.

Answer

Fixed at 8

Answer

Controlled by the OS

Answer

Unlimited

Question 7

Which THREE factors influence the duration of a database upgrade?

Accepted Answer

Number of database objects. The duration of a database upgrade is directly driven by the volume of work the upgrade engine must process, so the number of database objects (A) matters because each object (tables, indexes, constraints, procedures) must be parsed, converted, and recompiled, and more objects mean more metadata operations. System resources (B), specifically CPU and I/O throughput, determine how fast those operations execute, since an upgrade is largely CPU-bound for compilation and I/O-bound for data and log writes, so faster resources shorten the elapsed time. Database size (E) is also a primary factor because larger data volumes require more time for data conversion, index rebuilds, and log generation during the upgrade. The color of the server (C) is irrelevant since physical appearance has no bearing on performance, and the number of users (D) is not a direct factor in upgrade duration because the upgrade is typically performed with the database in a restricted or single-user mode, so concurrent user count does not affect how long the upgrade itself takes.

Answer

The color of the server

Answer

Number of users

Question 8

What is the default behavior when a session is set to use True Cache?

Accepted Answer

Transparent read routing. The session automatically routes queries to the cache if valid.

Answer

Manual route selection

Answer

Requires a DB restart

Answer

Error if cache is empty

Question 9

A PDB named FINPDB in your CDB is currently open in READ WRITE mode and is actively serving users. You need to perform a planned maintenance operation that requires all user sessions to be disconnected and the PDB to be unavailable for new connections, but you want the operation to complete as quickly as possible without waiting for sessions to end naturally. Which command should you use?

Accepted Answer

ALTER PLUGGABLE DATABASE FINPDB CLOSE IMMEDIATE;. CLOSE IMMEDIATE is designed for planned maintenance where sessions must be terminated quickly and the PDB must become unavailable. It rolls back active transactions, disconnects users, and performs a checkpoint so the PDB can be reopened cleanly. A plain CLOSE waits indefinitely for sessions, CLOSE ABORT skips the checkpoint and risks recovery, and SAVE STATE only preserves open mode across instance restarts.

Answer

ALTER PLUGGABLE DATABASE FINPDB CLOSE ABORT;

Answer

ALTER PLUGGABLE DATABASE FINPDB SAVE STATE;

Answer

ALTER PLUGGABLE DATABASE FINPDB CLOSE;

Question 10

You have a query that is performing poorly due to a stale execution plan. You want to lock the current optimal plan to prevent the optimizer from changing it during subsequent runs. What should you do?

Accepted Answer

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE loads existing plans from the cursor cache into SQL Plan Baselines, allowing you to lock the current optimal plan. This prevents the optimizer from changing it because the baseline will be used.

Answer

Run DBMS_STATS.LOCK_TABLE_STATS

Answer

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Answer

Use SQL Tuning Advisor to accept a new profile