Re: Question of "Kill Session"

kiber <member34753_at_dbforums.com> wrote in message news:<3217209.1060348706_at_dbforums.com>...
> Hi All,
>
> I looked through docs, but found nothing (((
> I have some third-party application, which spawn many Oracle sessions,
> but didn't close them all after close (((( I need to close these
> orphant sessions.
> I use Oracle 9i on W2k.
>
> This is the quotation from doc about "alter system kill session":
> "Marks a session as dead, rolls back ongoing transactions, releases all
> session locks, and partially recovers session resources.
> If the session is performing some activity that must be completed,
> such as waiting for a reply from a remote database or rolling back a
> transaction, Oracle waits for this activity to complete, marks the
> session as dead, and then returns control to you. If the waiting
> lasts a minute, Oracle marks the session to be killed and returns
> control to you with a message that the session is marked to be
> killed. The PMON background process then marks the session as dead
> when the activity is complete.
> Whether or not the session has an ongoing transaction, Oracle does not
> recover the entire session state until the session user issues a
> request to the session and receives a message that the session has
> been killed."
>
> Does it mean that if I have some long running query, which run more than
> a minute after DBA typed "alter system kill session", session would be
> marked as KILLED and If I forgot to Commit/Rollback or I couldn't (for
> instance, because session was created by third party application) after
> this long-running query, than my session would be waste some server
> resources for ever? I tried to find some parameters regarding timeouts
> for such situation, but it was fruitless ((( Does it help to use
> profiles with idle_time limit?
>
> "IMMEDIATE - rolls back ongoing transactions, releases all session
> locks, recovers the entire session state, and returns control to you
> immediately."
>
> I tried to issue "alter system kill session immediate" - but session was
> marked as killed - it means that my session wastes some server resources
> again as I mention above!!!

Yes, it does mean that. You may need to use your OS to kill the session directly to let PMON clean it up. The manual is somewhat misleading, but it does return control to you immediately. All the other stuff may take forever. At least on unix, although I suspect this is an Oracle thing across platforms, where Oracle simply cannot know what the OS is not telling it about tightly looping processes. Whether the server resources wasted are significant is another matter - it was worse in older versions. I think Oracle _should_ believe alter session over what it's sessions say (or don't say, which is the problem), as documented.

jg

--
@home.com is bogus.
http://www.computerworld.com/securitytopics/security/hacking/story/0,10801,83854,00.html