OCP Hard Questions — 25 Difficult 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 supports up to 4096 columns in a table.

Answer

1000

Answer

8192

Answer

2048

Question 2

When configuring parallel DML, what is the effect of setting 'FORCE' on a statement?

Accepted Answer

It forces parallel execution. The FORCE hint forces the statement to run in parallel regardless of the cost optimizer's decision.

Answer

It disables parallel DML

Answer

It runs sequentially

Answer

It causes an error

Question 3

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

Accepted Answer

It moves data to the data files. The flush operation moves data from the memory buffer to the physical data blocks.

Answer

It validates data constraints

Answer

It clears the cache

Answer

It deletes the data

Question 4

Which THREE actions are performed during the 'RESTORE DATABASE' command?

Accepted Answer

Restoring datafiles to the disk.. Restoring files, updating the control file, and verifying existence are key.

Answer

Opening the database with RESETLOGS.

Answer

Performing media recovery.

Question 5

What is the recommended approach for updating data in a table using Lock-Free Reservations?

Accepted Answer

Use standard UPDATE. The application performs the update, and Oracle handles the non-blocking logic.

Answer

Disable row locking

Answer

Call a special procedure

Answer

Use a trigger

Question 6

In 23ai, when using the 'JSON relational duality' feature, how are changes to the JSON document propagated to the underlying tables?

Accepted Answer

Automatically propagated by the database. Updates made via JSON Duality Views are transparently propagated to the underlying relational tables using the view's defined mapping.

Answer

Only allowed via stored procedures

Answer

Manual re-synchronization

Answer

Requires a trigger

Question 7

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 8

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 9

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 10

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 11

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 12

Which THREE operations are automatically performed by RMAN when executing the 'RECOVER TABLE' command?

Accepted Answer

Exporting the recovered table to the target database using Data Pump. The RECOVER TABLE command creates an auxiliary instance, performs a partial restore and recovery of the tablespace containing the table, and then exports the table metadata to the target database.

Answer

Automatic deletion of all backups older than the restore point

Answer

Automatic conversion of the database to NOARCHIVELOG mode

Question 13

You are performing a cross-platform data migration using RMAN CONVERT. The destination platform has different endianness. Which file type must be converted before it can be transported?

Accepted Answer

Datafiles. Datafiles in a platform with different endianness must be converted using the CONVERT command to be readable by the target instance.

Answer

Redo log files

Answer

Parameter files

Answer

Control files

Question 14

You need to determine why a particular session is experiencing high 'db file sequential read' waits. Which view provides the most detailed information about the specific blocks being read?

Accepted Answer

V$SESSION_WAIT. V$SESSION_WAIT includes P1, P2, and P3 columns which often identify the file and block being accessed during the wait.

Answer

V$BH

Answer

V$SQL

Answer

V$FILESTAT

Answer

V$SQL_PLAN

Question 15

A critical application query is experiencing performance issues due to stale optimizer statistics. You want to use the Optimizer Statistics Advisor to identify the root cause. Which action must you perform to trigger the advisor?

Accepted Answer

Call DBMS_STATS.REPORT_STATS_ADVISOR_TASK to generate a report on statistics-related issues.. The Optimizer Statistics Advisor can be invoked using the DBMS_STATS.REPORT_STATS_ADVISOR_TASK function to analyze and report on statistics health.

Answer

Run the AWR report and look for 'Stats' in the top wait events.

Answer

Enable the SQL Tuning Advisor for the specific SQL_ID.

Answer

Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

Question 16

You need to capture a specific set of SQL statements that are causing high CPU usage for later tuning. Which tool allows you to group these statements and transport them across different database environments?

Accepted Answer

SQL Tuning Sets. SQL Tuning Sets are specifically designed to capture, store, and transport SQL statements along with their execution context and statistics.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 17

You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?

Accepted Answer

The SPM Evolve Task is disabled. The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.

Answer

SQL Plan Baselines are full

Answer

Statistics are gathered manually

Answer

The database is in NOARCHIVELOG mode

Question 18

You have a query that is performing poorly due to a stale execution plan. You want to lock the current optimal plan to prevent the optimizer from changing it during subsequent runs. What should you do?

Accepted Answer

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. SQL Plan Baselines allow you to store and fix execution plans for specific SQL statements.

Answer

Run DBMS_STATS.LOCK_TABLE_STATS

Answer

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Answer

Use SQL Tuning Advisor to accept a new profile

Question 19

When migrating to 23ai, which file is used to specify the target home directory in AutoUpgrade?

Accepted Answer

config.cfg. The AutoUpgrade .cfg file uses the 'target_home' parameter.

Answer

init.ora

Answer

oratab

Answer

spfile.ora

Question 20

In 23ai, if you want to audit 'CREATE TABLE' statements, which policy type should you use?

Accepted Answer

Unified Audit Policy. A Unified Audit Policy for system events or actions is used.

Answer

Fine-Grained Auditing (FGA)

Answer

Logon Trigger

Answer

Traditional Audit