4 Replies Latest reply: Jan 2, 2013 9:14 AM by Mark Williams-Oracle RSS

    Restricted session & Kill Session

    934234
      Hello everybody,

      1) In which case do I need enabled restricted sessions?

      2)Where “ALTER SYSTEM KILL SESSION” command will be useful?

      Thanks in advance
        • 1. Re: Restricted session & Kill Session
          sb92075
          931231 wrote:
          Hello everybody,

          1) In which case do I need enabled restricted sessions?
          during Oracle DB upgrade

          >
          2)Where “ALTER SYSTEM KILL SESSION” command will be useful?
          never


          Handle:     931231
          Status Level:     Newbie
          Registered:     May 1, 2012
          Total Posts:     6
          Total Questions:     6 (5 unresolved)

          why so many unanswered questions?
          • 2. Re: Restricted session & Kill Session
            Salman Qureshi
            Hi,
            1) In which case do I need enabled restricted sessions?
            Whenever you want to perform some maintenance operations in your database and you don't want anyone to access the database except user SYS, you can enable restricted session.
            2)Where “ALTER SYSTEM KILL SESSION” command will be useful?
            When you want to kill a session which is no longer responding or hung or doing some long running operation which is disturbing your performance or you want to stop that processing etc.

            Salman
            • 3. Re: Restricted session & Kill Session
              Osama_Mustafa
              1) In which case do I need enabled restricted sessions?
              database maintenance operations

              you can check by
              select logins from v$instance;
              2)Where “ALTER SYSTEM KILL SESSION” command will be useful?
              when you want to end user connection with database.
              • 4. Re: Restricted session & Kill Session
                Mark Williams-Oracle
                Salman Qureshi wrote:
                Hi,
                1) In which case do I need enabled restricted sessions?
                Whenever you want to perform some maintenance operations in your database and you don't want anyone to access the database except user SYS, you can enable restricted session.
                2)Where “ALTER SYSTEM KILL SESSION” command will be useful?
                When you want to kill a session which is no longer responding or hung or doing some long running operation which is disturbing your performance or you want to stop that processing etc.

                Salman
                Hi Salman,

                I think you'll find that "restricted session mode" does not limit login ability to only the SYS user as you mention.

                As an example, consider the following.

                Session 1:
                SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 1 22:07:03 2013
                
                Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                
                SQL> connect / as sysdba
                Connected.
                SQL> shutdown immediate;
                Database closed.
                Database dismounted.
                ORACLE instance shut down.
                SQL> startup restrict;
                ORACLE instance started.
                
                Total System Global Area 2137886720 bytes
                Fixed Size                  2256912 bytes
                Variable Size            1258295280 bytes
                Database Buffers          872415232 bytes
                Redo Buffers                4919296 bytes
                Database mounted.
                Database opened.
                SQL>
                Session 2:
                SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 1 22:07:51 2013
                
                Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                
                SQL> connect markwill
                Enter password:
                Connected.
                SQL> select logins from v$instance;
                
                LOGINS
                ----------
                RESTRICTED
                
                1 row selected.
                
                SQL>
                As you can see in Session 2 I am clearly not connecting as SYS user, yet I am capable of connecting to an instance started in restricted mode.

                Rather than limiting to only user SYS it limits login ability to users with the RESTRICTED SESSION System Privilege (granted directly or via role).

                Regards,

                Mark