Query sessions and locks

Viewing Memory Use for Each User Session:
=================================

The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use for each session:

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
   FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
   AND stat.STATISTIC# = name.STATISTIC#
   AND name.NAME = 'session uga memory' and username=;


session stats:
-------------------
select a.sid, b.name, a.value
from v$sesstat a, v$statname b
    where a.statistic#=b.statistic#
    and b.name like '%workarea%'
    and a.sid=73;


use this sql to check on the user how created the lock, and the sql statment that created that lock:
=======================================================================

select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial#,c.terminal, d.sql_text
from sys.dba_objects a,
v$locked_object b,
v$session c,
v$sqltext d
where a.object_id = b.object_id
and c.sid = b.session_id
and c.sql_hash_value = d.hash_value;

SELECT s.SID, s.status, s.process, s.osuser, a.sql_text, p.program
  FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value
   AND s.sql_address = a.address
   AND s.paddr = p.addr And s.sid=379;
 

and chenc DBA_DDL_LOCKS

DBA_DML_LOCKS

select b.session_id,a.owner,a.object_type,a.object_name,b.process,b.locked_mode from dba_objects a,v$locked_object b where a.object_id=b.object_id and  locked_mode = 3;


==========lock in objects:==========

SQL> select a.session_id,a.object_id,b.logon_time,a.oracle_username,b.osuser from  v$locked_object a,v$session b where a.session_id=b.sid and  object_id=420600;

SESSION_ID  OBJECT_ID LOGON_TIM ORACLE_USERNAME
---------- ---------- --------- ------------------------------
        83       1348 20-OCT-08 D5735PGM
       113       1348 20-OCT-08 D5735PGM
        90       1348 08-OCT-08 D5735PGM