Oracle: Script to check for table locks

Script to check for Oracle table locks:

Display details on which table is being locked by which process/user


select s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;

Oracle: Script to display current slow job details

Oracle Script to display slow jobs details


column e_dttm format a15
column usr format a10
column hp format 9999999999
select
TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm,      -- Interval End Time
m.intsize_csec/100 ints,                       -- Interval size in sec
s.username usr,
m.session_id sid,
m.session_serial_num ssn,
ROUND( m. cpu) cpu100,                           -- CPU usage 100th sec
m.physical_reads prds,                         -- Number of physical reads
m.logical_reads lrds,                          -- Number of logical reads
m.pga_memory pga,                              -- PGA size at end of interval
m.hard_parses hp,
m.soft_parses sp,
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from  v$sessmetric m, v$session s
where (m. physical_reads > 100
or     m. cpu > 100
or     m. logical_reads > 100)
and   m. session_id = s. sid
and   m. session_serial_num = s. serial#
order by m. physical_reads DESC, m. cpu DESC, m. logical_reads DESC;

Oracle: SQL for displaying current processes, userids

use the followings sql for displaying current running processes, userids:

SET TERMOUT    ON
SET HEADING    ON


SET PAGESIZE   40
SET LINESIZE   180
SET NEWPAGE    0
SET VERIFY     OFF
SET ECHO       OFF
SET UNDERLINE  =
SET FEEDBACK   ON
SET LONG       1000
SET EMBED      ON


COLUMN sid_ser        FORMAT a10            HEADING ' SID/Ser'
COLUMN sid            FORMAT 99999          HEADING ' SID'
COLUMN username       FORMAT a6             HEADING 'Oracle|User'
COLUMN osuser         FORMAT a10    TRUNC   HEADING 'O/S User'
COLUMN machine        FORMAT a10            HEADING 'Machine'
COLUMN program        FORMAT a20            HEADING 'Program'
COLUMN F_Ground       FORMAT 99999          HEADING 'F''Ground|Process'
COLUMN B_Ground       FORMAT 99999          HEADING 'B''Ground|Process'
COLUMN sql_text       FORMAT a45 word_wrap  HEADING 'SQL Text'
COLUMN disk_reads     FORMAT 99,999         HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets    FORMAT 9,999,999      HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999      HEADING 'Rows|Processed|(*1000)'
COLUMN sorts          FORMAT 99,999         HEADING 'Sorts'
COLUMN executions     FORMAT 9,999,999,999      HEADING 'Exctn'


TTITLE Center 'SQL Currently Executing'


SELECT /*+ ORDERED */
       s.sid || ',' || s.serial# as sid_ser,
       s.username,
       s.osuser,
       x.sql_text,
       x.disk_reads     / 1000 AS disk_reads,
       x.buffer_gets    / 1000 AS buffer_gets,
       x.rows_processed / 1000 AS rows_processed,
       x.sorts
       ,x.executions
       ,x.address
  FROM v$session S,
       v$process P,
       v$sql X
 WHERE LOWER(s.osuser)      LIKE LOWER(NVL('&os_user%', '%'))
   AND s.username           LIKE UPPER(NVL('&oracle_user%', '%'))
   AND s.sid                LIKE NVL('&sid', '%')
   AND s.type                 != 'BACKGROUND'
   AND s.sql_address           = x.address
   AND s.sql_hash_value        = x.hash_value
--   AND s.username         NOT IN ('SYS','SYSTEM')
   AND s.paddr                = p.addr
 ORDER
     BY S.sid
;