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

Multi-Selectmedium

Which TWO actions are required to enable Flashback Database on a production environment?

Select 2 answers

A.Restart the database in UPGRADE mode.

B.Ensure the database is in ARCHIVELOG mode.

C.Set DB_RECOVERY_FILE_DEST_SIZE to zero.

D.Set the DB_FLASHBACK_RETENTION_TARGET parameter.

E.Drop all existing restore points.

AnswersB, D

This is a mandatory prerequisite for Flashback Database.

Why this answer

Flashback Database requires the database to be in ARCHIVELOG mode and the recovery area to be configured.

Full explanation →

Multi-Selectmedium

You are reviewing security best practices for 23ai. Which THREE of the following represent recommended actions to harden a production instance?

Select 3 answers

A.Apply the latest quarterly Release Update (RU)

B.Use Oracle Key Vault for TDE master key management

C.Enforce strong password profiles

D.Run database in NoArchiveLog mode for security

E.Disable all default schemas immediately

AnswersA, B, C

Regular patching mitigates known vulnerabilities.

Why this answer

Using Oracle Key Vault, enforcing password complexity, and applying the latest Release Updates are core hardening practices.

Full explanation →

MCQeasy

A DBA needs to identify which PDBs are currently using more than their allocated TEMP tablespace quota. Which view should be queried?

A.V$PDB_TEMP_SPACE_USAGE

B.DBA_TEMP_FILES

C.V$SESSTAT

D.DBA_USERS

AnswerA

This view provides real-time information on temporary tablespace utilization per PDB.

Why this answer

V$TEMP_SPACE_HEADER and V$TEMPSEG_USAGE are standard, but V$PDB_TEMP_SPACE_USAGE provides the specific view for monitoring PDB-level temporary tablespace usage.

Full explanation →

Multi-Selectmedium

Which TWO statements are true regarding the use of a Recovery Catalog?

Select 2 answers

A.It can be used to store backup scripts.

B.It is required to perform RMAN backups.

C.It is physically located inside the target database.

D.It stores metadata about backups for multiple databases.

E.It eliminates the need for control file autobackups.

AnswersA, D

Persistent scripts are a key feature of the catalog.

Why this answer

A recovery catalog provides history beyond the control file limit and centralizes metadata for multiple databases.

Full explanation →

MCQmedium

When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?

A.The time spent on disk I/O only

B.The time spent on instance background processes

C.The time the database was open

D.The total time spent by sessions in the database

AnswerD

DB Time is the sum of CPU and wait time for all user sessions.

Why this answer

DB Time represents the total time spent by foreground processes in the database, indicating overall load.

Full explanation →

MCQmedium

What is the primary function of the RMAN 'SWITCH' command?

A.To move the database to a new server.

B.To update the control file to point to a new datafile copy.

C.To switch between primary and standby.

D.To switch the log file.

AnswerB

This is the primary function of SWITCH.

Why this answer

The SWITCH command updates the control file to point to a new location for datafiles, typically after a copy has been restored.

Full explanation →

MCQhard

You have a 19c database. Which tool must you run to ensure the database is ready for an upgrade to 23ai?

A.utlrp.sql

B.dbv

C.catupgrd.sql

D.preupgrade.jar

AnswerD

This is the pre-upgrade assessment tool.

Why this answer

The Pre-Upgrade Information Tool (preupgrade.jar) is mandatory for evaluating the source database.

Full explanation →

Multi-Selecthard

Which THREE factors influence the optimizer's choice of an execution plan?

Select 3 answers

A.Optimizer statistics

B.Indexes on the queried columns

C.The current time of day

D.Initialization parameters

E.User password strength

AnswersA, B, D

Statistics are the foundation of cost estimation.

Why this answer

The optimizer considers statistics, system parameters, and schema objects.

Full explanation →

MCQhard

You are performing a cross-platform transportable tablespace migration from Linux to Windows. Which RMAN command is required to convert the datafiles for the destination platform?

