Recently I posted about an ORA-04030 Oracle error we received at work. At the end of many hours of pain, the solution was to swap the apparently damaged physical memory for fresh sticks of memory. This post is a follow up to highlight that an Oracle ORA-04030 can be generated by an application error as well.
As soon as the application started generating the ORA-04030 error, we immediately started working through the first set of points from our last excursion with this error. Thankfully, when looking through the Oracle log files it was throwing an error on a particular PL/SQL package. After inspecting the PL/SQL package, there was a clear opportunity for the PL/SQL procedure to spiral out of control and consume all the memory on the server.
In this instance, the PL/SQL procedure was building up a tree of information using the Oracle CONNECT BY PRIOR clause. As you can imagine, if for some reason a node had itself as its parent or child or any recursive relationship; the SQL statement would loop infinitely. Soon enough, the statement has retrieved a million or more rows and the server suddenly has no more memory left and it throws an ORA-04030 error.
The solution in this case was just as simple as the cause, remove the recursive data. For safe measure, there will be checks added using a BEFORE ROW INSERT and BEFORE ROW UPDATE triggers to make sure that the same problem doesn’t resurface in the future.
