23c 会话只读测试

23c  会话只读测试 环境准备： [oracle@db23 ~]$  /opt/oracle/product/23c/dbhomeFree/bin/sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Thu Nov 28 20:58:06 2024 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1608409424 bytes Fixed Size    10043728 bytes Variable Size   503316480 bytes Database Buffers 1090519040 bytes Redo Buffers     4530176 bytes Database mounted. show pdbs; Database opened. SQL>      CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 PDB1   MOUNTED SQL> alter pluggable database pdb1 open; Pluggable database altered. 登录pdb [oracle@db23 ~]$ sqlplus zc/zc@db23:1521/PDB1 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 28 21:02:07 2024 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle.  All rights reserved. Last Successful login time: Tue Nov 26 2024 20:32:53 -05:00 Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL>  进行测试： SQL> create table  t1 (id int); Table created. SQL> create sequence s1 cache 5; Sequence created. SQL>  insert into t1 values(1); 1 row created. SQL>  insert into t1 values(1); 1 row created. SQL> commit; Commit complete. SQL> update t1 set id=2; 2 rows updated. SQL>  commit; Commit complete. SQL> delete from t1; 2 rows deleted. SQL>  commit; Commit complete. SQL> insert into t1 values(1); 1 row created. SQL> SQL> insert into t1 values(2); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; ID ---------- 1 2 增删改查都没问题 修改参数： SQL> show parameter read_only NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ hybrid_read_only      boolean FALSE read_only      boolean FALSE read_only_open_delayed      boolean FALSE SQL> alter session set read_only=true; Session altered. SQL> show parameter read_only NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ hybrid_read_only      boolean FALSE read_only      boolean TRUE read_only_open_delayed      boolean FALSE 修改后的测试情况： SQL> create sequence s2 cache 5; create sequence s2 cache 5 * ERROR at line 1: ORA-28193: Can perform read operations only SQL>  create table  t2 (id int);  create table  t2 (id int) * ERROR at line 1: ORA-28193: Can perform read operations only SQL> update t1 set id=2; update t1 set id=2 * ERROR at line 1: ORA-28193: Can perform read operations only SQL>  delete from t1;  delete from t1              * ERROR at line 1: ORA-28193: Can perform read operations only SQL> select *  from t1; ID ---------- 1 2 SQL> select * from t1 for update; select * from t1 for update               * ERROR at line 1: ORA-28193: Can perform read operations only 测试sequence ： select  s1.nextval,t1.* from t1; SQL> select  s1.nextval,t1.* from t1;    NEXTVAL    ID ---------- ---------- 1     1 2     2 sequence没有问题. 通过触发器设置某个用户只读  create or replace trigger logon_ro after logon on zc.schema declare begin   execute immediate 'alter session set read_only=true'; end; / 测试： [oracle@db23 ~]$  /opt/oracle/product/23c/dbhomeFree/bin/sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Thu Nov 28 21:17:04 2024 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle.  All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL> alter session set container=PDB1; Session altered. SQL>  create or replace trigger logon_ro after logon on zc.schema declare begin   execute immediate 'alter session set read_only=true'; end; /   2    3    4    5    6    7   Trigger created. [oracle@db23 ~]$ sqlplus zc/zc@db23:1521/PDB1 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 28 21:18:07 2024 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle.  All rights reserved. Last Successful login time: Thu Nov 28 2024 21:17:47 -05:00 Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL>  delete from t1;  delete from t1 * ERROR at line 1: ORA-28193: Can perform read operations only