OCP Practice Exam 2 — 20 Questions

Question 1

When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?

Accepted Answer

The total time spent by sessions in the database. 'DB time' in an AWR report represents the cumulative time spent by all foreground sessions in database calls — essentially the total amount of work the database performed, including CPU and wait time. A high DB time relative to elapsed time indicates sessions are spending significant time waiting or consuming CPU, signaling potential performance pressure.

Answer

The time spent on disk I/O only

Answer

The time spent on instance background processes

Answer

The time the database was open

Question 2

Which view displays the current settings for the AWR retention period?

Accepted Answer

DBA_HIST_WR_CONTROL. DBA_HIST_WR_CONTROL contains the snapshot settings including retention and interval.

Answer

DBA_SNAPSHOTS

Answer

V$DATABASE

Answer

V$INSTANCE

Question 3

You are configuring a recovery catalog in Oracle 23ai. Which command must you execute first to ensure the catalog is ready to register the target database?

Accepted Answer

CREATE CATALOG;. The CREATE CATALOG command must be executed to initialize the schema before registration.

Answer

REGISTER DATABASE;

Answer

CONNECT CATALOG;

Answer

UPGRADE CATALOG;

Question 4

You are configuring a PDB. What happens to the PDB if you drop the CDB?

Accepted Answer

All PDBs are dropped. All PDBs are automatically removed when the CDB is dropped.

Answer

PDBs remain as standalone

Answer

PDBs become unusable orphans

Answer

PDBs are migrated to another CDB

Question 5

Which command allows you to change the state of a PDB to READ ONLY?

Accepted Answer

ALTER PLUGGABLE DATABASE OPEN READ ONLY. ALTER PLUGGABLE DATABASE [PDB_NAME] OPEN READ ONLY is the correct syntax.

Answer

OPEN PDB READ ONLY

Answer

ALTER PDB READ ONLY

Answer

ALTER DATABASE PDB OPEN READ ONLY

Question 6

You are tuning a complex SQL statement. You want to see the execution plan that the optimizer is currently choosing. Which dynamic performance view is most useful?

Accepted Answer

V$SQL_PLAN. V$SQL_PLAN shows the execution plan for statements currently in the library cache.

Answer

V$SESSION

Answer

V$SQLAREA

Answer

V$SYSSTAT

Question 7

When using the VECTOR data type, what is the maximum number of dimensions supported?

Accepted Answer

2048. Oracle 23ai supports up to 2048 dimensions for the vector data type.

Answer

512

Answer

1024

Answer

4096

Question 8

Which file stores the metadata for a PDB being unplugged?

Accepted Answer

XML file. The unplug process generates an XML file to describe the PDB.

Answer

TXT file

Answer

JSON file

Answer

SQL file

Question 9

You are performing a tablespace recovery. What is the first thing that must happen to the tablespace before recovery can start?

Accepted Answer

The tablespace must be taken offline.. The tablespace must be taken offline to perform recovery on its constituent datafiles.

Answer

The tablespace must be set to read-only.

Answer

The database must be shut down.

Answer

The database must be in restricted mode.

Question 10

What is the consequence of not running the 'catupgrd.sql' script after a major version upgrade?

Accepted Answer

Data dictionary remains in old version format. The data dictionary will be inconsistent, and many objects will be invalid, leading to database failure.

Answer

Only performance is affected

Answer

The database will run in read-only mode

Answer

The database will not start

Question 11

Which feature allows you to capture SQL statements from the cursor cache and load them into a SQL Tuning Set?

Accepted Answer

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET. DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET is the procedure used to pull queries from memory.

Answer

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

Answer

DBMS_STATS.GATHER_SYSTEM_STATS

Answer

DBMS_WORKLOAD_REPOSITORY.EXTRACT_SQL

Question 12

When using Wide Tables, what is the impact on performance for queries that select only a small subset of columns?

Accepted Answer

No significant performance degradation. Wide Tables in 23ai are optimized to handle many columns without significant performance degradation compared to vertical splitting.

Answer

Severe performance degradation

Answer

Requires partitioning

Answer

Requires a full table scan

Question 13

Which THREE types of information are captured in an AWR snapshot?

Accepted Answer

Library cache statistics. AWR captures workload, system statistics, and session activity.

Answer

Tablespace creation scripts

Answer

Database user passwords

Question 14

The Automatic SQL Plan Management (ASPM) feature is enabled. A new execution plan is identified for a high-load query that performs better during testing. How does the database verify this plan before evolving the baseline?

Accepted Answer

It executes the SQL statement using the new plan and compares its performance metrics against the current baseline.. The database performs a verification task where it executes the new plan and compares it against the baseline performance to decide if it should be accepted.

Answer

It flags the plan for manual DBA approval via the DBMS_SPM package.

Answer

It automatically promotes the plan based on the optimizer cost estimation.

Answer

It relies on the Advisor to run a full analysis of the explain plan tree.

Question 15

You need to ensure that RMAN automatically deletes archived redo logs after they have been backed up twice to tape. Which configuration command should you use?

Accepted Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE SBT;. The correct RMAN syntax to automatically delete archived redo logs after they have been backed up a specified number of times to a device type is 'CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP n TIMES TO DEVICE TYPE <device>'. This policy tells RMAN to delete archived logs once the specified redundancy to the given device (SBT = tape) is satisfied.

Answer

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Answer

SET ARCHIVELOG DELETION POLICY TO 2 COPIES;

Answer

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Question 16

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) is a graph-based index specifically designed for approximate nearest neighbor search in high-dimensional vector spaces. It provides fast and accurate similarity search by building a multi-layer graph structure. Other index types like inverted, B-tree, or bitmap are not optimized for vector similarity.

Answer

Inverted Index

Answer

B-tree

Answer

Bitmap

Question 17

Which of the following is true regarding Vector embeddings in 23ai?

Accepted Answer

They are stored as arrays of floats. Vectors are typically generated by AI models and stored as arrays of floats.

Answer

They are stored as metadata

Answer

They are stored as images

Answer

They are stored as text

Question 18

A production database experiences a sudden performance degradation. You want to analyze the last 10 minutes of activity to pinpoint blocked sessions. Which approach is best?

Accepted Answer

Query V$ACTIVE_SESSION_HISTORY. ASH data is stored in memory and is the primary source for recent, short-term performance diagnostics.

Answer

Generate an AWR report for the last hour

Answer

Check the Alert Log for ORA errors

Answer

Run the SQL Access Advisor

Question 19

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 (STS) are designed to capture a specific set of SQL statements along with their execution context, and they can be exported and transported to other databases for tuning or plan comparison. This makes them the correct tool for grouping high-CPU SQL and moving it across environments.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 20

Which TWO methods can be used to tune SQL statements?

Accepted Answer

Manual addition of optimizer hints. Option C (Manual addition of optimizer hints) is correct because optimizer hints are directives embedded in SQL statements (e.g., /*+ INDEX(...) */ or /*+ FULL(...) */) that influence the query optimizer's execution plan, directly tuning statement performance. Option E (SQL Tuning Advisor) is correct because it is an Oracle tool that analyzes a SQL statement, its execution plan, and statistics, then produces tuning recommendations such as SQL profiles, indexes, or restructured SQL. The other options do not tune SQL statements: increasing backups (A) is a backup/recovery activity, dropping the database (B) is a destructive DDL operation, and changing user roles (D) is a security/privilege change, none of which affect query execution plans or SQL performance.

Answer

Increasing the number of backups

Answer

Dropping the database

Answer

Changing user roles