Verifica Locks Registrados

-- Lista as Sessão com locks ativos

set serveroutput on size 20000
set pagesize 110
declare
cursor c1 is
select o.OBJECT_ID,substr(o.OBJECT_NAME,1,25) object_name,s.machine,
o.OBJECT_TYPE,substr(o.owner,1,15) owner,
l.OS_USER_NAME,l.ORACLE_USERNAME,l.PROCESS,decode(l.locked_mode,1,'No Lock',2,'Row Share',3,'Row Exclusive',
4,'Share', 5,'Share Row Excl', 6,'Exclusive',null) locked_mode, S.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID  and s.sid=l.session_id
order by 2,6;


begin

    dbms_output.put_line(rpad('-',130,'-'));
    dbms_output.put_line(    rpad('OBJECT_NAME',30)||
                rpad('OBJECT_TYPE',12)||
                rpad('LOGIN_Oracle',20)||
                rpad('LOGIN_REDE',15)||
                rpad('LOCKED_MODE',15)||
                rpad('MAQUINA',15));
    dbms_output.put_line(rpad('-',130,'-'));

            for t1 in c1 loop

                dbms_output.put_line(
                 rpad(t1.object_name,30)||
                rpad(t1.object_type,12)||
                rpad(t1.oracle_username|| '(' || t1.sid || ',' || t1.serial# || ')',20)||
                rpad(t1.os_user_name,15)||
                rpad(t1.locked_mode,15)||
                rpad(t1.machine,15));
            end loop;
    dbms_output.put_line(rpad('-',130,'-'));
end;
/

-- Filtra informações da sessão selecionada

select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session where sid =&sessao
/

Exemplo:
SQL> select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
  2  from v$session where sid =11;

 SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---- ------------- -------------- --------------- -------------
  11         27139              5             895             1

-- Mostra o registro que esta locado.

DECLARE

SELECT DEPTNO FROM scott.dept
WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,&Row_wait_obj,&Sid,&wait_block,&row_wait)
BEGIN
END
/



SQL> select object_type,owner,object_name   from dba_objects   where object_id=27139
  2  /

OBJECT_TYPE        OWNER     OBJECT_NAME
------------------ --------- -----------------------------------------------------------------------
TABLE              INFBMJLP  DEPT

SQL> SELECT DEPTNO FROM infbmjlp.EMP
  2  WHERE rowid = DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1);
SELECT DEPTNO FROM infbmjlp.EMP
                            *
ERRO na linha 1:
ORA-01410: invalid ROWID


SQL> SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAGoDAALAAAAN/AAB

SQL> select DEPTNO FROM infbmjlp.EMP
  2   WHERE rowid  in (SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual);

não há linhas selecionadas


######################

SQL>  select * from v$lock
  2  where id1=31712
  3  /

ADDR     KADDR          SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
-------- -------- --------- -- --------- --------- --------- --------- --------- ---------
16D85D30 16D85D44        28 TM     31712         0         3         0      2386         0
16D85CAC 16D85CC0        46 TM     31712         0         3         0      2396         0
16D85A9C 16D85AB0        47 TM     31712         0         3         0        12         0

SQL> select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
  2  from v$session WHERE ROW_WAIT_OBJ#=31712;

      SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
--------- ------------- -------------- --------------- -------------
       28         31712              6              14             1
       47         31712              6              14             1

######################

-- Mostra sessão em espera de liberação de locks
select sid,ROW_WAIT_obj#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session WHERE ROW_WAIT_OBJ# IN (
select object_id
from dba_objects
where object_name = '&Obj_name')
/

-- Mostra as sessões que estão locando um registro --
Entrada: OWNER,ID_OBJET0,ROWID
Saida: Usuario/Terminal

-- Retorno o rowid da Chave de pesquisa
Entrada:OWNER,OBJETO,CAMPO(S),
Saida: ROWID
select DEPTNO FROM infbmjlp.EMP
  WHERE rowid  in (SELECT DBMS_ROWID.ROWID_CREATE(1,27139,11,895,1) from dual);

   create or replace function F_ROW_ID(OWNER In ALL_OBJECTS.OWNER%TYPE,
  OBJETO ALL_OBJECTS.OBJECT_NAME%TYPE,
  CHAVE VARCHAR2) return varchar2 is
    Result varchar2(100);
    Row_id varchar2(18);
    Object_id all_objects.object_id%type;
   
  begin
    execute immediate 'Select rowid from '||OWNER||'.'||OBJETO||' where '
    ||CHAVE into Row_Id;
   
    execute immediate 'Select object_id from all_objects'
    ||' where owner='||OWNER||' and OBJECT_NAME='||OBJETO into OBJECT_Id;
      
   
    return(Result+OBJECT_ID);
  end F_ROW_ID;

--