Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) (OCP) OCP Questions 151–225 | Page 3/5

151

MCQhard

You are performing a TSPITR (Tablespace Point-in-Time Recovery). What is the purpose of the auxiliary instance?

A.To act as a standby database.

B.To host the recovery catalog.

C.To manage the control file backups.

D.To perform the recovery of the target tablespaces.

AnswerD

The auxiliary instance hosts the recovered tablespaces temporarily.

Why this answer

The auxiliary instance is used to restore and recover the target tablespaces to the desired point in time before exporting the data back to the primary database.

Full explanation →

152

MCQeasy

What is the primary benefit of Lock-Free Reservations in high-concurrency environments?

A.Faster queries

B.Reduced row-level locking contention

C.Better compression

D.Automatic indexing

AnswerB

This allows multiple sessions to update values without locking each other.

Why this answer

It eliminates wait events associated with row-level locking for numeric updates.

Full explanation →

153

MCQeasy

Which initialization parameter determines the maximum number of PDBs allowed in a CDB?

A.MAX_PDB_CONTAINERS

B.DB_FILES

C.PDB_LIMIT

D.CONTAINERS_MAX

AnswerA

This parameter limits the number of PDBs.

Why this answer

MAX_PDB_CONTAINERS defines the hard limit for PDBs in a CDB.

Full explanation →

154

MCQeasy

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

A.A table name

B.A user ID

C.An execution plan version

D.A unique identifier for a SQL statement

AnswerD

SQL IDs allow tracking of specific statements.

Why this answer

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

Full explanation →

155

MCQhard

You notice that a specific query is performing very slowly due to excessive 'direct path read' waits. What is the most likely cause?

A.The query is performing a full table scan

B.The library cache is too small

C.The PGA is too large

D.The redo logs are too small

AnswerA

Full table scans bypass the buffer cache, causing direct path reads.

Why this answer

Direct path reads often occur during full table scans when the data is not in the buffer cache.

Full explanation →

156

MCQeasy

Which command is used to start the AutoUpgrade process?

A.sqlplus / as sysdba

B.opatch

C.dbua

D.java -jar autoupgrade.jar

AnswerD

This is the correct command syntax.

Why this answer

The command is 'java -jar autoupgrade.jar -config <config_file>'.

Full explanation →

157

Multi-Selectmedium

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

Select 2 answers

A.SQLNET.ENCRYPTION_TYPES_SERVER

B.TNS_ENCRYPTION_ENABLED

C.SQLNET.ENCRYPTION_SERVER

D.SQLNET.AUTHENTICATION_SERVICES

E.NETWORK_SECURE_MODE

AnswersA, C

This specifies the allowed encryption algorithms for the server.

Why this answer

SQLNET.ENCRYPTION_SERVER and SQLNET.ENCRYPTION_TYPES_SERVER are the primary parameters for configuring server-side network encryption.

Full explanation →

158

Multi-Selecthard

Which TWO of the following statements about True Cache are correct?

Select 2 answers

A.It replaces Data Guard

B.It requires a dedicated database

C.It supports DML operations

D.It resides in the application tier

E.It is a read-only cache

AnswersD, E

Designed to be close to the app.

Why this answer

True Cache is read-only and is deployed in the application tier.

Full explanation →

159

MCQmedium

You need to migrate a non-CDB database to a PDB in a 23ai CDB. Which utility is the recommended tool to assess compatibility before migration?

A.DBMS_PDB.CHECK_PLUG_COMPATIBILITY

B.RMAN CONVERT

C.DBUA

D.SQL Developer

E.Data Pump

AnswerA

This function performs a series of checks on the source database to ensure it can be plugged into the target CDB.

Why this answer

DBMS_PDB.CHECK_PLUG_COMPATIBILITY is the standard PL/SQL procedure to identify potential issues before plugging a non-CDB into a CDB.

Full explanation →

160

MCQhard

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?

A.V$SESSION_WAIT

B.V$BH

C.V$SQL

D.V$FILESTAT

E.V$SQL_PLAN

AnswerA

P1/P2/P3 reveal file and block numbers for I/O waits.

Why this answer

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

Full explanation →

161

MCQmedium

Which procedure in the DBMS_SPM package is used to evolve a SQL plan baseline?

