OCP Exam Questions and Answers

Automatic Workload Repository (AWR) report

The AWR report captures snapshots of database activity, including SQL statistics ranked by CPU consumption, over a specified interval such as the last hour. This directly identifies the top CPU-consuming SQL statements, unlike real-time tools that lack historical aggregation.

SQL Access Advisor

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.

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE captures the existing optimal plan from the shared pool and loads it into a SQL plan baseline, which pins that plan so the optimizer cannot substitute a different one on later executions. This directly satisfies the requirement to lock the current plan against change.

V$SQL_PLAN

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.

Query V$ACTIVE_SESSION_HISTORY

V$ACTIVE_SESSION_HISTORY samples active sessions every second, retaining recent history in memory, so it exposes which sessions blocked others during the last 10 minutes. This directly satisfies the stem's requirement to pinpoint blocked sessions within that short window.

To identify performance bottlenecks and provide recommendations

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.

V$PDBS

V$PDBS lists every pluggable database within a container database, including its open mode and restricted status, directly satisfying the requirement to report open status across all PDBs. Querying it from the CDB root returns one row per PDB, unlike V$DATABASE, which describes only the current container.

DBMS_PDB.CHECK_PLUG_COMPATIBILITY

DBMS_PDB.CHECK_PLUG_COMPATIBILITY validates a non-CDB's data dictionary against the target CDB before migration, reporting violations that would block plugging. It satisfies the stem's requirement to detect compatibility issues prior to starting, unlike DBMS_PDB.DESCRIBE, which only generates the XML manifest without performing any compatibility checks.

DBMS_PDB.CHECK_PLUG_COMPATIBILITY

DBMS_PDB.CHECK_PLUG_COMPATIBILITY validates a non-CDB's data dictionary against 23ai CDB requirements before plugging, reporting violations that would block migration. It satisfies the stem's pre-migration compatibility assessment constraint, unlike DBMS_PDB.DESCRIBE (metadata only) or transportable tablespace checks, which do not evaluate full plug-in readiness.

To act as a template for new PDBs

PDB$SEED is a read-only template containing the system and sysaux datafiles that Oracle copies when you issue CREATE PLUGGABLE DATABASE. It supplies the starting file structure for every new PDB, matching the stem's template purpose.

ALTER PLUGGABLE DATABASE UNPLUG

UNPLUG writes the PDB's metadata into its own XML manifest and closes it, letting you move the data files and plug them into a different CDB via CREATE PLUGGABLE DATABASE. This satisfies the no-Data-Pump constraint by relocating files directly.

Opens all PDBs in read-write mode

Issuing this command opens every pluggable database whose current open mode is mounted, transitioning each to read-write, provided the CDB itself is open. It satisfies the requirement to bring all PDBs online simultaneously without naming them individually, respecting each PDB's saved state and any instance-restriction settings.

VECTOR

The VECTOR data type is mandatory for storing vector embeddings in Oracle Database 23ai; it holds the dimensional float array and supports the distance functions and indexes used by AI Vector Search. No other type satisfies the stem's storage requirement.

Rows remain immutable until deleted manually

Rows past the 'no drop' period stay immutable; the retention lock only prevents deletion, it never authorises it. Oracle Blockchain Tables expose no automatic purge, so rows persist until an explicit DELETE runs. This satisfies the stem's default-behaviour constraint: expiry of the no-drop window merely permits deletion, it does not trigger it.

HNSW

HNSW builds a hierarchical navigable small-world graph, enabling logarithmic-time approximate nearest-neighbour traversal instead of exhaustive comparison. This directly addresses the slow similarity search by pruning the candidate set during query, satisfying the performance constraint. Unlike flat or IVF indexes, it delivers low-latency recall without scanning every vector.

Memoptimized Write

Memoptimized Write buffers inserts in a dedicated memory area and flushes them to disk in batches, bypassing much of the per-row redo and buffer overhead. This directly satisfies the minimal-overhead requirement for high-frequency single-table inserts, unlike conventional row-by-row paths.

Reduced row-level locking contention

Lock-Free Reservations let concurrent transactions reserve column values without holding row-level locks, eliminating the blocking waits that serialise inserts and updates. This directly reduces row-level locking contention, the bottleneck the stem identifies in high-concurrency workloads.

It resides in the application tier

True Cache runs as a separate Oracle instance in the application tier, close to the application servers rather than alongside the primary database. This placement reduces network round-trip latency for read queries while the primary database remains the single source of truth.

It is a read-only cache

True Cache is strictly read-only: it serves queries but never accepts writes. All DML is routed to the primary database, and the cache refreshes itself automatically from the primary, keeping data current without any application-side write logic.

V$ENCRYPTED_TABLESPACES

V$ENCRYPTED_TABLESPACES lists every tablespace with its encryption status, so querying it directly satisfies the requirement to verify which tablespaces are encrypted in 23ai. It reports the ENCRYPTED column plus the encryption algorithm, giving authoritative confirmation without inspecting datafiles or wallet configuration.

Oracle Wallet

Oracle Wallet stores and manages the TDE master encryption key, which is mandatory for column and tablespace encryption in 23ai. Without this keystore, the database cannot encrypt or decrypt data, so it directly satisfies the stem's requirement for implementing Transparent Data Encryption.

Simplified audit management and unified audit trail

Unified Auditing merges traditional and fine-grained auditing into a single configuration and writes all records to one unified audit trail, eliminating separate audit configurations and their scattered trails. This delivers simplified audit management, the primary benefit sought.

Physical Standby Database

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.

Pre-Upgrade Information Tool

The Pre-Upgrade Information Tool directly satisfies the requirement to identify incompatible parameters before upgrading. It analyses the source database and reports deprecated and obsolete initialisation parameters, along with other pre-upgrade fix-ups, producing a detailed report that must be reviewed and resolved prior to running the upgrade itself.

PASSWORD_VERIFY_FUNCTION

PASSWORD_VERIFY_FUNCTION names a PL/SQL function that Oracle invokes during password changes, letting you enforce bespoke complexity rules beyond the built-in limits. Referencing your custom function in the profile clause satisfies the requirement to apply organisation-specific verification logic to every user assigned that profile.

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE SBT;

This policy instructs RMAN to delete archived redo logs only after two successful SBT backups, matching the stem's twice-to-tape requirement. The BACKED UP n TIMES TO DEVICE TYPE clause is the precise mechanism controlling automatic archivelog deletion.

CREATE CATALOG;

CREATE CATALOG initialises the recovery catalog schema and its internal packages inside the nominated schema. Until this runs, the catalog contains no repository structures, so REGISTER DATABASE cannot store target metadata. It is the mandatory first step before any target registration.

To revert the entire database to a point in time

Flashback Database uses flashback logs to return the entire database to an earlier point in time, reversing block-level changes without a full restore. This whole-database rewind is its primary purpose, distinct from object-level or row-level flashback features.

V$RECOVERY_FILE_DEST

V$RECOVERY_FILE_DEST reports the Fast Recovery Area's configured location, disk quota limit, space used, and space reclaimable, directly satisfying the need to check current space usage. Querying it reveals whether the recovery area has filled to its quota, which commonly causes RMAN's inability to access it.

Datafiles

Datafiles store blocks in the source platform's endian format, so RMAN CONVERT must translate their byte order before the destination can open them. The differing endianness named in the stem is precisely what makes conversion mandatory for these files.

Row movement must be enabled on the table.

Enabling row movement lets Oracle reassign a row's physical location during undo-based restoration, which Flashback Table requires to return dropped rows to their original blocks. Without it, the command fails with ORA-08189, so row movement is the mandatory prerequisite the stem demands before execution.