ORA-01456: Read-Only Transaction - Fix DML Restrictions

ORA-01456: Cannot Perform INSERT/DELETE/UPDATE in Read-Only Transaction

Error Overview

Error Text: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

The ORA-01456 error is raised when an application attempts to execute a DML statement (INSERT, UPDATE, DELETE, or MERGE) within a transaction that has been explicitly set to read-only mode using SET TRANSACTION READ ONLY. It also appears on Oracle Active Data Guard (ADG) standby databases where the standby is in read-only mode and DML is attempted — either directly by the application or implicitly by a trigger or function.

Common Causes

1. Explicit SET TRANSACTION READ ONLY

• A transaction was started with SET TRANSACTION READ ONLY for a consistent read snapshot
• Application code then attempts DML before the transaction is committed or rolled back
• The session remains in read-only mode until the transaction ends

2. Active Data Guard Standby Database

• Application connects to a standby database opened in READ ONLY WITH APPLY mode
• The application does not differentiate between primary and standby in its connection pool
• Write operations routed to a standby through an incorrect load balancer rule

3. Flashback Query with Consistent Read Requirement

• Application issues DBMS_FLASHBACK.ENABLE_AT_TIME or ENABLE_AT_SYSTEM_CHANGE_NUMBER
• Flashback mode disables DML to maintain read consistency
• Session continues attempting DML while flashback is active

4. Database or Tablespace in Read-Only Mode

• The target tablespace has been set to READ ONLY
• The entire database was opened READ ONLY (e.g., during a backup or migration)
• Pluggable database (PDB) opened in read-only mode for reporting

5. Application Not Resetting Transaction Mode After Consistent Read Block

• Code path that sets READ ONLY for a reporting section does not always issue COMMIT / ROLLBACK before the next write section

Diagnostic Queries

Check Current Transaction Mode

-- Check if the current session has an active read-only transaction
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  t.read_only,
  t.start_time,
  t.status AS txn_status,
  t.used_ublk
FROM v$session s
LEFT JOIN v$transaction t ON s.taddr = t.addr
WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');

Check All Read-Only Transactions in the Database

-- Find all sessions in read-only transactions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  t.read_only,
  t.start_time
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE t.read_only = 'YES'
ORDER BY t.start_time;

Check Database Open Mode

-- Confirm database open mode (READ WRITE vs READ ONLY)
SELECT
  db_unique_name,
  open_mode,
  database_role,
  switchover_status,
  dataguard_broker
FROM v$database;

Check Tablespace Read/Write Status

-- Identify read-only tablespaces
SELECT
  tablespace_name,
  status,
  contents,
  logging
FROM dba_tablespaces
WHERE status = 'READ ONLY'
ORDER BY tablespace_name;

Check Flashback Status for the Session

-- Determine if flashback is enabled for the current session
SELECT
  dbms_flashback.get_system_change_number AS current_scn,
  SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user
FROM dual;

-- Check for flashback-enabled sessions
SELECT
  fb.sid,
  s.username,
  s.program,
  fb.scn
FROM (
  SELECT sid, value AS scn FROM v$sesstat
  JOIN v$statname ON v$sesstat.statistic# = v$statname.statistic#
  WHERE v$statname.name = 'flashback session read SCN'
    AND value > 0
) fb
JOIN v$session s ON fb.sid = s.sid;

Step-by-Step Resolution

1. End the Read-Only Transaction Before DML

The most direct fix: commit or rollback the read-only transaction before attempting any write operation.

-- WRONG: DML inside a READ ONLY transaction
-- SET TRANSACTION READ ONLY;
-- SELECT COUNT(*) FROM orders;
-- SELECT SUM(amount) FROM orders WHERE order_date = TRUNC(SYSDATE);
-- INSERT INTO audit_log (action, log_time) VALUES ('REPORT_RUN', SYSDATE);  -- ORA-01456!

-- CORRECT: End the read-only transaction before writing
SET TRANSACTION READ ONLY;
SELECT COUNT(*) FROM orders;
SELECT SUM(amount) FROM orders WHERE order_date = TRUNC(SYSDATE);
COMMIT;  -- End the read-only transaction

-- Now DML is permitted
INSERT INTO audit_log (action, log_time) VALUES ('REPORT_RUN', SYSDATE);
COMMIT;

2. Separate Read and Write Code Paths

-- Pattern: explicit transaction boundary management
DECLARE
  v_order_count NUMBER;
  v_order_total NUMBER;
BEGIN
  -- Phase 1: Read-only consistent snapshot
  SET TRANSACTION READ ONLY NAME 'daily_report_snapshot';

  SELECT COUNT(*), SUM(amount)
  INTO v_order_count, v_order_total
  FROM orders
  WHERE order_date = TRUNC(SYSDATE);

  COMMIT;  -- End read-only transaction before any write

  -- Phase 2: Write audit record (separate transaction)
  INSERT INTO report_runs (run_date, order_count, order_total, run_by)
  VALUES (SYSDATE, v_order_count, v_order_total, USER);

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Orders: ' || v_order_count || ', Total: ' || v_order_total);
END;
/

3. Fix Active Data Guard Connection Routing

For ADG environments, application connections must distinguish between read/write and read-only endpoints:

-- On the primary: check the database role
SELECT db_unique_name, database_role, open_mode FROM v$database;
-- PRIMARY, READ WRITE

-- On the standby:
SELECT db_unique_name, database_role, open_mode FROM v$database;
-- STANDBY, READ ONLY WITH APPLY

