/* Formatted on 2020/7/8 上午 09:31:31 (QP5 v5.163.1008.3004) */

SELECT DISTINCT

       'Alter '

       || CASE

             WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE'

             ELSE object_type

          END

       || ' '

       || owner

       || '.'

       || object_name

       || ' compile;'

  FROM dba_objects

 WHERE (owner, object_name) IN

          (    SELECT owner, name

                 FROM dba_dependencies

           START WITH referenced_owner = 'XXXXX'

                      AND referenced_name = 'XXXXX'

                      AND TYPE IN

                             ('FUNCTION',

                              'PACKAGES',

                              'PACKAGE BODY',

                              NULL,

                              'PROCEDURE',

                              'SYNONYM')

           CONNECT BY NOCYCLE PRIOR owner = referenced_owner

                              AND PRIOR name = referenced_name);