Click here to check the difference between GV$SESSION and V$SESSION
Get process and session details from request id using V$SESSION
SELECT p.spid,s.sid,s.SERIAL#,s.sql_hash_value,s.sql_address,s.*,p.*,
fcrs.ORACLE_PROCESS_ID, fcrs.ORACLE_SESSION_ID, fcrs.OS_PROCESS_ID
FROM apps.fnd_concurrent_requests fcrs, v$session s, v$process p
WHERE 1=1
AND fcrs.ORACLE_SESSION_ID = s.audsid
AND fcrs.OS_PROCESS_ID = s.process
AND fcrs.ORACLE_PROCESS_ID = p.spid
AND fcrs.request_id = '188952068';
Get process and session details from request id using GV$SESSION
SELECT p.spid,s.sid,s.SERIAL#,s.sql_hash_value,s.sql_address,s.*,p.*,
fcrs.ORACLE_PROCESS_ID, fcrs.ORACLE_SESSION_ID, fcrs.OS_PROCESS_ID
FROM apps.fnd_concurrent_requests fcrs, gv$session s, v$process p
WHERE 1=1
AND fcrs.ORACLE_SESSION_ID = s.audsid
AND fcrs.OS_PROCESS_ID = s.process
AND fcrs.ORACLE_PROCESS_ID = p.spid
AND fcrs.request_id = '188952068';
Get SQL Query of running concurrent request
SELECT s.sql_fulltext,s.*
FROM v$sql s
WHERE hash_value='&sql_hash_value'; --from above query gv$session.sql_hash_value
Finding the SQL query of the sessions
SELECT a.sql_text, b.sql_hash_value
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = '2815' --from gv$session.sid
ORDER BY a.piece;
Find blocking sessions with v$session
SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL;
Find blocking sessions using v$lock
SELECT
l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;
Blocking sessions with all available information
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
Identifying blocked objects
SELECT sid, id1 FROM v$lock WHERE TYPE='TM';
SELECT object_name FROM dba_objects WHERE object_id=20127;
select * from DBA_LOCKS where session_id in
(select session_id from dba_ddl_locks where name like 'OBJECT_NAME');
select * from dba_ddl_locks where name like 'OBJECT_NAME';
select * from v$session where sid in
(select session_id from dba_ddl_locks where name like 'OBJECT_NAME');
select * from DBA_LOCKS where session_id in
(select sid from v$session where status='INACTIVE')
select * from DBA_LOCKS ;
select * from v$session where osuser='USERNAME';
Killing Sessions
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';
--Killing multiple sessions:
BEGIN
FOR r IN (select sid,serial# from v$session where username = 'USER')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid|| ',' ||
r.serial# || '''';
END LOOP;
END;
--Disconnecting the Session
alter system disconnect session '<SID>,<Serial#>' IMMEDIATE;
--Kill the Server Process
kill -9 <Unix Process Id from SID>
Comments
Post a Comment