Hard Difficulty Questions

1000

Why it fails: This was the limit in older versions.

8192

Why it fails: This exceeds the current architecture limit.

2048

Why it fails: This is not the maximum limit.

PARALLEL_DEGREE_LIMIT

Why it fails: This controls the degree of parallelism.

DML_PARALLEL_MODE

Why it fails: This is not a valid parameter.

PARALLEL_EXECUTION_ENABLED

Why it fails: This is not a valid parameter.

SQL Developer

Why it fails: Not for database engine upgrades.

RMAN

Why it fails: RMAN is for backups.

DBUA

Why it fails: DBUA is deprecated.

They automatically gather statistics

Why it fails: SQL Plan Baselines do not gather statistics; that is the job of the Automatic Statistics Gathering job and DBMS_STATS. It tempts because baselines and statistics both affect plan selection and are often tuned together, yet a baseline merely records and enforces previously accepted plans.

They store the actual table data

Why it fails: SQL Plan Baselines store accepted execution plans in the SQL Management Base, referencing plan hash values and outlines; table data resides in segments, never in baselines. It tempts because baselines do influence how statements execute, but they capture plan information only, not row data.

They require the Oracle Enterprise Manager

Why it fails: SQL Plan Baselines are managed through PL/SQL packages such as DBMS_SPM and the SQL Management Base, with no Enterprise Manager dependency. It tempts because Enterprise Manager exposes a graphical interface for viewing and loading baselines, but that is convenience, not a requirement.

It wraps around to 0

Why it fails: The counter halts at its maximum rather than wrapping; wrap-around is characteristic of fixed-width integer overflow in languages like C, not of lock-free reservation counters. Assuming modular arithmetic here misapplies a low-level numeric behaviour to a managed reservation mechanism.

It allows further updates anyway

Why it fails: Updates are refused once the counter hits its maximum; the reservation silently stops incrementing rather than permitting further writes. Allowing updates anyway would defeat the purpose of a bounded counter, so this option misstates the documented ceiling behaviour.

It switches to locking mode

Why it fails: Lock-free reservations never fall back to locking; the reservation counter simply stops accepting further increments once it hits its ceiling. Locking is a separate concurrency mechanism entirely, so this option confuses two unrelated strategies rather than describing the documented overflow behaviour.

Traditional audit

Why it fails: Traditional auditing captures every access to the table, including INSERT, UPDATE and DELETE, so it cannot isolate one user's SELECT statements. It is tempting because it is the default auditing mechanism, and it would be correct for broad, database-wide activity tracking rather than this narrow, user-and-statement-specific requirement.

Flashback Data Archive

Why it fails: Flashback Data Archive stores historical row versions for temporal queries, not audit records of who ran which statement. It is tempting because it preserves table history, and it would be correct for querying past data values with AS OF, not for capturing SELECT activity by a specific user.

Database Trigger

Why it fails: A database trigger fires on DML events such as INSERT, UPDATE or DELETE, and cannot intercept SELECT statements for auditing. Triggers would be the correct mechanism to log row-level data changes, not to capture read access against a specific table.

Fixed at 8

Why it fails: This is not a hard-coded value.

Controlled by the OS

Why it fails: Oracle manages this internally.

Unlimited

Why it fails: The carousel enforces a configurable ceiling; it does not permit infinite snapshots. Unlimited retention appeals because the feature cycles automatically, appearing boundless, yet the documented maximum is 8 per PDB, after which the oldest snapshot is discarded to make room.

The color of the server

Why it fails: Server colour has no bearing on upgrade duration; it is a physical attribute, not a configuration or workload variable. It is tempting because it is a concrete, easily observed property of the hardware, but the factors that actually govern upgrade time are data volume, object count and hardware performance.

Number of users

Why it fails: User count does not directly extend upgrade duration, since upgrades process database objects and data, not concurrent sessions. It is tempting because user numbers often correlate with workload size, but the actual determinants are data volume, number of objects and hardware throughput.

Run DBMS_STATS.LOCK_TABLE_STATS

Why it fails: LOCK_TABLE_STATS freezes statistics so the optimizer cannot refresh them; it does not capture or fix an existing execution plan. It is tempting because locking prevents plan changes, and would be correct when stale or fluctuating statistics are causing unstable plans, not when a specific good plan must be preserved.

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Why it fails: Pinning a plan requires SQL Plan Baselines or a stored outline; OPTIMIZER_FEATURES_ENABLE merely reverts optimiser behaviour to an older release, which may coincidentally reproduce the plan but does not lock it. It is tempting when a regression follows a database upgrade, where emulating the prior version's optimiser is the intended remedy.

