Would be obliged if someone can help me with the following required script.
I need a script please which kills any session recieved from a certain user (i.e userA) which stays on the DB for more then 5 minutes. if a query has been executing for more then 5 minutes it should be killed by the script.
Any help would be highly appreciated my friends :)
Pl post exact OS and database versions. Read the documentation on using DBA_PROFILES - no script is necessary
Accept my apology for missing the basics :(
OS : SunOS gigagram1 5.10 Generic_142900-11 sun4u sparc SUNW,SPARC-Enterprise
DB : SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 2 19:04:27 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
In the meantime i am going through your provided documents also.
No apology is needed :-) Versions are requested because options/choices vary by version - and a solution not appropriate or available for your specific version would not be of much help.
Pl see these links for 10.2.0.4
I would like to kill the query please which is run by the said user.
Then why not simply deny that "bad" user access to the database for running "bad" queries? ;-)
Killing long running queries is treating thye symptom. Not the problem. The user is firing off that query to get some kind of answer to a question. You killing that query does not answer that question. Which means the user is likely simply going to fire it off again.
It could be that the SQL query the user has put together is less than optimal in finding the answer. It could be that the question is ambiguous and not well framed ito the data model and data available. It could be that the SQL is optimally designed, but the data volumes are simply very large. Etc and etc.
So rather ask these questions to determine WHAT and WHY the user is executing long running queries and address these. Killing that user's sessions is not any kind of solution. Automating the killing of such sessions are even less of an answer and is highly questionable as just wtf is going on, on that database?