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

Security updates are a standard component of Oracle patches, addressing vulnerabilities in the Oracle binaries and database software. This satisfies the stem's requirement for items generally included in a patch, as Oracle bundles critical patch updates (CPUs) specifically to remediate known CVEs across supported product versions.

Enabling row movement lets Oracle reassign a row's physical location during undo-based restoration, which Flashback Table requires to return dropped rows to their original blocks. Without it, the command fails with ORA-08189, so row movement is the mandatory prerequisite the stem demands before execution.

Recompiling invalid objects rebuilds PL/SQL units and views whose dependencies changed during the upgrade, since schema modifications invalidate stored code. This restores executable status and correct dependency chains, a required post-upgrade task before the database can serve application workloads reliably.

Verifying current patch levels confirms the source database meets 23ai's minimum release and patch prerequisites, since unsupported or unpatched versions can block or fail the upgrade. This satisfies the pre-upgrade check for a supported, adequately patched starting point.

Native Network Encryption encrypts data in transit between client and database using configured algorithms and integrity checksums, without requiring certificates or Oracle wallet setup. It satisfies the requirement for an encrypted application-server-to-database connection at the protocol layer.

The DBMS_BLOCKCHAIN_TABLE package provides procedures to verify row integrity, including checking hash values and signatures against the blockchain ledger. It is the supported PL/SQL interface for validating that blockchain rows have not been tampered with.

DBMS_COMPARISON requires a database link to the remote instance, since it reads both schemas concurrently to compare rows and report differences. Without that link, the package cannot reach the comparison database, so creating one satisfies the prerequisite for verifying integrity across the old and new 23ai instances.

DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format displays the execution plan along with runtime statistics for the last execution of the cursor. It shows actual rows, consistent gets, and other metrics per operation. This is a standard method to obtain detailed execution statistics for a SQL statement, provided the cursor is still in the library cache and statistics were collected (e.g., via GATHER_PLAN_STATISTICS hint or STATISTICS_LEVEL=ALL).

Multiplexing writes identical copies of the control file to separate locations, so one disk's failure leaves a usable copy for instance recovery. This satisfies the availability requirement implied by running in ARCHIVELOG mode, where control file loss otherwise halts recovery.

The 'log file sync' wait event occurs when a user session commits a transaction and must wait for the log writer (LGWR) to write the redo entries to the online redo log files. If the storage subsystem hosting the redo logs has high latency, commits will be slow. Placing redo logs on faster, lower-latency storage directly reduces the time spent waiting for the write to complete, thus reducing 'log file sync' waits.

The CDB instance manages all background processes, including root-level tasks and PDB-level operations, across every container in the multitenant architecture. It satisfies the constraint of spanning all containers, unlike a PDB instance which only governs its own pluggable database's processes.

Storage indexes on Exadata work by storing min/max values for columns in storage cells. If a predicate applies a function to a column, the storage index cannot be used because the function's result is not stored. By ensuring predicates are on bare columns, the storage index can filter out irrelevant storage regions, reducing physical I/O. This is a key best practice for maximizing storage index effectiveness.

DELETE OBSOLETE removes backups and archived redo logs that fall outside the configured retention policy, satisfying the requirement to purge unneeded backups. It evaluates the current retention configuration rather than deleting everything, unlike DELETE BACKUP or DELETE EXPIRED.

The DBMS_XPLAN.DISPLAY_CURSOR function can display the actual execution plan of a SQL statement that is currently in the library cache, along with runtime statistics. By specifying the 'ALLSTATS LAST' format, you can see the actual rows processed, consistent gets, physical reads, and other execution statistics for the last execution of the cursor. This method has minimal overhead because it retrieves information from the cursor's runtime statistics that are already collected by the database, without needing to re-execute the statement or enable additional tracing.

The AWR report captures snapshots of database activity, including SQL statistics ranked by CPU consumption, over a specified interval such as the last hour. This directly identifies the top CPU-consuming SQL statements, unlike real-time tools that lack historical aggregation.

AutoUpgrade includes post-upgrade cleanup, removing obsolete artefacts and invalid objects left behind after the database conversion. This satisfies the stem's requirement for a genuine AutoUpgrade action, as the tool performs remediation steps after the upgrade completes rather than only pre-checks or the upgrade itself.

A reverse key index reverses the bytes of each indexed column value, which spreads inserts across many leaf blocks instead of concentrating them on the rightmost leaf block. This reduces contention on the index during high-concurrency inserts. For a batch job inserting many rows, a reverse key index can significantly decrease 'enq: TX - index contention' waits while still enforcing uniqueness. However, it may affect range scans, so it is best for unique or primary key indexes on monotonically increasing values.

