Standard DB Queries

Session details when SPID is known
=============================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where b.spid in( )
and a.paddr = b.addr;


Session details when CLIENT PROCESS is known
========================================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where a.process like '%%'
and a.paddr = b.addr;


Wait Event details when SPID is known
=================================

select a.sid,a.serial#,a.status,(sysdate-logon_time)*24*60 MINSDURATN,b.event,b.p1,b.p2,b.p3
from v$session a ,v$session_wait b
where
a.spid= and
a.sid=b.sid ;

Find OS process Id for a session
select spid, pid
from v$process
where addr = (select paddr from v$session where sid = );
Find the most recent sql by a session
select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid;
Who is blocking whome
select
(select username from v$session where sid=a.sid) blocker,
a.sid,’ is blocking ‘,
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
Identify corrupted objects using file# and block#

select segment_name from dba_segments where file_id= and between BLOCK_ID AND BLOCK_ID+BLOCKS-1;

Session information and tracing

 

Trace My session ID

select sys_context(’USERENV’,'SID’) from dual;
Oracle Session information user
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,
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;

SQL statement executed by a session

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

Find Objects locked by a session and the sql locking it

select c.owner,c.object_name,c.object_type,
b.sid,b.serial#,b.status,b.osuser,b.machine,s.sql_text from v$locked_object a ,v$session b,dba_objects c,v$sqltext_with_newlines s
where b.sid = a.session_id
and a.object_id = c.object_id and b.sql_address=s.address and b.sql_hash_value=s.hash_value;

Which session/sql is blocking what session/sql

select s1.sid||’,'||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||’,'||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

Enable sql trace with bind before executing a sql

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;

Enable sql trace with binds for a running session

alter session set TRACEFILE_IDENTIFIER = ‘APPS_MYTAG’;

EXEC dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE);

Other options

 exec dbms_system.set_int_param_in_session(&&sid,&&serial,’MAX_DUMP_FILE_SIZE’,2147483647);

exec sys.dbms_system.set_BOOL_param_in_session(94,239,’sql_trace’,true);
exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

To disable trace

exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

10 G onwards

SELECT   b.name, b.value_string, sq.sql_text
FROM     v$sql_bind_capture b, v$sql sq, v$session s
WHERE    sq.sql_id = b.sql_id
AND      sq.address = b.address
AND      sq.child_address = b.child_address
AND      sq.parsing_user_id = s.user#
AND      s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;

 

Script to show how long a statement will run

column ssid format 9999 heading SID
column opname format a15 Heading Operation
column target format a28 Heading Target
column es format 999.9 Heading "Time|Ran"
column tr format 999.90 Heading "Time|Left"
column pct format 990 Heading "PCT"
column RATE Heading "I/O |Rate/m" just right

select
sid ssid,
substr(OPNAME,1,15) opname,
target,
trunc((sofar/totalwork)*100) pct,
to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0')||'M' Rate,
elapsed_seconds/60 es,
time_remaining/60 tr
from v$session_longops
where time_remaining > 0
order by start_time
/

Show What A Current User Is Doing

select sid, serial#, status, server from v$session where username = 'BROWNBH';

Select All Users Active In The System

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username