OCP Practice Exam 2 — 20 Questions

Question 1

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

Accepted Answer

It raises an error. The database prevents further updates until the value is reset.

Answer

It wraps around to 0

Answer

It allows further updates anyway

Answer

It switches to locking mode

Question 2

What is the primary function of the RMAN 'SWITCH' command?

Accepted Answer

To update the control file to point to a new datafile copy.. The SWITCH command updates the control file to point to a new location for datafiles, typically after a copy has been restored.

Answer

To move the database to a new server.

Answer

To switch between primary and standby.

Answer

To switch the log file.

Question 3

When configuring Parallel DML in 23ai, which parameter must be set to 'ALWAYS' to ensure parallel execution for DML statements without hints?

Accepted Answer

ENABLE_PARALLEL_DML. Setting ENABLE_PARALLEL_DML to ALWAYS ensures that all DML operations are executed in parallel if possible.

Answer

PARALLEL_DEGREE_LIMIT

Answer

DML_PARALLEL_MODE

Answer

PARALLEL_EXECUTION_ENABLED

Question 4

You want to use Memoptimized Write to speed up data ingestion. Which type of table should you use?

Accepted Answer

Heap-organized table. Memoptimized Write is specifically enabled on tables that have the MEMOPTIMIZE FOR WRITE clause.

Answer

External table

Answer

Global temporary table

Answer

Index-organized table

Question 5

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

Accepted Answer

AI Vector Search. JSON Duality, Lock-Free Reservations, and Vector Search are key 23ai management/performance features.

Answer

Flashback Data Archive

Answer

Tablespace encryption

Question 6

Which TWO RMAN settings significantly improve performance during backup operations on an Exadata system?

Accepted Answer

CONFIGURE BACKUP OPTIMIZATION ON;. Backup optimization and parallelism settings directly impact throughput on high-performance storage.

Answer

CONFIGURE CHANNEL ... RATE 10M;

Answer

CONFIGURE MAXSETSIZE UNLIMITED;

Answer

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

Question 7

Which THREE conditions must be met to perform a successful RMAN cross-platform transportable tablespace migration?

Accepted Answer

The RMAN CONVERT command must be used if endianness differs.. The databases must be at compatible levels, endian formats must be checked, and the tablespace must be read-only.

Answer

The source platform must be the same endian as the target.

Answer

The source and destination databases must have the same DBID.

Question 8

You need to identify the top SQL statements consuming the most CPU resources in an Oracle 23ai database over the last hour. Which tool provides the most direct analysis of this performance issue?

Accepted Answer

Automatic Workload Repository (AWR) report. AWR reports provide aggregated data over a specific time range, making them ideal for identifying resource consumers.

Answer

SQL Tuning Advisor

Answer

Active Session History (ASH) report

Answer

SQL Plan Management

Question 9

How is AI Vector Search integrated into SQL?

Accepted Answer

Using standard SQL syntax. Vector search uses standard SQL operators like VECTOR_DISTANCE.

Answer

Via external API calls

Answer

Via a new programming language

Answer

Via PL/SQL only

Question 10

Which type of operation is ideal for Blockchain tables?

Accepted Answer

Audit logs and financial records. Blockchain tables are designed for audit trails where history must be preserved.

Answer

General configuration tables

Answer

Transient cache data

Answer

High-frequency temporary storage

Question 11

Which TWO features of 23ai help improve performance of high-concurrency workloads?

Accepted Answer

Lock-Free Reservations. Lock-Free Reservations and Memoptimized Write are specifically for high-concurrency improvements.

Answer

Wide tables

Answer

Blockchain tables

Answer

JSON Duality

Question 12

If you are in ARCHIVELOG mode, what is the primary benefit of having a multiplexed control file?

Accepted Answer

Redundancy against disk failure.. Multiplexing control files provides fault tolerance against disk failure of the control file location.

Answer

Faster backup performance.

Answer

Ability to perform point-in-time recovery.

Answer

Automatic database tuning.

Question 13

You have a vector index and notice search performance is slow. Which index type should you use to optimize similarity search?

Accepted Answer

HNSW. HNSW (Hierarchical Navigable Small World) index is the standard for efficient vector similarity search in 23ai.

Answer

Inverted Index

Answer

B-tree

Answer

Bitmap

Question 14

A user accidentally dropped a critical table. You decide to use Flashback Table. What is the mandatory prerequisite before this command can execute?

Accepted Answer

Row movement must be enabled on the table.. Row movement must be enabled for the table to move rows back to their original IDs, and flashback must be enabled.

Answer

The table must have a Primary Key.

Answer

The database must be in ARCHIVELOG mode.

Answer

The Recycle Bin must be disabled.

Question 15

You need to perform a table-level point-in-time recovery. Which RMAN command syntax is correct?

Accepted Answer

RECOVER TABLE HR.EMPLOYEES UNTIL SCN 12345;. RECOVER TABLE ... UNTIL SCN is the syntax for table-level recovery.

Answer

RESTORE TABLE ...

Answer

FLASHBACK TABLE ...

Answer

IMPORT TABLE ...

Question 16

You need to perform a Table Point-in-Time Recovery (TPITR) to restore a table to a state before a mistaken DROP TABLE operation. Which RMAN command performs this operation while minimizing impact to other tablespaces?

Accepted Answer

RECOVER TABLE ... UNTIL SCN ...;. The RECOVER TABLE command is specifically designed for table-level recovery, which creates an auxiliary instance automatically to extract the table data.

Answer

FLASHBACK TABLE ... TO SCN ...;

Answer

IMPORT FROM BACKUPSET ...;

Answer

RESTORE TABLE ... UNTIL SCN ...;

Answer

null

Question 17

What is the main requirement for an existing table to be converted into a Blockchain table?

Accepted Answer

Tables cannot be converted to Blockchain. A table cannot be converted to a Blockchain table; it must be created as such.

Answer

The table must have an index

Answer

The table must be partitioned

Answer

The table must be empty

Question 18

You are configuring a PDB to use Local Undo. What requirement must be met before setting the UNDO_TABLESPACE parameter?

Accepted Answer

The PDB must be closed. The CDB must be in local undo mode, and the PDB must be open in restricted mode or closed.

Answer

The CDB must be in read-only mode

Answer

The CDB must be in ARCHIVELOG mode

Answer

The PDB must be a proxy PDB

Question 19

In True Cache, how is consistency maintained with the primary database?

Accepted Answer

Asynchronous replication. True Cache uses the database's internal replication mechanisms to stay consistent.

Answer

Manual refreshes

Answer

Polling

Answer

Synchronous writes

Question 20

Which Oracle feature allows for verifying the integrity of Blockchain rows?

Accepted Answer

DBMS_BLOCKCHAIN_TABLE. The DBMS_BLOCKCHAIN_TABLE package provides verification functions.

Answer

Audit Vault

Answer

Data Guard

Answer

Flashback Query