Transaction – Arsalan Dehghani's Oracle Blog

ORA-04091 is an Oracle error code that indicates that a trigger, stored procedure, or package has encountered a mutating table error. This error occurs when a trigger or procedure tries to modify a table that is already being modified by the same transaction.

To resolve the ORA-04091 error, you can consider the following approaches:

1. Restructure the code: Review the trigger or procedure code to see if there is a way to rewrite it without causing the mutating table error. This may involve using a compound trigger, autonomous transactions, or other techniques to avoid the conflict.

2. Use a pragma directive: You can use the PRAGMA AUTONOMOUS_TRANSACTION directive to create an autonomous transaction within the trigger or procedure. This allows the trigger or procedure to perform its operations independently of the main transaction, avoiding the mutating table error.

3. Use a Statement-level trigger: To avoid changing table errors, use triggers at the statement level instead of at the row level. Statement-level triggers fire once for each DML statement, not for each affected row. This ensures that they do not cause conflicts with the table state.

It is important to carefully analyze the code and the specific scenario to determine the most appropriate solution for the ORA-04091 error.

V$TRANSACTION lists the active transactions in the system.

(i) The following columns together point to a transaction. (i.e.) The combination of the following should give a unique transaction id for that database.

XIDUSN – Undo segment number
XIDSLOT – NUMBER Slot number
XIDSQN – NUMBER Sequence number

(ii) The following columns explain the number of undo blocks / undo records used per transaction.
USED_UBLK – Number of undo blocks used
USED_UREC – Number of undo records used

In the case of transaction rollback, the above columns will estimate the number of undo blocks that need to be rolled back.

The number of undo records and undo blocks (USED_UREC and USED_UBLK) decreases while the transaction rolls back. When they reach 0, the transaction disappears from v$transaction.

The following query can be used to monitor the transaction rollback.
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR;

(iii) The STATUS following column explains the status of a transaction.

ACTIVE – Explains the transaction is active.

Before performing a normal/transactional shutdown, YOU can check this view to understand any ACTIVE transactions.
SELECT XIDUSN, XIDSLT, XIDSEQ , SES_ADDR, STATUS FROM V$TRANSACTION;

Posts