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

Oracle Wallet stores and manages the TDE master encryption key, which is mandatory for column and tablespace encryption in 23ai. Without this keystore, the database cannot encrypt or decrypt data, so it directly satisfies the stem's requirement for implementing Transparent Data Encryption.

V$ENCRYPTED_TABLESPACES lists every tablespace with its encryption status, so querying it directly satisfies the requirement to verify which tablespaces are encrypted in 23ai. It reports the ENCRYPTED column plus the encryption algorithm, giving authoritative confirmation without inspecting datafiles or wallet configuration.

BACKUP PLUGGABLE DATABASE restricts the backup scope to the named PDB alone, so only that pluggable database's datafiles and control file metadata are copied. This satisfies the multitenant constraint of isolating one PDB without touching the CDB root or other PDBs.

The ALTER TABLE ... MODIFY (column ENCRYPT) command encrypts an existing column that was not previously encrypted. It is used for column-level encryption when you want to encrypt specific columns containing sensitive data. This command causes the column data to be encrypted using the TDE master key. It is a required step if you have existing columns that need encryption, as TDE does not automatically encrypt existing data without explicit commands.

Vector distance calculations execute through native SQL operators, letting the optimiser push computations into the database engine rather than fetching vectors to clients. This satisfies the scale constraint by avoiding per-row round trips, enabling parallel execution and index-assisted approximate search across large vector sets.

V$RECOVERY_FILE_DEST reports the Fast Recovery Area's configured location, disk quota limit, space used, and space reclaimable, directly satisfying the need to check current space usage. Querying it reveals whether the recovery area has filled to its quota, which commonly causes RMAN's inability to access it.

V$SQL_PLAN displays the execution plans for cursors currently in the library cache. It shows the plan that was actually used for each child cursor, including operations, costs, and cardinalities. By joining to V$SQL on SQL_ID and CHILD_NUMBER, you can retrieve the exact plan for the most recent execution, making it the correct choice.

Lock-Free Reservations allow multiple sessions to update a numeric column concurrently without row-level locking, by using a reservation mechanism that tracks changes in a separate structure. This eliminates contention on the row and improves throughput. It is designed for high-concurrency updates to a single row, exactly the scenario described.

ALTER PLUGGABLE DATABASE ... CLOSE IMMEDIATE closes a single PDB without affecting the CDB instance or other PDBs. It rolls back that PDB's active transactions and disconnects its users. This is the correct way to isolate maintenance to one container while keeping the rest of the CDB available.

FORCE overrides session or object-level restrictions that would otherwise downgrade a DML statement to serial execution, compelling the parallel coordinator to engage. It satisfies the stem's constraint by guaranteeing parallel DML runs regardless of conflicting settings.

This command deletes archived redo logs that have already been backed up at least once to disk, which is safe because the backups preserve recoverability. It frees space in the fast recovery area immediately while honoring the principle that only backed-up logs are removed. The retention policy ensures logs still needed for recovery are retained, preventing premature deletion that would break restore capability.

Literals force Oracle to hard-parse every distinct statement, so each execution incurs full parse and optimisation cost, inflating parse time. Bind variables let one parsed cursor be reused across executions, eliminating repeated hard parsing. This directly addresses the high parse time described in the stem.

Block-level media recovery uses the RECOVER ... BLOCK syntax and can be executed while the database is open, restoring and recovering only the specified block from an RMAN backup. It requires the data file to be online and the database in ARCHIVELOG mode, exactly matching this scenario where only one block in file 7 is corrupt.

The AutoUpgrade utility generates detailed log files that include specific error messages and recommendations. For a DBMS_LOB related error, the logs may indicate a missing prerequisite patch or a need to run the preupgrade fixup script. Following the recommended actions, such as applying a patch or re-running the preupgrade tool, is the correct approach to resolve the issue before proceeding with the upgrade.

RESTORE DATAFILE 7 pulls the most recent usable backup of that file from the fast recovery area or backup set, and RECOVER DATAFILE 7 applies archived and online redo to roll it forward to a consistent SCN. Because the control file is intact, RMAN knows the exact checkpoint and can perform the targeted recovery without touching other data files, minimizing downtime for the SALES tablespace.

HNSW builds a hierarchical navigable small-world graph, enabling logarithmic-time approximate nearest-neighbour traversal instead of exhaustive comparison. This directly addresses the slow similarity search by pruning the candidate set during query, satisfying the performance constraint. Unlike flat or IVF indexes, it delivers low-latency recall without scanning every vector.

ASH samples active session activity every second into a circular buffer, so it exposes transient spikes and brief contention that AWR's ten-minute snapshots smooth over. This short-term sampling interval is precisely what makes it suited to analysing short-term performance.

