Script: To display Locks and give the SID and Serial# of the Session to Kill [ID 1020007.
6] Modified 26-JAN-2012 Type SCRIPT Status PUBLISHED Checked for relevance on 25-August-2010
Abstract This script displays locks and gives the SID and Serial Number of the session to kill.
Product Name, Product Version Oracle Server, 8.1.7 to 11.2.0 Platform Platform Independent Date Created 25-Jul-1996
Instructions Execution Environment: SQL, SQL*Plus Access Privileges: Requires SELECT on V$LOCK, V$SESSION, SYS.USER1, SYS.OBJ$. Usage: sqlplus user/<password> Instructions: Copy the script to a file and execute it from SQL*Plus. PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.
Description The report generated by this script gives information on sessions which are holding locks and gives the information needed to kill using the ALTER SYSTEM KILL SESSION command.
References
Script SET ECHO off REM NAME: TFSLKILL.SQL REM USAGE:"@path/tfslkill" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$ REM ------------------------------------------------------------------------ REM PURPOSE: REM The report generated by this script gives information on sessions REM which are holding locks and gives the information needed to kill REM using the ALTER SYSTEM KILL SESSION command. REM ------------------------------------------------------------------------ REM Main text of script follows:
set linesize 132 pagesize 66 break on Kill on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 [scriptname] - [abstract] https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doc... 1 of 3 20/06/2012 15:49 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a35 heading "Table Name" column owner format a9 column Address format a18 column ctime heading "Seconds" select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID||','||S.SERIAL# Kill, U1.NAME||'.'||substr(T1.NAME,1,20) tab, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request, l.ctime from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5 /
============== Sample Output: ==============
Username Term Kill String Table Name Lock Hld Lock Req Seconds ---------- ------ ------------- --------------------- -------- --------- ------- SCOTT ttyp9 7,5159 SCOTT.TABLE_CONFIG Row Excl SCOTT ttyr5 8,941 SCOTT.TT Row Excl SYS ttyq3 6,8885 SYS.CLUSTERS Exclusiv SCOTT.TABLE_CONFIG Row Excl SYSTEM ttyq4 11,6593 SCOTT.TABLE_CONFIG Row Excl SYSTEM ttyr6 12,253 SYS.SYSINDEXES_ Exclusiv SCOTT.TT Row Excl USUPPORT ttyp1 9,6081 SYS.SYSEXTENTS Exclusiv
Disclaimer EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE, PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE. ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY TIME WITHOUT NOTICE.
Limitation of Liability IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, [scriptname] - [abstract] https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doc... 2 of 3 20/06/2012 15:49 DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE. SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY. ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Keywords DATA_COLLECT; KILL SESSION; LOCK; SCRIPT; SESSION; V$LOCK; V$SESSION Back to top Copyright (c) 2007, 2010, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement [scriptname] - [abstract] https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doc... 3 of 3 20/06/2012 15:49