Use SQL Tuning Advisor to accept a new profile

Why it fails: SQL Tuning Advisor generates recommendations and SQL profiles that adjust cardinality estimates; accepting a profile does not pin the current plan against optimizer changes. It is tempting because profiles can stabilise performance, and would be correct when the plan is poor and needs tuning rather than merely being frozen.

Automatic deletion of all backups older than the restore point

Why it fails: RECOVER TABLE restores a table or partition to a point in time by creating a temporary auxiliary instance, then exporting and importing; it never prunes the backup repository. Retention is governed by the configured retention policy and DELETE OBSOLETE. Automatic cleanup is tempting because recovery operations often involve auxiliary files, but those are removed, not historical backups.

Automatic conversion of the database to NOARCHIVELOG mode

Why it fails: RMAN's RECOVER TABLE performs an auxiliary-instance restore and Data Pump import into the target; it never alters the database's archiving mode, which must already be ARCHIVELOG for the operation to work. It is tempting because auxiliary-instance recovery does require archived redo, but switching log mode is a manual DBA action, not an automatic step.

User passwords

Why it fails: User passwords are stored as hashed credentials in the data dictionary, not in the PDB XML metadata file, which holds structural definitions. It is tempting because the file describes the whole database, but authentication secrets are never exported there.

Full table data

Why it fails: The PDB XML metadata file stores structural definitions such as object names, schemas, and tablespace mappings, not row-level table contents. It is tempting because the file describes the database fully, yet actual data resides in the datafiles, not the metadata.

Only allowed via stored procedures

Why it fails: Duality views are updatable directly through DML on the view itself, which the database translates into changes on the underlying base tables; stored procedures are not required. The option tempts because procedural encapsulation is a familiar way to control writes, and would suit scenarios demanding bespoke validation logic rather than native duality-view updates.

Manual re-synchronization

Why it fails: Duality views propagate document changes automatically to base tables via the database's own DML translation, so no manual re-synchronisation step exists. Manual synchronisation tempts because it resembles ETL or materialised-view refresh patterns, and would be the right approach when replicating between genuinely independent systems that lack a shared update mechanism.

Requires a trigger

Why it fails: Duality views are read-write through the view itself; Oracle propagates inserts, updates and deletes to the underlying tables automatically, so no trigger is needed. Triggers would be the correct mechanism for propagating changes made directly to base tables back into a JSON representation.

FLASHBACK TABLE ... TO SCN ...;

Why it fails: FLASHBACK TABLE TO SCN relies on undo data retained in the undo tablespace, which cannot reach back before a DROP TABLE once the segment is gone. It is tempting because it is fast and non-intrusive, and would be correct for reversing recent row-level DML within the undo retention window.

IMPORT FROM BACKUPSET ...;

Why it fails: IMPORT FROM BACKUPSET restores a whole tablespace or database from a backupset, not a single table to a past point in time, so it cannot target the pre-DROP state. It is tempting because it recovers from RMAN backups, and would be correct for transporting tablespaces between databases.

RESTORE TABLE ... UNTIL SCN ...;

Why it fails: RMAN has no RESTORE TABLE command; table-level recovery uses RECOVER TABLE ... UNTIL SCN, which creates a temporary auxiliary instance and Data Pump export. RESTORE TABLE is tempting because RESTORE handles datafile and tablespace recovery, but it cannot target a single table without affecting the containing tablespace.

null

Why it fails: A null option supplies no RMAN command, so it cannot perform any recovery, let alone a table point-in-time recovery to before the DROP. It is tempting when the listed commands appear unfamiliar, but selecting nothing would only be correct if no valid recovery syntax existed among the choices.

512

Why it fails: 512 is a valid dimension count but not the maximum, so it fails a question asking for the ceiling. It is tempting because powers of two feel like natural limits, yet Oracle's VECTOR type permits up to 65535 dimensions, leaving 512 far below the true cap.

1024

Why it fails: 1024 exceeds the VECTOR type's supported dimension limit, so the column definition would be rejected at creation. It is tempting as a power-of-two ceiling, but the actual maximum is 65535, making 1024 merely a valid mid-range value rather than the cap.

4096

