Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    36

    Unhappy Unanswered: A Problem Dropping Users

    Hello! How are you? I have a problem very weird! I explain. I have an application made with Java and Oracle among others. One of the things that the application does is create and delete other databases. For example if I want to create a new database called A1(actually, what I create is not exactly a database but it is not important, think that is a new entry in a table ok?) first of all I create a new user called like the database will be A1 in this case. It is all ok the problem comes when I want to delete that “database”. I drop it perfectly but an exception appears when I try to drop the user.
    The code that I have to do that is:

    con=gdc.ConexionAlumno("system","alumno_system");

    ps=con.prepareStatement("drop user "+usuario+" cascade");
    afectados = ps.executeUpdate();



    the exception tells:

    java.sql.SQLException: ORA-01940: no se puede borrar un usuario conectado actualmente( It’s imposible to drop an user who is connected now)

    but look at this; If I want to delete the user A1 I connect to the database whit other user

    con=gdc.ConexionAlumno("system","alumno_system");
    (system is the user and alumno_system the password)

    I have trayed another thing to make sure that the user is not connected, this:

    con=gdc.ConexionAlumno(usuario,usuario);
    con.close();


    where “usuario” is the user that I want to drop. But the same error!

    I need a lot of help with that, summing up I need someone tells me how to make sure that the user is not connect.

    Thanks! Bye!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    select username from v$session

  3. #3
    Join Date
    Jul 2005
    Posts
    36

    very very very thanks!!!!

    ok and If I do that probably it tells me that the user connected is the user I want to drop (because it is the exception) but what I wan to get I that the user connected to disconnect do you understand me???

    Thanks!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess you can not just disconnect another user (unlike yourself, issuing the "DISCONNECT" command). To get rid of him, you will probably have to kill his session.

    To do so, you'll have to have the ALTER SYSTEM privilege granted and use ALTER SYSTEM KILL SESSION (or ALTER USER DISCONNECT SESSION) command.

    Parameters involved are SID and SERIAL# which can be found querying "v$session" (and, perhaps, "v$process")

  5. #5
    Join Date
    Jul 2005
    Posts
    36

    Thanks!

    Ok!i´m Going To Try It And I Will Tell You !!!

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If I understand you ... You want issue a DROP USER command from the source code and it will drop the user that is currently connected... If that's
    the case, you might put in the source code something like....
    behind the scenes, re-logon as a priv. user. Issue the drop user command
    then logoff the user... You can't drop a user account if that user is currently connected to the database.

    HTH
    Gregg

  7. #7
    Join Date
    Jul 2005
    Posts
    36

    Thanks!

    Hello Everybody! I Get Solve My Problem Doing "alter System Kill Sessions! Thank You, It Has Been A Lot Of Helpfull For Me.
    Bye!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •