OCP 23ai New Features — Timed 40 Questions

Question 1

You are migrating a legacy application to use Wide Tables in 23ai. What is the maximum number of columns supported by a Wide Table?

Accepted Answer

4096. Oracle 23ai Wide Tables support up to 4096 columns, a significant increase over the traditional 1000-column limit for standard tables. This allows schemas with many sparse attributes (common in IoT, telemetry, and JSON-like data) to be stored natively without resorting to EAV models or JSON columns. The 4096 limit is the documented maximum for Wide Tables in 23ai.

Answer

1000

Answer

8192

Answer

2048

Question 2

To enable lock-free reservations on a table, which clause must be used in the CREATE or ALTER TABLE statement?

Accepted Answer

LOCK-FREE RESERVATIONS. Oracle's lock-free reservations feature is enabled by adding the LOCK-FREE RESERVATIONS clause to a CREATE TABLE or ALTER TABLE statement. This clause activates the reservation mechanism that lets sessions reserve rows without holding row-level locks, improving concurrency for high-contention workloads. The exact syntax is required — no other keyword combination activates the feature.

Answer

ENABLE LOCK-FREE

Answer

RESERVATIONS ON

Answer

CONCURRENT DML

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

Which component allows True Cache to provide high performance for read-only workloads by caching data on the application tier?

Accepted Answer

True Cache. True Cache is a read-only cache that resides in the application tier to offload the primary database.

Answer

Result Cache

Answer

Data Guard

Answer

Oracle Connection Manager

Question 5

Which SQL function is used to calculate the similarity between two vectors?

Accepted Answer

VECTOR_DISTANCE(). VECTOR_DISTANCE is the standard operator for similarity search in 23ai.

Answer

COSINE_SIM()

Answer

DIST()

Answer

SIMILARITY()

Question 6

You are configuring Parallel DML and want to ensure the session uses the maximum available resources. Which hint should you use?

Accepted Answer

/*+ PARALLEL(table, n) */. The PARALLEL hint allows you to specify the degree of parallelism for a statement.

Answer

/*+ MAX_PARALLEL */

Answer

/*+ SERIAL */

Answer

/*+ DML_PARALLEL */

Question 7

Which parameter controls the behavior of the Memoptimized Write buffer size?

Accepted Answer

MEMOPTIMIZE_POOL_SIZE. The MEMOPTIMIZE_POOL_SIZE parameter manages the memory allocated for the fast ingest buffer.

Answer

DB_BUFFER_CACHE

Answer

LARGE_POOL_SIZE

Answer

FAST_INGEST_BUFFER

Question 8

When creating a Wide Table, what is the physical storage impact compared to a standard table?

Accepted Answer

It is more space-efficient for sparse data. Wide Tables are more space-efficient for sparse data because they use a compressed storage format that efficiently handles nulls and repeating values, reducing the storage footprint compared to a standard table with many columns. This makes them ideal for scenarios where many attributes are optional or sparsely populated.

Answer

It disables compression

Answer

Requires separate tablespaces

Answer

It uses more storage due to padding

Question 9

Which THREE actions can be performed on an HNSW vector index in 23ai?

Accepted Answer

ALTER INDEX ... REBUILD. In Oracle Database 23ai, an HNSW (Hierarchical Navigable Small World) vector index is created with the CREATE INDEX statement using the VECTOR indexing clause, so option C (CREATE INDEX) is correct. The index can be rebuilt to refresh its structure after data changes or parameter adjustments using ALTER INDEX ... REBUILD, making option B correct. HNSW indexes are also usable by the VECTOR_DISTANCE function for similarity searches, so option E is correct. Option A (DROP INDEX) is not among the marked correct answers here, and option D (PARTITION) is not a supported action for HNSW vector indexes in 23ai, as partitioning applies to other index types rather than HNSW vector indexes.

Answer

DROP INDEX

Answer

PARTITION

Question 10

When configuring Memoptimized Write, what is the role of the 'flush' operation?

Accepted Answer

It moves data to the data files. In Memoptimized Write, the flush operation moves buffered data from the in-memory write buffer to the persistent data files on disk. It ensures durability by writing the buffered rows to the database's datafiles, making them permanent and recoverable.

Answer

