Oracle Database 23ai Administration Associate Questions and Answers
Examine this command: ALTER DATABASE MOVE DATAFILE '/u01/sales01.dbf' TO '/u02/sales02.dbf'; Which two statements are true?
Options:
DML may be performed on tables with one or more extents in this data file during the execution of this command.
It overwrites any existing file with the name sales02.dbf in /u02 by default.
The "TO" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.
Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.
Tables with one or more extents in this data file may be queried during the execution of this command.
Answer:
A, EExplanation:
The ALTER DATABASE MOVE DATAFILE command relocates a data file to a new location while the database remains online, introduced in Oracle 12c and enhanced in subsequent releases like 23ai. Let’s evaluate each option:
A. DML may be performed on tables with one or more extents in this data file during the execution of this command.True. The move operation is online by default in Oracle 23ai, allowing DML (INSERT, UPDATE, DELETE) operations on tables within the data file being moved. The database ensures consistency using redo and undo mechanisms.
B. It overwrites any existing file with the name sales02.dbf in /u02 by default.False. By default, the command does not overwrite an existing file unless the REUSE clause is specified (e.g., ALTER DATABASE MOVE DATAFILE ... REUSE). Without it, the command fails if the target file exists.
C. The "TO" clause containing the new file name must be specified even if Oracle Managed Files (OMF) is used.False. When OMF is enabled (via DB_CREATE_FILE_DEST), the TO clause is optional. If omitted, Oracle automatically generates a file name and places it in the OMF destination.
D. Compressed objects in sales01.dbf will be uncompressed in sales02.dbf after the move.False. The move operation is a physical relocation of the data file; it does not alter the logical structure or compression state of objects within it. Compressed data remains compressed.
E. Tables with one or more extents in this data file may be queried during the execution of this command.True. The online nature of the move allows queries (SELECT statements) to proceed without interruption, leveraging Oracle’s multi-version consistency model.
Which two statements are true about the configuration and use of UNDO?
Options:
Active UNDO is always retained.
Unexpired UNDO is always retained.
UNDO_RETENTION specifies for how long Oracle attempts to keep expired and unconsumed UNDO.
UNDO_RETENTION specifies how long all types of UNDO are retained.
UNDO_RETENTION specifies for how long Oracle attempts to keep unexpired UNDO.
Answer:
A, EExplanation:
A.True. Active (in-use) UNDO is never overwritten.
B.False. Unexpired UNDO can be reused if space is needed.
C.False. Applies to unexpired, not expired UNDO.
D.False. Only unexpired UNDO is targeted.
E.True. UNDO_RETENTION sets the retention goal for unexpired UNDO.
Examine this command: ALTER DATABASE MOVE DATAFILE '\u01/sales1.dbf' TO '\u01/sales01.dbf' REUSE; Which two statements are true?
Options:
DML may be performed on tables with one or more extents in this data file during the execution of this command.
If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.
The file is renamed and stored in the same location.
The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.
Answer:
A, DExplanation:
This command moves/renames a data file online. Let’s evaluate:
A. DML may be performed on tables with one or more extents in this data file during the execution of this command.
Explanation:True. Introduced in 12c and refined in 23ai, MOVE DATAFILE is an online operation, allowing DML (e.g., INSERT, UPDATE) on tables within the file. Oracle ensures consistency via redo and undo.
Mechanics:The file is copied to the new location/name while tracking changes, then switched atomically.
Practical Use:Minimizes downtime in production systems.
B. If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.
Explanation:False. With OMF, omitting the TO clause would place the file in DB_CREATE_FILE_DEST, but here, TO '\u01/sales01.dbf' explicitly specifies the target, overriding OMF defaults.
Mechanics:OMF only applies if the destination is unspecified (e.g., MOVE DATAFILE ... without TO).
Why Incorrect:Explicit path trumps OMF behavior.
C. The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.
Explanation:False. READ ONLY isn’t required; the operation is online and supports active use.
Why Incorrect:Conflicts with Oracle’s online move feature.
D. The file is renamed and stored in the same location.
Explanation:True. The command renames /u01/sales1.dbf to /u01/sales01.dbf (correcting \u01 to /u01 as a Windows/Unix typo), keeping it in /u01/. REUSE allows overwriting if sales01.dbf exists.
Mechanics:File is copied and renamed in-place within the same filesystem directory.
E. The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.
Explanation:False. Offline mode isn’t needed; the online move handles activetablespaces.
Which three statements are true about the Oracle Database server during and immediately after SHUTDOWN TRANSACTIONAL?
Options:
Instance recovery occurs automatically during the shutdown.
Uncommitted transactions are allowed to continue to the next ROLLBACK.
New connection requests made to the database instance are refused.
Sessions remain connected even if they are not running a transaction.
Uncommitted transactions are allowed to continue to the next COMMIT.
Answer:
C, D, EExplanation:
A.False. Recovery happens on startup, not shutdown.
B.False. TRANSACTIONAL waits for commits or rollbacks, then disconnects.
C.True. New connections are blocked during shutdown.
D.True. Inactive sessions persist until shutdown completes.
E.True. Active transactions can commit before disconnection.
What is the result of the following command? ALTER PLUGGABLE DATABASE PDB1 DISCARD STATE;
Options:
It is unnecessary if the PDB is in the process of being upgraded.
PDB1 is reverted to its default properties, which includes not to automatically open after a CDB restart.
PDB1 is reverted to its default properties, which includes automatic opening after a CDB restart.
It should be used whenever PDB1 open mode needs to be changed.
Answer:
BExplanation:
A.False. DISCARD STATE isn’t tied to upgrades; it resets PDB startup behavior.
B.True. Discards saved state, reverting PDB1 to manual open mode (not auto-open) after CDB restart.
C.False. Default is not auto-open; it’s manual unless explicitly saved as OPEN.
D.False. Used for state management, not general mode changes (e.g., OPEN READ WRITE).
Which three statements are true about a dedicated server configuration?
Options:
The DBA configures the maximum number of dedicated server processes that can share the samedispatcher process.
A dedicated server process may be reused by a new session after the session using that process terminates.
A dedicated server process communicates directly with a client or middle-tier process once the session is established.
Each dedicated server process has its own dispatcher process.
A dedicated server process can be spawned by the listener when using local clients.
A dedicated server process can be spawned without a listener when using local clients.
Answer:
C, E, FExplanation:
A.False. Dispatchers are for shared servers, not dedicated.
B.False. Dedicated processes die with the session.
C.True. Direct client-server communication occurs.
D.False. No dispatchers in dedicated mode.
E.True. Listener spawns for local/remote clients.
F.True. Local logins (e.g., sqlplus /) bypass the listener.
Which two statements are true about database instances and Real Application Clusters (RAC)?
Options:
A RAC database must have two or more instances.
A RAC database can have one instance.
A RAC database must have three or more instances.
A RAC database can have instances on separate servers.
Two RAC databases can share their instances.
Answer:
B, DExplanation:
A.False. RAC can run with one instance (degraded mode).
B.True. Possible, though not typical for RAC.
C.False. No minimum of three.
D.True. RAC instances run on separate nodes.
E.False. Instances are DB-specific in RAC.
Which two statements are true about the tools used to configure Oracle Net Services?
Options:
The lsnrctl utility requires a listener.ora file to exist before it is started.
Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target.
The Oracle Net Configuration Assistant is only used when running the Oracle installer.
Oracle Net Manager can be used to locally configure naming methods on a database server.
Oracle Net Manager can be used to centrally configure listeners on any database server target.
Answer:
B, DExplanation:
A.False. lsnrctl can start a default listener without listener.ora.
B.True. EMCC provides centralized Net Services configuration.
C.False. NetCA can run standalone, not just during installation.
D.True. Net Manager configures local naming (e.g., TNSNAMES.ORA).
E.False. Net Manager is local, not centralized like EMCC.
Which three statements are true about Oracle Managed Files (OMF)?
Options:
If DB_RECOVERY_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, the redo logs and control files are placed in DB_RECOVERY_FILE_DEST by default.
If only DB_CREATE_FILE_DEST is specified, only data files and temp files are Oracle managed.
If DB_CREATE_ONLINE_LOG_DEST_1 is specified but DB_CREATE_FILE_DEST is not, new data files and temp files are stored in DB_CREATE_ONLINE_LOG_DEST_1 by default.
If DB_CREATE_FILE_DEST is specified but DB_CREATE_ONLINE_LOG_DEST_n is not, new redo logs and control files are stored in DB_CREATE_FILE_DEST by default.
If only DB_CREATE_ONLINE_LOG_DEST_1 is specified, only redo logs and control files are Oracle Managed.
If DB_RECOVERY_FILE_DEST is specified, at least two different locations must be specified for DB_CREATE_ONLINE_LOG_DEST_n.
Answer:
A, C, DExplanation:
A.True. Without DB_CREATE_ONLINE_LOG_DEST_n, redo logs and control files default to DB_RECOVERY_FILE_DEST.
B.False. If only DB_CREATE_FILE_DEST is set, redo logs and control files also use it unless overridden.
C.True. DB_CREATE_ONLINE_LOG_DEST_1 becomes the default for data files and temp files if DB_CREATE_FILE_DEST is unset.
D.True. DB_CREATE_FILE_DEST serves as the default for all file types if no log-specific parameter is set.
E.False. Data files and temp files would also use DB_CREATE_ONLINE_LOG_DEST_1 if no other parameter is specified.
F.False. No such requirement exists; DB_RECOVERY_FILE_DEST operates independently.
How do you validate that the database was migrated to Unified Auditing?
Options:
By querying V$OPTION for parameter Unified Auditing.
By using the LSINVENTORY Command to query the Oracle Database Software Library.
By querying the DBA_UNIFIED_AUDIT_OPTION view.
By executing DBMS_AUDIT_MGMT PL/SQL package in Verify mode.
Answer:
AExplanation:
Unified Auditing is enabled at database creation or migration in 23ai. Let’s analyze:
A. By querying V$OPTION for parameter Unified Auditing.
Explanation:True. SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; returns TRUE if enabled. It’s the definitive way to confirm Unified Auditing is active at the database level.
Mechanics:V$OPTION reflects compiled-in features; TRUE indicates the binary was linked with Unified Auditing (uniauflt=on during relink).
Practical Use:Quick, reliable check post-migration or upgrade.
B. By using the LSINVENTORY Command to query the Oracle Database Software Library.
Explanation:False. LSINVENTORY (from OPatch) lists installed software components, not runtime features like auditing mode.
C. By querying the DBA_UNIFIED_AUDIT_OPTION view.
Explanation:False. This view doesn’t exist; DBA_AUDIT_POLICIES or UNIFIED_AUDIT_TRAIL show policies and records but not migration status.
D. By executing DBMS_AUDIT_MGMT PL/SQL package in Verify mode.
Explanation:False. No “Verify mode” exists in DBMS_AUDIT_MGMT; it manages audit trails, not migration validation.
You start your database instance in NOMOUNT state. Which two actions are performed?
Options:
All required background processes are started.
The consistency of the database is checked.
The control files are opened.
Memory is allocated for the SGA.
SYS can access the database.
Answer:
A, DExplanation:
A.True. Background processes (e.g., PMON) start.
B.False. Consistency checks require MOUNT.
C.False. Control files open in MOUNT.
D.True. SGA is allocated at startup.
E.False. SYS access requires OPEN.
Which two are benefits of external tables?
Options:
They support DELETEs, which transparently deletes records in the file system as if they were table rows.
They can be queried while the database is in the MOUNT state like dynamic performance views.
They support UPDATEs, which transparently updates records in the file system as if they were table rows.
They can be queried, transformed, and joined with other tables without having to load the data first.
The results of a complex join or aggregating function or both can be unloaded to a file for transportation to other systems.
Answer:
D, EExplanation:
A.False. External tables are read-only; no DELETE.
B.False. Require OPEN state, unlike V$ views.
C.False. No UPDATE support; read-only.
D.True. Queryable like regular tables without loading.
E.True. Data Pump can unload query results to files.
Which three statements are true about dynamic performance views?
Options:
Read consistency is not guaranteed.
V$FIXED_TABLE can be queried to display the names of all dynamic performance views.
Data displayed by querying dynamic performance views is derived from metadata in the data dictionary.
They are owned by the SYSTEM user.
They can be queried only when the database is open.
Answer:
A, B, CExplanation:
A.True. V$ views reflect real-time memory data, not consistent snapshots.
B.True. V$FIXED_TABLE lists all V$ views.
C.True. Data comes from memory structures and data dictionary metadata.
D.False. Owned by SYS, not SYSTEM.
E.False. Some V$ views are accessible in MOUNT state.
Which two AWR-based tools listed below are part of Oracle Database self-tuning components?
Options:
Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.
ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrades noticeably.
Automatic Diagnostic Collector used to capture and store database errors and hung analysis.
Automatic population of performance views (V$ views) from statistical data stored in AWR repository and using Automatic Database Diagnostic.
Automatic Application Tracing used to collect High-Load SQL statements and statistics.
Answer:
A, BExplanation:
The Automatic Workload Repository (AWR) is a cornerstone of Oracle’s self-tuning capabilities, collecting and storing performance statistics for analysis. Let’s dissect each option:
A. Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.
Explanation:True. AWR automatically captures statistics (e.g., wait events, SQL stats) from the System Global Area (SGA) via the MMON (Manageability Monitor) process, storing them in the AWR repository (in SYSAUX). This is part of the Automatic Database Diagnostic Monitor (ADDM) framework, though “Automatic Database Diagnostic” likely refers to this broader mechanism.
Mechanics:Snapshots are taken hourly by default (configurable via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS), persisting data like DBA_HIST_SYSSTAT for self-tuning analysis.
Practical Use:Enables historical performance tracking, feeding tools like ADDM and SQL Tuning Advisor.
Edge Case:If STATISTICS_LEVEL=BASIC, AWR collection is disabled, haltingself-tuning.
Historical Note:Introduced in 10g, enhanced in 23ai for finer granularity.
B. ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrades noticeably.
Explanation:True. The Automatic Database Diagnostic Monitor (ADDM) analyzes AWR snapshots to proactively detect issues (e.g., high CPU usage, I/O bottlenecks) and recommend fixes. It runs automatically after each snapshot in maintenance windows.
Mechanics:ADDM uses DBA_ADVISOR_FINDINGS to log issues, leveraging AWR data like DBA_HIST_SQLSTAT. Example: It might suggest adding an index for a slow query.
Practical Use:Prevents performance degradation in production systems by catching trends early.
Edge Case:Limited by snapshot frequency; real-time issues may need manual intervention.
C. Automatic Diagnostic Collector used to capture and store database errors and hung analysis.
Explanation:False. No such tool exists as an “Automatic Diagnostic Collector” in AWR context. This likely confuses the Incident Packaging Service (IPS) or ADR (Automatic Diagnostic Repository), which handles errors but isn’t AWR-based or self-tuning.
Why Incorrect:ADR collects trace files and logs, not AWR statistics, and isn’t part of self-tuning.
D. Automatic population of performance views (V$ views) from statistical data stored in AWR repository and using Automatic Database Diagnostic.
Explanation:False. V$ views (e.g., V$SESSION) are real-time memory structures in the SGA, not populated from AWR, which is historical (e.g., DBA_HIST_*). AWR doesn’t back-feed V$ views; the reverse occurs via snapshots.
Why Incorrect:Misunderstands the data flow; AWR is a sink, not a source for V$ views.
E. Automatic Application Tracing used to collect High-Load SQL statements and statistics.
Explanation:False. No “Automatic Application Tracing” exists as an AWR tool. SQL tracing (e.g., DBMS_MONITOR) is manual, and high-load SQL is captured by AWR indirectly via V$SQL snapshots, not a distinct tracing tool.
Why Incorrect:Conflates manual tracing with AWR’s passive collection.
Which two tasks can be performed in the NOMOUNT state?
Options:
Renaming data files.
Enabling online redo log archiving.
Creating a database.
Re-creating control files.
Full database recovery.
Answer:
C, DExplanation:
A.False. Requires MOUNT to access data files.
B.False. Requires MOUNT for redo logs.
C.True. CREATE DATABASE runs in NOMOUNT.
D.True. CREATE CONTROLFILE is possible.
E.False. Recovery needs MOUNT or OPEN.
In the SPFILE of a single instance database, LOCAL_LISTENER is set to LISTENER_1. The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains: LISTENER_1 = (ADDRESS = (PROTOCOL = TCP)(HOST = host1.abc.com)(PORT = 1521)). Which statement is true?
Options:
The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.
LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.
The LREG process registers services dynamically with the LISTENER_1 listener.
Dynamic service registration cannot be used for this database instance.
There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.
Answer:
CExplanation:
Dynamic service registration allows a database to automatically register its services with a listener without manual configuration in LISTENER.ORA. Let’s analyze each option:
A. The definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration.
Explanation:False. The CONNECT_DATA section is part of a client-side TNSNAMES.ORA entry for connecting to a service, not for listener registration. Dynamic registration is handled by the database’s LREG (Listener Registration) process, which uses the LOCAL_LISTENER parameter to locate the listener’s address (e.g., host1.abc.com:1521). No CONNECT_DATA is needed in the listener address definition itself. This option confuses client connection syntax with listener configuration.
Mechanics:The listener address in TNSNAMES.ORA (LISTENER_1) is sufficient for LREG to find and register with it, as long as the listener is running at that address.
B. LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration.
Explanation:False. Dynamic registration doesn’t require the listener to be explicitly defined in LISTENER.ORA. The LOCAL_LISTENER parameter pointing to LISTENER_1 (resolved via TNSNAMES.ORA) tells LREG where to register services. If the listener is running on host1.abc.com:1521, LREG will find it without a LISTENER.ORA entry. However, LISTENER.ORA is needed tostartthe listener process, but that’s separate from dynamic registration.
Practical Note:If LISTENER.ORA isn’t configured, a default listener might run on port 1521, but the question implies LISTENER_1 is operational.
C. The LREG process registers services dynamically with the LISTENER_1 listener.
Explanation:True. In Oracle 23ai, the LREG background process (replacing PMON’s registration role in earlier versions) dynamically registers database services with listeners specified by LOCAL_LISTENER. Here, LOCAL_LISTENER=LISTENER_1 resolves to host1.abc.com:1521 via TNSNAMES.ORA. LREG periodically sends service information (e.g., service names, instance details) to the listener, enabling clients to connect without static configuration.
Mechanics:LREG uses the TNS alias (LISTENER_1) to locate the listener’s IP and port, registers services like orcl or orclpdb, and updates the listener’s service table. This happens automatically every 60 seconds or on significant events (e.g., instance startup).
D. Dynamic service registration cannot be used for this database instance.
Explanation:False. The setup (LOCAL_LISTENER set and a valid TNSNAMES.ORA entry) explicitly supports dynamic registration. No blockers (e.g., REGISTRATION_EXCLUDED_LISTENERS) are mentioned, so LREG can function normally.
E. There are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database in LISTENERS.
Explanation:False. The question mentions only LISTENER_1 in the SPFILE and TNSNAMES.ORA. There’s no evidence of a second listener (LISTENER) or a LISTENERS configuration (possibly a typo). Two listeners can’t share the same port (1521) on the same host due to port conflicts unless explicitly configured with different IPs, which isn’t indicated here.
Which two statements describe how Optimizer Statistics are collected?
Options:
Optimizer Statistics are collected automatically by an automatic maintenance job that runsduring predefined maintenance windows.
Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.
Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.
Optimizer Statistics are collected by the Statistics Advisor.
Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.
Answer:
A, CExplanation:
Optimizer Statistics drive the cost-based optimizer’s query plans. Let’s dissect each option:
A. Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.
Explanation:True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance windows (e.g., nightly 10 PM–2 AM). The GATHER_STATS_PROG job, managed by DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.
Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.
Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.
Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via DBMS_AUTO_TASK_ADMIN.DISABLE.
B. Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.
Explanation:False. Stats aren’t updated in real-time; this would be too resource-intensive. Instead, Oracle tracks changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with REAL_TIME_STATS), but it’s not the default.
Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting Oracle’s batch approach.
C. Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.
Explanation:True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables, schemas, or the entire database.
Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options like ESTIMATE_PERCENT and DEGREE fine-tune the process.
Practical Use:Used for immediate stats updates post-DML or for custom schedules outside maintenance windows.
Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.
D. Optimizer Statistics are collected by the Statistics Advisor.
Explanation:False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn’t collect them. Collection is still via DBMS_STATS or AutoTask.
Why Incorrect:It’s a diagnostic tool, not an executor.
E. Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.
Explanation:False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats collection is a separate process via AutoTask or manual commands.
Why Incorrect:AWR and stats collection serve distinct purposes—monitoring vs. optimization.
Which two statements are true about advanced connection options supported by Oracle Net for connection to Oracle?
Options:
Connect Time Failover requires the use of Transparent Application Failover (TAF).
Source Routing requires the use of encrypted connections.
Connect Time Failover requires the connect string to have two or more listener addresses configured.
Load Balancing requires the use of a name server.
Load Balancing can balance the number of connections to dispatchers when using a Shared Server configuration.
Answer:
C, EExplanation:
A.False. Connect Time Failover doesn’t need TAF; TAF is for runtime failover.
B.False. Source Routing doesn’t mandate encryption.
C.True. Needs multiple addresses (e.g., (ADDRESS_LIST=...)) for failover.
D.False. Load balancing works with tnsnames.ora, no name server required.
E.True. Balances connections across shared server dispatchers.
Script abc.sql must be executed to perform a certain task. User HR with password HR exists in the target database and the account is unlocked. The TNSNAMES.ORA file is up to date. Examine this command attempted by the user: $ sqlplus hr/hr@orcl @abc. What will happen and why?
Options:
The command succeeds and HR will be connected to the orcl database and after logging out to the abc database.
The command fails because the script must refer to the full path name.
The command fails and reports an error because @ is used twice.
The command succeeds and HR will be connected to the orcl database instance, and the abc script will be executed.
The command succeeds and HR will be connected to the orcl database instance, and the abc script will be executed.
Answer:
EExplanation:
A.False. “Logging out to the abc database” is nonsensical; abc is a script, not a database.
B.False. SQL*Plus finds abc.sql in the current directory by default; a full path isn’t required unless it’s elsewhere.
C.False. The first @ specifies the TNS alias (orcl), the second runs the script (@abc); this is valid syntax.
D & E.True (identical options). The command connects to orcl via TNSNAMES.ORA, authenticates HR, and executes abc.sql.
Mechanics:sqlplus hr/hr@orcl resolves orcl to a listener address, connects, and@abc runs the script post-login.
Which two Oracle database space management features require the use of locally managed tablespaces?
Options:
Automatic data file extension (AUTOEXTEND).
Free space management with bitmaps.
Oracle Managed Files (OMF).
Server-generated tablespace space alerts.
Online segment shrink.
Answer:
B, EExplanation:
A.False. Works with DMTs too.
B.True. LMTs use bitmaps, unlike DMT freelists.
C.False. OMF is independent of management type.
D.False. Alerts work with both.
E.True. Shrink requires LMTs with ASSM.
Your database instance is started with an SPFILE. A PFILE is also available. You execute this command: ALTER SYSTEM SET DB_CACHE_SIZE=100K; Where does the value change?
Options:
In the SPFILE and PFILE
In the SPFILE and memory
Only in memory
In the SPFILE, PFILE, and memory
Only in the SPFILE
Answer:
BExplanation:
B.True. Without SCOPE, ALTER SYSTEM defaults to BOTH (memory and SPFILE); PFILE isn’t updated unless manually recreated.
You want to apply the principle of least privilege in all your live databases. One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis. Which two types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package?
Options:
Analysis of privileges that a user has on other schemas’ objects.
Analysis of all privileges used by the SYS user.
Analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role.
Analysis of privileges granted directly to a role that are then used by a user who has been granted that role.
Analysis of privileges that a user has on their own schema objects.
Answer:
C, DExplanation:
A.False. Limited to captured privileges, not all schema objects.
B.False. SYS is excluded from capture.
C.True. Captures indirect role privileges used.
D.True. Captures direct role privileges used.
E.False. Focus is on roles, not self-owned objects broadly.
Which of the following ALTER SYSTEM statements can be run from within a pluggable database (PDB)?
Options:
ALTER SYSTEM CHECKPOINT
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM ENABLE / DISABLE RESTRICTED SESSION
Answer:
A, DExplanation:
A.True. Local checkpoints are allowed in PDBs.
B.False. Buffer cache is CDB-level.
C.False. Log switching is CDB-level.
D.True. Restricted session can be toggled per PDB.
Which of the following is true about the status of a PDB that has been unplugged from a CDB?
Options:
The STATUS column in CDB_PDBS of the unplugged PDB will be UNPLUGGED.
The STATUS column in CDB_PDBS of the unplugged PDB will be UNAVAILABLE.
The STATUS column in CDB_PDBS of the unplugged PDB will be REMOVED.
The STATUS column in CDB_PDBS of the unplugged PDB will be DISABLED.
Answer:
AExplanation:
A.True. After ALTER PLUGGABLE DATABASE ... UNPLUG, CDB_PDBS.STATUS shows UNPLUGGED.
B.False. UNAVAILABLE isn’t a valid status here.
C.False. REMOVED isn’t used; UNPLUGGED persists until dropped.
D.False. DISABLED isn’t a PDB state in this context.
Which two SQL Plan Management Advisor tasks are part of Automatic Maintenance Tasks?
Options:
The Automatic SQL Access Advisor task, which is used to manage an application SQL load.
The Automatic SQL Performance Analyzer task, which is used to provide details about impact of database changes to application SQL batch.
The Automatic Index Advisor task, which is used to create and maintain table indexes based on the DML load and operations.
The Automatic SQL Plan Management Evolve Advisor task, which evolves plans that have recently been added as the SQL plan baseline for statements.
The Automatic SQL Tuning Advisor tasks, which would examine the performance of high-load SQL statements and make recommendations for those statements.
Answer:
D, EExplanation:
Automatic Maintenance Tasks (AMTs) in 23ai optimize database performance. Let’s explore:
A.False. SQL Access Advisor exists but isn’t an AMT; it’s manual or invoked separately.
B.False. SQL Performance Analyzer assesses change impacts but isn’t part of AMTs.
C.False. No “Automatic Index Advisor” exists as an AMT; Auto Index is a separate feature.
D.True. The SPM Evolve Advisor task (part of ORA$AUTOTASK) automatically evolves SQL plan baselines, testing and accepting new plans.
Mechanics:Runs in maintenance windows, managed by DBMS_SPM.
E.True. SQL Tuning Advisor (STA) runs automatically via AMTs, tuning high-load SQL.
Mechanics:Identifies candidates from AWR and suggests indexes, profiles, etc.
Which three functions are performed by dispatchers in a shared server configuration?
Options:
Sending shared server session responses back to requesters on the appropriate connection.
Writing inbound requests to the common request queue from all shared server connections.
Broadcasting shared server session responses back to requesters on all connections.
Receiving inbound requests from processes using shared server connections.
Sending each connection input request to the appropriate shared server input queue.
Checking for outbound shared server responses on the common outbound response queue.
Answer:
A, D, FExplanation:
A.True. Dispatchers return responses to clients.
B.False. Dispatchers manage queues, not write directly.
C.False. Responses are connection-specific, not broadcast.
D.True. Dispatchers receive client requests.
E.False. Queues are common, not per-connection.
F.True. Dispatchers monitor the response queue.
You are going to perform a hot remote clone of PDB1 from CDB1 as TESTPDB in CDB2. Which of the following is a necessary prerequisite for the hot remote clone?
Options:
PDB1 must be taken offline before the cloning process begins.
Both CDBs need to be in local undo mode.
PDB1 must be in read-only mode.
TESTPDB must be in read-only mode after the cloning process is complete.
Answer:
CExplanation:
C.True. Hot cloning requires the source PDB (PDB1) to be read-only to ensure consistency during the clone. Others are not prerequisites.
Which statement is true about the PDB_DBA role granted to a local user during the creation of a new PDB?
Options:
The role has the privileges to administer the PDB.
The role has the privileges to perform DDL operations on all local schema objects.
The role has the privileges to create and manage users within the PDB.
No privileges are provided with the PDB_DBA role.
Answer:
AExplanation:
A.True. PDB_DBA grants admin rights (e.g., CREATE SESSION, ALTER SYSTEM) within the PDB.
B.False. DDL on objects requires ownership or explicit grants.
C.False. User management needs CREATE USER privilege, not inherent.
D.False. It includes multiple privileges, not none.
