Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Restrictions in a READ ONLY Transaction

By: FYIcenter.com

(Continued from previous topic...)

What Are the Restrictions in a READ ONLY Transaction?

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

(Continued on next topic...)

  1. What Is a Transaction?
  2. How To Start a New Transaction?
  3. How To End the Current Transaction?
  4. How To Create a Test Table for Transaction Testing?
  5. How To Commit the Current Transaction?
  6. How To Rollback the Current Transaction?
  7. What Happens to the Current Transaction If a DDL Statement Is Executed?
  8. What Happens to the Current Transaction If the Session Is Ended?
  9. What Happens to the Current Transaction If the Session Is Killed?
  10. How Does Oracle Handle Read Consistency?
  11. What Is a READ WRITE Transaction?
  12. What Is a READ ONLY Transaction?
  13. How To Set a Transaction To Be READ ONLY?
  14. What Are the Restrictions in a READ ONLY Transaction?
  15. What Are the General Rules on Data Consistency?
  16. What Are Transaction Isolation Levels Supported by Oracle?
  17. What Is a Data Lock?
  18. How Data Locks Are Respected?
  19. How To Experiment a Data Lock?
  20. How To View Existing Locks on the Database?
  21. What Is a Dead Lock?
  22. How Oracle Handles Dead Locks?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...