OCP Practice Test 14 — 15 Questions

Question 1

You are investigating an RMAN backup failure. The log indicates that RMAN is unable to access the recovery area. Which view should you query to check the current space usage of the Fast Recovery Area?

Accepted Answer

V$RECOVERY_FILE_DEST. V$RECOVERY_FILE_DEST provides the directory, space limit, and space used for the FRA.

Answer

V$FLASHBACK_DATABASE_LOG

Answer

V$DATABASE_BLOCK_CORRUPTION

Answer

V$RMAN_STATUS

Question 2

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.. To use Flashback Table, row movement must be enabled on the table because the operation may change the physical rowids of rows as it restores the table to a previous point in time. This is a mandatory prerequisite. Without row movement enabled, the flashback operation will fail.

Answer

The table must have a Primary Key.

Answer

The database must be in ARCHIVELOG mode.

Answer

The Recycle Bin must be disabled.

Question 3

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. The AWR report is the most direct tool for identifying top SQL by CPU consumption over a specific time period such as the last hour. AWR automatically captures snapshots of performance statistics, including SQL ordered by CPU time, and the report presents this data in a ready-to-read format. ASH provides session-level sampling for shorter, more granular analysis, but AWR is the standard tool for hourly or longer-range top-SQL identification.

Answer

SQL Tuning Advisor

Answer

Active Session History (ASH) report

Answer

SQL Plan Management

Question 4

An administrator needs to identify the top wait events causing performance degradation in the database over the last hour. Which tool provides the most granular, session-level view of these wait events?

Accepted Answer

Active Session History (ASH). ASH provides real-time, session-level performance data, making it the ideal tool for analyzing short-term, granular performance spikes.

Answer

SQL Tuning Advisor

Answer

Automatic Database Diagnostic Monitor (ADDM)

Answer

Automatic Workload Repository (AWR)

Question 5

Which component is required to implement Transparent Data Encryption (TDE) in 23ai?

Accepted Answer

Oracle Wallet. A keystore (wallet) is required to hold the master encryption key for TDE.

Answer

Oracle Audit Vault

Answer

Oracle Key Vault

Answer

Oracle Data Guard

Question 6

What is the purpose of the 'no drop' clause in Blockchain table creation?

Accepted Answer

Prevent table deletion. The 'NO DROP' clause in a Blockchain table specifies a retention period during which the table cannot be dropped, protecting the immutable ledger from accidental or malicious deletion. It is part of the table's immutability guarantees, ensuring that once data is written, the table itself persists for the defined duration. This is distinct from row-level retention, which prevents deletion of individual rows.

Answer

Restrict access

Answer

Prevent data modification

Answer

Protect index integrity

Question 7

Which TWO of the following are benefits of using an RMAN recovery catalog?

Accepted Answer

Stores backup metadata for multiple databases.. Option A is correct because an RMAN recovery catalog is a separate schema (typically in an Oracle database) that can store backup metadata for multiple target databases, unlike the control file which is per-database. Option E is correct because the recovery catalog retains RMAN metadata indefinitely, allowing backup history to be preserved far longer than the control file's limited retention (governed by CONTROL_FILE_RECORD_KEEP_TIME), which is essential for long-term restore and reporting needs. Options B, C, and D are incorrect: the catalog does not perform encryption (that is done via RMAN encryption settings or wallet), does not remove the need for archived redo logs (required for consistent backups and point-in-time recovery), and does not improve database performance since it is only a metadata repository.

Answer

Automatically encrypts all backups.

Answer

Eliminates the need for archivelogs.

Answer

Provides faster database performance.

Question 8

What happens if a user tries to drop a Blockchain table that still contains rows within the retention period?

Accepted Answer

The command fails with an error. Blockchain tables in Oracle enforce immutability for rows within the retention period. If a user attempts to DROP a blockchain table that still holds rows whose retention window has not expired, Oracle raises an error (ORA-05723 or similar) and the DROP is rejected. This protects the tamper-evident ledger guarantee — rows cannot be removed before their retention period elapses.

Answer