A.DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

B.DBMS_SPM.DROP_SQL_PLAN_BASELINE

C.DBMS_SPM.ALTER_SQL_PLAN_BASELINE

D.DBMS_SPM.LOAD_PLANS_FROM_SQLSET

AnswerA

This verifies and promotes unaccepted plans to accepted.

Why this answer

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE is the standard procedure to evolve baselines.

Full explanation →

162

MCQeasy

Which type of operation is ideal for Blockchain tables?

A.General configuration tables

B.Transient cache data

C.Audit logs and financial records

D.High-frequency temporary storage

AnswerC

These require immutability.

Why this answer

Blockchain tables are designed for audit trails where history must be preserved.

Full explanation →

163

MCQhard

You have a query that is using a nested loops join, but it should be using a hash join. The optimizer statistics are correct. How can you force a hash join without changing the SQL text?

A.Change the STATISTICS_LEVEL

B.Use SQL Plan Management

C.Create an index

D.Use a SQL Profile

AnswerD

A SQL Profile can provide hints to the optimizer to steer it toward a hash join.

Why this answer

SQL Plan Baselines or SQL Profiles can influence the optimizer without changing application code.

Full explanation →

164

MCQmedium

What is the result of a 'vector similarity' search?

A.Rows sorted by similarity distance

B.The average value

C.A random sample

D.A list of exact matches

AnswerA

This is how similarity is ranked.

Why this answer

It returns rows sorted by distance.

Full explanation →

165

MCQmedium

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

A.It is more space-efficient for sparse data

B.It disables compression

C.Requires separate tablespaces

D.It uses more storage due to padding

AnswerA

Oracle optimizes storage for the increased column count.

Why this answer

Wide tables are implemented to be space efficient, utilizing Oracle's storage engine enhancements.

Full explanation →

166

MCQeasy

What is the standard tool to manage CDB/PDB configuration in 23ai?

A.SQL Developer

B.Net Manager

C.SQL*Plus

D.OEM

AnswerC

Standard CLI tool for DB administration.

Why this answer

SQL*Plus or SQLcl are the standard interfaces for managing CDBs.

Full explanation →

167

Multi-Selecthard

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

Select 3 answers

A.Restoring datafiles to the disk.

B.Updating the control file with file locations.

C.Opening the database with RESETLOGS.

D.Verifying the integrity of the backup pieces.

E.Performing media recovery.

AnswersA, B, D

This is the primary restore action.

Why this answer

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

Full explanation →

168

MCQeasy

Which view provides information about the open status of all PDBs in a CDB?

A.V$INSTANCE

B.V$CONTAINERS

C.V$PDBS

D.V$DATABASE

E.DBA_PDBS

AnswerC

V$PDBS is the correct dynamic performance view for PDB status.

Why this answer

V$PDBS provides the current status of each PDB in the container database.

Full explanation →

169

MCQmedium

You are performing a SQL tuning exercise. How do you create a SQL Tuning Set (STS) to be used as input for the SQL Tuning Advisor?

A.DBMS_SQLTUNE.CREATE_SQLSET

B.DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

C.DBMS_SPM.CREATE_BASELINE

D.DBMS_STATS.CREATE_STAT_TABLE

AnswerA

This creates the container to hold SQL statements.

Why this answer

DBMS_SQLTUNE.CREATE_SQLSET is the required procedure to initialize an STS container.

Full explanation →

170

MCQeasy

An administrator needs to perform maintenance on a specific PDB while keeping the rest of the CDB and other PDBs fully operational. What is the correct state to set the PDB to?

A.RESTRICTED

B.SHUTDOWN

C.READ ONLY

D.MOUNTED

AnswerD

The MOUNTED state allows PDB maintenance operations while the CDB remains open.

Why this answer

Setting a PDB to MOUNTED state allows administrators to perform administrative tasks, such as renaming datafiles or performing point-in-time recovery, without opening the PDB for user access.

Full explanation →

171

Multi-Selecthard

Which THREE configuration steps are required to implement True Cache?

Select 3 answers

A.Update the connection pool

B.Configure the True Cache instance

C.Enable on the application side

D.Manual data refresh scripts

E.Disable Data Guard

