Package compilation in a production environment. Can this cause any problems?

Question

Our biggest client (150 billion $ of turnover) is afraid about compiling package during the online production time can cause database to hang.

I think it is impossible since the package I want to compile are alone and isolated. They are used to correct data or to analize them.

My chief told something about SGA? Is it possible?

What do you think about?

Answer 1

Compiling a PL/SQL package in online production is problematic if:

• the package is currently being executed, or
• the package has state (such a body variables) and has been used in one of the sessions that's still logged in

The former can block sessions (most likely just the one that compiles the packages due to a library cache pin lock), the latter one will lead to the following error message in all session that try to use the package again after the compiliation:

ORA-04061: existing state of package body "SCHEMA.PACKAGE" has been invalidated

I don't think there's any specific problem related to the SGA.