show logon_time username machine and locks for sessions.sql

on June 23, 2008 at 1:24 pm
Posted In: Oracle DB and SQL performance analysis (Cat)

set verify off

break on Username on report
compute count of Username on Username

set linesize 180
set pagesize 9999

SELECT  UNIQUE RPAD(TO_CHAR(A.LOGON_TIME,’DD-MM-YY:HH24.MI’),16) LOGON_TIME,
rpad(A.USERNAME,10)username,rpad(A.OSUSER,6)osuser,rpad(A.MACHINE,20)machine,rpad(A.PROGRAM,25)program,
rpad(C.OBJECT_NAME,30)object,rpad(C.OBJECT_TYPE,10)obtype 
FROM  V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C
WHERE  A.TYPE   = ‘USER’
AND  A.SID   = B.SESSION_ID
AND  B.OBJECT_ID  = C.OBJECT_ID;