This command transitions SALESPDB from MOUNTED to READ WRITE in one step and is issued from the CDB root. Because the PDB datafiles are already open at the CDB instance level when the PDB is MOUNTED, opening it requires no additional instance startup and is the standard, lowest-effort recovery action for this exact symptom.

Must be unique for each database in a Data Guard configuration.

This sequence mounts the database, restores all data files from backup, recovers the database up to the specified SCN, and then opens with RESETLOGS. Incomplete recovery requires the database to be mounted, and after recovery, RESETLOGS is mandatory to reset the online redo logs and create a new incarnation.

Oracle 23ai caps VECTOR columns at 2048 dimensions, matching the maximum array length the vector data type stores. Exceeding this limit is rejected at DDL time, so 2048 is the correct ceiling for dense and sparse vectors alike.

A multitenant container database shutdown cascades to its pluggable databases: the CDB closes every PDB first, then shuts itself down. This ordered teardown satisfies the default behaviour, whereas PDBs cannot remain open once the root container and instance terminate.

Marking a block corrupt during an RMAN backup records it in V$DATABASE_BLOCK_CORRUPTION, satisfying the need to track physical corruption for later recovery. RMAN populates this view automatically, so subsequent BACKUP VALIDATE or RECOVER CORRUPTION operations can target the affected blocks without rescanning the entire datafile.

Lock-Free Reservations let concurrent transactions decrement or increment numeric columns, such as inventory or account balances, without row-level locks. This removes serialisation and lock contention between sessions, directly improving throughput for high-concurrency workloads where many transactions target the same rows.

Reissuing the OPEN command without the RESTRICTED clause transitions REPORTSPDB to READ WRITE mode with normal connection semantics, allowing the developer to connect and create a local user. Because the statement names only REPORTSPDB, other PDBs in FINCDB retain their current open modes, satisfying the requirement to limit the change to one PDB.

SHOW CON_NAME returns the name of the container to which the current session is connected, directly answering the requirement to display the active container. It works in both CDB root and PDB contexts without needing to query data dictionary views.

Data Masking replaces sensitive values with realistic but fictitious data, preserving referential integrity and application behaviour while removing genuine personal or confidential information. This directly satisfies the non-production constraint: development, test and training environments need realistic datasets without exposing live production data, so masking enables safe sharing outside production.

Preferences allow granular control over how stats are gathered for a specific object.

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET polls the shared pool's cursor cache over a specified interval, extracting SQL statements and their execution statistics directly into a SQL Tuning Set. This satisfies the stem's requirement to capture statements from the cursor cache, unlike alternatives that load sets from AWR snapshots or manual SQL text.

DB Time is the sum of the time spent by all foreground sessions in database calls (CPU time plus wait time). Elapsed Time is the actual wall-clock time of the AWR snapshot interval. If DB Time exceeds Elapsed Time, it means that on average, more than one session was active at the same time. For example, if DB Time is 10 minutes and Elapsed Time is 5 minutes, the average concurrency was 2. This indicates that the database was handling multiple concurrent requests, which is normal in a busy system but can highlight contention if waits are high.

Elapsed Time measures the total wall-clock duration of the SQL execution, from parse through fetch completion, including all waits and CPU time. It reflects the full end-to-end latency the user experiences, not just CPU consumption or database time.

V$PX_SESSION exposes one row per active parallel execution server session, showing degree of parallelism, slave set, and session pairing. This gives the finest-grained real-time view of parallel server activity, unlike aggregate views such as V$PX_PROCESS or V$SQL.

Schema-level privilege analysis can be scoped to a specific schema or the entire database. This flexibility allows DBAs to focus on particular schemas of interest or analyze all schemas. The scope is defined when creating the privilege analysis policy.

AI Vector Search ships natively in 23ai, letting developers store embeddings in VECTOR columns and run similarity queries through SQL. It removes the need for a separate vector database, simplifying management of AI-driven workloads within the existing Oracle Database engine.

A duality view is defined over one or more relational tables and presents them as JSON documents. Applications can issue JSON queries and DML against the view, and Oracle translates those operations into relational operations on the base tables, so both views stay consistent with a single storage footprint. This directly satisfies the requirement for synchronized relational and JSON access without duplication.

Configures the listener endpoint.

Parallel DML in Oracle 23ai retains full transactional integrity: each parallel execution server performs its portion of the work within the same transaction, and a failure triggers a coordinated rollback of all changes. This satisfies the stem's requirement that no partial data persists when the transaction fails.

