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