1. For finding out the current running sql for a particular user
SELECT a.sql_text
FROM v$session s,
v$sqlarea a
WHERE s.user = 'USER'
AND s.status ='ACTIVE'
AND s.sql_hash_value=a.hash_value
AND s.sql_address =a.address;
2. To find locked objects in Oracle
3. To see all the seesion for a user
select * from v$session s where s.username = 'PROQ';
4. To see locked objects and the session
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
And then kill the session with this
alter system kill session '94,2168';
5. Tables by user
select owner,count(*) from all_all_tables group by owner;