What Are the Restrictions in a READ ONLY Transaction in Oracle?

There are lots of restrictions in a READ ONLY transaction:

• You can not switch to READ WRITE mode.
• You can not run any INSERT, UPDATE, DELETE statements.
• You can run SELECT query statements.

The tutorial exercise below shows you some of the restrictions:

SQL> connect HR/fyicenter

SQL> SET TRANSACTION READ ONLY;
Transaction set.

SQL> SET TRANSACTION READ WRITE;
ORA-01453: SET TRANSACTION must be first statement of
   transaction

SQL> INSERT INTO fyi_links (url, id)
  2  VALUES ('sql.com', 113);
ORA-01456: may not perform insert/delete/update operation
  inside a READ ONLY transaction

SQL> DELETE FROM fyi_links where id = 110;
ORA-01456: may not perform insert/delete/update operation
  inside a READ ONLY transaction

SQL> SELECT * FROM fyi_links;
     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06

⇒ General Rules on Data Consistency in Oracle

⇐ Set a READ ONLY Transaction in Oracle

⇑ Understanding SQL Transaction Management in Oracle

⇑⇑ Oracle Database Tutorials