Oracle Database Interview Question:

What Are the Restrictions in a Oracle READ ONLY Transaction?

Submitted by: Administrator

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/globalguideline

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 ggl_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 ggl_links where id = 110;
ORA-01456: may not perform insert/delete/update operation
inside a READ ONLY transaction

SQL> SELECT * FROM ggl_links;
<pre> ID URL NOTES COUNTS CREATED
------- ---------------- ---------- ---------- ---------
101 globalguideline.COM 07-MAY-06
110 globalguideline.COM 07-MAY-06</pre>
Submitted by: Administrator