Oracle ORA-01456 Error: Causes and Solutions Complete Guide

ORA-01456: May Not Perform INSERT/DELETE/UPDATE Inside a READ ONLY Transaction

ORA-01456 occurs when a session attempts to execute DML statements (INSERT, UPDATE, or DELETE) within a transaction that has been explicitly set to READ ONLY mode using SET TRANSACTION READ ONLY. Oracle's READ ONLY transaction mode is designed to guarantee data consistency by providing a fixed point-in-time view of the database, and it strictly prohibits any data modifications. This error is most commonly encountered in reporting procedures, batch jobs, or distributed database environments where transaction modes are not carefully managed.

---

Top 3 Causes

1. Explicit SET TRANSACTION READ ONLY Followed by DML

The most common cause is a developer explicitly setting the transaction to READ ONLY for consistent reporting, then attempting to write results to a table within the same transaction.

-- This will raise ORA-01456
SET TRANSACTION READ ONLY;

SELECT SUM(amount) INTO v_total FROM sales WHERE sale_date = TRUNC(SYSDATE);

-- ERROR: ORA-01456 triggered here
INSERT INTO report_summary (report_date, total)
VALUES (TRUNC(SYSDATE), v_total);

2. DML Inside a Stored Procedure or Trigger After READ ONLY Is Set

Procedures or triggers that call SET TRANSACTION READ ONLY in an early step but attempt DML in a later step will fail. This is especially tricky in complex packages where transaction state is not clearly documented.

-- Problematic procedure
CREATE OR REPLACE PROCEDURE generate_report AS
    v_count NUMBER;
BEGIN
    SET TRANSACTION READ ONLY;  -- Read-only mode set here

    SELECT COUNT(*) INTO v_count FROM orders
    WHERE order_date = TRUNC(SYSDATE);

    -- ORA-01456 raised here!
    INSERT INTO report_log (log_date, order_count)
    VALUES (TRUNC(SYSDATE), v_count);

    COMMIT;
END;
/

3. READ ONLY Transaction Propagated via Database Links

In distributed environments, a READ ONLY transaction initiated on a remote site via a DB link can propagate to the local session, causing ORA-01456 when local DML is attempted.

-- Checking if a DB link is contributing to a READ ONLY transaction
SELECT s.sid,
       s.serial#,
       DECODE(BITAND(t.flag, 1), 1, 'READ ONLY', 'READ WRITE') AS tx_type
FROM   v$session s
JOIN   v$transaction t ON s.taddr = t.addr
WHERE  s.audsid = USERENV('SESSIONID');

---

Quick Fix Solutions

Always terminate the READ ONLY transaction with COMMIT or ROLLBACK before executing any DML. Then start a new transaction (which defaults to READ WRITE).

-- CORRECT approach: separate READ ONLY and DML transactions
DECLARE
    v_total NUMBER;
BEGIN
    -- Step 1: READ ONLY transaction for consistent read
    SET TRANSACTION READ ONLY;

    SELECT SUM(amount) INTO v_total
    FROM   sales
    WHERE  sale_date = TRUNC(SYSDATE);

    COMMIT;  -- End READ ONLY transaction explicitly

    -- Step 2: New transaction defaults to READ WRITE
    INSERT INTO report_summary (report_date, total_amount)
    VALUES (TRUNC(SYSDATE), v_total);

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Explicitly set READ WRITE if needed
SET TRANSACTION READ WRITE;

UPDATE employees
SET    last_updated = SYSDATE
WHERE  department_id = 20;

COMMIT;

---

Prevention Tips

1. Enforce coding standards for transaction boundaries.
Always pair SET TRANSACTION READ ONLY with an explicit COMMIT or ROLLBACK before any DML block. Add this check to your code review checklist and document any procedure that changes the transaction mode.

-- Recommended pattern
SET TRANSACTION READ ONLY NAME 'Consistent reporting snapshot';
-- ... read-only queries only ...
COMMIT; -- Always explicitly close the READ ONLY transaction

-- DML in a separate block
INSERT INTO audit_log VALUES (SYSDATE, 'Report completed');
COMMIT;

2. Implement explicit exception handling for ORA-01456.
Trap the error at the application or PL/SQL level to fail gracefully and alert the team when a READ ONLY violation occurs.

DECLARE
    e_read_only EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_read_only, -1456);
BEGIN
    -- business logic here
    NULL;
EXCEPTION
    WHEN e_read_only THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('ORA-01456: DML not allowed in READ ONLY transaction. '
                             || 'Commit or rollback before executing DML.');
        RAISE;
END;
/

---

Summary

ORA-01456 is straightforward to fix once you understand Oracle's transaction model. The golden rule is simple: never mix READ ONLY transaction mode with DML in the same transaction. Always commit or rollback to close the READ ONLY transaction first, then perform your writes in a new, separate transaction.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.