OCP Practice Test 18 — 15 Questions

Question 1

You have a query that is being incorrectly optimized because the statistics are not representative of the actual data distribution. What is the best method to handle this without changing the global statistics?

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

Accepted Answer

Use DBMS_STATS.SET_TABLE_PREFS. Table-specific statistics collection with specific parameters or using preference settings can help.

Answer

Run the SQL Tuning Advisor

Answer

Modify the init.ora parameter

Answer

Create a SQL Plan Baseline

Question 2

Which THREE features are enabled in 23ai to simplify database management?

Accepted Answer

AI Vector Search. AI Vector Search (B) is correct because Oracle 23ai introduces native VECTOR data type, vector indexes, and vector_distance functions, letting DBAs store and query embeddings directly in the database without a separate vector store, which simplifies AI-driven management and search workloads. JSON Duality (C) is correct because 23ai provides JSON Relational Duality Views, allowing the same underlying relational data to be accessed as JSON documents or as relational tables, eliminating the need to maintain duplicate schemas and simplifying application and data management. Lock-Free Reservations (D) is correct because 23ai enables concurrent transactions to reserve row capacity without blocking on row locks, reducing lock contention and deadlocks and thereby simplifying high-concurrency transaction management. Flashback Data Archive (A) is not one of the three new 23ai simplification features highlighted here, as it is a long-established Total Recall capability for tracking historical data, and Tablespace encryption (E) is likewise a pre-existing Transparent Data Encryption feature for data-at-rest protection, not a new 23ai management-simplification feature.

Answer

Flashback Data Archive

Answer

Tablespace encryption

Question 3

Which TWO files are modified to configure a secured listener?

Accepted Answer

listener.ora. Option A, listener.ora, is correct because it defines the listener's protocol addresses and static service registrations, and it is where you add the TCPS (TCP with SSL/TLS) protocol address and the SECURE_PROTOCOL_* / SSL_CLIENT_AUTHENTICATION parameters needed to enable a secured listener. Option E, sqlnet.ora, is correct because it holds the client/server-side Oracle Net security settings such as WALLET_LOCATION, SSL_VERSION, SSL_CIPHER_SUITES, and SSL_CLIENT_AUTHENTICATION, which the listener reads to negotiate encrypted connections. Option B, spfile.ora, is not correct because it stores instance initialization parameters, not listener or Oracle Net security configuration. Option C, init.ora, is not correct because it is a legacy instance parameter file and does not configure listener security. Option D, tnsnames.ora, is not correct because it only maps net service names to connect descriptors for clients, not the listener's secured configuration.

Answer

spfile.ora

Answer

init.ora

Answer

tnsnames.ora

Question 4

When running parallel DML in 23ai, what happens to the data if the transaction fails?

Accepted Answer

The entire transaction is rolled back. Parallel DML operations are fully atomic and follow standard rollback procedures.

Answer

Partial data is committed

Answer

Only the DML part is rolled back

Answer

Requires a manual cleanup

Question 5

You want to backup the control file automatically whenever a change is made to the database structure. Which command do you use?

Accepted Answer

CONFIGURE CONTROLFILE AUTOBACKUP ON. CONFIGURE CONTROLFILE AUTOBACKUP ON is the correct RMAN command to enable automatic control file and SPFILE backups whenever the database structure changes or a backup is performed. This setting is persistent in the RMAN configuration and ensures the control file is backed up to the configured autobackup location (FRA or a specified format).

Answer

BACKUP CONTROLFILE ON

Answer

ALTER SYSTEM SET CONTROLFILE_AUTOBACKUP=TRUE

Answer

SET CONTROLFILE AUTOBACKUP ON

Question 6

Your Oracle Database 23ai instance is running on an Exadata platform. You have a large table with a column of type VECTOR storing 768-dimensional embeddings. Users frequently run similarity searches using the VECTOR_DISTANCE function with the COSINE metric. To improve query performance, you create a vector index. After creation, you notice that some queries do not use the index and perform full table scans. What is the most likely reason for this behavior?

Accepted Answer

The queries are using a distance metric that differs from the one specified when the vector index was created, so the index cannot be used.. Vector indexes are created with a specific distance metric. If a query uses a different metric in VECTOR_DISTANCE, the optimizer cannot use the index and resorts to a full table scan. Ensuring that the query's metric matches the index's metric is essential for index usage.

Answer

The vector index is automatically disabled after any DML operation on the table and must be rebuilt before it can be used again.

Answer

The VECTOR column is defined with the FLOAT64 format, which is not supported by vector indexes.

Answer

The vector index is created with the default accuracy parameter, which may cause the optimizer to choose a full table scan for certain queries.

Question 7

How does Memoptimized Write affect data integrity?

Accepted Answer

It maintains ACID compliance. Memoptimized Write is fully compliant with ACID properties.

Answer

It bypasses ACID compliance

Answer

It uses eventual consistency

Answer

It is only for non-critical data

Question 8

You are the lead DBA for a CDB named PRODCDB that contains a PDB named SALESPDB. A regulatory audit requires that SALESPDB be opened in READ ONLY mode for the next 48 hours, while all other PDBs in the CDB must remain fully available for normal application traffic. You connect to the root container as SYSDBA and execute: ALTER PLUGGABLE DATABASE SALESPDB OPEN READ ONLY; The command completes successfully. Which statement describes the resulting state of SALESPDB?

Accepted Answer

SALESPDB is open in READ ONLY mode, which allows queries against its data but blocks any DML or DDL changes until it is reopened in READ WRITE mode.. Opening a specific PDB in READ ONLY mode is done with ALTER PLUGGABLE DATABASE <pdb> OPEN READ ONLY executed from the root. The named PDB becomes queryable but not modifiable, while other PDBs and the root retain their current open modes. Because the statement scopes to SALESPDB only, normal application traffic to other PDBs continues uninterrupted, satisfying the audit requirement without a CDB-wide outage.

