In order to kill their own sessions, they need to be able to positively identify their own sessions...and that may be problematic. 42 APP_DEV sessions coming from APP_SERVER...which one is the one they want to kill?
I would suggest a two phase approach.Phase 1 - Provide tools/training for the developer to be able to positively identify their own session. They can then use the appropriate information (sid, serial#) as input for a dba to validate the session and terminate it. This might also provide an opportunity for the dba to determine the reason (bad sql, bad sql coder, application issues, database issue) and make corrections.
Phase 2 - Assuming that Phase 1 is successful and the developers have shown the ability and responsibility, the process could be codified and appropriate privileges granted.
Regards, Daniel Fink Bobak, Mark wrote:
Dick,In the scenario you describe, it sounds like the developer had privilege to kill any session, not just his own. When this thread started, the question was if there was a way to allow developers to kill their own sessions. The two are completely different. I'd never buy in to a non-DBA being able to kill arbitrary sessions, at least not without very special circumstances and lots of training. Now, if the question is the ability for him to kill his own sessions, on non-production databases, I may buy into that......I do acknowledge that your CPU limit profile may be a cleaner and easier to maintain solution....depending on the circumstances....-Mark*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Goulet, Richard*Sent:* Monday, August 17, 2009 3:45 PM *To:* mschmitt@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx *Subject:* RE: killing oracle processesThe biggest reason that I can drop on you and yes this did in fact happen in prod, was a developer who believed that a user was blocking his activity & he killed their session. Now I think that it being the CFO running some financial report near to closing time did a lot to bolster my contention that they should not have that privilege, because that afternoon we were revoking it.*/Dick Goulet/* Senior Oracle DBA PAREXEL International------------------------------------------------------------------------*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Michael Schmitt*Sent:* Monday, August 17, 2009 1:49 PM *To:* 'oracle-l@xxxxxxxxxxxxx' *Subject:* RE: killing oracle processes Hi Rich,Thanks for the suggestion. We are actually on Oracle 10g. I am not sure if resource limits will work for us in this case since it is typically scheduled batch jobs or ad-hoc reports that need to be killed. These all tend to run as a common user, and will sometimes be expected to run for long periods of time. Some have suggested in separate emails that there is really no issue in development environments, but this request includes production. I am trying to think of a technical reason why it should not be done (beyond knowing what is being done in the environment we support).Thanks*From:* Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx] *Sent:* Monday, August 17, 2009 12:39 PM *To:* Michael Schmitt; oracle-l@xxxxxxxxxxxxx *Subject:* RE: killing oracle processesMichael,I'm going to assume that your atleast on Oracle 9i. If so have you tried enabling resource limits in their profiles? I normally create a developer profile that kills a statement after 30 minutes of CPU time. It always seems to work.*/Dick Goulet/* Senior Oracle DBA PAREXEL International------------------------------------------------------------------------*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Michael Schmitt*Sent:* Monday, August 17, 2009 11:34 AM *To:* 'oracle-l@xxxxxxxxxxxxx' *Subject:* killing oracle processesHi All,I had a quick question that I was hoping the list could help me out with. We have a group of developers who are requesting the ability to kill their own processes in the database (PRD/DEV/TST). For example, if a poorly written report gets kicked off, one of their jobs chooses a poor execution plan, or an OWB process gets left out there. The only reason they can really offer is that they do not have to wait for the DBA team to respond. I am trying to think of technical reasons why this would not work.I can write a script to limit the process to be killed to their stuff, but something about this still makes me feel uneasy. Is there anything that I should worry about? Any thoughts?Thanks