AnswersA, B, C

Required for routing.

Why this answer

True Cache requires a connection, configuration, and a compatible application driver.

Full explanation →

172

MCQeasy

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

A.Tables with large rows

B.Tables with long text

C.Tables with more than 1000 columns

D.Tables across multiple disks

AnswerC

The feature expands the column limit.

Why this answer

Wide Tables allow for a significantly increased number of columns.

Full explanation →

173

MCQeasy

What is the primary benefit of using Unified Auditing in 23ai?

A.Simplified audit management and unified audit trail

B.Improved query performance for application tables

C.Increased storage capacity

D.Automatic data masking

AnswerA

This is the main purpose of the feature.

Why this answer

Unified Auditing provides a single, unified audit trail for all audit records from various sources.

Full explanation →

174

MCQmedium

You need to monitor wait events for a specific PDB. Which view is most appropriate?

A.V$PDB_WAIT

B.V$SYSTEM_EVENT

C.V$SESSION

D.DBA_WAIT_EVENTS

AnswerC

Contains CON_ID to filter by container.

Why this answer

V$SESSION filtered by CON_ID allows viewing PDB-specific sessions.

Full explanation →

175

Multi-Selecteasy

Which TWO are benefits of TDE?

Select 2 answers

A.Helps with compliance

B.Protects data at rest

C.Reduces disk space

D.Simplifies login

E.Increases performance

AnswersA, B

Required for many standards.

Why this answer

TDE provides security for data at rest and helps with regulatory compliance.

Full explanation →

176

MCQeasy

Which database feature is recommended for high-frequency inserts into a single table with minimal overhead?

A.Materialized Views

B.Flashback

C.Memoptimized Write

D.Partitioning

AnswerC

This is the specific feature for high-frequency fast ingest.

Why this answer

Memoptimized Write (Fast Ingest) is designed for high-frequency inserts.

Full explanation →

177

Multi-Selectmedium

Which TWO views are used to check for plug-in compatibility?

Select 2 answers

A.V$PDB_STATUS

B.V$PDB_COMPATIBILITY

C.DBA_PDB_ERRORS

D.PDB_PLUG_IN_VIOLATIONS

E.DBA_OBJECTS

AnswersB, D

Shows version compatibility.

Why this answer

PDB_PLUG_IN_VIOLATIONS and DBMS_PDB.CHECK_PLUG_COMPATIBILITY are standard.

Full explanation →

178

MCQeasy

When upgrading a database, what is a 'Flashback Database' used for?

A.Quick recovery after a failed upgrade

B.User authentication

C.Data migration

D.Performance tuning

AnswerA

This is the primary use during upgrades.

Why this answer

Flashback Database allows you to quickly revert to a point-in-time if the upgrade fails.

Full explanation →

179

Multi-Selecteasy

Which THREE items are generally included in an Oracle Patch?

Select 3 answers

A.Application source code

B.Security updates

C.Bug fixes

D.Performance improvements

E.New hardware drivers

AnswersB, C, D

Essential for patching.

Why this answer

Patches typically contain bug fixes, security updates, and performance improvements.

Full explanation →

180

MCQhard

A user accidentally dropped a critical table. You decide to use Flashback Table. What is the mandatory prerequisite before this command can execute?

A.The table must have a Primary Key.

B.The database must be in ARCHIVELOG mode.

C.The Recycle Bin must be disabled.

D.Row movement must be enabled on the table.

AnswerD

Flashback Table requires 'ENABLE ROW MOVEMENT' to reconstruct the row IDs.

Why this answer

Row movement must be enabled for the table to move rows back to their original IDs, and flashback must be enabled.

Full explanation →

181

MCQeasy

You are creating a new PDB from an existing one using the CREATE PLUGGABLE DATABASE command. Which clause must you include to ensure the new PDB has a different name and storage location?

A.MOVE DATAFILES

B.USING STORAGE MAP

C.SNAPSHOT COPY

D.FILE_NAME_CONVERT

AnswerD

This clause is mandatory when cloning a PDB to specify the source and destination file paths.

Why this answer

The CREATE PLUGGABLE DATABASE statement requires the file_name_convert clause to define the directory structure for the new PDB datafiles.

Full explanation →

