fklocking.sql

set serveroutput on
declare
 procedure print_all(s varchar2) is begin null;
  dbms_output.put_line(s);
 end;
 procedure print_ddl(s varchar2) is begin null;
  dbms_output.put_line(s);
 end;
begin
 dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
 for a in (
        select count(*) samples,
        event,p1,p2,o.owner c_owner,o.object_name c_object_name,p.object_owner p_owner,p.object_name p_object_name,id,operation,min(p1-1414332420+4) lock_mode,min(sample_time) min_time,max(sample_time) max_time,ceil(10*count(distinct sample_id)/60) minutes
        from dba_hist_active_sess_history left outer join dba_hist_sql_plan p using(dbid,sql_id) left outer join dba_objects o on object_id=p2 left outer join dba_objects po on po.object_id=current_obj#
        where event like 'enq: TM%' and p1>=1414332420 and sample_time>sysdate-15 and p.id=1 and operation in('DELETE','UPDATE','MERGE')
        group by
        event,p1,p2,o.owner,o.object_name,p.object_owner,p.object_name,po.owner,po.object_name,id,operation
        order by count(*) desc
 ) loop
  print_ddl('--  '||a.operation||' on '||a.p_owner||'.'||a.p_object_name||' has locked '||a.c_owner||'.'||a.c_object_name||' in mode '||a.lock_mode||' for '||a.minutes||' minutes between '||to_char(a.min_time,'dd-mon hh24:mi')||' and '||to_char(a.max_time,'dd-mon hh24:mi'));
  for s in (
   select distinct regexp_replace(cast(substr(sql_text,1,2000) as varchar2(60)),'[^a-zA-Z ]',' ') sql_text
   from dba_hist_active_sess_history join dba_hist_sqltext t using(dbid,sql_id)
   where event like 'enq: TM%' and p2=a.p2 and sample_time>sysdate-90
  ) loop
   print_all('--      '||'blocked statement: '||s.sql_text);
  end loop;
  for c in (
    with
      c as (
      select p.owner p_owner,p.table_name p_table_name,c.owner c_owner,c.table_name c_table_name,c.delete_rule,c.constraint_name
      from dba_constraints p
      join dba_constraints c on (c.r_owner=p.owner and c.r_constraint_name=p.constraint_name)
      where p.constraint_type in ('P','U') and c.constraint_type='R'
    )
    select c_owner owner,constraint_name,c_table_name,connect_by_root(p_owner||'.'||p_table_name)||sys_connect_by_path(decode(delete_rule,'CASCADE','(cascade delete)','SET NULL','(cascade set null)',' ')||' '||c_owner||'"."'||c_table_name,' referenced by') foreign_keys
    from c
    where level<=10 and c_owner=a.c_owner and c_table_name=a.c_object_name
    connect by nocycle p_owner=prior c_owner and p_table_name=prior c_table_name and ( level=1 or prior delete_rule in ('CASCADE','SET NULL') )
    start with p_owner=a.p_owner and p_table_name=a.p_object_name
  ) loop
   print_all('--      '||'FK chain: '||c.foreign_keys||' ('||c.owner||'.'||c.constraint_name||')'||' unindexed');
   for l in (select * from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name) loop
    print_all('--         FK column '||l.column_name);
   end loop;
   print_ddl('--      Suggested index: '||regexp_replace(translate(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name,c.owner),chr(10)||chr(13),'  '),'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*','CREATE INDEX ON \1 \3;'));
   for x in (
     select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),'  ')) ddl
     from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
     and column_name in (select column_name from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name)
     )
   loop
    print_ddl('--      Existing candidate indexes '||x.ddl);
   end loop;
   for x in (
     select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),'  ')) ddl
     from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
     )
   loop
    print_all('--       Other existing Indexes: '||x.ddl);
   end loop;
  end loop;
 end loop;
end;
/