rem $TOOLS/locks.sql
rem
rem Displays locking information for the current ORACLE_SID database
rem
rem Last Change 12/04/96 by Brian Lomasky
rem
set termout off
set heading off
col dbname1 new_value dbname noprint
select name dbname1 from v$database;
set termout on
set heading on
set pagesize 9999
column osuser format a14 heading "-----O/S------|Username   Pid"
column username format a17 heading "-----ORACLE-----|Username  ID  Ser"
column locktype format a10 heading "Type"
column held format a9 heading "Lock Held"
column object_name format a15 heading "Object Name" wrap
column request format a9 heading "  Lock|Requested"
column id1 format 999999
column id2 format 9999
spool locks.lis
ttitle center 'Lock report for the ' &&dbname ' database' skip 2
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
	rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
	decode(l.type, 
		'MR', 'Media Reco', 
		'RT', 'Redo Thred',
		'UN', 'User Name',
		'TX', 'Trans',
		'TM', 'DML',
		'UL', 'PL/SQL Usr',
		'DX', 'Dist. Tran',
		'CF', 'Cntrl File',
		'IS', 'Inst State',
		'FS', 'File Set',
		'IR', 'Inst Reco',
		'ST', 'Disk Space',
		'TS', 'Temp Seg',
		'IV', 'Cache Inv',
		'LS', 'Log Switch',
		'RW', 'Row Wait',
		'SQ', 'Seq Number',
		'TE', 'Extend Tbl',
		'TT', 'Temp Table',
		l.type) locktype,
	' ' object_name,
	decode(lmode,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',' ') held,
	decode(request,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',' ') request
	from v$lock l, v$session s, v$process p
	where s.sid = l.sid and
		s.username <> ' ' and
		s.paddr = p.addr and
		l.type <> 'TM' and
		(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
	rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
	decode(l.type, 
		'MR', 'Media Reco', 
		'RT', 'Redo Thred',
		'UN', 'User Name',
		'TX', 'Trans',
		'TM', 'DML',
		'UL', 'PL/SQL Usr',
		'DX', 'Dist. Tran',
		'CF', 'Cntrl File',
		'IS', 'Inst State',
		'FS', 'File Set',
		'IR', 'Inst Reco',
		'ST', 'Disk Space',
		'TS', 'Temp Seg',
		'IV', 'Cache Inv',
		'LS', 'Log Switch',
		'RW', 'Row Wait',
		'SQ', 'Seq Number',
		'TE', 'Extend Tbl',
		'TT', 'Temp Table',
		l.type) locktype,
	object_name,
	decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',NULL) held,
	decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',NULL) request
	from v$lock l, v$session s, v$process p, sys.dba_objects o
	where s.sid = l.sid and
		o.object_id = l.id1 and
		l.type = 'TM' and
		s.username <> ' ' and
		s.paddr = p.addr
union
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
	rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
	decode(l.type, 
		'MR', 'Media Reco', 
		'RT', 'Redo Thred',
		'UN', 'User Name',
		'TX', 'Trans',
		'TM', 'DML',
		'UL', 'PL/SQL Usr',
		'DX', 'Dist. Tran',
		'CF', 'Cntrl File',
		'IS', 'Inst State',
		'FS', 'File Set',
		'IR', 'Inst Reco',
		'ST', 'Disk Space',
		'TS', 'Temp Seg',
		'IV', 'Cache Inv',
		'LS', 'Log Switch',
		'RW', 'Row Wait',
		'SQ', 'Seq Number',
		'TE', 'Extend Tbl',
		'TT', 'Temp Table',
		l.type) locktype,
	'(Rollback='||rtrim(r.name)||')' object_name,
	decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',NULL) held,
	decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
		5,'Sh Row Ex',6,'Exclusive',NULL) request
	from v$lock l, v$session s, v$process p, v$rollname r
	where s.sid = l.sid and
		l.type = 'TX' and
		l.lmode = 6 and
		trunc(l.id1/65536) = r.usn and
		s.username <> ' ' and
		s.paddr = p.addr
	order by 5, 6;
spool off
exit