SQL> show user
User is "SYS"
 
SQL> set transaction read only;
 
Transaction set
 
SQL> insert into t_table values(3);
 
1 row inserted
 
SQL> commit;
 
Commit complete

---sys用户 set transaction read only不生效
SQL> select * from t_table;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
 
SQL>
SQL>
SQL> ---换另一个普通用户
SQL> conn tbl_bck/system@second
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as tbl_bck@SECOND
 
SQL> create table t_transaction(a int);
 
Table created
 
SQL> set transaction read only;
 
Transaction set
 
SQL> insert into t_table values(3);
 
insert into t_table values(3)
 
ORA-00942: table or view does not exist

---仅在非sys用户 set transaction read only才生效 
SQL> insert into t_transaction values(3);
 
insert into t_transaction values(3)
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction
 
SQL> delete from t_transaction;
 
delete from t_transaction
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction
 
SQL> update   t_transaction set a=3;
 
update   t_transaction set a=3
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction

--truncate为ddl语句不受set transaction read only影响
SQL> truncate table t_transaction;
 
Table truncated

SQL> show user
User is "tbl_bck"
---也可以用dbms_transaction包实现上述的只读事务功能
SQL> exec dbms_transaction.read_only;
 
PL/SQL procedure successfully completed
 
SQL> insert into t_transaction values(8);
 
insert into t_transaction values(8)
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction

--dbms_transaction.read_only|write只能用于事务开始而非结束
SQL> exec dbms_transaction.read_write;
 
begin dbms_transaction.read_write; end;
 
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 60
ORA-06512: at line 2
 
SQL>
SQL>
SQL> set transaction read write;
 
set transaction read write
 
ORA-01453: SET TRANSACTION must be first statement of transaction

---必须用dbms_transaction.commit或rollback方可结束一个只读事务
SQL> exec dbms_transaction.commit;
 
PL/SQL procedure successfully completed
 
SQL> insert into t_transaction values(8);
 
1 row inserted
 
SQL> commit;
 
Commit complete

---dbms_transaction包另一个功能可以直接内嵌于存储过程中,进行针对性的控制
SQL> create or replace procedure proc_transaction
  2  as
  3  v_a pls_integer;
  4  begin
  5  dbms_transaction.read_only;
  6  select count(1) into v_a from dual;
  7  dbms_transaction.commit;
  8  insert into t_transaction values(9);
  9  commit;
 10  end;
 11  /
 
Procedure created
 
SQL> exec proc_transaction;
 
PL/SQL procedure successfully completed
 
SQL> select * from t_transaction;
 
                                      A
---------------------------------------
                                      8
                                      9