| In various critical situations Oracle DBA has to decide | | | | abnormally with murdering background processes. |
| to kill existing session or kill the background process of | | | | In above critical situation we need to kill or terminate or |
| Oracle. At this moment remote Oracle DBA should | | | | murder some session or process using command line |
| need to terminate session/process called as murder of | | | | or GUI utility like OEM, TOAD etc. But command line is |
| session. | | | | more easy and helpful to solve such critical situation in |
| Which situation kill session or process needed: | | | | remote DBA support services. |
| Then question is raised that in under which type of | | | | How to kill session using command line of Oracle: |
| situation remote dba should need to kill session and | | | | Using SQL*Plus (kill session with alter system |
| process. We are explaining those situations and | | | | command): It is very simple way to kill session using |
| incidences of database.Some critical situation Oracle | | | | SQL command. Just check serial no and sid (system |
| DBA should need to take decision to ternminate | | | | identifier) from v$session view as follows and use |
| session. | | | | "alter system kill session" with sid and serial# |
| Kill session needed under following typical and critical | | | | command. SQL>select sid,serial# from v$session |
| circumstances. | | | | where machine='GPTWORKGROUP'; |
| 1. When blocking lock occurs and other sessions are | | | | SID SERIAL# |
| waiting to acquire a lock on same object. But blocker | | | | 9 171 |
| session doesn't end the transaction. At this moment | | | | 1 row selected |
| Remote oracle dba should need to identify blocking | | | | SQL> alter system kill session '9,171'; |
| session and terminate it for clearing resources for | | | | System altered. |
| other sessions. | | | | OR |
| 2. When maximum connections reached error occurs. | | | | SQL>alter system kill session '9,171' immediate; |
| No room available for any new connection or session. | | | | System altered. |
| At this time Remote Oracle DBA should need to kill | | | | And terminated user session will get message in |
| some of idle processes from Oracle database. | | | | sqlplus that "your session has been killed". We can get |
| 3. When database found in hang status and shutdown | | | | output of status as "killed" from v$session for |
| abort command also doesn't work, at same time | | | | terminated session. |
| Remote Dba should need to terminate instance | | | | |