How to end a session immediately by alter system kill session in ORACLE

This article mainly shows you "how to end a session immediately in ORACLE". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn "how to end a session immediately in ORACLE".

In general, when you kill a session, execute alter system kill session 'sid,serial#'; directly and then query and find that the session state dropped by KILL changes to KILLED state and does not end immediately. Query the session again later to confirm that it has ended. What if you want to end a session immediately?

After querying ORACLE DOC, I learned that

ORACLE DOC:

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.

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, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

When session is active, alter system kill session only identifies session as killed state and does not release resources held by session, so after we execute alter system kill session, we see that the session still exists, waiting for the PMON process to reclaim resources and release locks, etc.

If:

Do not add IMMEDIATE, just mark the transaction as interrupt, wait for the PMON process to reclaim the resources occupied by the SESSION and release locks, etc.

Add IMMEDIATE, that is, add immediate after alter system kill session 'sid,serial,@sid' to end the session immediately.

The above is all the contents of the article "how to end a session immediately with alter system kill session in ORACLE". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!