It validates data constraints

Answer

It clears the cache

Answer

It deletes the data

Question 11

Which TWO parameters are essential for configuring the environment for Wide Tables?

Accepted Answer

DB_BLOCK_SIZE. Wide tables rely on specific database settings to handle the large number of columns.

Answer

SQL_TRACE

Answer

PARALLEL_MAX_SERVERS

Answer

MAX_COLUMNS

Question 12

When configuring Lock-Free Reservations, what must be done to the column being updated?

Accepted Answer

It must be numeric. The column must be a numeric type enabled for reservations.

Answer

It must be a primary key

Answer

It must be in a partitioned table

Answer

It must be indexed

Question 13

In the context of Wide Tables, what is a recommended best practice?

Accepted Answer

Maintain logical data grouping. Maintaining logical data grouping is a recommended best practice for Wide Tables because it improves query performance and manageability by clustering related columns together, which can reduce I/O and improve compression. Wide Tables in Oracle are designed to store many columns, but grouping related data logically helps the database optimize storage and access paths.

Answer

Only use for read-only data

Answer

Disable all indexes

Answer

Use as many columns as possible

Question 14

When using parallel DML, which system parameter limits the degree of parallelism?

Accepted Answer

PARALLEL_DEGREE_LIMIT. PARALLEL_DEGREE_LIMIT restricts the degree of parallelism.

Answer

MAX_PARALLEL_THREADS

Answer

DB_PARALLEL_LIMIT

Answer

SESSION_PARALLEL_MAX

Question 15

Which of these is a requirement for using True Cache?

Accepted Answer

Support from the connection pool. True Cache requires a connection pool that supports True Cache features.

Answer

An exclusive server

Answer

Special SQL syntax

Answer

A manual cache update script

Question 16

When migrating to 23ai, what is the impact of switching to the VECTOR data type for existing applications?

Accepted Answer

Code updates for vector handling. Applications need to be updated to use the new native vector support.

Answer

No code changes required

Answer

Requires a new driver

Answer

Complete rewrite of SQL

Question 17

In 23ai, what does 'Wide Tables' refer to?

Accepted Answer

Tables with more than 1000 columns. Wide Tables allow for a significantly increased number of columns.

Answer

Tables with large rows

Answer

Tables with long text

Answer

Tables across multiple disks

Question 18

What is the main benefit of JSON Duality Views?

Accepted Answer

Simplified data access. It provides a bidirectional view of the data, both as JSON and as relational rows.

Answer

Automatic translation

Answer

Lower licensing costs

Answer

Faster storage

Question 19

A database administrator is working with a JSON collection in Oracle Database 23ai. They need to enforce that every document in the collection contains a field 'employeeId' and that its value is unique across all documents. Which approach should be used?

Accepted Answer

Define a JSON schema with a unique constraint on the employeeId property. Oracle Database 23ai allows defining JSON schemas for collections, including validation keywords like 'unique' on properties. This declaratively enforces that the employeeId field exists and is unique across documents, providing a native and efficient solution.

Answer

Use a database trigger to check for duplicates before insert

Answer

Create a unique index on the JSON field using a JSON search index

Answer

Create a materialized view that computes uniqueness

Question 20

You are designing a new application on Oracle Database 23ai that will use AI Vector Search. The application needs to store vector embeddings for product descriptions and perform similarity searches. Which two statements are true about the VECTOR data type and vector indexes in 23ai? (Choose two.)

Accepted Answer

The VECTOR data type can store vectors with up to 65,535 dimensions, and the dimension count must be specified at column creation time if the column is not defined as flexible.. The VECTOR data type supports up to 65,535 dimensions, and you can specify dimension and format at column definition. Vector indexes are created with CREATE VECTOR INDEX and are automatically maintained for DML, enabling efficient approximate similarity searches. These are core capabilities of AI Vector Search in 23ai.

Answer

Vector indexes in 23ai support only exact nearest neighbor searches, not approximate searches, to guarantee accuracy.

Answer

The VECTOR data type stores vectors only in binary format, and you must use the TO_VECTOR function to convert numeric arrays before insertion.

Answer

A VECTOR column can be indexed only if it is declared with the FLEXIBLE keyword, which allows variable dimensions.