The FROM ACTIVE DATABASE clause copies datafiles directly from the source database's memory and online files over the network, bypassing backup media entirely. This eliminates the restore phase, satisfying the scenario's need for speed, since no backup retrieval or staging is required before duplication proceeds.

Connecting RMAN to the standby and the recovery catalog lets backups taken on the standby be recorded directly in the shared catalog, satisfying the offload requirement. The standby must be registered as its own target so its backup metadata is captured and visible for restore operations.

CONFIGURE CONTROLFILE AUTOBACKUP ON makes RMAN write the control file and server parameter file automatically after every structural change, such as adding a datafile or tablespace. This directly satisfies the stem's requirement for automatic backup on database structure modification, without manual intervention.

V$ACTIVE_SESSION_HISTORY samples active sessions every second, retaining recent history in memory, so it exposes which sessions blocked others during the last 10 minutes. This directly satisfies the stem's requirement to pinpoint blocked sessions within that short window.

Creating the XML metadata file during unplugging captures the PDB's data dictionary, tablespace definitions and service details, which the destination CDB requires to register and integrate the plugged-in PDB correctly. Without this manifest, the target CDB cannot reconcile the PDB's structure, so the operation fails to preserve data integrity across the two containers.

Implementing a solution closes the tuning lifecycle by applying the chosen change to the production system. This satisfies the stem's requirement for a lifecycle component, since tuning progresses from observation through diagnosis to corrective action, and implementation is the stage where the remedy is actually deployed and its effect realised.

ARCHIVELOG mode is required for the upgrade process because it allows for recovery in case of failure. Additionally, sufficient disk space is needed for the new Oracle home, data dictionary changes, and archive logs generated during the upgrade. Verifying these prerequisites prevents interruptions and ensures the upgrade can complete successfully.

SQL Tuning Sets capture a defined group of SQL statements with their execution context, and can be exported and imported into another database. This satisfies the stem's requirement to group high-CPU statements and transport them across environments.

V$LOCK shows locks held and requested, including TX locks. By joining V$LOCK with V$SESSION, you can identify the blocking session and the object causing the contention. This is the primary diagnostic step to find the root cause of row lock contention. It helps determine which session is holding the lock and what SQL it is executing.

V$LOCK shows current lock holders and requesters, including the blocking session ID and the object ID for TX enqueue waits. Joining V$LOCK with DBA_OBJECTS and V$SESSION identifies the exact row-level conflict. This is the correct first step to pinpoint which session holds the lock and what object is involved, enabling targeted resolution.

Vector indexes in Oracle Database 23ai are created with a specific distance metric, such as COSINE, EUCLIDEAN, or DOT. If a query uses a different distance metric in VECTOR_DISTANCE, the optimizer cannot use the index and must perform a full table scan. This is a common cause of index not being used.

Memoptimized Write buffers rows in the SGA but still applies redo and undo logging, so committed transactions remain durable and recoverable. Maintaining ACID compliance satisfies the integrity requirement, since buffering changes performance only, not transactional guarantees.

The auxiliary instance is a separate, temporary Oracle instance where the target tablespaces' datafiles are restored and recovered to the chosen point in time, isolating the recovery work from the production database before the recovered tablespaces are exported and plugged back in.

When cloning a PDB locally using CREATE PLUGGABLE DATABASE ... FROM, the source PDB must be in READ ONLY mode to ensure a consistent copy. This prevents changes to the source during the cloning process, guaranteeing the clone is a point-in-time snapshot. If the source is open READ WRITE, the command will fail.

Lock-Free Reservations let concurrent transactions reserve column values without holding row-level locks, eliminating the blocking waits that serialise inserts and updates. This directly reduces row-level locking contention, the bottleneck the stem identifies in high-concurrency workloads.

Automatic PGA Memory Management uses PGA_AGGREGATE_TARGET to dynamically allocate work areas. When a query spills to disk, it indicates insufficient PGA memory for the operation. Increasing this parameter gives the database more memory to allocate to work areas, reducing or eliminating temp space usage. This is the correct action to address the spills.

The SQL ID is the hash-derived identifier Oracle assigns to a SQL statement's text in the shared pool, letting you trace that statement across V$SQL, AWR and ASH. It identifies the statement itself, not a session or execution plan.

Direct path reads occur when Oracle bypasses the buffer cache and reads blocks straight into PGA memory, typical of parallel or serial full table scans on large segments. Excessive waits therefore point to a full table scan rather than index or rowid access.

AutoUpgrade ships as a standalone Java archive, so invoking it with java -jar autoupgrade.jar launches the upgrade process and presents its command console. No database connection or SQL client is involved, distinguishing it from in-database utilities.

