Skip to content

oracle TM lock, how-to find who forgot index on FK ?

oracle TM lock, how-to find who forgot index on FK ? published on Комментариев к записи oracle TM lock, how-to find who forgot index on FK ? нет

Lets do TM lock and find out what object should be indexed

create table tt ( id number ,fld varchar2(50) );
create table ttt ( id number ,id_tt number );
alter table tt add constraint pk_id primary key (id) ;
alter table ttt add foreign key ( id_tt ) references tt (id);
insert into tt select level ,rpad ('x',40) from dual connect by level <=100;
commit;

sess 1

 insert into ttt values ( 1,1);

sess 2

 delete from tt where id=1;

I use Tanel Poder sripts in this example
this one ( ashtop.sql )
and this one

oid.sql

— Script by Tanel Poder (http://www.tanelpoder.com)

— Look up object info by object id

col o_owner heading owner for a25
col o_object_name heading object_name for a30
col o_object_type heading object_type for a18
col o_status heading status for a9

select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id
from
dba_objects
where
object_id in (&1)
order by
o_object_name,
o_owner,
o_object_type

[свернуть]

result :

or use this scipt to find nonindexed foreing keys:

fk_no_indx

select table_name, column_name
from ( select c.table_name, cc.column_name, cc.position column_position
from all_constraints c, all_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.constraint_type = ‘R’ and c.owner=cc.owner and c.owner=’&1′
minus
select i.table_name, ic.column_name, ic.column_position
from all_indexes i, all_ind_columns ic
where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner=’&1′);

[свернуть]
18:13:51 (1)c##bushmelev_aa@u10pgp> @fk_no_indx C##BUSHMELEV_AA


TABLE_NAME | COLUMN_NAME
---------- | ---------------
TTT        | ID_TT

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика