oracle: мониторинг объектов схемы

1. Посмотреть кто к каким объектам схемы обращается.

-- кто к каким объектам схемы обращается
SELECT a.object,
       a.type,
       a.sid,
       b.username,
       b.osuser,
       b.program
FROM   v$access a,
       v$session b
WHERE  a.sid   = b.sid
AND    a.owner = UPPER('arbor')
ORDER BY a.object;

Используем представление V$ACCESS.
V$ACCESS содержит информацию о блокировках, которые в данный момент наложены на объекты library cache. Это делается для того чтобы они не ушли из library cache пока они требуются для выполнения sql запроса.

2. Есть программа, нужно узнать к каким объектам она обращается.

-- основываясь на п.1, можно посмотреть информацию о том: какая конкретная программа
-- какие объекты использует
SELECT a.object,
       a.TYPE,
       a.sid,
       b.username,
       b.osuser,
       b.program
  FROM v$access a, v$session b
 WHERE     a.sid = b.sid
       AND a.owner = UPPER ('arbor')
       AND b.program LIKE 'ck_KenanPaymentCreate%'
ORDER BY a.object;

oracle: мониторинг блокировок

oracle: блокировки (locks), защёлки (latches), enqueues
Мониторинг блокировок (http://my-oracle.it-blogs.com.ua/)

Запросы для мониторинга блокировок в базе.

-- смотрим блокировки, установленные текущими транзакциями в базе,
-- которые блокируют запросы на блокировку от других сессий (where block=1)

SELECT l.SID,s.username,s.program,l.TYPE,l.LMODE,ROUND(CTIME/60) "Time(Min)",
(SELECT object_name FROM dba_objects WHERE object_id=lo.object_id) obj,
DECODE(lo.locked_mode,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   TO_CHAR(lo.locked_mode)) mode_held
FROM V$LOCK l, v$locked_object lo, v$session s
WHERE block=1
AND l.sid=lo.session_id AND l.sid=s.sid;

-- смотрим активные сессии, которые чего то ждут, и чего они собственно ждут

SELECT s.program,s.username,s.osuser,s.machine,sw.* FROM v$session_wait sw,v$session s
WHERE sw.sid=s.sid
AND sw.event<>'rdbms ipc message' AND sw.event NOT LIKE 'SQL*Net%'
ORDER BY sw.event ASC;

-- смотрим блокировки, установленные текущими транзакциями в базе,
-- кто и на что и какой тип блокировки установил

SELECT  oracle_username, session_id,s.program,object_name,DECODE(a.locked_mode,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   TO_CHAR(a.locked_mode)) mode_held
FROM V$LOCKED_OBJECT a,DBA_OBJECTS b,v$session s WHERE a.object_id = b.object_id
AND a.session_id=s.sid;

oracle: DBMS_SESSION аналогия ALTER SESSION для PL/SQL

Этот пакет DBMS_SESSION предоставляет доступ к SQL командам ALTER SESSION и SET ROLE для PL/SQL
Дока: DBMS_SESSION