A.ALTER TABLESPACE ... CONVERT;

B.RMAN TRANSPORT ...

C.CONVERT TABLESPACE ... TO PLATFORM 'Microsoft Windows x86 64-bit';

D.RMAN COPY ...

AnswerC

This correctly identifies the destination platform endian format.

Why this answer

The CONVERT TABLESPACE command is used to change the endian format for cross-platform migrations.

Full explanation →

MCQeasy

You want to use Memoptimized Write to speed up data ingestion. Which type of table should you use?

A.External table

B.Global temporary table

C.Index-organized table

D.Heap-organized table

AnswerD

This is the type compatible with the feature.

Why this answer

Memoptimized Write is specifically enabled on tables that have the MEMOPTIMIZE FOR WRITE clause.

Full explanation →

MCQmedium

You are configuring a PDB to use Local Undo. What requirement must be met before setting the UNDO_TABLESPACE parameter?

A.The CDB must be in read-only mode

B.The PDB must be closed

C.The CDB must be in ARCHIVELOG mode

D.The PDB must be a proxy PDB

AnswerB

Local undo changes require the PDB to be closed or restricted.

Why this answer

The CDB must be in local undo mode, and the PDB must be open in restricted mode or closed.

Full explanation →

MCQeasy

How is AI Vector Search integrated into SQL?

A.Via external API calls

B.Via a new programming language

C.Using standard SQL syntax

D.Via PL/SQL only

AnswerC

Vector operations are native to SQL.

Why this answer

Vector search uses standard SQL operators like VECTOR_DISTANCE.

Full explanation →

MCQmedium

Which parameter ensures that the listener uses a wallet for SSL?

A.WALLET_LOCATION

B.SECURE_CONTROL_LISTENER

C.SSL_CIPHER_SUITES

D.SSL_SERVER_CERT_DN

AnswerA

This parameter points to the wallet file.

Why this answer

The listener.ora file must be configured with the SSL_CLIENT_AUTHENTICATION and WALLET_LOCATION parameters.

Full explanation →

MCQeasy

You need to perform a table-level point-in-time recovery. Which RMAN command syntax is correct?

A.RESTORE TABLE ...

B.RECOVER TABLE HR.EMPLOYEES UNTIL SCN 12345;

C.FLASHBACK TABLE ...

D.IMPORT TABLE ...

AnswerB

Correct syntax for table PITR.

Why this answer

RECOVER TABLE ... UNTIL SCN is the syntax for table-level recovery.

Full explanation →

MCQeasy

Which RMAN command displays the database structure and associated datafiles?

A.DESCRIBE DATABASE

B.LIST SCHEMA

C.SHOW SCHEMA

D.REPORT SCHEMA

AnswerD

This command displays the current database architecture.

Why this answer

REPORT SCHEMA shows the layout of the database and its datafiles.

Full explanation →

MCQhard

You are migrating an Oracle 23ai database to a different platform with different endianness. Which RMAN command is used to convert the datafiles to the target platform format?

A.RMAN> CONVERT DATABASE ON TARGET PLATFORM;

B.RMAN> CONVERT DATAFILE '...' TO PLATFORM '...' FROM PLATFORM '...';

C.RMAN> RESTORE DATAFILE FROM PLATFORM;

D.ALTER DATABASE CONVERT TO PLATFORM;

AnswerB

This correctly identifies the source and destination endian formats for conversion.

Why this answer

The CONVERT DATAFILE command is required when the source and destination platforms have different endian formats.

Full explanation →

MCQmedium

You are configuring a recovery catalog in Oracle 23ai. Which command must you execute first to ensure the catalog is ready to register the target database?

A.CREATE CATALOG;

B.REGISTER DATABASE;

C.CONNECT CATALOG;

D.UPGRADE CATALOG;

AnswerA

This command creates the necessary tables in the RMAN schema.

Why this answer

The CREATE CATALOG command must be executed to initialize the schema before registration.

Full explanation →

Multi-Selecthard

