REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_user_ses_trace.sql
REM
REM <SQLDIR_GRP>TRACE USER</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports Various Session Information
REM	usefull for runaway process tracing.
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_user_ses_trace.sql
REM 
REM Example:
REM	s_user_ses_trace.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set lines 132

SELECT s.sid, s.status, q.sql_text    
FROM v$session s, v$sqltext q    
WHERE s.sql_hash_value = q.hash_value    
AND s.sql_address = q.address    
AND s.sid = &&SID
order by q.piece;


SELECT lockwait    
FROM v$session    
WHERE sid = &&sid;


SELECT l.*    
FROM v$session s, v$lock l    
WHERE s.lockwait = l.kaddr    
AND s.sid = &&sid;

SELECT s.sid, s.value, t.name    
FROM v$sesstat s, v$statname t    
WHERE s.statistic# = t.statistic#    
AND s.sid = &&sid;

SELECT *  
FROM v$session_wait  
where sid = &&sid;

select * from v$db_object_cache where sharable_mem > 1000000;

select name, sharable_mem
     from v$db_object_cache
    where sharable_mem > 60000
     and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
          or type = 'PROCEDURE')
     and kept = 'NO';


 select distinct
    decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',
                11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY','OTHER')
     ||' - '||kglnaown||'.'||kglnaobj "Eligible PL/SQL objects"
    from x$kglob
    where kglobhd4 in
      (select  ksmchpar from x$ksmsp 
        where ksmchcom='PL/SQL MPCODE' and ksmchsiz>5120);