Specify a schema and kill all sessions of a user (Script)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Specify a schema and kill all sessions of a user (Script)

Hybrid View

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Gurus,

    I need a script where I specify a schema and it kills all sessions associated with a particular user id.

    Anyone???

    Thanks!
    Hemant

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    How would you know if a user is accessing the schema in question? What if the user is mainly accessing a different schema? You want to kill that user's session?

    What do you mean by using a schema? Performing one query against a table in that schema? Continuously running scripts? Being the schema owner and being killed because you are accessing your own schema?

    If you want to kill a session:
    alter system kill session 'sid,serial#';

    You get sid and serial# from v$session, and they go inside single quotes in the alter statement.

  3. #3
    Join Date
    Oct 2000
    Posts
    467
    Originally posted by stecal

    If you want to kill a session:
    alter system kill session 'sid,serial#';
    Assuming, its on Unix. On Windows, its ORAKILL.
    Vinit

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Assuming nothing. That command works in SQL*Plus regardless of the platform. UNIX or Windows, if locks aren't released, there are other issues to get into (where orakill on Windows would apply, ipcs -a on UNIX, and so on).

    [Edited by stecal on 10-14-2002 at 03:42 PM]

  5. #5
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Gurus,

    Sorry for being vague.

    I mean, I pass as arguments a schema name and a userid on that schema to the procedure.

    It kills all sessions associated with that user on the schema.

    Thanks!
    Hemant

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Answer these questions:

    A user logs in to the database and does nothing, just sits there in an idle session. What schema is he or she using?

    A logged in user does a simply query against a table in your schema of interest and the processing time is less than one second. Is that user really "using" the schema, and how would your procedure catch that use of the schema?

    I don't think your questions are vague. I think what you are trying to accomplish isn't realistic or practical, given how Oracle works.

  7. #7
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Thanks Stecal, for the food for thought...

    I will think and get back.

    Thanks again!
    Hemant

Posting Permissions

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


Click Here to Expand Forum to Full Width