Which THREE characteristics are unique to PDBs?

Select 3 answers

A.Dedicated local users

B.Shared redo logs

C.Independent datafiles

D.Shared background processes

E.Individual services

AnswersA, C, E

Local users exist only within the PDB.

Why this answer

PDBs have their own datafiles, their own users, and their own services.

Full explanation →

MCQmedium

You need to ensure that the PDB$SEED template is updated with the latest patch level from the CDB. How should you proceed?

A.Run 'ALTER PLUGGABLE DATABASE PDB$SEED SYNC'

B.Drop and recreate PDB$SEED using the CREATE PLUGGABLE DATABASE command

C.Apply the patch to the CDB and let the system handle synchronization

D.Open PDB$SEED in READ WRITE mode and apply patches

AnswerC

Patching the CDB home is the standard procedure; the seed is updated automatically by the upgrade scripts.

Why this answer

The PDB$SEED is read-only. To update it, you must apply the patch to the CDB home, and the seed will be automatically synchronized during the next CDB startup or via the execution of the datapatch utility.

Full explanation →

MCQeasy

What is the purpose of the 'Seed' PDB (PDB$SEED)?

A.To store undo data

B.To act as a template for new PDBs

C.To hold CDB diagnostic logs

D.To store temporary data

AnswerB

PDB$SEED is the mandatory template for cloning.

Why this answer

PDB$SEED is the template for creating new PDBs.

Full explanation →

Multi-Selectmedium

Which TWO of the following are valid methods to move a database across platforms using RMAN?

Select 2 answers

A.Transportable Tablespaces

B.RMAN DUPLICATE DATABASE FROM ACTIVE DATABASE

C.Data Pump Export/Import with Full Database mode

D.RMAN BACKUP AS COPY

E.RMAN CONVERT DATABASE

AnswersA, E

A standard method for moving data across platforms using RMAN/Data Pump.

Why this answer

Transportable Tablespaces and RMAN CONVERT DATABASE are the two primary supported methods for moving data across platforms.

Full explanation →

MCQmedium

Which utility is used to perform a 'fixup' during the AutoUpgrade process?

A.srvctl

B.utlu122s.sql

C.catupgrd.sql

D.preupgrade_fixups.sql

AnswerD

This is the standard script for manual fixups.

Why this answer

AutoUpgrade performs fixups automatically, but you can also run them manually using the pre-upgrade fixup scripts.

Full explanation →

Multi-Selectmedium

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

Select 2 answers

A.V$CONTROLFILE

B.V$SESSION_WAIT

C.DBA_TABLES

D.DBA_USERS

E.V$SESSION

AnswersB, E

Gives session wait event data.

Why this answer

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

Full explanation →

MCQhard

You observe that a PDB is consuming excessive UNDO tablespace. You want to implement 'Local Undo' mode for the CDB. What is the consequence of this change?

A.Local Undo is enabled by default in 23ai

B.Shared undo tablespace will be automatically deleted

C.Only new PDBs will use local undo

D.All PDBs must be closed before switching to local undo

AnswerD

Changing from shared undo to local undo requires the CDB to be in a state where all PDBs are closed.

Why this answer

Enabling Local Undo mode means each PDB manages its own undo tablespace. This is a CDB-wide setting that requires all PDBs to be closed before the CDB can be switched to this mode.

Full explanation →

MCQhard

You are configuring Parallel DML and want to ensure the session uses the maximum available resources. Which hint should you use?

A./*+ MAX_PARALLEL */

B./*+ PARALLEL(table, n) */

C./*+ SERIAL */

D./*+ DML_PARALLEL */

AnswerB

This specifies the parallel degree.

Why this answer

The PARALLEL hint allows you to specify the degree of parallelism for a statement.

Full explanation →

Multi-Selectmedium

Which TWO methods can be used to copy a PDB?

Select 2 answers

A.Export/Import

B.SQL*Loader

C.Cloning

D.Unplug/Plug

E.Flashback

AnswersC, D

CREATE PLUGGABLE DATABASE ... AS CLONE.

Why this answer

Cloning and unplugging/plugging are standard methods.

Full explanation →

MCQmedium

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

A.It permanently changes the SQL code

B.It implements the recommended SQL Profile

C.It deletes the SQL from the cache

D.It updates the global statistics

AnswerB

Accepting applies the profile to influence the optimizer.

Why this answer

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

Full explanation →

MCQhard

What is the consequence of not running the 'catupgrd.sql' script after a major version upgrade?

A.Only performance is affected

B.The database will run in read-only mode

C.Data dictionary remains in old version format

D.The database will not start

AnswerC

The dictionary must be upgraded to match the engine version.

Why this answer

The data dictionary will be inconsistent, and many objects will be invalid, leading to database failure.

Full explanation →

MCQeasy

Which background process is responsible for capturing AWR snapshots?

A.PMON

B.MMNL

C.DBWn

D.LGWR

AnswerB

MMNL performs background tasks, including AWR snapshotting.

Why this answer

The MMNL (Manageability Monitor) process is responsible for AWR snapshots.

Full explanation →

Multi-Selecthard

Which THREE tasks are performed by the 'RECOVER' command during a data file restore?

Select 3 answers

A.Restoring the datafiles from backup.

B.Applying archived redo logs.

C.Applying the online redo logs.

D.Creating new control files.

E.Verifying the headers of the datafiles.

AnswersB, C, E

Recovery involves applying logs to reach the desired point.

Why this answer

It applies logs, verifies file status, and marks files as online.

Full explanation →

Multi-Selecthard

Which THREE are characteristics of Flashback Logs?

Select 3 answers

A.They are stored in the Fast Recovery Area (FRA).

B.They are backed up using RMAN.

C.They are circular in nature.

D.They contain redo information.

E.They are used to revert the database state.

AnswersA, C, E

FRA is the dedicated location for flashback logs.

Why this answer

They are stored in the FRA, they are circular, and they are not backups.

Full explanation →

MCQmedium

Which component is responsible for the verification of Blockchain tables?

A.DBMS_BLOCKCHAIN_TABLE

B.RMAN

C.The Database Vault

D.The SQL optimizer

AnswerA

This is the correct package.

Why this answer

The DBMS_BLOCKCHAIN_TABLE package is the interface for verification.

Full explanation →

MCQeasy

Which action is required before applying a PSU (Patch Set Update) to a 23ai database?

A.Change the global name

B.Take a RMAN backup

C.Stop the database

D.Recompile all objects

AnswerB

Backups are essential for recovery if a patch fails.

Why this answer

It is critical to take a full backup of the database before applying any patches.

Full explanation →

Multi-Selectmedium

Which TWO of the following are valid RMAN retention policies?

Select 2 answers

A.CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

B.CONFIGURE RETENTION POLICY TO 100GB;

C.CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

D.CONFIGURE RETENTION POLICY TO NONE;

E.CONFIGURE RETENTION POLICY TO INFINITE;

AnswersA, C

This is a valid redundancy-based policy.

Why this answer

Redundancy and Recovery Window are the two primary retention policies.

Full explanation →

MCQeasy

Which parameter defines the location of the PDB metadata files during a plug operation?

A.PDB_FILE_NAME_CONVERT

B.DB_CREATE_FILE_DEST

C.DATA_FILE_DIR

D.FILE_NAME_DIR

AnswerA

Helps relocate datafiles during PDB operations.

Why this answer

The file_name_convert parameter or explicit pathing is used.

Full explanation →

MCQhard

Which command is used to move a PDB from one CDB to another without using Data Pump?

A.CREATE PDB AS CLONE

B.DBMS_PDB.COPY

C.ALTER PLUGGABLE DATABASE UNPLUG

D.MOVE PLUGGABLE DATABASE

AnswerC

Unplugging generates the XML metadata for transport.

Why this answer

The unplug/plug method using an XML metadata file is the standard for PDB transport.

