|
l_ind_column VARCHAR2(30); pl_ind_name VARCHAR2(30); pl_ind_owner VARCHAR2(30); pl_index VARCHAR2(2000); f_owner VARCHAR2(30); f_table_name VARCHAR2(30); /* Cursor c1 simply selects each Foreign Key constraint from the DBA View DBA_CONSTRAINTS. No need at this stage to limit the query to 'ENABLED' constraints, we'll simply report the status in the log file. For each constraint, we'll construct the column list, using cursor c2, which combine to form the foreign key constraint returned in cursor c1 */ CURSOR c1 IS SELECT constraint_name,owner,table_name,status,r_owner,r_constraint_name FROM dba_constraints WHERE constraint_type='R' AND owner between upper(from_schema) and upper(to_schema) ORDER BY owner; CURSOR c2(cons_name VARCHAR2,cons_owner VARCHAR2) IS SELECT column_name FROM dba_cons_columns WHERE constraint_name=cons_name AND owner=cons_owner ORDER BY dba_cons_columns.position; /* For each returned constraint, we need to fins a matching index, firstly we fetch each index name with c3, and then construct the index columns with cursor c4 in their correct order until we find a match with the foreign key constraint */ CURSOR c3(ind_table varchar2,tab_owner varchar2) IS SELECT index_name, owner FROM dba_indexes WHERE table_name=ind_table AND table_owner=tab_owner; CURSOR c4(ind_name varchar2,ind_owner varchar2) IS SELECT column_name FROM dba_ind_columns WHERE INDEX_NAME=ind_name AND INDEX_OWNER=ind_owner ORDER BY dba_ind_columns.column_position; CURSOR c5(for_owner varchar2,for_constraint varchar2) IS SELECT owner,table_name FROM dba_constraints WHERE OWNER=for_owner AND CONSTRAINT_NAME=for_constraint; BEGIN WRITE_TO_TABLE_Y_N:='&&WRITE_TO_TABLE_Y_N'; from_schema:= '&&SCHEMA'; IF from_schema = 'ALL' THEN begin from_schema := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; to_schema := 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'; end; ELSE to_schema := from_schema; END IF; DBMS_OUTPUT.PUT_LINE(' Missing Indexes for Foreign Keys'); DBMS_OUTPUT.PUT_LINE(' --------------------------------'); FOR c1_rec in c1 LOOP /* looping for each foreign key constraint */ pl_cons_column := NULL; pl_foreign_key := NULL; pl_ind_column := NULL; pl_ind_name := NULL; pl_ind_owner := NULL; pl_index := NULL; f_owner:=NULL; F_table_name:=NULL; OPEN c5(c1_rec.r_owner,c1_rec.r_constraint_name); FETCH c5 INTO f_owner,f_table_name; CLOSE c5; OPEN c2(c1_rec.constraint_name,c1_rec.owner); FETCH c2 INTO pl_cons_column; pl_foreign_key := pl_cons_column; -- the first col in the foreign key <> LOOP /* constructing the foreign key columns, delimiting each column with a ',' */ FETCH c2 into pl_cons_column; EXIT WHEN c2%NOTFOUND; pl_foreign_key := pl_foreign_key||','||pl_cons_column; END LOOP constraint_names; /* we now have a table and foreign key definition for which we need an index */ CLOSE c2; OPEN c3(c1_rec.table_name,c1_rec.owner); <> LOOP /* for each index found for this table */ FETCH c3 INTO pl_ind_name,pl_ind_owner; EXIT WHEN c3%NOTFOUND; OPEN c4(pl_ind_name,pl_ind_owner); FETCH c4 INTO pl_ind_column; pl_index := pl_ind_column; -- the first column in the index IF pl_index=pl_foreign_key THEN -- check this doesn't already match CLOSE c4; -- the foreign key EXIT index_name; END IF; IF pl_index = SUBSTR(pl_foreign_key,1,LENGTH(pl_index)) THEN /* we only need construct the whole index while it's leading edge still matches the constrained foreign key columns */ <> LOOP /* construct the whole index in the same way as th |