Prior to Oracle 23c release you have two options for OPEN_MODE with pluggable databases either read/write (default mode) or read only.

With read-only mode all types of accounts (local PDB accounts) or common-user accounts can perform read-only operations on the pluggable database tables.

In oracle 23c a new mode is introduced which is hybrid read-only mode…(local PDB accounts) can perform ONLY read operations against tables while common-user accounts can performed read/write operations against tables in the pluggable database system.

Let us check the traditional v$pdbs view to check my current 23c pluggable databases OPEN_MODE

SQL> select name, open_mode from v$pdbs;

Also, let us explore the NEW database view V$CONTAINER_TOPOLOGY that includes the column IS_HYBRID_READ_ONLY:

SQL> select *from V$CONTAINER_TOPOLOGY;

— will create common user called C##DBA granted DBA role

SQL> CREATE USER C##DBA IDENTIFIED BY test123 CONTAINER=ALL;

SQL> grant dba to C##DBA CONTAINER=ALL;

SQL> alter user C##DBA default role all;

— will create local user in pluggable database PDB2 with the name dba1

SQL> create user dba1 identified by test123;

SQL> grant dba to dba1;

SQL> alter user dba1 default role all;

Now, Let us enable hybrid read only mode:

sqlplus / as sysdba

SQL> ALTER PLUGGABLE DATABASE PDB2 CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN HYBRID READ ONLY;

Pluggable database altered.

Checking from container level the pluggable database PDB2 is now open with “Hybrid Read Only”mode:

Let us test the expected functionality:

As local user I will be able to perform “SELECT” queries only….any data modification queries such as insert,update,delete will not work:

sqlplus “dba1/test123″@PDB2

SQL> select * from hr.table1;

FNAME                LANAME

——————– ——————–

EMAD                 AL-MOUSA

Tim                  Allen

SQL> insert into hr.table1 values (‘Adam’,’Smith’);

insert into hr.table1 values (‘Adam’,’Smith’)

               *

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

Now, let us try with common-user C##DBA….the account should be able to do all kind of operations (SELECT,INSERT,UPDATE,DELETE) unlike “local”database account:

sqlplus “C##DBA/test123″@PDB2

SQL> select * from hr.table1;

FNAME                LANAME

——————– ——————–

EMAD                 AL-MOUSA

Tim                  Allen

SQL> insert into hr.table1 values (‘Adam’,’Smith’);

1 row created.

SQL> commit;

Commit complete.

Someone would ask…nice feature but what benefits does it provide ?

• It will help with “maintenance” activities where you want to restrict application account from performing any data modifications for some time and only DBA container level can modify data.

• It will help with patching especially with datapatch step where SQL code modification is required to be done with NO locking in-place.

• It can help with forensics activities and investigation, to limit accounts within pluggable database level from performing any data modification while analysis and investigation is in-place.