1 2 Previous Next 23 Replies Latest reply on Sep 9, 2011 2:52 AM by Hemant K Chitale

    How to disconnect a user who continues to connect

    711405
      Hi all,

      I am seeing a quite strange issue here.

      The schema owner '****' is connected to the database. Please disconnect and try again.

      I tried to disconnect the user with the following command
      select sid,serial# from v$session where username = '****';
      alter system kill session '131,6' immediate;
      I then see the the commands got successfully executed, and for a little while, there is not such user connecting to my database as I can do another select and no row is selected. But within some short amount of time, I see a new connection. In this case, how can I terminate the connection once and for all? Please help me and many thanks!
        • 1. Re: How to disconnect a user who continues to connect
          sb92075
          user3789189 wrote:
          Hi all,

          I am seeing a quite strange issue here.

          The schema owner '****' is connected to the database. Please disconnect and try again.

          I tried to disconnect the user with the following command
          select sid,serial# from v$session where username = '****';
          alter system kill session '131,6' immediate;
          I then see the the commands got successfully executed, and for a little while, there is not such user connecting to my database as I can do another select and no row is selected. But within some short amount of time, I see a new connection. In this case, how can I terminate the connection once and for all? Please help me and many thanks!
          You report mystery, but provide no useful details to reproduce.

          do as below so we can know complete Oracle version & OS name.

          Post via COPY & PASTE complete results of
          SELECT * from v$version;


          Handle:      user3789189
          Status Level:      Newbie
          Registered:      Jul 10, 2009
          Total Posts:      73
          Total Questions:      27 (25 unresolved)

          so many questions & so few answers.
          How SAD!

          Edited by: sb92075 on Sep 6, 2011 7:04 PM
          • 2. Re: How to disconnect a user who continues to connect
            JustinCave
            If you want to prevent a particular user from logging in to the database, you can revoke the user's CREATE SESSION privilege, i.e.
            REVOKE CREATE SESSION 
              FROM application_owner
            assuming the CREATE SESSION privilege is granted directly rather than via a role. On the other hand, it's not obvious to me exactly what problem you're trying to solve so I'm not certain that you really want to revoke privileges.

            Justin
            • 3. Re: How to disconnect a user who continues to connect
              Hemant K Chitale
              "The schema owner '****' is connected to the database. Please disconnect and try again."
              Is this an Oracle Error Message (what error number) ? Or is it a message from a tool / application that you are using ?
              What commands / actions are you attempting that result in this message ?

              Hemant K Chitale
              • 4. Re: How to disconnect a user who continues to connect
                711405
                Hi,

                Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                PL/SQL Release 11.1.0.7.0 - Production
                CORE 11.1.0.7.0 Production
                TNS for Linux: Version 11.1.0.7.0 - Production
                NLSRTL Version 11.1.0.7.0 - Production

                Thanks for your sympathy. I feel sad too with those unanswered questions. :)
                • 5. Re: How to disconnect a user who continues to connect
                  711405
                  I am actually using RCU to drop that schema before I can re-create that schema. But clearly dropping schema would fail if it is still being used, i.e. still connecting to the database.

                  Therefore, I tried to disconnect the users and hoped to be able to drop the schema. But in a short while, the schema is connected again. I am asking how to disconnect the user once and for all, and then I am able to drop the schema.

                  Hopefully this time my intention and question is more clear.
                  • 6. Re: How to disconnect a user who continues to connect
                    711405
                    This is the error message from RCU when it tried to drop the schema.
                    • 7. Re: How to disconnect a user who continues to connect
                      sb92075
                      user3789189 wrote:
                      Hi,

                      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                      PL/SQL Release 11.1.0.7.0 - Production
                      CORE 11.1.0.7.0 Production
                      TNS for Linux: Version 11.1.0.7.0 - Production
                      NLSRTL Version 11.1.0.7.0 - Production

                      Thanks for your sympathy. I feel sad too with those unanswered questions. :)
                      bcm@bcm-laptop:~$ sqlplus
                      
                      SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 6 21:02:20 2011
                      
                      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                      
                      Enter user-name: user1/user1
                      
                      Connected to:
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      
                      SQL> !ps -ef | grep LOCAL
                      oracle   24677 24676  0 21:02 ?        00:00:00 oraclev112 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
                      bcm      24678 24676  0 21:02 pts/0    00:00:00 /bin/bash -c ps -ef | grep LOCAL
                      bcm      24680 24678  0 21:02 pts/0    00:00:00 grep LOCAL
                      I've not had good success with KILL SESSION from inside the DB.
                      I've had better success at OS level
                      kill -9 24667

                      Is your session LOCAL or not?
                      • 8. Re: How to disconnect a user who continues to connect
                        585179
                        user3789189 wrote:
                        I am actually using RCU to drop that schema before I can re-create that schema. But clearly dropping schema would fail if it is still being used, i.e. still connecting to the database.

                        Therefore, I tried to disconnect the users and hoped to be able to drop the schema. But in a short while, the schema is connected again. I am asking how to disconnect the user once and for all, and then I am able to drop the schema.

                        Hopefully this time my intention and question is more clear.
                        You can try this
                        - revoke create session privilege or lock the schema
                        - disconnect / kill current connection
                        - drop the schema


                        Cheers
                        • 9. Re: How to disconnect a user who continues to connect
                          711405
                          Thanks! My session is actually remote (not sure if this is the correct word to use), i.e. if I am do the query I got the following returned record, where my_remote_machine is different current machine's hostname.

                          USERNAME SID SERIAL# MACHINE
                          myuser 139 19561 my_remote_machine

                          In this case, how can I kill the process? I guess killing the result from "ps -ef | grep LOCAL" would not work, correct?
                          • 10. Re: How to disconnect a user who continues to connect
                            JustinCave
                            You would need to be logged in to the database server in order to kill an operating system process.

                            From your earlier description, though, it sounds like you can kill the session without too much difficulty without resorting to killing the operating system process. It sounds like you just want to revoke the CREATE SESSION privilege from my user as I posted in my initial reply.

                            Justin
                            • 11. Re: How to disconnect a user who continues to connect
                              Hemant K Chitale
                              Wouldn't you be able to stop the application server/client/program that connects to the database using this account ?


                              Hemant K Chitale


                              Another option is to change the password (or disable OS Authentication if it is using OS Authentication) for the account before you kill the session. That way, the next connection attempt (after the kill) would fail because the password is invalid.

                              Hemant K Chitale

                              Edited by: Hemant K Chitale on Sep 7, 2011 12:46 PM
                              • 12. Re: How to disconnect a user who continues to connect
                                711405
                                I actually would love to know what application server/client/program running on the remote machine that connects to the database using this account. Is there any method to find that out, for example from v$session ?

                                Your second option is really sub-optimal, sorry to say that. I would think directly revoking the privilege of or locking the user, as others have suggested.
                                • 13. Re: How to disconnect a user who continues to connect
                                  sb92075
                                  Is there any method to find that out, for example from v$session ?
                                  AUDIT CREATE SESSION
                                  • 14. Re: How to disconnect a user who continues to connect
                                    Hemant K Chitale
                                    Your second option is really sub-optimal
                                    Wierd ? You are going to DROP the account and then reCREATE the account again. How does it matter what the current password is ?
                                    Whatever is the remote program that connects may still be attempting to connect again. If you reCREATE the account with the same password and are still in the process of creating all the schema objects in the account ..... that remote program may well reconnect successfully to your incomplete schema.



                                    You have used V$SESSION. Look at the columns in this view. They can provide you some information about the remote program (PROGRAM, MODULE, MACHINE, OSUSER are all columns in V$SESSION).


                                    Hemant K Chitale
                                    1 2 Previous Next