Hybrid Read Only in Oracle 23 ai

“Hybrid Read only mode” one of the nicest improvements in Oracle 23ai.

With this , we can have the pluggable databases ( PDB) either as “read/write” / “read only” .

In this “read only” mode , the common users will able to update the tables , as if PDB is “open” and “read write” mode the local (PDB) users will not be able to update / insert any tables; they can “select only” from the pdb objects.

This features enables the DBAs to patch , update the database objects in a pluggable database.

Here is an example.

There is a new column name ‘IS_HYBRID_READ_ONLY’ to the v$view ( v$container_topology )

Log on to CDB and review the PDB status.

Session 1

SQL> show con_name;

CON_NAME 
------------------------------
CDB$ROOT

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    
___________ _____________ ______________________ 
CDB$ROOT    READ WRITE    NO                     
PDB$SEED    READ ONLY     NO                     
FREEPDB1    READ WRITE    NO 

Session 2

In this PDB , let us create a user in PDB and give him access to create a table , quota unlimited on “USERS” tablespace. Note that , the user need not created with “LOCAL” as the prefix.

SQL> show user
USER is "LOCAL_MOHIDEEN"

SQL> create table emp as select object_name as emp_name , object_type as dept from all_objects ;
Table created.

SQL> insert into emp values ( 'Zahir' , 'IT') ;
1 row created.

SQL> commit;
Commit complete.

As the PDB is open for “READ WRITE” , the local user was able to create a table and insert a record in to the table.

Now , let us go to the session 1 and make the PDB as hybrid read only.

Session 1

In this session , we made the PDB as “hybrid read only” and created a common user ( C##MOHIDEEN) and give him access

to the table owned by the local user ( LOCAL_MOHIDEEN). Note that common user has to be created with C## as prefix.

SQL> show con_name;

CON_NAME 
------------------------------
CDB$ROOT

SQL> alter pluggable database freepdb1 close immediate;

Pluggable database FREEPDB1 altered.

SQL> alter pluggable database freepdb1 open hybrid read only;

Pluggable database FREEPDB1 altered.

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

CON_NAME    OPEN_MODE     IS_HYBRID_READ_ONLY    
___________ _____________ ______________________ 
CDB$ROOT    READ WRITE    NO                     
PDB$SEED    READ ONLY     NO                     
FREEPDB1    READ WRITE    YES 

Session 2

Now , let us go to the other session and try to insert a record in to the table (EMP).

SQL> show user;
USER is "LOCAL_MOHIDEEN"

SQL> insert into emp values ('John Doe','HR');
insert into emp values ('John Doe','HR')
            *
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Help: https://docs.oracle.com/error-help/db/ora-16000/

SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.7.0.25.01

This time , let us log on to the PDB as the common user ( c##mohideen).

SQL> show user
USER is "C##MOHIDEEN"

SQL> insert into local_mohideen.emp values ( 'John Doe','HR'); 
1 row created.

SQL> commit;
Commit complete.

There we have it .

With the cdb user , I can do read/write operations, where with the local user the database is read only.

Hybrid Rocks! :-)

Comments Welcome!

Originally published at https://zahirmohideen.blogspot.com/2025/07/hybrid-read-only-in-oracle-23ai.html