Oracle 23ai - Hybrid Read-Only Mode for Pluggable Databases

In Oracle 23ai, a new open mode called Hybrid Read-Only has been introduced for pluggable databases (PDBs). When a PDB is set to this mode, local users can only connect with read-only access. However, common users are not restricted and can still perform write operations. SQL> ALTER PLUGGABLE DATABASE ORCLORPDB OPEN HYBRID READ ONLY; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLORPDB READ WRITE NO Testing Write Access as SYS User Now, we connect to ORCLORPDB as the SYS user and test write operations: sqlplus "sys/sys@target:1522/ORCLORPDB as sysdba" SQL> show user; USER is "SYS" SQL> select OPEN_MODE from v$pdbs; OPEN_MODE ---------- READ WRITE SQL> create table vahid.tbl1(id number); Table created. As seen above, the SYS user sees the OPEN_MODE as READ WRITE, even though the PDB was opened in HYBRID READ ONLY mode. Testing Write Access as Local User Now, let’s repeat the test with a local user named VAHID: sqlplus "vahid/reeyuuy@target:1522/ORCLORPDB" SQL> show user; USER is "VAHID" SQL> select OPEN_MODE from v$pdbs; OPEN_MODE ---------- READ ONLY SQL> create table vahid.tbl2(id number); ORA-16000: Attempting to modify database or pluggable database that is open for read-only access. As expected, the local user (VAHID) is not allowed to perform write operations. Checking Hybrid Read-Only Mode Status Regardless of how users connect to the PDB, the V$CONTAINER_TOPOLOGY view can be used to check whether the PDB is in HYBRID READ ONLY mode: SQL> select IS_HYBRID_READ_ONLY, CON_NAME, OPEN_MODE from V$CONTAINER_TOPOLOGY; IS_HYBRID_READ_ONLY CON_NAME OPEN_MODE -------------------- ---------- ---------- YES ORCLORPDB READ WRITE

Apr 3, 2025 - 01:40

Oracle 23ai - Hybrid Read-Only Mode for Pluggable Databases

In Oracle 23ai, a new open mode called Hybrid Read-Only has been introduced for pluggable databases (PDBs). When a PDB is set to this mode, local users can only connect with read-only access. However, common users are not restricted and can still perform write operations.

SQL> ALTER PLUGGABLE DATABASE ORCLORPDB OPEN HYBRID READ ONLY;
Pluggable database altered.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLORPDB                      READ WRITE NO

Testing Write Access as SYS User
Now, we connect to ORCLORPDB as the SYS user and test write operations:

sqlplus "sys/sys@target:1522/ORCLORPDB as sysdba"

SQL> show user;
USER is "SYS"

SQL> select OPEN_MODE from v$pdbs;
OPEN_MODE
----------
READ WRITE

SQL> create table vahid.tbl1(id number);
Table created.

As seen above, the SYS user sees the OPEN_MODE as READ WRITE, even though the PDB was opened in HYBRID READ ONLY mode.

Testing Write Access as Local User
Now, let’s repeat the test with a local user named VAHID:

sqlplus "vahid/reeyuuy@target:1522/ORCLORPDB"

SQL> show user;
USER is "VAHID"

SQL> select OPEN_MODE from v$pdbs;
OPEN_MODE
----------
READ ONLY

SQL> create table vahid.tbl2(id number);
ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

As expected, the local user (VAHID) is not allowed to perform write operations.

Checking Hybrid Read-Only Mode Status
Regardless of how users connect to the PDB, the V$CONTAINER_TOPOLOGY view can be used to check whether the PDB is in HYBRID READ ONLY mode:

SQL> select IS_HYBRID_READ_ONLY, CON_NAME, OPEN_MODE from V$CONTAINER_TOPOLOGY;

IS_HYBRID_READ_ONLY  CON_NAME   OPEN_MODE
-------------------- ---------- ----------
YES                  ORCLORPDB  READ WRITE