How can I deploy new version PL/SQL package in Oracle DB server under high load?

Question

There is a high loaded 24/7 system based on Oracle DB Server.
Many of the client applications work with the package pkg1.
Is there any possibility (Oracle implementation or best practice) of installing the new version of package pkg1 on the fly?
I mean without getting the 'resource busy' error and losing current sessions and etc?

Answer 1

If you're using Oracle 11, take a look at Edition-Based Redefinition.

Answer 2

Deploying new version of database packages, even in production, is not a problem, if you remember some rules:

• You can not deploy updated package bodies if some session is currently using it. For that you would need Edition-Based Redefinition, but in my experience that should not be necessary too often and might rather be a design flaw of your application. Besides, even EBR will require initiating new sessions, afaik.
• Deploying the package header will affect other parts, but normally you will have 99 percent of package updates in the body.
• To avoid loosing session information, create packages without any package session data: no package body-code, no package variables, neither in header or body, no constants either (yes, that is right, no CONSTANT definitions, they create state for some strange reason), no table functions. The easiest work-around for these restrictions is to have a separate "state"-package which has all these features and as little as possible of business logic.

With this in mind you can create package bodies which can be updated even in production without causing any harm. If the package really is used the recompile will wait for a lock on the package, in the worst case that might time out and you need to try again.

Again, if all this does not help a redesign might be necessary and helpful.