Remotely and quickly kill a session !, Remotely kill a session!
Today, A developer called A (not to mention the information) asked in the group:
The table is stuck when a large amount of data is inserted. I found that the table is locked and the session has been killed, but it cannot be unlocked. The status is killed. The table cannot be released and deleted, no. The system prompts that the resource is busy.
At that time, I just said rashly:
Kill-9 spid directly in the OS
Later, this buddy sent me How to get it:
I asked him to check: select spid, username, terminal, program from v $ process where addr in (select paddr from v $ session where username = XXX );
A gave:
Select object_name, machine, s. sid, s. serial #
From v $ locked_object l, dba_objects o, v $ session s
Where l. object_id = o. object_id and l. session_id = s. sid;
He also asked me:
Then let him execute in OS:
Kill-9 spid; (LINUX)
Orakill instance thread (Windows)
At this time, he said that he is a remote database and cannot log on to the server host through ssh. This is a headache!
I asked him:
|
Alter system kill session '123 '; |
Alter system kill session '123 '; |
Alter system kill session '123 '; |
The problem has not been solved yet:
At this time, A said that it has been resolved:
Alter system kill session '20140901' immediate; a force immediate is added.
Generally, when a session is killed, directly alter system kill session 'sid, serial #'
When the session is active, it identifies the session as killed or pseudo and does not release the resources held by the session, the alter system statement does not implicitly commit the current transaction .)
On the official website: (http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#SQLRF00902)
If the session is refreshing 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. thePMON 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.
I learned how to remotely and quickly kill a session. I used to rely on OS commands. Today I took a lesson! Also, thank you!