Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used. Solutions Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table : SELECT * FROM ( SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' AND c.owner = upper('&&owner') and cc.owner = upper('&&owner') MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name AND c.owner = upper('&&owner') and cc.owner = upper('&&owner') ) ORDER BY table_name, column_position;
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277920