Full explanation →

MCQhard

Which feature allows you to perform point-in-time recovery for a specific PDB without affecting others?

A.Flashback Database

B.RMAN PDB PITR

C.PDB Flashback

D.Full Database Recovery

AnswerB

Supports recovery of a PDB to a specific timestamp.

Why this answer

RMAN PDB-level point-in-time recovery is supported.

Full explanation →

MCQeasy

Which RMAN command should you use to verify that your backup pieces are not corrupted?

A.VALIDATE BACKUPSET

B.LIST BACKUP

C.CROSSCHECK BACKUP

D.RESTORE DATABASE VALIDATE

AnswerA

This is the specific command to check backup piece integrity.

Why this answer

VALIDATE BACKUPSET checks the integrity of the backup pieces.

Full explanation →

MCQmedium

You are using RMAN to backup a database. What does the 'COMPRESS' clause in the backup command do?

A.It validates the backup.

B.It encrypts the backup.

C.It reduces the backup size.

D.It speeds up the backup.

AnswerC

Compression reduces the stored file size.

Why this answer

The 'COMPRESS' clause reduces the size of the backup pieces by applying compression, saving storage space.

Full explanation →

MCQeasy

What is the main requirement for an existing table to be converted into a Blockchain table?

A.Tables cannot be converted to Blockchain

B.The table must have an index

C.The table must be partitioned

D.The table must be empty

AnswerA

Blockchain table properties must be defined at creation.

Why this answer

A table cannot be converted to a Blockchain table; it must be created as such.

Full explanation →

MCQmedium

In Oracle 23ai, you want to use Database Vault to prevent highly privileged users (like SYS) from accessing sensitive application data. Which component must be enabled and configured to achieve this separation of duty?

A.Virtual Private Database (VPD)

B.Data Redaction

C.Database Vault Realms

D.Label Security

AnswerC

Realms protect specific data structures from unauthorized privileged user access.

Why this answer

Database Vault Realms are specifically designed to restrict access to data by privileged users.

Full explanation →

MCQmedium

Which file stores the metadata for a PDB being unplugged?

A.XML file

B.TXT file

C.JSON file

D.SQL file

AnswerA

The XML file contains metadata for the PDB transport.

Why this answer

The unplug process generates an XML file to describe the PDB.

Full explanation →

MCQmedium

In the context of Wide Tables, what is a recommended best practice?

A.Maintain logical data grouping

B.Only use for read-only data

C.Disable all indexes

D.Use as many columns as possible

AnswerA

Even in wide tables, design matters.

Why this answer

Despite the high column limit, logical design should still be considered.

Full explanation →

MCQeasy

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

A.Connection scaling

B.Security authentication

C.Data encryption

D.Audit trail management

AnswerA

It manages server process overhead efficiently.

Why this answer

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

Full explanation →

MCQeasy

You need to ensure that RMAN automatically deletes archived redo logs after they have been backed up twice to tape. Which configuration command should you use?

A.CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE SBT;

B.CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

C.SET ARCHIVELOG DELETION POLICY TO 2 COPIES;

D.CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

AnswerA

This is the correct syntax for setting the deletion policy based on backup redundancy.

Why this answer

The CONFIGURE ARCHIVELOG BACKUP COPIED command with the TO DEVICE TYPE and num_copies parameters defines the retention policy for archived logs.

Full explanation →

Multi-Selecthard

Which THREE pieces of information are needed to restore a database to a specific point-in-time using RMAN?

Select 3 answers

A.The operating system password.

B.The current SPFILE.

C.The DBID of the database.

D.A valid control file backup.

E.The target timestamp or SCN.

AnswersC, D, E

Required if the control file is lost.

Why this answer

You need the DBID, the control file, and the point-in-time target.

Full explanation →

Multi-Selectmedium

Which TWO RMAN configurations are recommended to optimize backup performance in a high-transaction environment?

Select 2 answers

A.CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

B.CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

C.ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

