Verifica Locks em Tabelas

-- SESSAO 1

-- Lista as Sessão com locks ativos

set serveroutput on size 20000
set lines 200
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('-',180,'-'));
    dbms_output.put_line(    rpad('OBJECT_NAME',30)||
                rpad('OBJECT_TYPE',12)||
                rpad('LOGIN_Oracle',30)||
                rpad('LOGIN_REDE',30)||
                rpad('LOCKED_MODE',15)||
                rpad('MAQUINA',25));
    dbms_output.put_line(rpad('-',180,'-'));

            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# || ')',30)||
                rpad(t1.os_user_name,30)||
                rpad(t1.locked_mode,15)||
                rpad(t1.machine,25));
            end loop;
    dbms_output.put_line(rpad('-',180,'-'));
end;
/

-- rowid Aluno
select rowid from sa_academico_gnx.&tabela
where
ALU2ANOCAD='&ALU2ANOCAD'
and ALU2SEMCAD='&ALU2SEMCAD'
and ALU2SEQCAD='&ALU2SEQCAD'
/
-- Anotar SID
select distinct sid from v$mystat
/

233 AAAOZcAAbAAAEnfAAD

-- Irá ficar em LOCK
SELECT 1 FROM SA_ACADEMICO_GNX.&TABELA WHERE ROWID='&ROW_ID'
FOR UPDATE
/

-- Abrir nova seção
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, 'None', null, null, null, null
from lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holders;
commit;

column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;

/* Print out the result in a tree structured fashion */
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;

-- Fazer rollback na sessão 1.