Init parameterRedo, undo & archiving

UNDO_RETENTION

What it controls

Seconds of committed undo Oracle tries to keep, for long queries and Flashback Query.

Know this before you change it

With autoextending undo files Oracle tunes retention itself above this value. With fixed-size undo, size the tablespace for your longest query.

Default: 900

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'undo_retention';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'undo_retention' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET undo_retention SCOPE = SPFILE SID = '*';

Related scripts

• Undo usage and ORA-01555 riskUndo extents by status, then the longest query and any snapshot-too-old or out-of-space errors from V$UNDOSTAT (roughly the last four days).

Open in the parameter referenceOracle's reference

More in Redo, undo & archiving

• UNDO_TABLESPACEWhich undo tablespace the instance uses. Each RAC instance has its own.
• LOG_ARCHIVE_DEST_NWhere archived redo goes: a local location or a Data Guard standby service. Numbered 1 to 31.
• LOG_ARCHIVE_FORMATFile name pattern for archived logs written to a plain directory.
• ARCHIVE_LAG_TARGETForces a log switch after this many seconds, even when the database is quiet.
• LOG_BUFFERSize of the redo log buffer.
• FAST_START_MTTR_TARGETTarget seconds for crash recovery. Oracle writes dirty blocks early enough to meet it.