D.CONFIGURE MAXSETSIZE TO 10G;

E.CONFIGURE CONTROLFILE AUTOBACKUP OFF;

AnswersA, C

Increasing parallelism allows RMAN to read multiple datafiles simultaneously.

Why this answer

Parallelism and block change tracking are primary performance tuners.

Full explanation →

Multi-Selecthard

Which THREE tools can be used to manage security settings?

Select 3 answers

A.AutoUpgrade

B.Oracle Security Dashboard

C.RMAN

D.Enterprise Manager

E.SQL*Plus

AnswersB, D, E

Used for specialized security management.

Why this answer

SQL*Plus, EM, and the Oracle Security Dashboard are standard management tools.

Full explanation →

Multi-Selecthard

Which THREE are true regarding the use of 'FLASHBACK DATABASE'?

Select 3 answers

A.It deletes all archivelogs.

B.It requires the database to be in open state.

C.It can be performed on a closed database.

D.A Fast Recovery Area must be configured.

E.The database must be in ARCHIVELOG mode.

AnswersC, D, E

Flashback database is performed while the database is mounted.

Why this answer

It requires FRA, archivelog mode, and flashback logs.

Full explanation →

MCQeasy

Which RMAN command is used to start an RMAN session?

A.run rman

B.rman target /

C.start rman

D.sqlplus rman

AnswerB

This connects RMAN to the local database.

Why this answer

The 'rman' command is invoked from the operating system prompt to start the tool.

Full explanation →

MCQmedium

To enable lock-free reservations on a table, which clause must be used in the CREATE or ALTER TABLE statement?

A.ENABLE LOCK-FREE

B.RESERVATIONS ON

C.LOCK-FREE RESERVATIONS

D.CONCURRENT DML

AnswerC

This is the correct clause for the feature.

Why this answer

The LOCK-FREE RESERVATIONS clause enables updates to numeric columns without traditional row-level locking.

Full explanation →

Multi-Selecthard

Which THREE tasks are required when preparing for a database recovery using a new control file?

Select 3 answers

A.Perform RECOVER DATABASE USING BACKUP CONTROLFILE.

B.Mount the database.

C.Restore the control file from a backup.

D.Delete all existing archivelogs.

E.Open the database resetlogs.

AnswersA, B, C

This is necessary when the control file is replaced.

Why this answer

You need to restore, mount, and recover.

Full explanation →

Multi-Selecteasy

Which THREE components are included in an RMAN 'BACKUP DATABASE' command if not explicitly excluded?

Select 3 answers

A.Control File

B.Temporary Tablespace

C.SPFILE

D.Redo Logs

E.Datafiles

AnswersA, C, E

The control file is included when autobackup is enabled.

Why this answer

By default, RMAN backs up datafiles, the current control file, and spfile (if autobackup is enabled).

Full explanation →

MCQmedium

You are configuring a RMAN backup strategy for a multitenant container database (CDB) and want to ensure that a backup of the root container also includes all PDBs. Which configuration command achieves this automatically?

A.BACKUP DATABASE

B.CONFIGURE INCLUDE PDB BACKUP ON

C.CONFIGURE CDB BACKUP ALL ON

D.BACKUP CONTAINER ALL

AnswerA

In a CDB, issuing BACKUP DATABASE covers the root and all pluggable databases by default.

Why this answer

The 'CONFIGURE PDB BACKUP OPTIMIZATION ON' command is not for this, but 'CONFIGURE BACKUP OPTIMIZATION ON' is standard. Specifically, 'BACKUP DATABASE' in a CDB context handles all PDBs unless excluded.

Full explanation →

MCQmedium

What is the primary role of True Cache in a 3-tier architecture?

A.Database backup

B.Load balancing

C.Caching frequently accessed data

D.User authentication

AnswerC

To reduce database load.

Why this answer

It acts as a caching layer between the application and the database server.

Full explanation →

MCQhard

You are performing a cross-platform data migration using RMAN CONVERT. The destination platform has different endianness. Which file type must be converted before it can be transported?

