OCP Practice Test 10 — 10 Questions

Question 1

How is AI Vector Search integrated into SQL?

Accepted Answer

Using standard SQL syntax. AI Vector Search in Oracle 23ai is integrated directly into SQL using standard SQL syntax, allowing developers to store vectors in VECTOR columns and query them with functions like VECTOR_DISTANCE alongside regular relational predicates. This means no external API calls or new languages are needed — you can write SELECT statements that combine vector similarity search with traditional WHERE clauses. This tight integration is a key design goal of 23ai's vector capabilities.

Answer

Via external API calls

Answer

Via a new programming language

Answer

Via PL/SQL only

Question 2

Which THREE characteristics are unique to PDBs?

Accepted Answer

Dedicated local users. In a multitenant architecture, each pluggable database (PDB) can have its own dedicated local users (Option A), meaning user accounts created inside a PDB are scoped to that PDB and are not visible in other PDBs or the root, which is a defining characteristic of PDBs. Each PDB also owns its own independent datafiles (Option C), stored in its own tablespace set, so its physical storage is isolated from other PDBs even though they share the same instance. Additionally, each PDB is exposed through its own individual services (Option E), allowing clients to connect directly to a specific PDB by service name rather than to the container database as a whole. By contrast, shared redo logs (Option B) and shared background processes (Option D) are not unique to PDBs; in a multitenant container database, all PDBs share the same redo logs and the same set of background processes of the single instance, so these are characteristics of the CDB rather than distinguishing features of individual PDBs.

Answer

Shared redo logs

Answer

Shared background processes

Question 3

You are investigating high CPU utilization in the database. Which view provides the top SQL statements currently in memory based on CPU usage?

Accepted Answer

V$SQLAREA. V$SQLAREA contains aggregated statistics for SQL statements in the library cache, including CPU_TIME.

Answer

V$SESSION

Answer

V$SYSSTAT

Answer

V$LIBRARYCACHE

Question 4

Which THREE factors influence the optimizer's choice of an execution plan?

Accepted Answer

Optimizer statistics. The optimizer considers statistics, system parameters, and schema objects.

Answer

The current time of day

Answer

User password strength

Question 5

Which TWO of the following statements about True Cache are correct?

Accepted Answer

It resides in the application tier. Option D is correct because Oracle True Cache is deployed as a separate, lightweight instance that runs in the application tier, close to the application servers, so it can serve queries with minimal network latency. Option E is correct because True Cache is fundamentally a read-only, in-memory cache that serves consistent read queries and does not accept DML; write operations continue to go to the primary database. Option A is incorrect because True Cache does not replace Data Guard — Data Guard remains Oracle's disaster-recovery and data-protection solution, and True Cache is complementary to it. Option B is incorrect because True Cache does not require a dedicated database; it is an additional cache instance associated with the primary database, not a separate standalone database deployment. Option C is incorrect because True Cache does not support DML operations; it only serves read-only queries, with all DML directed to the primary database.

Answer

It replaces Data Guard

Answer

It requires a dedicated database

Answer

It supports DML operations

Question 6

You have a 19c database. Which tool must you run to ensure the database is ready for an upgrade to 23ai?

Accepted Answer

preupgrade.jar. The Pre-Upgrade Information Tool (preupgrade.jar) is mandatory for evaluating the source database.

Answer

utlrp.sql

Answer

dbv

Answer

catupgrd.sql

Question 7

Which of these is the most effective way to address performance issues caused by suboptimal plans?

Accepted Answer

SQL Plan Management. SQL Plan Management is specifically designed to manage and enforce optimal plans.

Answer

Dropping and recreating the database

Answer

Increasing the number of CPUs

Answer

Disabling indexes

Question 8

Which THREE actions can you take to improve execution plan stability?

Accepted Answer

Use SQL Profiles. SQL Profiles (C) improve plan stability by supplying optimizer hints and statistics adjustments that steer the optimizer toward a proven plan without changing application SQL. Freezing execution plans (D) locks a specific plan for a SQL statement so the optimizer cannot switch to a different, potentially worse plan. SQL Plan Baselines (E) capture and enforce known-good plans, allowing the optimizer to use only plans in the baseline and preventing regressions. Dropping and recreating indexes (A) is disruptive and can cause plan changes and instability, not stability. Increasing the SGA size daily (B) is arbitrary and does not directly stabilize execution plans.

Answer

Frequently drop and recreate indexes

Answer

Increase the SGA size every day

Question 9

Which TWO statements are true regarding the use of a Recovery Catalog?

Accepted Answer

It can be used to store backup scripts.. Option A is correct because the RMAN recovery catalog can store backup scripts (persistent named scripts) that can be executed against registered target databases, allowing centralized script management. Option D is correct because a single recovery catalog schema can register multiple target databases and store their backup metadata, including backup sets, image copies, archived logs, and datafile information. Option B is incorrect because RMAN can perform backups using only the target database control file as the repository; the recovery catalog is optional. Option C is incorrect because the recovery catalog resides in a separate database (or schema), not inside the target database, to protect metadata if the target is lost. Option E is incorrect because control file autobackups are still recommended and can be used even with a recovery catalog, especially to recover the catalog or when the catalog is unavailable.

Answer

It is required to perform RMAN backups.

Answer

It is physically located inside the target database.

Answer

It eliminates the need for control file autobackups.

Question 10

You are monitoring parallel DML performance. Which view provides the best insight into current parallel server activity?

Accepted Answer

V$PX_SESSION. V$PX_SESSION provides real-time information on parallel sessions.

Answer

V$DATABASE

Answer

V$SESSION

Answer

V$PARALLEL_DEGREE