Ch 08: Locking and Blocking

SELECT
    used_ublk
      AS num_undo_blocks
  FROM
      v$transaction t
    INNER JOIN
      v$session s
    ON t.addr = s.taddr
  WHERE
    s.sid = (
      SELECT
          sid
        FROM
          v$mystat
        WHERE
          rownum = 1
      )    

ORA-1555 is commonly caused by:

• undo segment is too small
• programs fetch across COMMITs
• block cleanout

Automatic UNDO Management

• Use UNDO_RETENTION
• Require auto-extensible UNDO tablespace

For fixed size UNDO tablespace, use UNDO Advisor from DBMS_ADVISOR.

Block Cleanout

Delayed block cleanout causes ORA-1555 when a block has a transaction ID stored and that transaction ID is not found in the UNDO tablespace.

If the transaction ID was found in the UNDO tablespace, then the COMMIT SCN could then be determined if the transaction was committed.

Since the transaction no longer exists in the UNDO tablespace, then the transaction must have reached a resolution: COMMIT or ROLLBACK.

MVC requires SCN.