Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 7 May 2012

Script: To Find Database locks

select
 (select username || ' - ' || osuser from v$session where sid=a.sid) blocker, a.sid || ', ' ||
 (select serial# from v$session where sid=a.sid) sid_serial, ' is blocking ',
 (select username || ' - ' || osuser from v$session where sid=b.sid) blockee, b.sid || ', ' ||
 (select serial# from v$session where sid=b.sid) sid_serial
 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;
--Generate a list of current oracle sids/pids
SELECT   NVL (a.username, 'Null') username, b.pid, a.sid,
         DECODE (a.terminal, '?', 'Detached', a.terminal) terminal, b.program,
         b.spid, a.osuser, a.serial#
    FROM v$session a, v$process b
   WHERE a.paddr = b.addr
ORDER BY A.username, b.pid;

--Displays information on all database session events
SELECT NVL(s.username, '(oracle)') AS username,
       s.sid, s.serial#, se.event, se.total_waits, se.total_timeouts,
       se.time_waited, se.average_wait, se.max_wait, se.time_waited_micro
FROM   v$session_event se, v$session s
WHERE  s.sid = se.sid
 AND    s.sid = &1
ORDER BY se.time_waited DESC;
--Displays I/O information on all database sessions
SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser, s.sid, s.serial#, si.block_gets,  si.consistent_gets,
       si.physical_reads, si.block_changes, si.consistent_changes
FROM   v$session s, v$sess_io si
WHERE  s.sid = si.sid
ORDER BY s.username, s.osuser;
--History of the event in a specific session
select event, total_waits, time_waited*10 tw_ms,
       average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = 32;
--List all session statistics for particualr sid
select name, value
from v$sesstat s, v$statname n
where sid = 32
and n.statistic# = s.statistic#
order by value desc;
--Check the CPU consumption for a session
select s.value
from v$sesstat s, v$statname n
where s.sid = 32
and n.statistic# = s.statistic#
and n.name = 'CPU used by this session';

--Getting specific row Information
Select owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_rowid.rowid_create (
        1, o.data_object_id, row_wait_file#,
        row_wait_block#, row_wait_row#
    ) row_id
from v$session s, dba_objects o
where sid = &sid
and o.data_object_id = s.row_wait_obj#;

0 comments:

Post a Comment