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;

Kill Oracle Session

Below scripts is quoted from http://www.dba-oracle.com/tips_killing_oracle_sessions.htm

rem session.sql - displays all connected sessions

set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
   v$sysstat a,
   v$license b,
   v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

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'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;

SPARC: Initiate ILOM

set /SP/network pendingipdiscovery=static pendingipaddress=17.15.21.77 pendingipgateway=17.15.21.1 pendingipnetmask=255.255.255.0 commitpending=true

set /SP hostname=gweek-rsc system_identifier=GOWEEKENDIT