Open Database in Restricted Mode

Restricted mode will only allow users with RESTRICTED SESSION privileges to access the database (typically DBA’s).

We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict;

After an instance is started, we can use alter system enable/disable restricted session to enable or disable restricted mode.

-- Put the database in restricted mode.

SQL> alter system enable restricted session;

-- Take the database out of restricted mode.

SQL> alter system disable restricted session;

We can check if the database is in restricted mode by the logins column from v$instance.

SQL> select logins from v$instance;

Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the “alter system kill session” command.

SQL> alter system kill session 'session-id, session-serial';

The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).