4 Things You Need To Know About Database Restricted Mode

For certain database operations, you need to place your database in restricted mode.
There are two ways to proceed to accomplish this task, and I will describe these in detail, in today’s article.

1. What Is Restricted Mode
2. Starting Up Database In Restricted Mode
3. Placing Open Database In Restricted Mode
4. Script To Kill Existing Sessions

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.

Why would you put a database in restricted session?
During certain maintenance task, you might want to restrict access to the database.
You want to be able to connect to the database to perform your tasks, but you do not want any other users mocking around, changing the data.
Such maintenance tasks could be upgrading a database with an export/import process, fixing replicated data, deleting specific data,
reorganizing tables, taking a consistent export. In these cases the restricted session is not a must, you could still proceed with
the database open read write, but you might encounter some problems down the road.

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.

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

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

The good thing about this method is, that all users connected to the database will get disconnected when you shutdown the database. These users will not be able to connect unless they have the RESTRICTED SESSION privilege.

3. Placing Open Database In Restricted Mode

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.

SQL> 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.
This script can be used any other time to generate the kill commands.

--check what sessions there are in the database

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.sql_id
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;


INST_ID   SID  SERIAL# SPID    USERNAME  PROGRAM            SQL_ID
------- ----- -------- ------- --------- ------------------ -------------
      1     4    33271 31370   HRAPP     JDBC Thin Client 
      1     5      369 25228   HRAPP     JDBC Thin Client   318fujwu02mz1
      1    43       59 25232   SYSADM    JDBC Thin Client   ar3mfzt410h9y
      1    46      415 25513   HRQUERY   JDBC Thin Client 

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

select 'alter system kill session ' || '''' || s.sid|| ',' || s.serial# || ',@' || s.inst_id || ''';' as "Kill_Session_Command"
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND    s.username not in ('SYS','SYSTEM')
ORDER BY s.inst_id,s.sid,s.serial#;

Kill_Session_Command
----------------------------------------
alter system kill session '4,33271,@1';
alter system kill session '5,369,@1';
alter system kill session '43,59,@1';
alter system kill session '46,415,@1';

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana