Oracle Check package modified and execute

ALTER SESSION SET NLS_DATE_FORMAT=’DD-MM-YYYY HH24:MI:SS’;

— Daily object modified report every 24h

SELECT owner,

object_name,

object_type,

last_ddl_time,

status

FROM dba_objects

WHERE     object_name IN

(SELECT A.REFERENCED_NAME

FROM DBA_DEPENDENCIES a

WHERE object_type IN (‘PROCEDURE’, ‘PACKAGE BODY’, ‘PACKAGE’))

AND last_ddl_time >= SYSDATE – 2

ORDER BY last_ddl_time DESC;

— INVALID Object modified report every 30 minutes

SELECT owner,

object_name,

object_type,

last_ddl_time,

status

FROM dba_objects

WHERE     object_name IN

(SELECT A.REFERENCED_NAME

FROM DBA_DEPENDENCIES a

WHERE     object_type IN

(‘PROCEDURE’, ‘PACKAGE BODY’, ‘PACKAGE’)

AND status = ‘INVALID’)

AND last_ddl_time >= SYSDATE – 2

ORDER BY last_ddl_time DESC;