Database in restricted mode.
2 min readRestrict 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#;
