How Oracle Enable Restricted Mode

Enable Restricted Mode

Restricted mode is a mode that only allow users who have RESTRICTED SESSION or SYSDBA system privilege to connect to the database. When the mode is enabled, the database notifies the listener to block any other users from accessing it.

Sometimes, you may need to enable the restricted mode for doing some maintenances. For example, changing DB character set or changing the name of a PDB.

For an idle database, we can start it up with RESTRICT option.

SQL> startup restrict

For an open database, you can follow the steps below to enable restricted mode.

1. Login as SYS

You can login as SYS either by providing its credential in at client side,

C:\Users\edchen>sqlplus sys@orcl as sysdba
...
Enter password:
...

or by OS authentication at server side.

[oracle@test ~]$ sqlplus / as sysdba
...

Then we should check the LOGINS status.

SQL> select logins from v$instance;

LOGINS
----------
ALLOWED

Currently, users are allowed to access.

2. Enable RESTRICTED SESSION

With the following statement, we can limit users to access the database.

SQL> alter system enable restricted session;

System altered.

Let's check current status.

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

Now users are limited to access. To connect a restricted database, users need a special system privilege.

To revert the RESTRICTED status, we can disable restricted mode.