Some Useful Scripts for Oracle database

Some Useful Scripts for Oracle database

Script to find the sid of the session you are logged in as

select distinct(sid) from v$mystat;

 

To see all active session

select username,osuser,sid,serial#, program,sql_hash_value,module from v$session where username is not null

and status =’ACTIVE’ and module is not null;

 

To see waiters

set linesize 1000

column waiting_session heading ‘WAITING|SESSION’

column holding_session heading ‘HOLDING|SESSION’

column lock_type format a15

column mode_held format a15

column mode_requested format a15

select

waiting_session,

holding_session,

lock_type,

mode_held,

mode_requested,

lock_id1,

lock_id2

from

dba_waiters

/

 

This query will show all users that have active transactions and the rollback segment each user is writing to:

col RBS format a15 trunc

col SID format 9999

col USER format a15 trunc

col COMMAND format a60 trunc

col status format a8 trunc

select r.name “RBS”, s.sid, s.serial#, s.username “USER”, t.status,

t.cr_get, t.phy_io, t.used_ublk, t.noundo,

substr(s.program, 1, 78) “COMMAND”

from v$session s, v$transaction t, v$rollname r

where t.addr = s.taddr

and t.xidusn = r.usn

order by t.cr_get, t.phy_io

/

 

 

To check Library Cache locks and pin

select /*+ all_rows */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, 0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,

‘Unknown’) mode_held,

decode(w.kgllkreq, 0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,

‘Unknown’) mode_requested

from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr

/

The result looks like: WAITING_SESSION HOLDING_SESSION LOCK ADDRESS MODE_HELD MODE_REQU

To monitor long running queries

set linesize 1000

select

OPNAME,

sid,SOFAR/TOTALWORK*100,

to_char(start_time,’dd-mon-yy hh:mi’) started,

elapsed_seconds/60,time_remaining/60

from

v$session_longops

where

sid =&sid

To see all lock objects

set term on;

set lines 130;

column sid_ser format a12 heading ‘session,|serial#’;

column username format a12 heading ‘os user/|db user’;

column process format a9 heading ‘os|process’;

column spid format a7 heading ‘trace|number’;

column owner_object format a35 heading ‘owner.object’;

column locked_mode format a13 heading ‘locked|mode’;

column status format a8 heading ‘status’;

select

substr(to_char(l.session_id)||’,’||to_char(s.serial#),1,12) sid_ser,

substr(l.os_user_name||’/’||l.oracle_username,1,12) username,

l.process,

p.spid,

substr(o.owner||’.’||o.object_name,1,35) owner_object,

decode(l.locked_mode,

1,’No Lock’,

2,’Row Share’,

3,’Row Exclusive’,

4,’Share’,

5,’Share Row Excl’,

6,’Exclusive’,null) locked_mode,

substr(s.status,1,8) status

from

v$locked_object l,

all_objects o,

v$session s,

v$process p

where

l.object_id = o.object_id

and l.session_id = s.sid

and s.paddr = p.addr

and s.status != ‘KILLED’

/

 

To see waits events across the database

set linesize 1000

column sid format 999

column username format a15 wrapped

column spid format a8

column event format a30 wrapped

column osuser format a12 wrapped

column machine format a25 wrapped

column program format a30 wrapped

select sw.sid sid

, p.spid spid

, s.username username

, s.osuser osuser

, sw.event event

, s.machine machine

, s.program program

from v$session_wait sw

, v$session s

, v$process p

where s.paddr = p.addr

and event not in (‘pipe get’,’client message’)

and sw.sid = s.sid

/

To see session wait event

select sid,seq#,wait_time,event,seconds_in_wait,state from v$session_wait where sid in (&sid)

 

To see all user accessing that objects

set linesize 1000

column object format a30

column owner format a10

select * from v$access where object=’&object_name’

/

This script gives information about the user sessions locking a particular object

set linesize 1000

column program format a15

column object format a15

select substr(username||'(‘|| se0.sid||’)’,1,5) “User Session”,

substr(owner,1,5) “Object Owner”,

substr(object,1,15) “Object”,

se0.sid,

substr(serial#,1,6) “Serial#”,

substr(program,1,15) “Program”,

logon_time “Logon Time”,

process “Unix Process”

from v$access ac, v$session se0

where ac.sid = se0.sid

and Object = ‘&PACKAGE’

order by logon_time,”Object Owner”,”Object”

/

To see sql plan

set linesize 9999

column QUERY format a999

set pages 250

set head off

set verify off

select id,lpad(‘ ‘,2*(depth-1)) || depth ||’.’ || nvl(position,0) || ‘ ‘|| operation || ‘ ‘|| options || ‘ ‘|| object_name ||’ ‘

||’cost= ‘|| to_char(cost)||’ ‘|| optimizer “QUERY”

from v$sql_plan

where hash_value = &sql_hash_value

order by child_number,id

/

To find server location

select nvl(username,’ORACLE SHADOW PROCESS’),

machine from

v$session where username is null

and rownum < 2

/