Oracle Database Discussions

On 10gR2 Tr64, we want some of sessions of db user USER1 lets say which are coming from MACHINE1 to be READ ONLY but if connections of USER1 are coming from MACHINE2 or MACHINE3 lets say, they will be READ WRITE.

I saw Kyte's Transaction Isolation Levels article;
http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html

and tried to set READ ONLY isolation level in a database logon trigger but it didn't work with alter session. Also setting this will only help until a transaction ends, but we want to stay until connection ends.

Are there any alternatives for this kind of a need or am I missing somthing?

Thank you.

SQL> conn hr/hr
Connected.
SQL> ALTER SESSION SET ISOLATION_LEVEL READ ONLY ;
ALTER SESSION SET ISOLATION_LEVEL READ ONLY
                                  *
ERROR at line 1:
ORA-00927: missing equal sign


SQL> conn hr/hr
Connected.
SQL> ALTER SESSION SET ISOLATION_LEVEL = READ ONLY ;
ALTER SESSION SET ISOLATION_LEVEL = READ ONLY
                                         *
ERROR at line 1:
ORA-02183: valid options: ISOLATION_LEVEL { SERIALIZABLE | READ COMMITTED }


SQL> conn hr/hr
Connected.
SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL> update employees set first_name = 'antu'
  2  where employee_id = 200 ;
update employees set first_name = 'antu'
       *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
transaction


SQL> rollback ;

Rollback complete.

SQL> update employees set first_name = 'antu'
  2  where employee_id = 200 ;

1 row updated.

SQL>

-- Samples included --

Message was edited by:
antu