Is recompiling Oracle Packages safe

Question

Hi We have a third party oracle based application, that ships with precompiled binary(wrapped) packages. But when I compile them in Oracle SQL Developer (right click -> compile all), they get invalidated.

Is recompiling a safe operation with no side effects?

Answer 1

Most third party applications advice you against editing/compiling their objects unless of course their support tells you to do so.

Since you do not know all the dependent objects, I would suggest you contact the third-part application's support team first before modifying their objects. If it is a bundled application, simply recompiling oracle objects may leave dependent applications/services in other tiers invalid.

Answer 2

The major side effect is that if you compile a package which another package is dependant upon you risk invalidating the dependant package for any existing sessions - even if the compile has no errors. This is fine for applications where sessions are short-lived but for applications where sessions are long-lived this is a problem. If you're using connection pools in JDBC the cached sessions will be long lived and likely invalidated. You have to flush the cached sessions to avoid the error.

The error you're looking for is "ORA-04068: existing state of packages has been discarded".

See here for more info.

Specifically with regards to SQL Developer - it does not handle recompilation of wrapped packages well. If you are going to recompile them try another tool like TOAD or PL/SQL Developer or use the "alter package" command in the SQL Plus command line.

Answer 3

If your packages get invalid are they actually invalid in the sense that they won't work anymore? Try to recompile an invalid package body using sqlplus.

SQL>alter package <package name> compile body;

If you get the message "compiled with errors"

SQL>show errors;

This will give some information about the error.

Generally speaking it is fine to recompile wrapped packages. Should not be a problem.

Answer 4

Personally, I'd avoid 'Recompile All' (in SQL Developer or TOAD) - especially in any environment where you have open database connections from other users or software.

In most situations you probably just want to recompile Invalid objects.

If you're on Oracle 10 or above, there are two in-built packages that will do this (although they may not be accessible to your role without speaking to your DBA).

UTL_RECOMP.RECOMP_PARALLEL(threads => 4, schema => :schema_owner)

DBMS_UTILITY.COMPILE_SCHEMA(schema => :schema_owner, compile_all => FALSE)

UTL_RECOMP is the new preferred way to do it. DBMS_UTILITY exists on earlier versions of Oracle, but would always compile everything - compile_all is a new optional flag, that lets us tell it to compile only invalid items.

If you are on an earlier version than 10, I'd suggest rolling your own compile invalid procedure - I found it useful to write this as a job that can be submitted via DBMS_JOB and then emails back progress via DBMS_SMTP (DBMS_MAIL in Ora 10).

My job recursively tries to compile INVALID objects where all dependencies are VALID, using the following SQL, until there are no changes between iterations.

SELECT  uo.object_name,uo.object_type
FROM    user_objects uo
WHERE   uo.status = 'INVALID'
MINUS   -- objects with invalid children
SELECT  uo.object_name,uo.object_type
FROM    user_objects uo,
    user_objects uo2,
    public_dependency pd
WHERE   uo.status = 'INVALID'
AND     uo.object_id =  pd.object_id
AND     pd.referenced_object_id =  uo2.object_id
AND       uo2.status = 'INVALID'