The command succeeds

Answer

The table is renamed to _OLD

Answer

The rows are archived then dropped

Question 9

When configuring Oracle Network Encryption, which TWO of the following parameters in the sqlnet.ora file are used to specify encryption settings?

Accepted Answer

SQLNET.ENCRYPTION_TYPES_SERVER. SQLNET.ENCRYPTION_SERVER (option C) is correct because it is the sqlnet.ora parameter that controls whether encryption is required, requested, accepted, or rejected for inbound connections on the server side, with valid values such as ACCEPTED, REJECTED, REQUESTED, and REQUIRED. SQLNET.ENCRYPTION_TYPES_SERVER (option A) is correct because it specifies the list of encryption algorithms the server will use, in order of preference, such as AES256, AES192, AES128, and 3DES168. Together these two parameters define the server-side encryption behavior for Oracle Network Encryption. Option B (TNS_ENCRYPTION_ENABLED) is not a valid sqlnet.ora parameter, option D (SQLNET.AUTHENTICATION_SERVICES) governs authentication methods such as NONE, ALL, or KERBEROS rather than encryption, and option E (NETWORK_SECURE_MODE) is not a recognized Oracle Net parameter.

Answer

TNS_ENCRYPTION_ENABLED

Answer

SQLNET.AUTHENTICATION_SERVICES

Answer

NETWORK_SECURE_MODE

Question 10

Which THREE of the following are benefits of the JSON Duality feature?

Accepted Answer

Simplified application development. Option B (Simplified application development) is correct because JSON Duality lets developers work with the same data either as relational tables/SQL or as JSON documents through MongoDB-compatible APIs, so teams can use their preferred model without building separate ETL or duplicate storage. Option C (Data consistency) is correct because the relational and JSON views are the same underlying data, so a change made through one interface is immediately visible through the other, eliminating synchronization drift between document and relational copies. Option E (Native SQL support) is correct because JSON Duality exposes the JSON collections as relational views queryable with standard SQL, allowing existing SQL tools, joins, and analytics to run directly against document data. Option A (Automatic data encryption) is not a JSON Duality benefit; encryption is handled by separate features such as Transparent Data Encryption or column-level encryption. Option D (No indexing required) is incorrect because JSON Duality does not remove the need for indexing — indexes are still required for query performance on both the relational and JSON sides.

Answer

Automatic data encryption

Answer

No indexing required

Question 11

You are seeing 'enq: TX - row lock contention' in your performance reports. What does this mean?

Accepted Answer

Sessions are competing for the same row locks. TX lock contention indicates that multiple sessions are trying to update the same rows.

Answer

The library cache is full

Answer

The tablespace is full

Answer

The database is waiting for I/O

Question 12

How do you ensure that a user cannot connect to the database via SQL*Plus but can connect via an application service?

Accepted Answer

Create a Logon Trigger. You can use a login trigger to check the program name (e.g., 'sqlplus.exe') and reject connections.

Answer

Modify TNSNAMES.ORA

Answer

Use Profile limits

Answer

Revoke CONNECT role

Question 13

You are analyzing an AWR report and notice a high value for 'db file scattered read'. What is the most likely cause of this wait event?

Accepted Answer

Full table scans or index fast full scans.. 'db file scattered read' is typically associated with full table scans or index fast full scans where multiple blocks are read into non-contiguous memory locations.

Answer

Insufficient PGA memory for sorting operations.

Answer

Excessive log file sync waits.

Answer

Heavy use of nested loop joins.

Question 14

Which view can you use to check the status of TDE wallet encryption?

Accepted Answer

V$ENCRYPTION_WALLET. V$ENCRYPTION_WALLET displays the status of the wallet used for TDE.

Answer

V$TDE_STATUS

Answer

DBA_ENCRYPTION_STATUS

Answer

DBA_WALLET

Question 15

What is the primary characteristic of a Blockchain table in 23ai?

Accepted Answer

It is immutable. It is an insert-only, immutable table.

Answer

It is for temporary data

Answer

It allows row deletion

Answer

It can be updated