Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Finding oracle Database locks

 (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#;


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform