When the developers for applications have moved on to other things, troubleshooting issues that arise during production support can get interesting. Here are two recent production issues with resolutions that required adjusting Oracle components and not application code.

Issue One: A developer unfamiliar with the app added a new feature so that a new child table got added to a parent table that had several existing child tables. Interestingly the child table foreign keys linked back to a unique constraint, not a primary key. The unique constraint was on a field WIDGET_NAME. Widget names sometimes change, so the app had a procedure to change this value. It invoked a stored procedure that updated the parent and child tables accordingly.

The new child table got added with a foreign key constraint. All appeared well, but it turned out the “change widget name” feature broke. This had not been regression tested. Turns out the foreign key on the new child table was not created with the clause INITIALLY DEFERRABLE. This foreign key clause cannot be changed after the fact, so it needed to be dropped and recreated. And the stored procedure needed an extra line to update the data in the new child table. After that, all worked fine.

Issue Two: Inserts suddenly stopped working on, you guessed it, the widget table. Turns out the primary key on the widget table on a number field usually populated by a sequence generator in a INSERT trigger. I say usually because the trigger would accept an INSERT with a supplied value as well. Well, somebody supplied a value that ended up colliding with values output by the sequence generator. I ended up running a PL/SQL block to move the sequence to a higher setting. I did it the brute force way, like this:

i number;
curr number;
t number;
        select widget_id_seq.nextval into curr from dual;
        for i in curr..200000 loop
                select asset_id_seq.nextval into t from dual;
        end loop;

I see Dan Morgan has a nifty reset sequence routine, but I didn’t get a chance to try it out.

Bottom Line: Triggers and non-default clauses in contraints can create situations developers might not expect when they encounter for the first time an existing database that nobody has touched for awhile!