Pages

Saturday, March 4, 2017

Oracle Restricted Session

Sometimes we need to work on a database without any other users being logged in. In this scenario, it is possible to restrict the database session. When the database is altered to restricted mode, only users with restricted session privileges can get access to the database. Technically it is in open mode.

If database is running, alter system command is used to enable and disable restricted session as follows. We can see, select logins from v$instance will verify this:






If database is shutdown state, we can startup database in restrict mode, if necessary. Later we can fire alter system command to disable restrict mode:


Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the “alter system kill session” command.

SQL>alter system kill session 'session-id, session-serial';

The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).




No comments:

Post a Comment