News Stay informed about the latest enterprise technology news and product updates.

True Oracle Blooper: Know your space usage

How not to guesstimate how much memory will be used by a PL/SQL script.

It is no simple task to calculate how much space will be eaten up by a PL/SQL script. Even the best estimations -- or guesstimations -- can be way off and chaos can ensue. Such was the case for member Dan, who was working as a developer on a project involving dynamic SQL/DML statement generation in an Oracle 7.3 environment.

I tried to capture all the generated statements in a PL/SQL table (now known as an index by array). I was experimenting with the PL/SQL tables, thinking: "Surely, a NULL or very short string won't take up much space in an element." So I made the PL/SQL table of VARCHAR2(32767) and figured each element would only take up as much space as each variable length string required.

My development database ran under Netware 3.x on a two-CPU Pentium (133 MHz) machine with 1 GB RAM. Every time I ran the test script, it would fill the array with a few tens of thousands of elements. But each time it failed it gave the message, "Can't allocate N bytes." I was really scratching my head.

To resolve the issue, I disconnected my session and reconnected in order to run it again. I did this over and over a few times.

Then the DBA walked over.

"What are you running? The entire system is out of memory, and the operating system is thrashing," he said. I explained that I was just running this one harmless script. The DBA looked at the script and thought it looked fine -- it had no infinite loops or recursions. He then went back to his cube and told me when to restart the script.

A few moments later, the DBA shouted, "Shut it down! Shut it down!" Within seconds, the system was completely out of memory and started acting like a fish out of water. Of course, I had to wait until my program puked before I could disconnect the session, which allowed Oracle to slowly return the allocated memory back to the operating system. Meanwhile, all the other developers work was interrupted until the system could recover.

The moral of the story: Don't assume to know how much space will be used by running one "harmless script."

For more true DBA bloopers, click:

Have your own tale of woe to share? Submit your backup/recovery snafus, tuning disasters and ugly upgrades. Stories of good intentions gone bad, over-ambitious and under-trained newbies, clueless consultants and even more clueless managers will all be accepted. Send your bloopers to us today!

Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.