Database in restricted mode.

Restrict Mode in which Oracle database allow making connection with special rights such as DBA, SYSDBA to perform the maintenance activity like rebuilding index, remove fragmentation etc. DBA can start database in restricted mode for planned maintenance activity. So, no other user such as application users will be able to connect with database until they have special rights.

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.

SQL> STARTUP RESTRICT;

SQL> > select logins from v$instance;

LOGINS
------
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;
Grant succeeded.

## To revoke access restricted mode access ##

SQL> REVOKE RESTRICTED SESSION FROM scott;
Revoke succeeded.

How to find the users that have restricted session access:

SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

Disable the session restricted mode or you can normal start the database.

SQL> alter system disable restricted session;

Check the status that restricted mode is disable:

SQL> Select logins from v$instance;

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

Without restart the database, we put it in restricted mode:

SQL> alter system enable restricted session;

Script To Kill Existing Sessions

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#;

Generate kill command for killing the session.

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#;