A memory leak occurs when a program allocates memory for a data structure and then never releases that memory and does not reuse that memory. The next time the program needs the data structure, it allocates more memory again. Over time, the available memory appears to be shrinking and this is called a "memory leak." Memory leaks are overcome by ensuring the memory allocated for a data structure is deallocated when you are done using that data structure.
It used to be that if you did not close a cursor, that memory stayed allocated and unusable to anything else. This is no longer the case with Oracle. If you open a cursor and forget to close it, Oracle will close the cursor for you when the PL/SQL block ends execution. This assumes your PL/SQL block does reach an end.
While Oracle does automatically close cursors, it is still a very good idea to close them in your code. You should close your cursor when you are done looping through the cursor's contents. This way, you free up some memory for your next cursor. Plus, closing cursors is good programming style.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading