Monitorar SQLs que geram Locks no Oracle

Essa semana eu precisei monitorar alguns locks que estavam ocorrendo em uma base de produção.
Só que os locks eram esporádicos, não dava para ficar na frente de uma tela de OEM esperando acontecer, além disso eu precisava descobrir o SQL que estava bloqueando algum recurso e o SQL que estava esperando pelo recurso.

Eu criei um job que roda de 2 em 2 minutos , ele verifica se existe alguma sessão com evento de espera tipo o : enq: TX - row lock contention.
Caso exista e o tempo de espera seja maior que X ele loga os SQLSs que estao bloqueando e esperando por um recurso,o SID das sessões,a data e o tempo de espera da sessão em uma tabela que criei para isso.

O Mais legal nisso foi que descobri a coluna Prev_sql_id da V$SESSION, com ela conseguimos saber o SQL que esta bloqueando algum recurso.

Abaixo segue o comando de criação do JOB e da Tabela para logar.

UPDATE:
 Legal olhar um post de quase 4 anos atrás, vendo agora não gosto muito desse código, então decide realizar algumas pequenas modificações , graças ao comentário do Mauro rafael, modifiquei na hora mesmo, só por capricho,rs.

Tabela:

CREATE TABLE LOG_LOCK (SQL_BLOCKER VARCHAR2(1000),
SID_BLOCKER INT, SQL_BLOCKED VARCHAR2(1000),
SID_BLOCKED INT,DATA_LOG DATE,WAIT INT);


Job:

BEGIN
  DBMS_SCHEDULER.create_job (
 job_name        => 'Monitor_Lock',
 job_type        => 'PLSQL_BLOCK',
 job_action      => '
DECLARE

V_BLOCKER INT;
V_BLOCKED INT;
V_SQL VARCHAR2(1000);
V_SQL2 VARCHAR2(1000);
V_TIME INT;
V_CTR INT;

BEGIN

    SELECT COUNT(*) INTO V_CTR
    FROM v$session s
    WHERE event LIKE ''enq: %'';


       FOR CRS IN (select s1.sid BLOCKER, s2.sid BLOCKED
       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)

       LOOP

          IF V_CTR >= 1 THEN

             V_BLOCKED:=CRS.BLOCKED;
             V_BLOCKER:=CRS.BLOCKER;

                 SELECT SQL_TEXT INTO V_SQL FROM V$SQL S
                 INNER JOIN V$SESSION V ON (V.Prev_sql_id=S.SQL_ID)
                 WHERE V.SID=V_BLOCKER;

                 SELECT SQL_TEXT ,V.SECONDS_IN_WAIT INTO V_SQL2,V_TIME FROM V$SQL S
                 INNER JOIN V$SESSION V ON (V.sql_id=S.SQL_ID)
                 WHERE V.SID=V_BLOCKED;


                 IF V_TIME > 30 THEN
                     INSERT INTO LOG_LOCK (SID_BLOCKER,SQL_BLOCKER,SID_BLOCKED,SQL_BLOCKED,DATA_LOG,WAIT) VALUES
                     (V_BLOCKER,V_SQL,V_BLOCKED,V_SQL2,SYSDATE,V_TIME);
                     COMMIT;

                END IF;

          END IF;

        END LOOP;


END;
 ',

 start_date      => trunc(sysdate)+18/24,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
 end_date        => NULL,
 enabled         => TRUE,
 comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

Matar job do Datapump Oracle

Pessoal vou passar um dica, quando se precisa matar um job do datapump. Quando você esta executando o export ou import do datapump, nao basta dar Control-C para parar esse job, quando se faz isso apenas pausa o job, em alguns casos nem isso. Vou listar o Procedimento para eliminar realmente o Job.

 1) Verifique as informações para eliminação do job.

SELECT OWNER_NAME,JOB_NAME FROM DBA_DATAPUMP_JOBS;

2) Saia do sqlplus e entre no datapump novamente com o commando abaixo:

impdp username/password@database attach=nome_do_job

3) Elimine o job com o commando :

$ Import>kill_job
Are you sure you wish to stop this job (y/n): y

4) Verifique se o job realmente foi eliminado

SELECT OWNER,JOB_NAME FROM DBA_DATAPUMP_JOBS;