monline

The documentation of Oracle 9iR2 says that the dbms_session.free_unused_user_memory() procedure can be used to free up memory that is not in use. They even have an example for index-by tables (in 9i they are called associative arrays) and it works just as expected. However the memory is only released if the table being used was indexed by a numeric datatype! For tables indexed by VARCHAR2 it will not free the memory. Try it yourself if you've doubts about it. Unfortunately I'm using VARCHAR-indexed PL/SQL tables quite heavily in my application (for caching table data) and this bug hit me pretty seriously. I've to rewrite a lot of code now so my app does not abort due to ORA-04030 (out of process memory) exceptions.