Month: January 2016

Monitoring Query for User, Session and Object

andreasvictorLeave a comment 
--Query for User Monitoring, with information Employee name, User name, Module Accessed
select 
vs.logon_time,
vs.module,
vs.sid,vs.serial#,
vs.client_identifier,
(
select last_name from per_people_f
where person_id in (
    select employee_id from fnd_user 
    where user_name = vs.client_identifier
    )
    and rownum=1
) employee_last_name,
vs.PLSQL_ENTRY_OBJECT_ID,vs.ROW_WAIT_BLOCK#,
vs.BLOCKING_SESSION_STATUS,vs.STATE,
vs.time_remaining_micro,
vs.sql_exec_start,
vs.*
 from v$session vs
where 1=1
and module like '%WMS%' --FNDSCSGN%'
order by vs.logon_time desc
;

Based on that query, we can kill session with the script below :
–ALTER SYSTEM KILL SESSION ‘sid,serial#’;
ALTER SYSTEM KILL SESSION ‘7518,25661’ IMMEDIATE;

And for more information, there are some script to do object monitoring:

--Query for Object Monitoring
select * from dba_objects
where status = 'INVALID';

--Query for Check Employee Id
select employee_id from fnd_user 
where user_name = :p_user_name;

--Query for Check Employee Name based on User Name
select last_name from per_people_f
where person_id in (
    select employee_id from fnd_user 
    where user_name = :p_user_name
);

--Query for Monitoring Session I/O and its user
select 
vs.sid,
vs.serial#,
vs.*
 from v$sess_io vi,v$session vs
where 1=1
and vi.sid = vs.sid
and vs.module like '%WMS%';

--Query for Locked Object Monitoring
select 
ao.object_name,
vo.* 
from V$LOCKED_OBJECT vo,
dba_objects ao
where vo.object_id = ao.object_id
;

Reference : PSOUG.ORG – View Locked Object in Oracle

Advertisement