e foreign key */ FETCH c4 INTO pl_ind_column; EXIT WHEN c4%NOTFOUND; pl_index:= pl_index||','||pl_ind_column; /* we do not need to continue with the index name loop if we already have a match on the foreign key */ IF pl_index=pl_foreign_key THEN CLOSE c4; EXIT index_name; END IF; /* if the leading edge differs - go back around the loop to see if there is a subsequent index that matches */ IF pl_index != SUBSTR(pl_foreign_key,1,LENGTH(pl_index)) THEN EXIT index_columns; END IF; END LOOP index_columns; END IF; CLOSE c4; END LOOP index_name; CLOSE c3; IF pl_index != pl_foreign_key OR pl_index IS NULL THEN /* Alternative means of output having first set serveroutput using: SET SERVEROUTPUT ON SIZE n where n is between 2000 and 1000000 to set the output limit. DBMS_OUTPUT.PUT_LINE(c1_rec.owner||'.'||c1_rec.constraint_name); */ IF WRITE_TO_TABLE_Y_N ='Y' or WRITE_TO_TABLE_Y_N ='y' THEN EXECUTE IMMEDIATE 'INSERT INTO foreign_key_exceptions VALUES (c1_rec.owner,c1_rec.constraint_name,c1_rec.status, c1_rec.table_name,pl_foreign_key)'; END IF; dbms_output.put_line('Constraint '||c1_rec.constraint_name||'('||c1_rec.status||') : Changing data in table '||f_owner||'.'||f_table_name||' will lock table '||c1_rec.owner||'.'||c1_rec.table_name); dbms_output.put_line('Create index for table '||c1_rec.owner||'.'||c1_rec.table_name||' on columns '||pl_foreign_key); dbms_output.put_line('************************'); COMMIT; END IF; END LOOP; END; / undefine WRITE_TO_TABLE_Y_N undefine SCHEMA 在执行脚本时，需要交互地输入1）中新建的表名，以及你想查看哪个schema下的信息如果输入ALL表示查看所有用户信息。根据得到的信息,在相应的外键上创建索引,只需要在application's schema下的对象上创建索引，
系统默认用户（sys,syste,sysman）下的对象不需要创建索引。
|