A.Redo log files

B.Parameter files

C.Datafiles

D.Control files

AnswerC

Datafiles require endianness conversion when moving between platforms with different endian formats.

Why this answer

Datafiles in a platform with different endianness must be converted using the CONVERT command to be readable by the target instance.

Full explanation →

MCQeasy

Which SQL function is used to calculate the similarity between two vectors?

A.COSINE_SIM()

B.DIST()

C.VECTOR_DISTANCE()

D.SIMILARITY()

AnswerC

This is the correct function.

Why this answer

VECTOR_DISTANCE is the standard operator for similarity search in 23ai.

Full explanation →

Multi-Selecthard

Which TWO components must be secured to maintain database integrity?

Select 2 answers

A.User manual

B.Data files

C.Backup files

D.System logs

E.Client browser

AnswersB, C

Core data integrity.

Why this answer

The data files and the backup files must be secured to prevent tampering.

Full explanation →

Multi-Selectmedium

When managing local and common users in a 23ai CDB, which TWO of the following statements are correct?

Select 2 answers

A.Common users can only be created in the root container.

B.Local users are automatically created in all PDBs by default.

C.Common users can grant local roles to other common users.

D.Common users must have names prefixed with C## or c##.

E.Local users can grant privileges to common users.

AnswersA, D

Common users must be created in CDB$ROOT to be visible across PDBs.

Why this answer

Common users must be created in the root (CDB$ROOT) and local users are specific to individual PDBs.

Full explanation →

MCQhard

If you want to use a PDB as a template for other PDBs, what state must the template PDB be in?

A.MOUNTED

B.READ ONLY

C.RESTRICTED

D.READ WRITE

AnswerB

Template PDBs must be opened read-only.

Why this answer

The PDB must be open in READ ONLY mode to be used as a template.

Full explanation →

MCQeasy

Which of these is the most effective way to address performance issues caused by suboptimal plans?

A.Dropping and recreating the database

B.Increasing the number of CPUs

C.SQL Plan Management

D.Disabling indexes

AnswerC

SPM enforces stable, optimal execution plans.

Why this answer

SQL Plan Management is specifically designed to manage and enforce optimal plans.

Full explanation →

MCQhard

You are restoring a database and need to use a backup control file. Which clause is mandatory in the recover command?

A.WITH EXTERNAL CONTROLFILE

B.USING BACKUP CONTROLFILE

C.RECOVER CONTROLFILE

D.USING OLD CONTROLFILE

AnswerB

This is the required clause.

Why this answer

The 'USING BACKUP CONTROLFILE' clause is required when you are not using the current control file.

Full explanation →

MCQeasy

Which parameter controls the frequency of AWR snapshot collection?

A.PROCESSES

B.DB_CACHE_SIZE

C.STATISTICS_LEVEL

D.OPTIMIZER_MODE

AnswerC

Setting this to TYPICAL or ALL enables AWR snapshots.

Why this answer

STATISTICS_LEVEL controls the automatic collection of AWR snapshots.

Full explanation →

MCQhard

When configuring Lock-Free Reservations, what must be done to the column being updated?

A.It must be numeric

B.It must be a primary key

C.It must be in a partitioned table

D.It must be indexed

AnswerA

Reservations only work on numbers.

Why this answer

The column must be a numeric type enabled for reservations.

Full explanation →

Multi-Selectmedium

Which THREE statements are true regarding the use of the 'ALTER PLUGGABLE DATABASE' command in a 23ai environment?

Select 3 answers

A.It can be used to upgrade the CDB dictionary

B.It can be used to delete the root container

C.It can be used to define a service for the PDB

D.It can be used to relocate a PDB to another CDB

E.It can be used to set a PDB to read-only mode

AnswersC, D, E

The SERVICE_NAME_CONCATE clause or set service allows PDB-specific service management.

Why this answer

The command is versatile, allowing for state changes, file management, and resource plan applications, but it cannot be used for tasks like creating the CDB itself.

