Dynamic script to compile all the invalids:-

SELECT CASE WHEN object_type = ‘SYNONYM’ AND owner = ‘PUBLIC’ THEN
‘alter ‘ || owner || ‘ ‘ || DECODE(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) || ‘ ‘ || object_name || ‘ ‘ || DECODE(object_type, ‘PACKAGE BODY’, ‘COMPILE BODY’, ‘COMPILE’) || ‘;’
ELSE
‘alter ‘ || DECODE(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) || ‘ ‘ || owner || ‘.’ || object_name || ‘ ‘ || DECODE(object_type, ‘PACKAGE BODY’, ‘COMPILE BODY’, ‘COMPILE’) || ‘;’
END “SQL_COMMANDS”
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’, ‘VIEW’, ‘PROCEDURE’, ‘FUNCTION’, ‘TRIGGER’, ‘SYNONYM’)
AND status = ‘INVALID’
ORDER BY DECODE(object_type, ‘TRIGGER’, ’99’, ’00’);
  • December 31, 2018 | 33 views
  • Comments