Memory leak when changing external table location

I just found a strange behavior in one of our PL/SQL procedures that is designed to load the data from external tables (files that arrive on the server). During one run, I noticed that “session PGA memory” was constantly increasing which made me look for a leak in the first place. To cut the very long story short, the part that caused the leak was ‘alter table’ command that changes the location of the external tables. This was done for each file that had to be loaded and there were tens of thousands of them. Here is the SQL in question:

execute immediate 'ALTER TABLE my_external_table LOCATION( FILELOC:''newfile.txt'')';

Heap dump can be used to see the leak. I ran the above command from 500 times to make sure it generates leak big enough to notice in the dump

begin
  for i in 1..500 loop
    execute immediate 'ALTER TABLE my_external_table LOCATION( FILELOC:''newfile.txt'')';
  end loop;
  execute immediate 'ALTER SESSION SET EVENTS ''immediate trace name heapdump level 536870913''';
end;

536870913 (or more easy to read: 0x20000001) means we want to dump PGA with level 2 depth. The generated dump will appear as .trc file in user_dump_dest folder and can be analyzed through Tanel’s very simple but also extremely usefull heapdump_analyzer utility. And with that we get this kind of output:

-- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com )
Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
---------- ------- ------------ ----------------- ----------------- -----------------
   2746816     167      16448 ,         pga heap,         freeable,  koh-kghu call
   2740136     167      16408 ,   koh-kghu call ,         freeable,  kollalo2
    888800      55      16160 ,         pga heap,             free,
    327160       5      65432 ,     top uga heap,         freeable,  session heap
    262048       4      65512 ,    top call heap,             free,
     82880      20       4144 ,     session heap,         freeable,  kxsFrame4kPage
     65432       1      65432 ,     top uga heap,         recreate,  session heap
     49224       1      49224 ,    top call heap,             free,
     41392       1      41392 ,     session heap,             perm,  perm
... (and so on)

What immediately gets our attention are the first two rows. “koh-kghu call, kollalo2” was increasing each time ALTER TABLE was called. Metalink search didn’t show any results regarding this, however we opened SR and they resolved it by saying it’s an unpublished bug number 6737245 and that it will be fixed in version 10.2.0.5.

PS. This post is about Oracle 10.2.0.4 on AIX 5.3, i don’t know if it is reproducible elsewhere.