182

Multi-Selectmedium

Which THREE tasks are performed after an upgrade is finished?

Select 3 answers

A.Create new users

B.Deinstall old version

C.Recompile invalid objects

D.Run timezone upgrade script

E.Gather dictionary statistics

AnswersC, D, E

Ensures code compatibility.

Why this answer

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

Full explanation →

183

Multi-Selectmedium

Which THREE things should be checked before upgrading to 23ai?

Select 3 answers

A.Current patch levels

B.Sufficient temporary tablespace

C.Database backup

D.Table indexes

E.Application user passwords

AnswersA, B, C

Required for a supported path.

Why this answer

System resources, disk space, and patch levels are critical checks.

Full explanation →

184

MCQmedium

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

A.Using Audit Vault

B.Using Database Vault

C.Using TDE

D.Using Native Network Encryption

AnswerD

NNE is for data-in-transit encryption.

Why this answer

Configuring Native Network Encryption (NNE) or TLS on the listener ensures encrypted traffic.

Full explanation →

185

MCQeasy

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

A.DBMS_BLOCKCHAIN_TABLE

B.Audit Vault

C.Data Guard

D.Flashback Query

AnswerA

This package handles integrity.

Why this answer

The DBMS_BLOCKCHAIN_TABLE package provides verification functions.

Full explanation →

186

MCQmedium

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?

A.The databases must use the same tablespace names

B.The databases must be on the same physical host

C.A database link must exist between the databases

D.The source database must be in Read-Only mode

AnswerC

A DB link is required for the package to compare data across instances.

Why this answer

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

Full explanation →

187

Multi-Selecthard

Which THREE pieces of information are displayed when you run 'SHOW PDBS'?

Select 3 answers

A.Creation Time

B.PDB GUID

C.Status

D.SGA Usage

E.PDB Name

AnswersA, C, E

The timestamp is included.

Why this answer

It shows the PDB name, status, and creation time.

Full explanation →

188

MCQeasy

If you are in ARCHIVELOG mode, what is the primary benefit of having a multiplexed control file?

A.Faster backup performance.

B.Redundancy against disk failure.

C.Ability to perform point-in-time recovery.

D.Automatic database tuning.

AnswerB

Having multiple copies protects against single-device failure.

Why this answer

Multiplexing control files provides fault tolerance against disk failure of the control file location.

Full explanation →

189

Multi-Selectmedium

Which TWO actions should be performed before dropping a PDB?

Select 2 answers

A.Close the PDB

B.Perform a full backup

C.Shrink the datafiles

D.Export all tables

E.Check for active sessions

AnswersA, E

Must be closed to drop.

Why this answer

Close the PDB and ensure no active sessions remain.

Full explanation →

190

MCQhard

Which component is responsible for managing background processes across all containers in a CDB?

A.Shared Service Layer

B.Global Process Manager

C.CDB Instance

D.PDB Instance

AnswerC

Processes are managed by the CDB level instance.

Why this answer

The instance (CDB root) manages background processes for all containers.

Full explanation →

191

MCQeasy

Which command is used to delete backups that are no longer needed based on the current retention policy?

A.DROP BACKUP

B.DELETE OBSOLETE

C.PURGE BACKUPS

D.DELETE BACKUP

AnswerB

This respects the CONFIGURE RETENTION POLICY setting.

Why this answer

DELETE OBSOLETE is the command to purge backups exceeding the retention policy.

Full explanation →

192

MCQhard

You are performing a cross-platform data migration. The source is Linux and the destination is Windows. Which RMAN tool do you use to convert the datafiles?

A.RMAN IMPORT

B.RMAN TRANSPORT

C.RMAN CONVERT

D.RMAN MIGRATION

AnswerC

The CONVERT command handles cross-endian migration.

Why this answer

The CONVERT command is used for cross-platform endianness issues.

Full explanation →

193

Multi-Selectmedium

Which TWO RMAN settings significantly improve performance during backup operations on an Exadata system?

Select 2 answers

A.CONFIGURE CHANNEL ... RATE 10M;

B.CONFIGURE MAXSETSIZE UNLIMITED;

C.CONFIGURE BACKUP OPTIMIZATION ON;

D.CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

