SR 4 DB

---***************************** ---*   Locking Tree            * ---*  By Eini Trujillo. Vzla   * ---*  April 2013               * ---***************************** set echo off set feed off column tm new_value file_time noprint column tbd new_value bd_name noprint set lin 180 select to_char(sysdate, 'YYYYMMDDHH24MISS' ) tm from dual ; prompt http://studioracle.blogspot.com/2014/05/locking-tree-script.html select instance_name tbd from v$instance ; spool locktree .. &bd_name .. &file_time ..log set lin 180 col Bloqueos for a100 SELECT  LPAD(' ',(LEVEL-1)*10) ||   LPAD(A.Bloqueando,10,'-') ||  LPAD(A.Bloqueado,10,'-') Locks FROM (select a.sid Bloqueando, b.sid Bloqueado from v$lock a, v$lock b where a.block in (1,2) and b.request >0 and a.id1=b.id1 and a.id2=b.id2) A CONNECT BY PRIOR a.Bloqueado = A.Bloqueando START WITH A.Bloqueando in (select  sid from  v$lock  l2 where block in (1

first of all, some lines about theory. interested transaction list (ITL) Information in the block header of every segment that determines whether a transaction was uncommitted when the database began modifying the block. Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. ITL Waits ITL Waits happens when a session sets a DML transaction and this needs to acquire an ITL slot within data header block but there is not enough space in the pctfree area hence the session has to wait that other DML transaction is commited o rolled back therefore an ITL slot will be avaiable. 

In this example we lost one extent (128M) because oracle uses in  locally managed tablespaces with a UNIFORM extent size  at least 64K per datafile  for Metadata Blocks . create   tablespace  TBS_TEST_BAD datafile   '/oradata/TEST/lm02.dbf'   size   524288 K   -- <-- 512M extent   management   local   uniform   size   128 M; Tablespace                          Size  MB    Free MB    Used MB Used Pct ------------------------------ ---------- ---------- ---------- -------- TBS_TEST_BAD                           512          384          128   25  % Then datafile has to be  a multiple of their extent size + 64K. create tablespace TBS_TEST datafile ' /oradata/TEST/lm01.dbf ' size 524352 K   -- <-- 512M  + 64K extent management local uniform size 128 M; Tablespace                         Size MB    Free MB    Used MB Used Pct ------------------------------ ---------- ---------- ---------- -------- TBS_TEST_GOOD