ASH scripts

USER session from ASH

select a.snap_id,a.dbid,a.instance_number,a.session_id,a.session_serial#,a.sql_id,a.user_id,a.session_state,a.blocking_session,
a.time_waited,to_char(a.sample_time,'mm-dd-yyyy hh24:mi:ss') as stime,a.event from dba_hist_active_sess_history a
where sample_time between TIMESTAMP'2012-09-13 12:25:00' AND TIMESTAMP'2012-09-13 12:30:00'


select snap_id,END_INTERVAL_TIME from dba_hist_snapshot where END_INTERVAL_TIME > sysdate-1  order by 1;
It runs faster if you use the snap_id;

Lock

prompt +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

prompt    Following output gives the details of blocker sessions

prompt ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select * from gv$lock where block=1;

select distinct blocking_instance,blocking_session from gv$session where blocking_session is not null order by 1,2;

set lines 199

select /*+ rule */ inst_id blocker_instance, sid blocker_sid, serial# blocker_sess_serial#, 

username, osuser, machine, type, status, event, sql_id, last_call_et, seconds_in_wait from gv$session where

(inst_id,sid) in (select distinct blocking_instance,blocking_session from gv$session where blocking_session is not null);

Lock on RAC

col os_user_name for a16

col object_name  for a24

col object_type  for a16

set pages 999 lines 132 feedback on

REM ---  Takes a bit longer to run..

SELECT s.osuser,s.sid,s.serial#,s.inst_id,v.oracle_username USERNAME, d.owner OBJECT_OWNER,

d.object_name, d.object_type, 

DECODE(l.block,

  0, 'Not Blocking',

  1, 'Blocking',

  2, 'Global') STATUS,

  DECODE(v.locked_mode,

    0, 'None',

    1, 'Null',

    2, 'Row-S (SS)',

    3, 'Row-X (SX)',

    4, 'Share',

    5, 'S/Row-X (SSX)',

    6, 'Exclusive', TO_CHAR(lmode)

  ) MODE_HELD

FROM gv$locked_object v, dba_objects d,

gv$lock l, gv$session s

WHERE v.object_id = d.object_id 

AND s.inst_id=l.inst_id

AND v.object_id = l.id1

and v.session_id = s.sid

ORDER BY oracle_username, session_id;