I am working on a seamless upgrade of a project. The project uses Hibernate and Oracle. My task is to upgrade the DB from version 1 to version 2 while the project is running. In our projects we have a package containing a trigger which will insert a row in update_table on update or deletion of any row of specific tables, suppose t1, t2 , t3. In version 2 we have some new columns in update_table, and in t1 and t2 too, and some updates in t1 and t3. In the upgrade of the DB , I am adding a column in update_table, then I am adding columns in t1 and t2, then I am upgrading all procedures and triggers by create or replace procedure. But while updating procedures, I get blocked. It keeps on waiting for several hours. When I stop my project after some time, the migration process starts again and successfully upgrades the procedures.
For further info, my project is also changing values in t1 and t2 frequently. But when I start my migration process, all update requests go into an idle state and after some time the connection times out. I am totally confused. One more thing: it runs successfully with less requests on my project for updating of table values of t1 and t2. Please help. Thanks.
The reason you get blocked is that ongoing transactions are holding locks that prevent someone from changing the object until that transaction finishes. Unfortunately, another transaction starts before the first one finishes, so the change to the object cannot obtain the exclusive lock it needs, for a very long time.
There are two things the DBA can do. In Oracle 9i and 10g, you can use the DBMS_REDEFINITION package to make online changes to tables while users continue their transactions. Unfortunately for you, the DBMS_REDEFINITION package does not apply to packages, procedures or triggers. Which means you are left with the other option, which is to schedule some application downtime in order to make these changes.