Oracle Database Discussions

Whats up all...

I would like to keep an eye on a PACKAGE BODY that's being modified which invalidates usage. I realize I can do this using:

SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE object_name = 'GOLF_CONTEST_PKG'
AND owner = 'GOLF_OWNER'
AND object_type = 'PACKAGE BODY'
/

However, I need to know WHO did it. I have DBA_AUDIT_TRAIL turned on, but it's only at a level where I can watch for INVALID logins/locking accounts.

What can I do to ensure that I catch that this body going invalid? Can I create a trigger? Right now, my solution is to check LAST_DDL_TIME on the body, check the dependents using DBA_DEPENDENCIES, and querying from DBA_AUDIT_TRAIL to see if it comes up with anything. I'm going to scrape the DBA_ERRORS table with the following code:

SELECT *
FROM dba_errors
WHERE NAME = 'GOLF_CONTEST_PKG'
AND owner = 'GOLF_OWNER'
AND TYPE = 'PACKAGE BODY'
/

If the spool from that single query above is not "no rows selected," then I'm going to notify myself and a coworker who's been noticing the invalidation on the body.

Also, this is strange behavior, because the body actually looks to be reverting back to an old version when this happens; code is missing. There are only 3 people who work on this code, and the aforementioned developer is the one who's seen it happen twice in the last month and has recompiled the package only to find that it's an old version, and he need to recreate the body using archived code.

Version: Oracle 10.2.0.4
Platform: 64-bit Red Hat

Thanks!