Cheeky Cockatoo

create or replace procedure library_lock as
	cursor blockers is
		select distinct sid, serial#, username, osuser, machine
		from
			v$session
		where
			saddr in (
				select kgllkses from x$kgllk lock_a
				where kgllkreq=0 and
				  exists (
					select lock_b.kgllkhdl
					from x$kgllk lock_b
					where kgllkses in (
					select s.saddr
					from v$session_wait w, v$session s
					where w.sid = s.sid and
					w.event like 'library cache %'
					) and
					lock_a.kgllkhdl = lock_b.kgllkhdl and
					kgllkreq > 0
					)
				);

	cursor statements(the_sid in number) is
		select
			a.sql_text
		from
			v$sqlarea a,
			v$open_cursor c
		where
			c.sid = the_sid and
			c.address = a.address
	;
begin
	dbms_output.enable(100*1024);
	for b in blockers loop
		dbms_output.put_line('sid: ' || b.sid);
		dbms_output.put_line('serial#: ' || b.serial#);
		dbms_output.put_line('username: ' || b.username);
		dbms_output.put_line('osuser: ' || b.osuser);
		dbms_output.put_line('machine: ' || b.machine);
		dbms_output.put_line('statements:');
		for s in statements(b.sid) loop
			dbms_output.put_line(s.sql_text);
			dbms_output.put_line('------------');
		end loop;
	end loop;
end;
/