Oracle Database Performance Troubleshooting Diary

Find block sessions

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;

Find User, Hostname and Program Information

select
   substr(a.spid,1,9) pid,
   substr(b.sid,1,5) sid,
   substr(b.serial#,1,5) ser#,
   substr(b.machine,1,6) box,
   substr(b.username,1,10) username,
   -- b.server,
   substr(b.osuser,1,8) os_user,
   substr(b.program,1,30) program
from
   v$session b,
   v$process a
where
b.paddr = a.addr
and type='USER' and b.username = 'xxxxxx'
order by spid;

select * from user_source where name like 'RESE%';

alter user itservice identified by s2Atrupr2hE;
Select * from v$session;

select
   'ALTER SYSTEM KILL SESSION ''' || sid || ',' || b.serial# || ''';'
from
   v$session b,
   v$process a
where
b.paddr = a.addr
and type='USER' and b.username like 'USERNAME%'
order by spid;

Find Job information

SELECT o.name object_name, u.name object_owner, o.obj# job_id FROM
obj$ o, user$ u WHERE
o.owner# = u.user# and o.obj# = 42;

Get SQl ID

select * from v$session where sid = 42; 

Get SQL Text

set heading off;
select * from v$sqltext where sql_id = 'd4k1q04fmwrjq' order by piece;