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

[hemanr]

Hi Gurus,

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

Anyone???

Thanks!

[stecal]

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.

[vinit]

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.

[stecal]

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]

[hemanr]

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!

[stecal]

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.

[hemanr]

Thanks Stecal, for the food for thought...

I will think and get back.

Thanks again!