Why it fails: 4096 is a permitted dimension count, not the ceiling, so it cannot answer a maximum-dimensions question. It is tempting as a plausible power-of-two limit, but Oracle's VECTOR type actually supports up to 65535 dimensions, so 4096 falls short of the true maximum.

Only performance is affected

Why it fails: Functionality is severely impacted.

The database will run in read-only mode

Why it fails: It will likely crash or have corrupted metadata.

The database will not start

Why it fails: It might start but will be unusable.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

Why it fails: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE loads existing execution plans into SQL plan baselines for plan stability, not SQL statements into a SQL Tuning Set. It tempts because both features read the cursor cache, but the requirement is capturing statements, which DBMS_SQLSET.CAPTURE_CURSOR_CACHE_SQLSET performs.

DBMS_STATS.GATHER_SYSTEM_STATS

Why it fails: DBMS_STATS.GATHER_SYSTEM_STATS collects system-level statistics such as CPU speed and I/O throughput for the optimiser, not SQL statements. It tempts because it also gathers data affecting SQL performance, but it never populates a SQL Tuning Set; DBMS_SQLSET.CAPTURE_CURSOR_CACHE_SQLSET does that.

DBMS_WORKLOAD_REPOSITORY.EXTRACT_SQL

Why it fails: DBMS_WORKLOAD_REPOSITORY.EXTRACT_SQL does not exist; the package exposes procedures such as CREATE_SNAPSHOT and AWR report functions, not SQL extraction into a SQL Tuning Set. It tempts because AWR and SQL Tuning Sets both concern captured SQL, but loading from the cursor cache uses DBMS_SQLSET.CAPTURE_CURSOR_CACHE_SQLSET.

Severe performance degradation

Why it fails: Reading fewer columns reduces I/O, so narrow projections over wide tables typically perform well rather than degrade severely. The columnar format stores each column separately, letting the engine skip unreferenced data. Severe degradation would apply to row-store scans reading entire rows, which is the scenario this option wrongly assumes.

Requires partitioning

Why it fails: Partitioning is an independent physical design technique for pruning data by partition key; it is not a prerequisite for selecting a few columns from a wide table. Columnar layout alone delivers the projection benefit. Partitioning would be the right answer when queries filter on a partition column across large datasets.

Requires a full table scan

Why it fails: Columnar storage lets queries read only the referenced columns, so selecting a small subset avoids scanning the remaining columns entirely. A full table scan applies to row-oriented layouts. Wide tables are chosen precisely because column pruning keeps narrow projections fast, making this claim the opposite of the actual behaviour.

Tablespace creation scripts

Why it fails: These are not performance metrics.

Database user passwords

Why it fails: Security data is not in AWR.

It flags the plan for manual DBA approval via the DBMS_SPM package.

Why it fails: ASPM verifies candidate plans automatically by comparing real execution statistics against the baseline, so manual DBA approval is not the mechanism. It tempts because DBMS_SPM does expose manual plan-evolution procedures; those apply when automatic SQL plan management is disabled or a plan is loaded deliberately.

It automatically promotes the plan based on the optimizer cost estimation.

Why it fails: Cost estimation alone cannot confirm a plan performs better, since the optimizer's estimates are what the baseline already distrusts. It tempts because cost is the optimizer's normal selection mechanism; that applies when choosing among plans without an existing baseline, not when validating one against it.

It relies on the Advisor to run a full analysis of the explain plan tree.

Why it fails: ASPM verifies a candidate plan by executing it and comparing its performance statistics against the existing baseline, not by explain-plan tree analysis. The SQL Tuning Advisor produces recommendations from a single plan's structure, so it is tempting here, but it cannot supply the comparative execution evidence ASPM requires.

It validates data constraints

Why it fails: The flush operation forces buffered Memoptimized Write data from the memoptimize pool to persistent storage; it does not validate constraints, which Oracle enforces during DML regardless. It is tempting because flushing sounds like a consistency check, and it would be correct if the requirement were constraint enforcement rather than durability of buffered writes.

It clears the cache

Why it fails: Memoptimized Write's flush operation does not clear the cache; it forces buffered writes from the row store to persistent storage, ensuring durability at commit boundaries. Clearing cache describes invalidation or eviction, which is a different mechanism entirely and would not satisfy the write-durability requirement this operation exists to provide.

It deletes the data

Why it fails: Flush writes buffered rows to disk for durability; it never removes data, which is the role of delete or truncate. It is tempting because flushing a buffer can sound like clearing it, and it would be correct if the goal were purging rows, not persisting Memoptimized Write changes.