Package referencing a private db link becomes periodically invalid - how to debug?

user_802058 0
Viewed 157

I reference a private db link inside a package but the package body becomes periodically invalid and I don't know why.

CREATE DATABASE LINK my_link
   CONNECT TO foo IDENTIFIED BY bar
   USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ...

CREATE OR REPLACE
PACKAGE TEST AS
    PROCEDURE test;
END TEST;
/

CREATE OR REPLACE
PACKAGE BODY TEST AS
    PROCEDURE test
    AS
        cur SYS_REFCURSOR;
    BEGIN
        OPEN cur FOR SELECT * FROM mytable@my_link;
    END test;
END TEST;
/

SELECT COUNT(1)
FROM user_objects
WHERE object_name = 'TEST'
AND object_type = 'PACKAGE BODY'
AND status != 'VALID';

1

When I call that package it becomes valid. But in some time it's invalid again. How to debug?

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Many thanks in advance.

oracle11g oracle
2 Answers

Oracle invalidates things for its own internal reasons when dependencies are affected. As you noted, objects are automatically validated when then are again used. There is nothing to do here, or to debug.

user_129134910

The next time the package is invalid, use DBA_OBJECTS and DBA_AUDIT_TRAIL to try to find out who changed what objects when the package was invalidated. Make sure to run these queries before you recompile the package since recompiling will change the LAST_DDL_TIME column.

--Find when the package was modified, and any other objects modified around the same time.
select *
from dba_objects
order by last_ddl_time desc;

--Find activity around the same time as the object changes.
--(This query will only work if you have specific types of auditing enabled.)
select *
from dba_audit_trail
where timestamp between timestamp '2021-06-17 12:00:00' and timestamp '2021-06-17 13:00:00'
order by timestamp;

Although dependencies are complicated, and the objects may get automatically validated when they are needed, it's a good idea to track down these invalidations. In practice, databases either have zero invalid objects or hundreds of invalid objects so keep your database clean.

user_4091720
Related