A pl/sql script to recompile all invalid objects in oracle. Simple but powerfull.
DECLARE
obj_name_ User_Objects.object_name%TYPE;
obj_type_ User_Objects.object_type%TYPE;
str_run_ VARCHAR2(200);
cid_ INTEGER;
ret_ INTEGER;
CURSOR Invalid_Objects_ IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type ASC;
BEGIN
FOR Get_Rec_ IN Invalid_Objects_ LOOP
BEGIN
obj_name_ := Get_Rec_.object_name;
obj_type_ := Get_Rec_.object_type;
IF (obj_type_ = 'FUNCTION') THEN
str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'JAVA SOURCE') THEN
str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
ELSIF (obj_type_ = 'JAVA CLASS') THEN
str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
ELSIF (obj_type_ = 'PACKAGE') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'PACKAGE BODY') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
ELSIF (obj_type_ = 'PROCEDURE') THEN
str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TRIGGER') THEN
str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'VIEW') THEN
str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
ELSIF (obj_type_ = 'DIMENSION') THEN
str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TYPE') THEN
str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
END IF;
--
cid_ := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
ret_ := DBMS_SQL.EXECUTE(cid_);
DBMS_SQL.CLOSE_CURSOR(cid_);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF (DBMS_SQL.IS_OPEN(cid_)) THEN
DBMS_SQL.CLOSE_CURSOR(cid_);
END IF;
END;
END;
END LOOP;
END;
Source:
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/02/15/0382.htmy