SQL*Loader (sqlldr) loads data from external files into Oracle tables. The command sqlldr u1/oracle TABLE=emp uses defaults since no control file is specified. Let’s evaluate:
A . It overwrites the data for Alan and adds data for Curl and Bob.
False. SQLLoader’s default mode is APPEND, not REPLACE. It doesn’t “overwrite” existing rows unless REPLACE or TRUNCATE is specified in a control file. Here, row 1, Alan, 2 exists, and SQLLoader will either skip it (if a primary key rejects duplicates) or raise an error, but it won’t overwrite. 3, Curl, 4 and 4, Bob, 4 are appended.
Mechanics:Without a control file, SQL*Loader assumes APPEND and matches columns positionally (ENO, ENAME, DN).
B . It generates a log that contains control file entries, which can be used with normal SQL*Loader operations.
True. SQL*Loader always generates a log file (e.g., emp.log) when invoked. With no control file specified, it auto-generates one internally and logs it, including entries like LOAD DATA INFILE ’emp.dat’ APPEND INTO TABLE emp FIELDS TERMINATED BY ‘,’ (ENO, ENAME, DN). This can be reused.
Practical Use:The log’s control section is editable for future runs (e.g., changing to REPLACE).
C . It appends data from EMP.DAT to EMP.
True. Default behavior without a control file is APPEND, adding new rows (3, Curl, 4 and 4, Bob, 4) to EMP. Existing rows (1, Alan, 2, 2, Ben, 2) remain unless constrained (e.g., unique key violations).
Mechanics:SQL*Loader processes each line of emp.dat, skipping duplicates if constrained, appending otherwise.
D . It generates a SQL script that it uses to load data from EMP.DAT to EMP.
False. SQL*Loader doesn’t generate SQL scripts; it uses direct path or conventional path loading, not SQL scripts. The log contains control file syntax, not a script.
E . It overwrites all data in EMP with data from EMP.DAT.
False. REPLACE or TRUNCATE would overwrite, but these require a control file with those options. Default APPEND preserves existing data.

A .False. One instance can’t mount multiple DBs without multitenant.
B .True. RAC allows multiple instances to share one DB.
C .False. Same as A; not possible without CDB.
D .True. An instance can start in NOMOUNT with no DB.
E .True. Standard single-instance configuration.

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.

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

Dictionary-managed tablespaces (DMTs) use the data dictionary for extent management, whilelocally managed tablespaces (LMTs) use bitmaps. Let’s evaluate compatibility:
A . Capacity planning growth reports based on historical data in the Automatic Workload Repository (AWR).
True. AWR tracks space usage (e.g., DBA_HIST_TBSPC_SPACE_USAGE) regardless of tablespace type, enabling growth reports for both DMTs and LMTs.
Mechanics:MMON collects metrics like segment growth, stored in SYSAUX, accessible via EM or scripts.
Practical Use:Helps predict when to add data files, universal across management types.
B . Online table segment shrink.
False. ALTER TABLE … SHRINK SPACE requires LMTs with Automatic Segment Space Management (ASSM), unavailable in DMTs, which lack bitmap-based free space tracking.
Why Incorrect:DMTs use freelists, incompatible with shrink operations.
C . Online index segment shrink.
False. Like tables, ALTER INDEX … SHRINK SPACE requires LMTs with ASSM, not supported in DMTs.
Why Incorrect:Same limitation as B; DMTs can’t compact online.
D . Oracle Managed Files (OMF).
True. OMF automates file naming and placement (via DB_CREATE_FILE_DEST) for both DMTs and LMTs, agnostic to extent management.
Mechanics:Example: CREATE TABLESPACE ts1; creates an OMF file in either type.
Edge Case:DMTs are rare in 23ai, but OMF still applies.
E . Automatic data file extension (AUTOEXTEND).
True. AUTOEXTEND ON allows data files to grow as needed, supported in both DMTs and LMTs since early versions.
Mechanics:ALTER DATABASE DATAFILE … AUTOEXTEND ON NEXT 100M; works universally.

D .True. The Shared Pool caches data dictionary metadata (e.g., table definitions) in the Library Cache and Dictionary Cache, accessible to all processes. Others serve different purposes (e.g., Large Pool for backups).

A .True. Enabled per session with ALTER SESSION ENABLE RESUMABLE.
B .True. Multiple suspensions can occur in one session.
C .False. Works with dictionary-managed tablespaces too.
D .False. Timeout is session-specific.
E .True. Privilege doesn’t prevent suspension; quota limits do.
F .False. Triggers execute but can’t suspend themselves.

A .False. LREG handles registration in 23ai, not PMON.
B .False. CKPT (Checkpoint) process updates the control file.
C .True. PMON cleans up after failed processes (e.g., releases locks).
D .True. PMON rolls back uncommitted transactions of failed processes.
E .False. SMON (System Monitor) manages temporary segment cleanup.

A .False. Diagnostic collection is ADR, not AMT.
B .True. Resource Manager allocates resources to AMTs.
C .True. Scheduler defines maintenance windows.
D .False. Packaging is IPS/ADR, not AMT.
E .False. Alert log is separate from AMTs.
F .True. Tasks like stats collection are AMTs.

A .True. sqlplus @script.sql works from shell scripts.
B .False. RMAN uses its own client, not SQL*Plus.
C .False. Bundled with Oracle DB software.
D .False. Command-line only; no GUI in 23ai.
E .True. @script runs scripts interactively.
F .True. Commands like SHOW PARAMETER are unique.

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

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.

A .False. External tables are read-only.
B .True. Defines how external data (e.g., CSV) is mapped to SQL.
C .False. Data is external, not in the DB.
D .False. Storage is external; Oracle doesn’t define it.

A .False. Session limits don’t tie to password changes.
B .False. CPU limits don’t lock accounts.
C .False. No direct “logged-in days” parameter.
D .True. PASSWORD_REUSE_MAX=UNLIMITED prevents reuse.
E .True. PASSWORD_LOCK_TIME sets inactivity lockout.

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.

A .False. Not all trace files indicate errors; some log normal activity.
B .True. Server processes write traces (e.g., ora_s000_123.trc) to the file system.
C .True. Background processes (e.g., ora_lgwr_123.trc) also write traces.
D .False. Names include process type and PID, not just DB name and sequence.
E .False. Traces go to DIAGNOSTIC_DEST, not necessarily FRA unless configured there.

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.

A .False. Backups are via RMAN, not AMTs.
B .True. Stats collection is an AMT.
C .False. Error logging isn’t an AMT.
D .True. SPM tasks are included.
E .False. Notifications aren’t AMTs.
F .True. Segment Advisor is an AMT.

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.

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.

A .False. ADR is file-based, not in a schema.
B .False. No such default exists.
C .True. ADR logs ASM diagnostics.
D .True. Supports Clusterware diagnostics.
E .False. Defaults to $ORACLE_BASE or /u01/app/oracle if unset.

C .True. Hot cloning requires the source PDB (PDB1) to be read-only to ensure consistency during the clone. Others are not prerequisites.

A .True. CREATE SPFILE FROM PFILE works when idle.
B .False. Includes dynamic parameters too.
C .True. CREATE PFILE FROM SPFILE generates a PFILE.
D .False. A PFILE can start the instance if no SPFILE exists.
E .True. Dynamic parameters (e.g., DB_CACHE_SIZE) are modifiable.

A .True. Integrates with MOS for patches and support.
B .True. Manages Exadata, etc.
C .True. Covers WebLogic and other middleware.
D .False. Web-based, but not built into the DB.
E .False. Can manage stopped instances via agents.

A .False. V$TABLESPACE doesn’t show free space directly.
B .False. It’s from the control file, not the data dictionary.
C .True. Renaming in MOUNT state updates DBA_DATA_FILES post-open.
D .False. DBA_TABLESPACES shows attributes, not free space directly (use DBA_FREE_SPACE).
E .True. V$TABLESPACE reflects control file data.