Full explanation →

MCQeasy

What is the purpose of the 'Database Vault' option?

A.To improve query performance

B.To encrypt backups

C.To prevent DBA access to sensitive data

D.To manage user passwords

AnswerC

This is the core purpose of Database Vault.

Why this answer

Database Vault restricts access to application data by privileged users like SYS.

Full explanation →

MCQeasy

You are implementing AI Vector Search in Oracle Database 23ai. Which data type is mandatory for storing vector embeddings?

A.BLOB

B.CLOB

C.VECTOR

D.JSON

AnswerC

The VECTOR data type is the native format for vector embeddings.

Why this answer

The VECTOR data type is specifically designed for high-performance similarity search in Oracle 23ai.

Full explanation →

MCQmedium

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

A.The command succeeds

B.The table is renamed to _OLD

C.The command fails with an error

D.The rows are archived then dropped

AnswerC

The database enforces the protection period.

Why this answer

Blockchain tables protect against deletion of the table itself if rows are within the protection period.

Full explanation →

MCQhard

You want to audit only the SELECT statements performed by a specific user on a specific table. How is this achieved?

A.Traditional audit

B.Unified Audit Policy with a WHEN clause

C.Flashback Data Archive

D.Database Trigger

AnswerB

This allows for precise conditional auditing.

Why this answer

Use a Unified Audit Policy with the 'WHEN' clause to filter the user and the 'OBJECT' clause for the table.

Full explanation →

Multi-Selecthard

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

Select 3 answers

A.Creating a SQL profile

B.Restructuring the SQL query syntax

C.Renaming database tables

D.Creating a new index

E.Updating optimizer statistics

AnswersA, D, E

SQL profiles provide extra information to the optimizer to produce better plans.

Why this answer

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

Full explanation →

Multi-Selecthard

Which THREE of the following are valid methods to patch an Oracle 23ai database?

Select 3 answers

A.OPatchAuto

B.Fleet Patching and Provisioning (FPP)

C.OPatch

D.Data Pump Patching

E.SQL*Plus Patch

AnswersA, B, C

Automated tool for applying RUs to grid infrastructure and database homes.

Why this answer

OPatch, OPatchAuto, and Fleet Patching and Provisioning (FPP) are the standard methods for patching Oracle databases.

Full explanation →

MCQmedium

What happens when you execute 'ALTER PLUGGABLE DATABASE ALL OPEN'?

A.Opens only PDB$SEED

B.Opens all PDBs in read-write mode

C.Opens all PDBs in read-only mode

D.Starts the CDB instance

AnswerB

The ALL clause applies to all PDBs.

Why this answer

All PDBs in the CDB are opened in READ WRITE mode.

Full explanation →

MCQmedium

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

A.Restrict access

B.Prevent data modification

C.Protect index integrity

D.Prevent table deletion

AnswerD

The primary function of the clause.

Why this answer

It prevents the table from being dropped until the retention period expires.

Full explanation →

Multi-Selectmedium

Which TWO components are essential for the operation of the Automatic Database Diagnostic Monitor (ADDM)?

Select 2 answers

A.SQL Plan Baselines

B.Data Pump files

C.AWR snapshots

D.ADDM Analysis Task

E.Listener control

AnswersC, D

ADDM requires this historical data.

Why this answer

ADDM needs AWR snapshots and the ADDM analysis task.

Full explanation →

MCQhard

You need to migrate a non-CDB to a PDB in an existing CDB. Which package should you use to check for compatibility issues before starting the migration?

A.DBMS_PDB_MIGRATE

B.DBMS_METADATA.CHECK

C.DBMS_CDB_UPGRADE

D.DBMS_PDB.CHECK_PLUG_COMPATIBILITY

AnswerD

This procedure checks if a non-CDB is compatible with the target CDB.

Why this answer

DBMS_PDB.CHECK_PLUG_COMPATIBILITY is the standard procedure for pre-migration validation.

Full explanation →

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