OCP Practice 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?

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

Question 11

Which THREE operations are automatically performed by RMAN when executing the 'RECOVER TABLE' command?

Accepted Answer

Exporting the recovered table to the target database using Data Pump. RECOVER TABLE is an RMAN command that performs a table-level point-in-time recovery by leveraging an auxiliary instance and a temporary auxiliary destination. Option B is correct because RMAN restores the tablespace(s) containing the target table to an auxiliary location (the auxiliary destination) rather than to the production datafiles. Option D is correct because RMAN automatically creates an auxiliary instance (spawned from the target database) to perform the recovery work. Option A is correct because, after the table is recovered in the auxiliary instance, RMAN uses Data Pump to export the table and then imports it into the target database. Option C is incorrect because RECOVER TABLE does not delete backups older than the restore point; backup retention is governed by the configured retention policy, not by this command. Option E is incorrect because RMAN never automatically converts the database to NOARCHIVELOG mode; ARCHIVELOG mode is required for this operation and is not changed by RECOVER TABLE.

Answer

Automatic deletion of all backups older than the restore point

Answer

Automatic conversion of the database to NOARCHIVELOG mode

Question 12

You are performing a cross-platform data migration from a Linux-based Oracle 23ai source database to a Solaris target. You intend to use RMAN CONVERT DATABASE. Which prerequisite must be met to ensure the migration succeeds?

Accepted Answer

The source and target platforms must have the same endian format.. When converting a database using RMAN, the source and target platforms must have the same endian format, or the conversion must be done via transportable tablespaces if the endianness differs. RMAN CONVERT DATABASE requires the platforms to share endianness.

Answer

The source database must use Automatic Storage Management (ASM).

Answer

The target database must be in ARCHIVELOG mode before starting the conversion.

Answer

The source database must be in restricted mode.

Question 13

Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?

Accepted Answer

AWR Report. The AWR (Automatic Workload Repository) Report includes a section titled 'SQL ordered by Elapsed Time' that lists SQL statements ranked by total elapsed time, making it the correct choice. This report aggregates performance statistics over a snapshot interval, capturing the cumulative impact of each SQL statement. The Alert Log and Listener Log do not provide SQL-level performance summaries, and ADDM focuses on diagnostic findings rather than raw SQL timing.

Answer

Alert Log

Answer

Listener Log

Answer

ADDM Report

Question 14

Which THREE types of information are contained in the PDB XML metadata file?

Accepted Answer

Datafile locations. The PDB XML metadata file (the XML file generated alongside a PDB, e.g., by the PDB metadata API or DBMS_PDB.DESCRIBE) records structural and configuration details of the pluggable database, not its business data. Option B is correct because the file lists the datafile locations (the paths of the PDB's datafiles, such as the SYSTEM, SYSAUX, and user tablespaces) so the PDB can be plugged in or cloned on another CDB. Option C is correct because the PDB GUID (the globally unique identifier assigned to the PDB) is stored in the metadata so the CDB can uniquely identify and track the plugged-in database. Option E is correct because the database character set (and national character set) of the PDB is captured in the XML, which is essential for compatibility checks when plugging the PDB into a new CDB. Option A is not part of the XML metadata — user credentials are stored in the data dictionary (e.g., SYS.USER$), not in the PDB description file. Option D is also incorrect because the XML contains only metadata about the PDB's structure and configuration, never the actual table rows, which reside in the datafiles.

Answer

User passwords

Answer

Full table data

Question 15

In 23ai, when using the 'JSON relational duality' feature, how are changes to the JSON document propagated to the underlying tables?

Accepted Answer

Automatically propagated by the database. JSON Relational Duality in Oracle 23ai provides automatic bidirectional synchronization between JSON documents and their underlying relational tables. When a JSON document is updated through a duality view, the database automatically propagates the changes to the base tables using the defined mappings. No manual re-synchronization, triggers, or stored procedures are required.

Answer

Only allowed via stored procedures

Answer

Manual re-synchronization

Answer

Requires a trigger

Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) OCP practice test

Three ways to study

Study OCP by domain

Study OCP by topic

Performance Tuning And Diagnostics practice questions

Core Administration And Cdb Pdb Architecture practice questions

23ai New Features practice questions

Security And Upgrades practice questions

Backup And Recovery practice questions

OCP fundamentals practice questions

OCP scenario practice questions

OCP troubleshooting practice questions

Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) (OCP) practice questions