KILLING USERS SESSIONS IN ORACLE Author JP Vijaykumar Oracle DBA Date Apr 14th 2010 A detailed discussion on redo, undo and Oracle's read consistency are beyond the scope of this document. A detailed discussion on setup, troubleshooting of user connections in MTS environment is beyond the scope of this document. Before embarking on a killing spree of Oracle sessions, let us explore the options, limitations, dos and don'ts. ------------------PART-I-- I want to find, how much time, it takes to insert 10 million records into a table, to commit or to rollback the operation. create table temp_jp(col1 number) tablespace users; set timing on select count(distinct(substr(rowid,1,15))) used_blks from temp_jp; USED_BLKS ---------- 0 declare begin for i in 1..10000000 loop insert into temp_jp values(i); end loop; end; / PL/SQL procedure successfully completed. Elapsed: 00:08:13.56 Created a table and inserted 10 million records into the table. The operation took 8 minutes 13.56 seconds. As you noticed, I did not commit intermittently inside my pl/sql procedure. At this point, I had the option to commit or rollback the entire operation of 10 million records inserted into the table. SQL> commit; Commit complete. Elapsed: 00:00:00.00 A commit of the operation took no time. Let us see, how much time, it takes to rollback the operation. Truncated the table temp_jp, re-inserted 10 million records and rolled back the operation. SQL> rollback; Rollback complete. Elapsed: 00:15:26.36 A rollback is a very slow and time consuming operation. From the displayed timing, the rollback operation took almost twice the amount of time, as that of the insert operation. ------------------PART-II- What are my options to kill a session? Killing the required session in the Oracle database: alter system disconnect session '146,794' immediate; alter system disconnect session '290,35013' post_transaction; alter system kill session '290,35013'; alter system kill session '290,35013' immediate; Killing the session at the OS level, on the local unix server: kill -9 #spid from v$process In 3 tier applications, Killing the session on the remote server: kill -9 #process from v$session ------------------PART-III In any event, when you kill a session, Oracle has to rollback the uncommitted transactions. Unless and untill all the uncommitted transactions are rolledback, the resources will not be released and the status of session will be 'KILLED' in the v$session view. Even, if you shutdown the db with an abort option, Oracle rolls back all the uncommitted transactions, during the next startup. ------------------PART-IV- 01 Never kill any session that has null username. It is a background process. If required, query the v$bgprocess view. 02 In MTS environment, kill the required session ONLY in Oracle. If a session is connecting to the database using dedicated server connection, then the session can be killed in oracle using sid or at OS level using the spid. 03 When querying on the basis of the sql statement being executed, remember, the query might be written in upper case or lower case or mixed case. The spacing given in the users executed sql query may varry from the sql query used to probe for session's details. Supply part of the sql query with a like operator, in upper case. If the sql was executed a long time ago, the sql might have aged out of shared pool. 04 In some applications, the business wants the session to be killed at unix level in the local server, where the database is running. Then identify the spid from v$process and kill it. 05 If the session is to be killed in the remote application server then use the process value. Here, it is safe to identify the machine also, where from the session is originating. 05 If every user connects to the database with their unique username, then use the developer's login username to isolate the session. 06 If, all the users connect to the database as the schema owner or as sysdba, then isolating a specific session for the kill is a difficult task. 07 To find the user's terminal info, ask the user to run the command from another session. select sys_context('userenv','host') from dual; select sys_context('userenv','terminal') from dual; 08 To find the user login name to the db, the user can query using show user select username from user_users; select user from dual; 09 To find a user's sid, the user can run any of these queries. SELECT sys_context('USERENV', 'SID') FROM dual; select distinct sid from v$mystat; The above queries only display the user's currently logged in session's sid. 10 Ask the user for details like login username, terminal name, the sql query the the user is executing. In any event, isolate the user's session, using as many pridicates as required. If necessary, check with the user and take the user's confirmation before killing the user's session. After all is said and done, pls be very careful in identifying the user's session. If you kill a wrong session, you invite trouble. ------------------PART-V-- This is a useful sql query, to identifying the offending oracle sessions and kill. This query displays, the sessions details like sid, serial#, username,osuser,spid, process and the executing sql. Select 'alter system kill session '''||sid||','|| s.serial#||''';'|| chr(10)||s.username||' '||Status||' '||osuser||' '|| process||' '|| s.program||' '||s.machine||' '||s.terminal||' '||p.spid||' '||chr(10)||sa.sql_text From v$process p, v$session s,v$sqlarea sa Where s.paddr=p.addr and s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+) --and upper(sa.sql_text) like '%SUPPY THE SQL IN UPPER CASE ONLY%' --and s.username='SUPPLY THE USERNAME' --and s.sid in (SUPPY USER'S SID) --and s.machine = 'SUPPY THE SERVER / MACHINE NAME' --and s.terminal = 'SUPPY THE TERMINAL NAME' --and process = 'SUPPY UNIX PROCESS ID FROM REMOTE SERVER/OR PARENT ID OF YOUR SPID' --and spid in (SUPPY UNIX ID FROM LOCAL UNIX SERVER); The commented lines are optional in the sql query. Modify the sql query, as per your requirement and use as many predicates as possible, from the commented "--" lines to isolate and identify the user's session. ------------------PART-VI- Sometimes, I may need to kill my session, before the job is completed. At the start of my session, I run this sql and save the output, for use, if required. select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session where sid in (select distinct sid from v$mystat); 'ALTERSYSTEMKILLSESSION'||CHR(39)||SID||','||SERIAL#||CHR(39)||';' -------------------------------------------------------------------------------- alter system kill session '281,62081'; Reference: http://www.scribd.com/doc/19275101/mts http://www.dba-oracle.com/concepts/shared_pool_mts.htm http://www.dba-oracle.com/t_mts_multithreaded_servers_shared.htm http://www.praetoriate.com/t_%20tuning_dedicated_connections.htm http://toolkit.rdbms-insight.com/jobs.php http://ss64.com/ora/syntax-redo.html http://www.oracle-base.com/articles/misc/KillingOracleSessions.php http://www.scribd.com/doc/2675003/Oracle-Redo-and-Rollback http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4974573906087 http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA292&lpg=PA292&dq=What+oracle+does+in+a+commit&source=bl&ots=RzVa8t3xZC&sig=B7B8TjlW3MXpKJezyZPN6oyyKbo&hl=en&ei=7A5iS_mYFZHQM7fwxMUC&sa=X&oi=book_result&ct=result&resnum=8&ved=0CBwQ6AEwBzgy#v=onepage&q=What%20oracle%20does%20in%20a%20commit&f=false