Oracle Database Restricted Mode

 1. What Is Restricted Mode

When a database is placed in restricted mode, only users that have RESTRICTED SESSION privilege granted to them can connect to the database. Users that lack this privilege will get an error.Users that are already connected to the database, prior to placing the database in restricted session, will not get disconnected.These sessions need to be manually killed after.

2. Starting Up Database In Restricted Mode

You can start up a database in restricted mode.Prior to starting up the database in restricted mode, you need to shutdown the running database first.

shutdown immediate;
startup restrict;
select logins from v$instance;

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

3. Placing Open Database In Restricted Mode

You can stop coming new connections if you do not want to shutdown the database, there is an ALTER SYSTEM command you can use to place the database in restricted mode.

alter system enable restricted session;

The downside to this method is that users already connected to the database will not get disconnected. You need to manually kill these sessions.

4. Script To Kill Existing Sessions

In order to help you kill the sessions that are sticking around after the database has been placed in restricted mode you can use the following script.

-- generate the kill command.

-- if you decide to kill the sessions, then run the generated commands.

SELECT 'ALTER SYSTEM KILL SESSION ' ||SID||','||SERIAL#||' IMMEDIATE;' FROM GV$SESSION WHERE STATUS='INACTIVE' and USERNAME NOT IN ('SYS','SYSTEM');