E.CONFIGURE CONTROLFILE AUTOBACKUP OFF;

AnswersC, D

Skips files that haven't changed.

Why this answer

Backup optimization and parallelism settings directly impact throughput on high-performance storage.

Full explanation →

194

MCQeasy

What is the default behavior of the Unified Audit trail in 23ai?

A.Not stored by default

B.Stored in the OS as XML files

C.Stored in the SYSTEM tablespace

D.Stored in the UNDO tablespace

AnswerC

Unified audit records are stored within the database.

Why this answer

Unified audit records are stored in a secure table in the SYSTEM tablespace.

Full explanation →

195

MCQeasy

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?

A.SQL Tuning Advisor

B.Automatic Workload Repository (AWR) report

C.Active Session History (ASH) report

D.SQL Plan Management

AnswerB

AWR provides historical performance data aggregated over time intervals.

Why this answer

AWR reports provide aggregated data over a specific time range, making them ideal for identifying resource consumers.

Full explanation →

196

MCQhard

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?

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

B.Call DBMS_STATS.REPORT_STATS_ADVISOR_TASK to generate a report on statistics-related issues.

C.Enable the SQL Tuning Advisor for the specific SQL_ID.

D.Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

AnswerB

This is the correct procedural call to generate insights into statistics management.

Why this answer

The Optimizer Statistics Advisor can be invoked using the DBMS_STATS.REPORT_STATS_ADVISOR_TASK function to analyze and report on statistics health.

Full explanation →

197

MCQmedium

When using AutoUpgrade to move to 23ai, which file format is required for the configuration?

A.CFG

B.JSON

C.YAML

D.XML

AnswerA

The configuration file must end in .cfg.

Why this answer

AutoUpgrade uses a configuration file in a specific text-based key-value format (cfg file).

Full explanation →

198

Multi-Selectmedium

Which THREE actions are part of the AutoUpgrade process?

Select 3 answers

A.Post-upgrade cleanup

B.Upgrading the database

C.Applying security patches

D.Rebuilding the OS

E.Pre-check analysis

AnswersA, B, E

Finalizes the upgrade.

Why this answer

AutoUpgrade handles the Pre-check, Upgrade, and Post-upgrade phases.

Full explanation →

199

MCQhard

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

A.The library cache is full

B.Sessions are competing for the same row locks

C.The tablespace is full

D.The database is waiting for I/O

AnswerB

TX locks represent transaction row-level locking.

Why this answer

TX lock contention indicates that multiple sessions are trying to update the same rows.

Full explanation →

200

MCQmedium

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?

A.Insufficient PGA memory for sorting operations.

B.Excessive log file sync waits.

C.Heavy use of nested loop joins.

D.Full table scans or index fast full scans.

AnswerD

These operations read multiple blocks into scattered memory buffers.

Why this answer

'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.

Full explanation →

201

MCQmedium

Which view displays the list of active Unified Audit policies?

A.UNIFIED_AUDIT_TRAIL

B.DBA_AUDIT_POLICIES

C.DBA_PRIV_AUDIT_POLICIES

D.AUDIT_UNIFIED_POLICIES

AnswerD

This is the correct view for unified audit policies.

Why this answer

AUDIT_UNIFIED_POLICIES shows the policies defined in the database.

Full explanation →

202

Multi-Selectmedium

Which TWO of these are common sources of performance degradation?

Select 2 answers

A.Renaming a table

B.High resource contention

C.Changing the database name

D.Inefficient SQL statements

E.Adding a new user

AnswersB, D

Locks and latches impact performance.

Why this answer

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

Full explanation →

203

MCQeasy

What is the primary function of the 'JSON relational duality' feature?

A.To compress JSON data

B.To map relational data to JSON

C.To encrypt JSON data

D.To index JSON data

AnswerB

Duality views bridge the two worlds.

Why this answer

It provides a unified way to work with JSON and relational data simultaneously.

Full explanation →

204

MCQhard

Which of the following is true regarding SQL Plan Baselines?

A.They automatically gather statistics

B.They store the actual table data

C.They require the Oracle Enterprise Manager

D.They provide stability for SQL execution plans

AnswerD

The goal of SPM is to ensure plan stability.

Why this answer

They allow the database to maintain a stable execution plan even if the environment (stats, indexes) changes.

Full explanation →

205

MCQeasy

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

A.DBA_SQL_PLAN_BASELINES

B.V$SESSION

C.V$SQL

D.DBA_INDEXES

AnswerA

This view lists all defined SQL plan baselines.

Why this answer

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

Full explanation →

206

MCQmedium

You need to perform a full database restore and recovery. The control file is lost. What is the correct sequence of operations?

A.RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS;

B.RESTORE SPFILE; STARTUP MOUNT; RECOVER DATABASE; ALTER DATABASE OPEN;

C.FLASHBACK DATABASE TO RESTORE POINT; ALTER DATABASE OPEN;

D.STARTUP NOMOUNT; RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS;

AnswerD

This is the standard procedure for restoring a database when the control file is lost.

Why this answer

After losing the control file, you must restore it from an autobackup before you can restore the datafiles and perform recovery.

Full explanation →

207

MCQmedium

You observe a significant performance regression after a database upgrade. You suspect that the optimizer has chosen a suboptimal execution plan. Which component of SQL Plan Management should you use to ensure the database only uses known, verified execution plans?

A.SQL Plan Baselines

B.Optimizer Statistics Advisor

C.SQL Tuning Sets

D.Automatic SQL Tuning Advisor

AnswerA

SQL Plan Baselines explicitly control plan evolution to prevent undesirable changes.

Why this answer

SQL Plan Baselines are used to ensure that only verified execution plans are used for a specific SQL statement, protecting against performance regressions.

Full explanation →

208

MCQhard

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

A.Create a Logon Trigger

B.Modify TNSNAMES.ORA

C.Use Profile limits

D.Revoke CONNECT role

AnswerA

Triggers allow for program-based access control.

Why this answer

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

Full explanation →

209

MCQhard

What happens if a Lock-Free Reservation numeric value reaches its maximum allowed limit?

A.It wraps around to 0

B.It allows further updates anyway

C.It raises an error

D.It switches to locking mode

AnswerC

Standard error handling applies.

Why this answer

The database prevents further updates until the value is reset.

Full explanation →

210

MCQmedium

Which file needs to be updated when you move the wallet location in 23ai?

A.init.ora

B.tnsnames.ora

C.sqlnet.ora

D.listener.ora

AnswerC

The wallet location is defined here.

Why this answer

The sqlnet.ora file contains the WALLET_LOCATION parameter that must be updated.

Full explanation →

211

MCQeasy

What command would you use to list all backups associated with a particular database in the recovery catalog?

A.REPORT BACKUP

B.LIST BACKUP

C.SHOW BACKUP

D.DISPLAY BACKUP

AnswerB

This lists all known backups.

Why this answer

LIST BACKUP is the standard command for this.

Full explanation →

212

Multi-Selecthard

Which THREE factors influence the duration of a database upgrade?

Select 3 answers

A.Number of database objects

B.System resources (CPU/IO)

C.The color of the server

D.Number of users

E.Database size

AnswersA, B, E

More objects take longer to process.

Why this answer

The number of objects, the amount of data, and system resources all impact performance.

Full explanation →

213

MCQmedium

You need to create a PDB from a remote PDB. Which clause must be included in the CREATE PLUGGABLE DATABASE command?

A.SOURCE ...@dblink

B.USING ...@dblink

C.REMOTE ...@dblink

D.FROM ...@dblink

AnswerD

Required for remote PDB cloning.

Why this answer

The FROM clause with a database link is required for remote cloning.

Full explanation →

214

Multi-Selectmedium

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

Select 2 answers

A.Reinstalling the OS

B.Reviewing log files

C.Querying V$VERSION

D.Running a test report

E.Checking user login

AnswersB, C

Primary verification method.

Why this answer

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

Full explanation →

215

MCQmedium

What is the default behavior when a session is set to use True Cache?

A.Manual route selection

B.Transparent read routing

C.Requires a DB restart

D.Error if cache is empty

AnswerB

This is the core feature.

Why this answer

The session automatically routes queries to the cache if valid.

Full explanation →

216

MCQmedium

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

A.V$TDE_STATUS

B.V$ENCRYPTION_WALLET

C.DBA_ENCRYPTION_STATUS

D.DBA_WALLET

AnswerB

This is the correct view.

Why this answer

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

Full explanation →

217

MCQmedium

Which of these is a requirement for using True Cache?

A.An exclusive server

B.Special SQL syntax

C.A manual cache update script

D.Support from the connection pool

AnswerD

The pool handles the routing.

Why this answer

True Cache requires a connection pool that supports True Cache features.

Full explanation →

218

Multi-Selecthard

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

Select 3 answers

A.DROP INDEX

B.ALTER INDEX ... REBUILD

C.CREATE INDEX

D.PARTITION

E.USE IN VECTOR_DISTANCE

AnswersB, C, E

Supported for maintenance.

Why this answer

HNSW indexes support building, rebuilding, and usage in similarity search.

Full explanation →

219

MCQhard

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?

A.Run DBMS_STATS.LOCK_TABLE_STATS

B.Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

C.Set OPTIMIZER_FEATURES_ENABLE to a previous version

D.Use SQL Tuning Advisor to accept a new profile

AnswerB

This function creates a baseline from the cursor cache, locking the plan.

Why this answer

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

Full explanation →

220

Multi-Selecthard

Which THREE components are shared between a CDB and its PDBs?

Select 3 answers

A.Undo tablespace

B.Datafiles

C.Background processes

D.Redo log files

E.SGA

AnswersC, D, E

Processes are shared at the instance level.

Why this answer

The SGA, background processes, and redo logs are shared.

Full explanation →

221

MCQhard

Your organization requires that all data stored in the 23ai database be encrypted at rest using Transparent Data Encryption (TDE). You have configured the Keystore. Which command correctly rotates the master encryption key without re-encrypting all existing data blocks immediately?

A.ALTER SYSTEM SET ENCRYPTION KEY;

B.ALTER TABLESPACE USERS REKEY;

C.ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY 'password' WITH BACKUP;

D.ALTER SYSTEM ROTATE MASTER KEY;

AnswerC

This is the standard command for rotating the TDE master key.

Why this answer

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY 'password' WITH BACKUP rotates the master key, which is a metadata-only operation for TDE tablespaces.

Full explanation →

222

MCQhard

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

A.DB_BUFFER_CACHE

B.LARGE_POOL_SIZE

C.FAST_INGEST_BUFFER

D.MEMOPTIMIZE_POOL_SIZE

AnswerD

This is the correct parameter.

Why this answer

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

Full explanation →

223

Multi-Selecthard

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

Select 3 answers

A.Exporting the recovered table to the target database using Data Pump

B.Restoration of the tablespace containing the table to an auxiliary location

C.Automatic deletion of all backups older than the restore point

D.Creation of an auxiliary instance

E.Automatic conversion of the database to NOARCHIVELOG mode

AnswersA, B, D

Data Pump is used to import the table back into the target database.

Why this answer

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.

Full explanation →

224

Multi-Selecteasy

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

Select 2 answers

A.V$SESSION_WAIT view

B.AWR Report

C.ASH Report

D.SQL Trace (tkprof)

E.ADDM Report

AnswersB, E

AWR reports provide aggregated data over user-defined periods, perfect for trend analysis.

Why this answer

AWR reports and ADDM reports are standard tools for analyzing performance trends over extended periods (hours, days, or weeks).

Full explanation →

225

MCQmedium

You are performing a cross-platform data migration from a Linux-based Oracle 23ai source database to a Solaris target. You intend to use RMAN CONVERT DATABASE. Which prerequisite must be met to ensure the migration succeeds?

A.The source database must use Automatic Storage Management (ASM).

B.The target database must be in ARCHIVELOG mode before starting the conversion.

C.The source and target platforms must have the same endian format.

D.The source database must be in restricted mode.

AnswerC

RMAN CONVERT DATABASE is only supported when the source and target platforms have identical endianness.

Why this answer

When converting a database using RMAN, the source and target platforms must have the same endian format, or the conversion must be done via transportable tablespaces if the endianness differs. RMAN CONVERT DATABASE requires the platforms to share endianness.

Full explanation →

Oracle Database 23ai Administrator Professional (OCP, 1Z0-183) (OCP) (OCP) — Questions 151–225