Oracle9i DBA -- Database: Fundamentals I (11)

Managing Undo Data

##CONTINUE##

• For sizing a UNDO tablespace the UNDO_RETENTION in seconds, number of undo data blocks generated per second and the overhead varies based on extent and file size (db_block_size) is required.
• Use the ALTER SYSTEM command for dynamic switching between UNDO tablespaces

  ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

• An undo segment with the status PENDING OFFLINE still contains active transactions. When no rows return from the query, then all transactions are complete, and the tablespace can be dropped.
• Oracle server automatically creates an UNDO tablespace called SYS_UNDOTBS when no undo tablespace name is specified.
• Deferred undo segments are dropped automatically
• Before any DML operation the undo information will always be stored in the undo segment.
• ‘Snapshot too old’ error may occur if there are long running queries. This generally takes place if the database has larger rollback extents. If the rollback extents are smaller, it will lead to undo extents getting deallocated.
• V$ROLLSTAT is used to view the undo statistic and the segment number. V$ROLLNAME is show the segment number and name. V$UNDOSTAT contains snapshots of the performance of the undo tablespace. V$TRANSACTION and V$SESSION can be used to view the active transactions.
• The ‘Set TRANSACTION ISOLATION LEVEL SERIALIZABLE’ will ensure read consistency of the transaction, though it is derimental to its performance.
• The different type of undo segments are SYSTEM, NONSYSTEM and Deferred. The deferred segment is created when a tablespace is brought offline. They are used for recovery when the transaction is brought online.
• USER_SEGMENT data dictionary contains all types of segments and their storage parameters for the user.