Oracle Database Foundations Advanced Practice Exam: Hard Questions 2025

A database administrator discovers that a critical batch process is experiencing performance degradation. Upon investigation, they find that multiple sessions are waiting on 'enq: TX - row lock contention' wait events. The application uses READ COMMITTED isolation level and updates customer records based on a queue table. What is the MOST likely root cause and appropriate solution?

An application developer writes a query that joins five tables with complex WHERE conditions. The execution plan shows a NESTED LOOPS join accessing one table via INDEX RANGE SCAN repeatedly. The query returns 50,000 rows and takes 45 seconds to complete. Statistics are current. What is the MOST appropriate optimization approach?

During a database recovery scenario, the DBA discovers that all online redo log groups have been corrupted, but archived redo logs are intact up to 2 hours ago. The database was performing normally until an unexpected power failure. The current control file is available. What is the recovery limitation and the MOST accurate statement about data recovery?

A development team reports that a DELETE statement removing 500,000 rows from a 10-million-row table is failing with error ORA-30036: unable to extend segment by 8 in undo tablespace. The undo tablespace is set to AUTOEXTEND with unlimited size, but the filesystem is 95% full. The UNDO_RETENTION parameter is set to 10800 (3 hours). What is the PRIMARY cause and the BEST immediate solution?

A query uses a correlated subquery in the WHERE clause: SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE customer_id = o.customer_id). The orders table has 5 million rows with 100,000 distinct customers. Execution takes 8 minutes. An index exists on customer_id. What represents the BEST optimization strategy?

A table TRANSACTIONS has a composite primary key on (account_id, transaction_date, sequence_num). A frequently executed query filters on transaction_date between two dates and account_id, but performance is degraded. The primary key index is in place. The optimizer shows an INDEX RANGE SCAN but examines many more rows than returned. What is the MOST likely explanation and solution?

During instance startup, a DBA observes the following progression: NOMOUNT succeeds, MOUNT succeeds, but OPEN fails with ORA-01113: file 5 needs media recovery. The database is in ARCHIVELOG mode. One datafile was restored from a backup taken 24 hours ago while other datafiles are current. What recovery approach is required?

A table has a column STATUS with values 'ACTIVE', 'PENDING', 'CLOSED'. Data distribution: 90% CLOSED, 8% ACTIVE, 2% PENDING. Most queries filter WHERE STATUS = 'ACTIVE' or WHERE STATUS = 'PENDING'. A standard B-tree index on STATUS provides minimal performance improvement. What is the BEST indexing strategy?

A database experiences a failure during a batch update that modified 3 million rows across multiple tables within a single transaction. The session terminated abnormally before COMMIT. Upon restart, users report the database is extremely slow. Checking v$transaction shows no active transactions, but v$fast_start_transactions shows significant activity. What is occurring and what should the DBA do?

An application executes: UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'; affecting 50,000 rows. Immediately after, without COMMIT, another session executes: SELECT COUNT(*) FROM products WHERE category = 'Electronics' AND price > 1000; with default isolation level. What will occur?