OCP Practice Exam 4 — 20 Questions

Question 1

Which TWO types of reports are most useful for diagnosing long-term database performance trends?

Accepted Answer

AWR Report. The AWR Report (B) is correct because Automatic Workload Repository snapshots persist performance data over long retention periods, enabling comparison of load, wait events, and top SQL across days or weeks to reveal long-term trends. The ADDM Report (E) is correct because Automatic Database Diagnostic Monitor analyzes AWR snapshots and produces findings and recommendations that track recurring performance issues over time, making it well suited for trend diagnosis. V$SESSION_WAIT (A) is a dynamic view showing only current or very recent wait activity, so it lacks historical depth. ASH Report (C) samples active sessions at fine granularity but typically covers short windows, not long-term trends. SQL Trace with tkprof (D) captures a single session's statement-level execution details for point-in-time tuning, not multi-period trend analysis.

Answer

V$SESSION_WAIT view

Answer

ASH Report

Answer

SQL Trace (tkprof)

Question 2

What is the result of setting STATISTICS_LEVEL to 'BASIC'?

Accepted Answer

It disables AWR and ADDM. Setting it to BASIC disables many diagnostic features, including AWR and ADDM.

Answer

It increases CPU overhead

Answer

It enables SQL Tuning Advisor

Answer

It enables all features

Question 3

What is the primary function of the Database Resident Connection Pooling (DRCP) in a 23ai environment?

Accepted Answer

Connection scaling. DRCP reduces the memory footprint of a database by pooling server processes.

Answer

Security authentication

Answer

Data encryption

Answer

Audit trail management

Question 4

In which view can you find information about the current SQL Plan Baselines?

Accepted Answer

DBA_SQL_PLAN_BASELINES. DBA_SQL_PLAN_BASELINES is the primary view for information about SQL plan baselines.

Answer

V$SESSION

Answer

V$SQL

Answer

DBA_INDEXES

Question 5

How can you view the recommendations made by the Automatic Database Diagnostic Monitor (ADDM)?

Accepted Answer

Querying DBA_ADVISOR_FINDINGS. ADDM recommendations can be viewed in AWR or via the DBA_ADVISOR_FINDINGS view.

Answer

Checking the alert log

Answer

Querying V$SGA

Answer

Running the catproc script

Question 6

Which TWO methods are used to verify the success of an upgrade?

Accepted Answer

Reviewing log files. Checking the log files and querying the dictionary version are standard verification methods.

Answer

Reinstalling the OS

Answer

Running a test report

Answer

Checking user login

Question 7

Which TWO views can be used to monitor current session performance?

Accepted Answer

V$SESSION_WAIT. V$SESSION and V$SESSION_WAIT are standard tools for real-time monitoring.

Answer

V$CONTROLFILE

Answer

DBA_TABLES

Answer

DBA_USERS

Question 8

Which THREE operations are permitted on Blockchain tables?

Accepted Answer

VERIFY. Blockchain tables in Oracle Database are append-only, tamper-evident tables, so the permitted operations are INSERT (C), SELECT (E), and VERIFY (A). INSERT (C) is allowed because new rows can only be added to the end of the chain, which is how the table grows while preserving its hash-linked row history. SELECT (E) is allowed because reading and querying rows is unrestricted and does not alter the chain. VERIFY (A) is allowed because it is the special operation that checks the table's hashes and signatures to confirm no rows have been tampered with. DELETE (B) and UPDATE (D) are not permitted, since blockchain tables are append-only and rows cannot be modified or removed after insertion.

Answer

DELETE

Answer

UPDATE

Question 9

When running the SQL Tuning Advisor, what does the 'Accept' action do?

Accepted Answer

It implements the recommended SQL Profile. Accepting a recommendation (like a profile) implements it in the database for future executions.

Answer

It permanently changes the SQL code

Answer

It deletes the SQL from the cache

Answer

It updates the global statistics

Question 10

What does the 'Elapsed Time' in a SQL statement represent?

Accepted Answer

The total duration of the SQL execution. It is the total wall-clock time spent executing the statement from start to finish.

Answer

The time spent on CPU only

Answer

The time since the SQL was parsed

Answer

The time spent waiting for I/O

Question 11

What does the 'SQL ID' in an Oracle database represent?

Accepted Answer

A unique identifier for a SQL statement. The SQL ID is a unique hash of the SQL text, used to identify statements in the library cache.

Answer

A table name

Answer

A user ID

Answer

An execution plan version

Question 12

Which TWO of these are common sources of performance degradation?

Accepted Answer

High resource contention. Suboptimal SQL and system-wide resource contention are the most common issues.

Answer

Renaming a table

Answer

Changing the database name

Answer

Adding a new user

Question 13

Which THREE tasks are performed after an upgrade is finished?

Accepted Answer

Recompile invalid objects. Post-upgrade, you must run the timezone script, recompile invalid objects, and gather dictionary statistics.

Answer

Create new users

Answer

Deinstall old version

Question 14

Which THREE things can prevent an AutoUpgrade process from starting?

Accepted Answer

Existing lock files. Insufficient space, incorrect permissions, or already running processes are common blockers.

Answer

Expired passwords

Answer

Too many users

Question 15

Which user is responsible for the management of the Unified Audit trail?

Accepted Answer

AUDIT_ADMIN. The AUDIT_ADMIN role is required for managing unified audit policies and data.

Answer

SYSDBA

Answer

DBA

Answer

SECURITY_ADMIN

Question 16

Which parameter controls the frequency of AWR snapshot collection?

Accepted Answer

STATISTICS_LEVEL. STATISTICS_LEVEL controls the automatic collection of AWR snapshots.

Answer

PROCESSES

Answer

DB_CACHE_SIZE

Answer

OPTIMIZER_MODE

Question 17

When using the SQL Tuning Advisor to analyze a SQL statement, which THREE types of recommendations can it provide to improve performance?

Accepted Answer

Creating a SQL profile. The SQL Tuning Advisor can suggest creating new indexes, refreshing optimizer statistics, and creating SQL profiles to alter the execution plan.

Answer

Restructuring the SQL query syntax

Answer

Renaming database tables

Question 18

What is the best way to ensure an encrypted connection between the application server and the database?

Accepted Answer

Using Native Network Encryption. The best way to ensure an encrypted connection between the application server and the database is to use Native Network Encryption. This feature encrypts data as it travels over the network between the client and the database server, using algorithms like AES. It is configured on the database server and client via parameters such as SQLNET.ENCRYPTION_CLIENT and SQLNET.ENCRYPTION_TYPES_CLIENT, and it does not require any changes to the application. It provides a straightforward way to secure data in transit.

Answer

Using Audit Vault

Answer

Using Database Vault

Answer

Using TDE

Question 19

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

Accepted Answer

DBMS_BLOCKCHAIN_TABLE. DBMS_BLOCKCHAIN_TABLE is the Oracle-supplied PL/SQL package that provides APIs to verify the integrity of rows in a blockchain table — including verifying row signatures, checking the hash chain, and validating the ledger. It is the only supported mechanism for cryptographic verification of blockchain table contents.

Answer

Audit Vault

Answer

Data Guard

Answer

Flashback Query

Question 20

You are migrating to 23ai and need to use the 'DBMS_COMPARISON' package to verify data integrity between the old and new instances. What is the prerequisite for this operation?

Accepted Answer

A database link must exist between the databases. DBMS_COMPARISON requires a database link between the source and target databases.

Answer

The databases must use the same tablespace names

Answer

The databases must be on the same physical host

Answer

The source database must be in Read-Only mode