Restrictions in a READ ONLY Transaction in Oracle

Q

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

✍: FYIcenter.com

A

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

 

Understanding SQL Transaction Management in Oracle

⇒⇒Oracle Database Tutorials

2016-10-17, 189👍, 0💬