denrepo

OracleObjects & schema

Foreign keys that are probably not indexed

Foreign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or deleted.

12c+

Not yet verified. How scripts are tested

ora-fk-no-index.sql 
1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN owner           FORMAT A204COLUMN table_name      HEADING "TABLE"      FORMAT A305COLUMN constraint_name HEADING "CONSTRAINT" FORMAT A306COLUMN fk_columns      HEADING "COLUMNS"    FORMAT A507 8SELECT c.owner, c.table_name, c.constraint_name,9       LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS fk_columns10FROM dba_constraints c11JOIN dba_cons_columns cc12  ON cc.owner = c.owner13 AND cc.constraint_name = c.constraint_name14WHERE c.constraint_type = 'R'15  AND c.owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')16  AND NOT EXISTS (17        SELECT 118        FROM dba_ind_columns ic19        WHERE ic.table_owner = c.owner20          AND ic.table_name = c.table_name21          AND ic.column_name = cc.column_name22          AND ic.column_position = cc.position)23GROUP BY c.owner, c.table_name, c.constraint_name24ORDER BY c.owner, c.table_name, c.constraint_name;

Save it as ora-fk-no-index.sql and run it with SQL> @ora-fk-no-index.

Open in denrepo

Helps with

More Oracle scripts: Objects & schema