The TX enqueue wait signifies transactional locking, where one session holds a row lock and others queue for that same row until the holder commits or rolls back. The wait therefore directly indicates concurrent sessions competing for identical rows, matching the reported contention.

The 'db file scattered read' wait arises from multiblock reads, which Oracle issues during full table scans and index fast full scans. Single-block index range scans produce 'db file sequential read' instead, making this the precise cause matching the stem's high value.

This sequence starts the instance in mount mode, restores the database from backup, performs incomplete recovery to the specified time using archived logs, and then opens the database with RESETLOGS. This is the standard procedure for point-in-time recovery. The RESETLOGS is required after incomplete recovery to reset the online redo logs and start a new incarnation.

The VECTOR data type is introduced in Oracle Database 23ai specifically for storing vector embeddings. It supports fixed dimensions, such as 128, and allows efficient similarity searches using vector distance functions. It is the native type for AI workloads and integrates with Oracle's vector search capabilities.

The ADMINISTER KEY MANAGEMENT SET KEY command is used to rotate the master encryption key. Including the USING TAG clause allows you to associate a tag with the new key for identification. The WITH BACKUP clause ensures that the keystore is backed up before the key is changed, which is a best practice. This command is executed in the CDB root or the PDB where the keystore is configured.

High resource contention directly degrades performance because CPU, memory, disk, and network capacity are finite; when multiple processes compete for the same constrained resource, queuing delays increase and throughput falls. This satisfies the stem's requirement for a common degradation source, as contention predictably slows response times across workloads.

JSON relational duality lets the database expose the same underlying relational tables simultaneously as JSON documents, without duplicating or transforming the stored data. This directly satisfies the stem's requirement for the primary function: mapping relational data to JSON, so applications query either representation interchangeably.

SQL Plan Baselines lock in a known-good execution plan for a SQL statement, preventing the optimiser from regressing to a worse plan after statistics or parameter changes. This directly delivers plan stability, the property the option asserts.

DBA_SQL_PLAN_BASELINES lists every SQL plan baseline in the database, showing the SQL handle, plan name, enabled and accepted status, and origin. Querying it reveals which plans are currently captured and active, which is exactly the baseline information required.

With the control file lost, the instance starts NOMOUNT, restores the control file from autobackup, mounts, restores datafiles, recovers, then opens with RESETLOGS. This sequence satisfies the requirement to restore and recover after control-file loss.

SQL Plan Baselines capture accepted, verified plans and make the optimizer use only those, preventing it from adopting a newly generated suboptimal plan after the upgrade. This directly enforces the requirement that only known, verified execution plans are used.

A Logon Trigger fires during session establishment and can inspect attributes such as the client program, allowing SQL*Plus sessions to be rejected while application-service connections proceed. This enforces the restriction at connection time without changing database accounts or privileges.

When a Lock-Free Reservation column reaches its defined maximum, the operation fails and raises an error rather than silently wrapping or truncating. This preserves data integrity, so the application must handle the exception and adjust the reservation or column limit.

The wallet location is defined by the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora, which the database reads at startup to locate the ewallet.p12 file. Moving the wallet requires editing this file so Oracle can find the keystore for transparent data encryption.

Each database object must be processed during upgrade scripts, so a larger object count lengthens the upgrade window. The number of objects is a direct driver of upgrade duration, alongside data volume and dependent object complexity, making it a genuine influencing factor.

SQL Monitor provides real-time monitoring of long-running SQL statements, including detailed execution statistics such as actual rows, memory, time, and wait events per operation. It is designed for exactly this scenario: a currently running query that needs in-depth diagnosis. You can generate a report using DBMS_SQL_MONITOR.REPORT_SQL_MONITOR, which gives the most comprehensive information for an active execution.

Upgrade scripts and the Data Pump or catupgrd logs record each component's execution, including errors and invalid objects. Reviewing them satisfies the stem's verification requirement by exposing failures that a version banner alone would conceal, confirming every component reached the expected state.

RMAN uses the Media Management Library (MML) API to interface with third-party media managers. The media manager vendor supplies a shared library (e.g., libobk.so on Linux) that must be installed and linked with Oracle. This library allows RMAN to send backup and restore requests to the media manager, which then handles tape operations.

When a session uses True Cache, read queries are routed to the cache automatically while writes continue to the primary database, with no application changes required. This transparent read routing satisfies the default behaviour, since the session need not specify which statements go to the cache.

V$ENCRYPTION_WALLET exposes the current state of the Transparent Data Encryption wallet, including whether it is OPEN, CLOSED or UNDEFINED, plus the wallet location. Querying this dynamic performance view is the supported method for checking TDE wallet encryption status.

The GATHER_PLAN_STATISTICS hint instructs the optimizer to collect execution statistics for all operations in the plan. These statistics, including estimated and actual rows, are then available in V$SQL_PLAN_STATISTICS_ALL. This is a standard method to compare estimates with actuals for a specific execution.

CLOSE IMMEDIATE rolls back uncommitted transactions, disconnects all sessions, and closes the PDB promptly without waiting for users to log off. It is the correct choice when a maintenance window requires fast, forced disconnection of all sessions and the PDB must become unavailable immediately.

RECOVER TABLE in RMAN allows you to recover a table to a point in time before the drop, even if the recycle bin was purged. It creates an auxiliary instance, restores the necessary data files and archived logs, and extracts the table. This is the most efficient RMAN method for table recovery when Flashback Drop is unavailable.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE captures the existing optimal plan from the shared pool and loads it into a SQL plan baseline, which pins that plan so the optimizer cannot substitute a different one on later executions. This directly satisfies the requirement to lock the current plan against change.

Background processes such as DBWn, LGWR, CKPT and SMON belong to the CDB instance and serve every PDB; PDBs have no separate processes. This shared instance-level architecture is what distinguishes a CDB from non-CDB deployments.

RECOVER TABLE uses Data Pump to export the recovered table from the auxiliary instance and import it into the target database, restoring the table's data and metadata. This satisfies the requirement that the table be returned to its original schema automatically.

AutoUpgrade generates detailed log files in the specified directory, including the status of each PDB and the CDB. Reviewing these logs is essential to identify any errors or warnings that occurred during the upgrade. The logs provide information about post-upgrade fixups and any manual steps required. This is a standard verification step after running AutoUpgrade.

AWR reports capture persistent, historical snapshots of database workload statistics, enabling comparison across days or weeks. This directly satisfies the long-term trend requirement, since the stem asks for diagnosing performance over time rather than instantaneous activity. Unlike real-time views, AWR retains data in the workload repository for retrospective analysis.

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

The AWR report satisfies the requirement for identifying SQL statements ranked by elapsed time, since its SQL ordered by Elapsed Time section aggregates statements across the snapshot interval. Unlike STATSPACK, which offers similar data but with less detail, AWR captures this automatically from the Automatic Workload Repository, directly answering the stem's summary request.

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS directly alters the interval parameter controlling Automatic Workload Repository snapshot frequency, satisfying the requirement to reduce collection overhead. Unlike manual snapshots or statistics-gathering changes, it adjusts the scheduled interval itself, so fewer snapshots are captured without disabling AWR entirely.

The PDB XML metadata file records datafile locations so the PDB can be cloned or plugged in elsewhere with correct file paths. This satisfies the requirement by capturing the physical storage layout needed for relocation.

JSON relational duality exposes JSON documents as relational views over underlying tables, so DML against the JSON view is automatically propagated to those tables by the database, requiring no manual synchronisation triggers or application-side writes.

Querying DBA_ADVISOR_FINDINGS returns ADDM's stored findings directly, since ADDM writes its results into the advisor framework tables rather than exposing a dedicated view. This satisfies the requirement to view recommendations without generating a new report, though the view holds raw finding rows rather than formatted advice.

PASSWORD_VERIFY_FUNCTION names a PL/SQL function that Oracle invokes during password changes, letting you enforce bespoke complexity rules beyond the built-in limits. Referencing your custom function in the profile clause satisfies the requirement to apply organisation-specific verification logic to every user assigned that profile.

Patching applies the required updates to the dictionary during an upgrade, ensuring the target environment's data definitions match the new release. This satisfies the phase's purpose of bringing dictionary content up to the supported version.

Yes. Blockchain tables accept standard SQL DML for inserting and querying rows, so ordinary INSERT and SELECT statements work unchanged. The restriction is that UPDATE and DELETE are blocked, preserving the tamper-evident, append-only hash chain.

This command restores the data file from the RMAN backup and then applies archived redo logs to bring it up to date. It is the correct procedure for recovering a data file from a backup. The data file must be offline before restoration, and after recovery it can be brought online. This command works with tape backups as long as the media manager is configured.

Without a Fast Recovery Area, RMAN writes backups to the current working directory of the RMAN session by default, since no other destination is configured. This satisfies the stem's constraint of no FRA being set, making the session's working directory the effective default location.

Real-Time SQL Plan Management in 23ai automatically detects and prevents plan regressions by validating new plans in real time. It ensures that only plans with equal or better performance are used, providing stability for critical queries. This directly addresses the need for stable execution plans after migration.

OPatch is Oracle's patching utility for applying bundle patches and interim patches to Oracle Database homes, including 23ai. It satisfies the requirement by handling patch conflict detection, inventory updates and rollback, which the newer datapatch and autoupgrade tools do not replace for this task.

TCPS encrypts traffic using TLS, satisfying 23ai's requirement for a secure listener. Unlike plain TCP, which transmits data unencrypted, TCPS authenticates endpoints and protects credentials and SQL payloads in transit. Oracle recommends TCPS specifically for encrypted listener communication, making it the appropriate protocol here.

Active Session History samples active sessions every second into a circular buffer, recording session identifier, wait event, and blocking details. This one-second granularity over the past hour gives the session-level wait breakdown that aggregated views such as AWR cannot provide.

A Blockchain table in Oracle 23ai is immutable: rows can be inserted but not updated or deleted, and each row is cryptographically chained to the previous one. This tamper-evident, append-only design is its defining characteristic.

In a multitenant architecture, each PDB has its own data dictionary and schema objects. If the developer connects to the CDB root, they will not see tables that reside in a PDB. The error suggests they are not in the correct container. They must connect directly to the PDB using its service name.

Snapshot Carousel retention is capped by the MAX_PDB_SNAPSHOTS parameter, which defaults to 8 and can be raised to 1024. This satisfies the stem's constraint by defining the exact per-PDB snapshot limit, rather than leaving retention unbounded or fixed at a single value.

Lock-free reservations rely on the database's standard UPDATE statement, which the runtime intercepts and converts into a reservation check against the reserved stock, so no special syntax is needed. Plain UPDATE therefore satisfies the recommended approach.

SQL Profiles supply auxiliary statistics and hints that let the optimiser choose a more consistent plan for a specific statement without altering the SQL text, improving execution plan stability by countering poor cardinality estimates that cause plan changes.

Unified Auditing uses policies to capture specific activities. To audit failed logons, you create a unified audit policy with the appropriate privilege or action, such as CREATE SESSION, and include the WHENEVER NOT SUCCESSFUL clause. Then you enable the policy. This approach is flexible and aligns with the unified auditing framework in 23ai.

RESTRICTED mode blocks all connections except those holding the RESTRICTED SESSION privilege, which administrators grant to specific users for maintenance. This satisfies the stem's constraint precisely: only privileged sessions may connect while the pluggable database remains open to those accounts alone.

By default, a session connected to a True Cache instance is read-only. Any attempt to execute DML (INSERT, UPDATE, DELETE) or DDL results in an error, such as ORA-16000. This is because True Cache is designed to serve read-only queries and does not support write operations. The session cannot modify data; writes must be directed to the primary database.

ORA-01438 is raised when a value exceeds the precision defined for a NUMBER column. In lock-free reservations, if the reservation would cause the column value to exceed its maximum, Oracle raises ORA-01438. This ensures that the column's integrity is maintained. The transaction fails, and the reservation is not applied, preventing data corruption.

BACKUP ARCHIVELOG NOT BACKED UP restricts the backup to archived redo log files that have never been backed up. DELETE INPUT then removes each input file from disk after it has been successfully backed up. This matches the scenario exactly, ensuring only new logs are backed up and the originals are removed to free space.

True Cache applies redo from the primary asynchronously, so cached data lags slightly behind but reads scale without blocking the primary. This satisfies the stem's consistency requirement: the cache converges to the primary's state rather than requiring synchronous coordination.

Flashback Database uses flashback logs to return the entire database to an earlier point in time, reversing block-level changes without a full restore. This whole-database rewind is its primary purpose, distinct from object-level or row-level flashback features.

Memoptimized Write buffers incoming rows in a dedicated SGA memory area, accumulating them before the database flushes them to disk in bulk. This staging in the SGA satisfies the scenario's requirement, since writes are not persisted directly to storage on arrival.

After running AutoUpgrade in analyze mode, it generates a detailed report listing potential issues, warnings, and recommendations. Best practice is to review this report thoroughly to understand what needs to be fixed. You can then decide to fix issues manually, use fixup mode for automatic fixes, or adjust the configuration. Only after resolving issues should you proceed to deploy mode. Reviewing the report is the critical next step.

Rows past the 'no drop' period stay immutable; the retention lock only prevents deletion, it never authorises it. Oracle Blockchain Tables expose no automatic purge, so rows persist until an explicit DELETE runs. This satisfies the stem's default-behaviour constraint: expiry of the no-drop window merely permits deletion, it does not trigger it.