Answer

SALESPDB is open in READ ONLY mode, but only for the current instance; any other instance in the CDB will still have SALESPDB open in READ WRITE mode.

Answer

SALESPDB remains in MOUNTED state because a PDB cannot be opened in READ ONLY mode until the CDB root is first restarted in READ ONLY mode.

Answer

SALESPDB is opened in READ ONLY mode, and because the CDB root is still READ WRITE, users connecting to SALESPDB can still perform DML in the root container only.

Question 9

A financial services company runs an Oracle Database 23ai application that performs high-volume, concurrent inserts into a transaction history table. The DBA observes significant buffer busy waits and wants to reduce contention without changing the application. Which 23ai feature should be implemented?

Accepted Answer

Memoptimized Rowstore – Fast Ingest. Memoptimized Rowstore – Fast Ingest is specifically designed to handle high-volume, concurrent inserts by buffering them in memory and writing to disk asynchronously, thereby reducing buffer busy waits. The other options do not directly address the insert contention issue described.

Answer

Hybrid Columnar Compression

Answer

Automatic Indexing

Answer

Real Application Clusters (RAC) with services

Question 10

What is the standard tool to manage CDB/PDB configuration in 23ai?

Accepted Answer

SQL*Plus. SQL*Plus or SQLcl are the standard interfaces for managing CDBs.

Answer

SQL Developer

Answer

Net Manager

Answer

OEM

Question 11

You are performing a SQL tuning exercise. How do you create a SQL Tuning Set (STS) to be used as input for the SQL Tuning Advisor?

Accepted Answer

DBMS_SQLTUNE.CREATE_SQLSET. DBMS_SQLTUNE.CREATE_SQLSET is the required procedure to initialize an STS container.

Answer

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

Answer

DBMS_SPM.CREATE_BASELINE

Answer

DBMS_STATS.CREATE_STAT_TABLE

Question 12

Which THREE items are generally included in an Oracle Patch?

Accepted Answer

Security updates. An Oracle Patch is a collection of fixes applied to the Oracle software, and it generally includes security updates (B), which address known vulnerabilities such as those documented in Oracle Critical Patch Updates; bug fixes (C), which resolve defects in the Oracle binaries and code; and performance improvements (D), which optimize the database or middleware behavior. These three are standard components because Oracle bundles them into patch sets, PSUs, and CPUs to keep the software secure, stable, and efficient. Application source code (A) is not part of an Oracle Patch, since patches modify Oracle's own software, not customer applications. New hardware drivers (E) are also not included, as Oracle Patches target Oracle software components rather than operating system or hardware driver updates.

Answer

Application source code

Answer

New hardware drivers

Question 13

Which THREE things should be checked before upgrading to 23ai?

Accepted Answer

Current patch levels. Option A (Current patch levels) is correct because Oracle requires the source database to be at a supported patch level (for example, 19c with the latest Release Update) before running the 23ai upgrade, and the upgrade utility will abort if the required patches are missing. Option B (Sufficient temporary tablespace) is correct because the upgrade process performs large sort and hash operations that need adequate TEMP space; insufficient temporary tablespace causes ORA-01652 errors and can fail the upgrade. Option C (Database backup) is correct because a full, tested backup (for example, an RMAN backup plus a guaranteed restore point) is mandatory so the database can be restored if the upgrade fails or must be rolled back. Option D (Table indexes) is not a pre-upgrade requirement, since indexes are automatically maintained and rebuilt as needed by the upgrade scripts. Option E (Application user passwords) is not checked before upgrading, as passwords are preserved in the data dictionary and are unaffected by the 23ai upgrade itself.

Answer

Table indexes

Answer

Application user passwords

Question 14

You are diagnosing a performance issue where a specific SQL statement is consuming excessive CPU. You have identified the SQL_ID and want to gather detailed execution statistics for this statement to understand its behavior. Which two methods can you use to obtain execution statistics for this SQL statement? (Choose two.)

Accepted Answer

Use DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format.. DBMS_SQL_MONITOR.REPORT_SQL_MONITOR and DBMS_XPLAN.DISPLAY_CURSOR with 'ALLSTATS LAST' are both valid methods to obtain detailed execution statistics for a specific SQL statement. SQL Monitor provides a comprehensive report including wait events and row source statistics, while DISPLAY_CURSOR with ALLSTATS LAST shows the execution plan with actual runtime statistics per operation.

Answer

Query V$SQL_PLAN_STATISTICS_ALL for the SQL_ID.

Answer

Query DBA_HIST_SQLSTAT for the SQL_ID.

Answer

Query V$SQLSTATS for the SQL_ID.

Question 15

A developer is creating a table with a lock-free reservation column to handle high-concurrency updates. The column is defined as NUMBER(5,0). After many transactions, the value reaches its maximum. What happens when a transaction attempts to reserve beyond the maximum?

Accepted Answer

The transaction receives an ORA-01438 value larger than specified precision allowed for this column error.. When a lock-free reservation would cause a numeric column to exceed its defined precision, Oracle raises ORA-01438. This error indicates that the value is larger than the specified precision allows. The transaction fails, and the reservation is not applied, preserving data integrity. This behavior is consistent with standard numeric column constraints and ensures that the column's limits are respected even under high concurrency.

Answer

The reservation succeeds but the value wraps around to the minimum.

Answer

The reservation is automatically converted to a standard update and succeeds.

Answer

The transaction receives an ORA-00001 unique constraint violated error.