-- Application connection strings should use separate services:
-- Primary service (for writes): uses the primary's service name
-- Standby service (for reads): uses the standby's service name

Configure separate Oracle services for primary and standby:

-- On the primary: create a read-write service
EXEC DBMS_SERVICE.CREATE_SERVICE('APP_RW', 'APP_RW');
EXEC DBMS_SERVICE.START_SERVICE('APP_RW');

-- On the standby: create a read-only service
EXEC DBMS_SERVICE.CREATE_SERVICE('APP_RO', 'APP_RO');
EXEC DBMS_SERVICE.START_SERVICE('APP_RO');

-- Application layer: route INSERT/UPDATE/DELETE to APP_RW
--                    route SELECT-only operations to APP_RO

4. Fix Tablespace Read-Only Issue

-- Check which tablespace contains the object receiving DML
SELECT
  t.table_name,
  t.tablespace_name,
  ts.status
FROM user_tables t
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
WHERE t.table_name = 'YOUR_TABLE';

-- If tablespace is READ ONLY and needs to accept writes:
ALTER TABLESPACE app_data READ WRITE;

-- Confirm change
SELECT tablespace_name, status FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

5. Fix Database Opened READ ONLY

-- If database itself is in READ ONLY mode (e.g., post-restore)
-- Check open mode
SELECT open_mode FROM v$database;

-- If running in mount state, open READ WRITE:
-- ALTER DATABASE OPEN;  -- from MOUNT state

-- If already open READ ONLY and no Data Guard, restart:
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- For PDB in read-only mode:
ALTER PLUGGABLE DATABASE app_pdb OPEN READ WRITE;

6. Disable Flashback Before DML

-- Check if flashback is enabled for the session
-- (ORA-01456 can occur when DBMS_FLASHBACK is active)

-- Disable flashback for the session before DML
EXEC DBMS_FLASHBACK.DISABLE;

-- Now DML is permitted
UPDATE orders SET status = 'CLOSED' WHERE order_id = 12345;
COMMIT;

Prevention Strategies

1. Transaction Wrapper Pattern for Reports

-- Encapsulate read-only transactions to ensure they always end cleanly
CREATE OR REPLACE PROCEDURE run_consistent_report (
  p_report_date IN DATE
) AS
  v_count NUMBER;
  v_total NUMBER;
BEGIN
  -- Explicit transaction start
  SET TRANSACTION READ ONLY NAME 'report_snapshot';

  BEGIN
    SELECT COUNT(*), SUM(amount)
    INTO v_count, v_total
    FROM orders
    WHERE TRUNC(order_date) = TRUNC(p_report_date);
    -- Always end the read-only transaction
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      COMMIT;  -- End read-only transaction even on error
      RAISE;
  END;

  -- Audit write happens in a new, separate transaction
  INSERT INTO report_history (report_date, row_count, total_amount, run_time)
  VALUES (p_report_date, v_count, v_total, SYSTIMESTAMP);
  COMMIT;
END;
/

2. Connection Pool Routing in Application Framework

// Java: route DML connections to primary, SELECTs to standby
@Transactional(readOnly = true)   // routes to standby service
public List<Order> getOrders() { ... }

@Transactional(readOnly = false)  // routes to primary service
public void saveOrder(Order o) { ... }

// Configure two DataSources in Spring:
// primaryDataSource  -> jdbc:oracle:thin:@//primary-host/APP_RW
// standbyDataSource  -> jdbc:oracle:thin:@//standby-host/APP_RO

3. Monitor Read-Only Transaction Duration

-- Alert for long-running read-only transactions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  t.start_time,
  ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 1440, 2) AS minutes_open,
  t.read_only
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE t.read_only = 'YES'
  AND (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 1440 > 30  -- > 30 minutes
ORDER BY t.start_time;

4. ADG Health Monitoring

-- Check Active Data Guard apply lag and open mode
SELECT
  name,
  db_unique_name,
  open_mode,
  database_role,
  TO_CHAR(SYSDATE - (SELECT MAX(next_time) FROM v$archived_log WHERE applied = 'YES' AND standby_dest = 'NO'), 'HH24:MI:SS') AS approx_lag
FROM v$database;

Related Errors

• ORA-01031 - Insufficient privileges
• ORA-01036 - Illegal variable name/number
• ORA-00604 - Error occurred at recursive SQL level
• ORA-02291 - Integrity constraint violated

Emergency Response

Quick Fixes

1. End the read-only transaction immediately

   -- In the offending session
   COMMIT;
   -- or
   ROLLBACK;
   -- Then retry the DML

2. Verify database role before attempting write

   SELECT open_mode, database_role FROM v$database;
   -- If READ ONLY WITH APPLY, you are on a standby — connect to primary instead

3. Disable Flashback if it was the cause

   EXEC DBMS_FLASHBACK.DISABLE;

4. Kill a stuck session holding a read-only transaction open

   SELECT sid, serial# FROM v$session s
   JOIN v$transaction t ON s.taddr = t.addr
   WHERE t.read_only = 'YES';
   
   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Post-Resolution Cleanup

-- Confirm no read-only transactions remain open
SELECT COUNT(*) FROM v$transaction WHERE read_only = 'YES';

-- Verify target tablespace is read/write
SELECT tablespace_name, status FROM dba_tablespaces
WHERE status != 'ONLINE'
ORDER BY tablespace_name;

-- Confirm database open mode
SELECT open_mode, database_role FROM v$database;

-- Test a simple DML to confirm writes are now permitted
INSERT INTO dual_test SELECT * FROM dual WHERE 1=0;
ROLLBACK;