Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Set a READ ONLY Transaction

By: FYIcenter.com

(Continued from previous topic...)

How To Set a Transaction To Be READ ONLY?

If you want a transaction to be set as READ ONLY, you need to the transaction with the SET TRANSACTION READ ONLY statement. Note that a DML statement will start the transaction automatically. So you have to issue the SET TRANSACTION statement before any DML statements. The tutorial exercise below shows you a good example of READ ONLY transaction:

SQL> connect HR/fyicenter

SQL> SET TRANSACTION READ ONLY;
Transaction set.

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

Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog

SQL> connect SYSTEM/password
Connected.

SQL>  DELETE FROM hr.fyi_links where id = 112;
1 row deleted.

SQL>  DELETE FROM hr.fyi_links where id = 113;
1 row deleted.

SQL> COMMIT;
Commit complete.

Go back to the "HR" SQL*Plus window.

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

SQL> COMMIT;
Commit complete.

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

As you can see that two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.

(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...