Script to check Locks & Inactive Sessions
sql>select s.blocking_session, s.sid, s.serial#, s.STATUS,s.seconds_in_wait from v$session s where blocking_session IS NOT NULL;
Blocking session locks
****************
set lines 200
set pages 200
SELECT inst_id,DECODE(request,0,'Holder: ','---> Waiter: ')||sid sess,
id1, id2, lmode, request, type,ctime/60 mts FROM gV$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request;
locks on objects
*****************
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name,PROCESS,SESSION_ID fROM
V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID and
B.Object_Name='MTL_TXN_REQUEST_LINES';
Inactive Users
*********
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status'INACTIVE';
Inactive sessions
*****************
SET HEADING ON
SET LINE 300
SET PAGES 9999
COL program FOR a23 TRUNC
COL process FOR a7
COL pid FOR a7
COL status FOR a8
COL login FOR a15
COL action FOR a25
COL module FOR a25
SELECT p.program, p.process AS PID, p.sid,p.serial#,
p.status, p.client_identifier AS LOGIN,p.action,
p.module, p.logon_time FROM (SELECT DISTINCT b.sid, b.serial#, b.status,
b.program, b.process, b.client_identifier, b.action, b.module, TO_CHAR (b.logon_time, 'dd-MON-yyyy hh24:mi:ss')
logon_time,
TRUNC (SYSDATE - b.logon_time) "Dy",
TRUNC (MOD ((SYSDATE - b.logon_time) * 24, 24)) "Hr",
TRUNC (MOD ((SYSDATE - b.logon_time) * 24 * 60, 60)) "Mi",
TRUNC (MOD ((SYSDATE - b.logon_time) * 24 * 60 * 60, 60)) "Sec"
FROM v$access a, v$session b, v$process c WHERE a.sid = b.sid
AND b.paddr = c.addr
AND b.status = 'INACTIVE'
AND ( b.action LIKE '%FRM%' OR b.action LIKE '%frm%' OR b.program LIKE '%TOAD%' OR b.program LIKE '%toad%' OR b.program LIKE 'SQL%' OR b.program LIKE '%sql%' OR b.program LIKE '%FRM%' OR b.program LIKE '%frm%' OR b.action LIKE 'SQL%' OR b.action LIKE 'sql%' OR b.action LIKE 'TOAD%'
OR b.action LIKE 'toad%')
AND ( TRUNC (MOD ((SYSDATE - b.logon_time) * 24, 24)) >= 12
OR TRUNC (SYSDATE - b.logon_time) >= 1)) p
ORDER BY p.logon_time;