Init parameterRedo, undo & archiving

UNDO_TABLESPACE

What it controls

Which undo tablespace the instance uses. Each RAC instance has its own.

Know this before you change it

After switching, the old tablespace stays in use until its active transactions finish. Drop it only once it shows no active undo.

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'undo_tablespace';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'undo_tablespace' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = BOTH SID = '*';11 12-- Remove it from the spfile to go back to the default at the next restart13ALTER SYSTEM RESET undo_tablespace 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 transactions and the undo they holdEvery open transaction with its session, undo size and start time. An old transaction from an idle session is usually someone who forgot to commit.

Open in the parameter referenceOracle's reference

More in Redo, undo & archiving

• UNDO_RETENTIONSeconds of committed undo Oracle tries to keep, for long queries and Flashback Query.
• 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.