spool /tmp/invalid.objects.sql
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY object_name,
owner,
object_type
)
LOOP
BEGIN
DBMS_OUTPUT.put_line('alter ' || cur_rec.object_type || ' ' || cur_rec.object_name || ' compile; ');
END;
END LOOP;
END;
/
spool off;
@/tmp/invalid.objects.sql
Or:
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type,
'PACKAGE',
1,
'PACKAGE BODY',
2,
2)
AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE',
'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4
)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ELSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
