Several application’s maintenance tasks in an database require keeping users out of it. For instance, performing a parallel recompilation of objects after a code update. In these cases one of the most useful tools is to use dynamic services, which allow us to disconnect clients in a controlled, convenient and centralized manner. However, even today (!), you can still find clients connected to the database outside of dynamic services… even through the SID (!).

A few years ago, a simple remedy was to shut down the database listener, but this solution, while straightforward, had big obvious drawbacks; most of them exacerbated when working with the grid infrastructure listener, as this listener can be serving docens or hundreds of additional PDBs. And this is where the option to put the database in restricted mode comes into play. It’s a complementary mode to the “read/write” and “read-only” database modes and it simply limits access – new connections – to the database for those users who have the “restricted session” privilege. Essentially, this includes DBAs by nature, but can include any user with that privilege.

Up to this point, the combination of using dynamic services and the ability to put the PDB in restricted mode – along with read/write or read-only modes -, and far to be perfect, isn’t a bad compromise.

---

Since Oracle 23c release, the new “Hybrid Read-Only” mode for PDBs is introduced. How does it differ from what we had before?

• “Hybrid Read-Only” mode is not complementing the “read/write” or “read-only” modes. Instead, it defines the PDB’s open mode (read/write or read-only) based on the connecting user. So like the Schrödinger cat, it is both in “read-only” and “read-write” modes at the same time. Its opening mode varies depending on who queries/experiences it.
• “Hybrid Read-Only” mode is designed for a multitenant architecture, where we can find local and common users. Local users will see – even if they query v$sql – the PDB in read-only mode and will be restricted by a read-only database in all respects. Common users will see the PDB and experience it as a read/write database.

What would be the main use case then?

This new mode only makes sense in a multitenant architecture. Not only because we can have common users, but also because it allows having a PDB Admin for each of the PDBs. With multitenant Oracle offers the option for a company to have an administration role job for the CDBs and other independent administrators for each PDB. In this scenario, restricted mode falls short, as a PDB Admin could connect to the database under that restricted mode.

With the new “Hybrid Read-Only” mode, we can block DDL, DML and DCLs even for PDB administrators, and we can still perform necessary maintenance tasks using common users. By the way, the CDB SYS/SYSTEM users are common users, even though they don’t have the “C##” prefix.

---

In this brief post I wanted to provide a useful insight into the technology, rather than showing you a practical exercise where you would simply see, for one same PDB, a local user working in read-only mode and a common user in read-write mode. They are great detail exercises out there; the implementation is very simple if you now decide this feature fits your needs.

Picture credit to Robert Lea