Setting database session to read only in 21c

Oracle introduced a new parameter read_only in 21c which is not documented in Database Reference yet at the time of this writing.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

[oracle@db-21 ~]$ sqlplus /nolog @q   SQL*Plus: Release 21.0.0.0.0 - Production on Tue Nov 30 10:39:56 2021 Version 21.4.0.0.0   Copyright (c) 1982, 2021, Oracle.  All rights reserved.   SQL> SQL> conn tc/tc@db-21/pdb Connected. SQL> SQL> create table t(x int);   Table created.   SQL> SQL> insert into t values (0);   1 row created.   SQL> commit;   Commit complete.   SQL> SQL> alter session set read_only=true;   Session altered.   SQL> SQL> update t   2     set x=1; update t        * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access     SQL> SQL> create table new_tab(x int); create table new_tab(x int) * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access     SQL> SQL> select *   2    from t   3    for update;   from t        * ERROR at line 2: ORA-16000: database or pluggable database open for read-only access     SQL> SQL> lock table t in exclusive mode; lock table t in exclusive mode * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access     SQL> SQL> alter session set read_only=false;   Session altered.   SQL> SQL> update t   2     set x=1;   1 row updated.   SQL> SQL> create table new_tab(x int);   Table created.   SQL> SQL> select *   2    from t   3    for update;            X ----------          1   SQL> SQL> lock table t in exclusive mode;   Table(s) Locked.

I believe it was originally introduced for Oracle Autonomous Database offerings since it is the only place where it is documented: Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted. There are no comparable database features that can provide the same functionality at this level of granularity. A typical usage of this when some application sessions should be set to Read-Only. We can set read_only=true in a logon trigger for that:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL> create or replace trigger logon_trg   2  after logon on tc.schema   3  declare   4  begin   5    execute immediate 'alter session set read_only=true';   6  end;   7  /   Trigger created.   SQL> SQL> conn tc/tc@db-21/pdb Connected. SQL> select *   2    from t   3    for update; select * * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access     SQL> SQL> conn system/manager@db-21/pdb Connected. SQL> select *   2    from tc.t   3    for update;            X ----------          1