Startup Restrict Mode

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict 

ORACLE instance started.

Total System Global Area 504366872 bytes

Fixed Size 743192 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 307200 bytes

Database mounted.

Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;

system altered

SQL> alter system disable restricted session;

system altered

NOTE:   Find and disconnect users connected during restricted session. Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected

To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;

USERNAME LOGON_TIM PROCESS

----------------- ------------ -------------------

SYS      17-NOV-10       1310796

              17-NOV-10      1343899

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode, If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;

LOGINS

----------

RESTRICTED