1 Reply Latest reply on Sep 15, 2016 11:21 PM by damorgan

    Object gets invalidated right after successful compilation;

    2978837

      Hello all.

       

      I'm experiencing a strange problem.

       

      In database A i got package that uses dblink inside it, just to open cursors. So

       

      OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE
      1SPARE_EXTEXT_LOAD_PKGPACKAGE BODYOPER_1STATUS_DESCTABLELNK_01HARD
      2SPARE_EXTEXT_LOAD_PKGPACKAGEOPER_1STATUS_DESCTABLELNK_01HARD
      3SPARE_EXTEXT_LOAD_PKGPACKAGE BODYOPER_1APPLICATION_CLIENT_INFOTABLELNK_01HARD

      ...

       

      No ddl operations are performed on database B (through LNK_01).

       

      I switched on ddl auding on OPER_1, and have found nothing.

       

      AUDIT TABLE,ALTER TABLE BY ACCESS;

       

      But when i try recompile SPARE_EXT.EXT_LOAD_PKG, it successfuly recompiles and after couple of seconds becomes invalid.

       

      What is the issue?

       

      Thx in advance.

       

      Database A

       

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

      PL/SQL Release 11.2.0.4.0 - Production

      CORE 11.2.0.4.0 Production

      TNS for Linux: Version 11.2.0.4.0 - Production

      NLSRTL Version 11.2.0.4.0 - Production

       

      Solaris 11 SPARC

       

      Database B

       

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

      PL/SQL Release 11.2.0.3.0 - Production

      CORE 11.2.0.3.0 Production

      TNS for Linux: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      Solaris 11 SPARC

        • 1. Re: Object gets invalidated right after successful compilation;
          damorgan

          I cannot tell you what is causing this in your specific case but I have fixed this for others in the future and often what I see is remote dependencies. Here is just one example:

           

          CREATE OR REPLACE PROCEDURE remote_proc AUTHID DEFINER IS

          x NUMBER;

          BEGIN

            SELECT COUNT(*) INTO x FROM dual@remotedb;   -- a database link

          END;

          /

           

          Network issues break the connection to the remote database for a fraction of a second and the result is predictable.

           

          Check your dependencies (dba_dependencies) and if it isn't there feel free to email me directly: dmorgan  @  forsythe.com and I will try to help you get to the bottom of it.