OCP CBT Question 518

Please solve the following question.

You are a DBA for a busy OLTP system. A critical batch report process, which reads a large historical table without committing, occasionally fails with `ORA-01555: snapshot too old`. The database is configured with `UNDO_RETENTION = 900` seconds. You want to minimize the occurrence of this error without significantly increasing disk space usage for undo. Which action should you take?

sql-- Scenario:-- 1. A long-running UPDATE statement modifies a large number of rows.-- 2. Concurrently, a batch report executes a SELECT statement against the same table, reading older blocks.-- 3. The UPDATE commits, and its undo segments are marked for reuse.-- 4. Before the batch report completes, its required undo information is overwritten.-- Current configuration:-- SHOW PARAMETER UNDO_RETENTION;-- NAME TYPE VALUE-- ------------------------------------ ----------- -------------------------------- undo_retention integer 900

Increase UNDO_RETENTION to a significantly higher value (e.g., 3600 seconds) and set RETENTION GUARANTEE.

Increase the size of the undo tablespace without changing UNDO_RETENTION.

Reduce the frequency or duration of the long-running UPDATE statements.

Implement FLASHBACK_TRANSACTION_QUERY to analyze transaction history.