SQLNET.ENCRYPTION_TYPES_SERVER lists the cipher suites the database server will accept for encrypted connections, satisfying the requirement to specify encryption settings in sqlnet.ora. It works alongside SQLNET.ENCRYPTION_SERVER, which enables or declines encryption, making it one of the two parameters that define server-side encryption configuration.

JSON Relational Duality Views provide a single source of truth where relational data is exposed as JSON documents, allowing both SQL and Oracle API for MongoDB to operate on the same data without duplication. This directly meets the requirement of supporting both access methods while avoiding transformation logic.

RECOVER DATABASE applies archived redo logs and any available incremental backups to the restored data files, rolling them forward to a consistent SCN. This is exactly the step that consumes the archived logs in the fast recovery area and completes media recovery, making it the correct command after the restore.

True Cache runs as a separate Oracle instance in the application tier, close to the application servers rather than alongside the primary database. This placement reduces network round-trip latency for read queries while the primary database remains the single source of truth.

DBMS_PDB.CHECK_PLUG_COMPATIBILITY validates a non-CDB's data dictionary against 23ai CDB requirements before plugging, reporting violations that would block migration. It satisfies the stem's pre-migration compatibility assessment constraint, unlike DBMS_PDB.DESCRIBE (metadata only) or transportable tablespace checks, which do not evaluate full plug-in readiness.

Issuing ALTER PLUGGABLE DATABASE SALESPDB OPEN READ ONLY transitions the PDB into a read-only state where users can query data but cannot perform DML or DDL against the PDB. This matches the audit requirement while leaving other PDBs untouched, because the command names only SALESPDB and the root container does not change its own open mode.

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE verifies unaccepted plans against the SQL performance history and promotes those that perform better to accepted status, optionally running in verify or commit mode. It is the sole DBMS_SPM procedure providing this evolution capability.

Blockchain tables are append-only and tamper-evident, with rows cryptographically chained so any alteration is detectable. That immutability suits audit logs and financial records, where preserving an unalterable historical trail of transactions is the governing requirement.

Vector similarity search returns rows ordered by their distance from the query vector, typically ascending so the nearest neighbours appear first. This directly satisfies the stem's requirement to know the search result, since the defining output is a ranked list based on similarity distance rather than exact matches.

Memoptimized Rowstore – Fast Ingest is designed for high-frequency, concurrent inserts. It buffers inserts in a memory area and writes them to disk asynchronously, reducing buffer busy waits and improving insert throughput. This directly addresses the contention observed by the DBA without application changes.

SAVE STATE records the PDB's current open mode so that on subsequent CDB startups the PDB is automatically reopened in that same mode. Opening it READ ONLY first and then saving state captures exactly the desired behavior. This is the supported mechanism for persisting a non-default open mode across restarts.

SQL*Plus remains the standard command-line tool for administering CDB and PDB configuration in Oracle 23ai, satisfying the requirement for a native, always-available interface. It connects directly to the root or a pluggable database, letting you run ALTER SYSTEM and ALTER DATABASE statements to modify container-level parameters without extra tooling.

V$PDBS lists every pluggable database within a container database, including its open mode and restricted status, directly satisfying the requirement to report open status across all PDBs. Querying it from the CDB root returns one row per PDB, unlike V$DATABASE, which describes only the current container.

DBMS_SQLTUNE.CREATE_SQLSET creates the empty SQL tuning set object, which is then populated from sources such as the cursor cache, AWR or SQL trace before being passed to the SQL Tuning Advisor as its input workload.

V$PGASTAT provides instance-level statistics on PGA memory usage, including total PGA allocated, cache hit percentage, and the number of multi-pass and one-pass executions for work areas. This view directly shows if operations are spilling to disk and the overall efficiency of PGA memory, making it the correct choice.

The application's connection pool must be updated so it can route read-only connections to the True Cache instance rather than only the primary database. This satisfies the application-tier routing requirement, directing eligible queries to the cache.

Unified Auditing merges traditional and fine-grained auditing into a single configuration and writes all records to one unified audit trail, eliminating separate audit configurations and their scattered trails. This delivers simplified audit management, the primary benefit sought.

Memoptimized Write buffers inserts in a dedicated memory area and flushes them to disk in batches, bypassing much of the per-row redo and buffer overhead. This directly satisfies the minimal-overhead requirement for high-frequency single-table inserts, unlike conventional row-by-row paths.

Flashback Database restores the entire database to an earlier point in time using flashback logs, letting you rewind past a failed upgrade quickly rather than restoring from backup. This directly satisfies the stem's requirement for fast recovery after an unsuccessful upgrade.