Re: growing PGA when re-using PL/SQL-tables

I've seen the same problem myself, about a year ago. Platform was O7 v 7.3.3.2, HP-UX 10.20. Confirmed with Oracle support that this is a bug. Upgrade to 7.3.3.5.1 was supposed to fix it.....and didn't. Can you send me (or post) the For...next loop code? That will trigger my memory of the work-around we came up with, and I'll send it back to you.

Craig Brady
Talisman Technologies Inc
specializing in Oracle/VLDB's/Data Warehousing/Tuning

    Pieter Steenhuis wrote in message <368A6BDD.2B0F5D17_at_worldonline.nl>...     Hi all,
    I already contacted Oracle customer support on this, but I'd like to have some input from you lot as well.

    I built a package, which performs a complex batch procedure.     To be able to store and rework data before inserting it into another table, I store up to 500 rows in a PL/SQL-table (a.k.a. index by table). Actually I'm using two tables, one for details and one for summaries, but this doesn't affect this problem.

    When I ran this package on our production database, it stopped after a while, and in the trace file the message ORA-04030 appeared, meaning that the process ran out of process memory (PGA).

    After examining this, I found out, that although I deleted the PL/SQL-table regularly, it kept eating up more and more memory, while I expected it to reuse the memory.

    I tried to reproduce this behaviour in a small procedure which copied the emp table in a PL/SQL-table and then deleted it again for about 100000 times, but this time memory stayed low.

    Can anyone tell me why the memory in my package isn't freed?

    I'm running on Oracle 7.3.3.4, PL/SQL 2.3.3.4 (I think.. can't verify it now)

    For deleting the PL/SQL-tables I use the built in procedure <table_name>.delete

    I've tried dbms_session.free_unused_user_memory right after this delete statement, but that didn't work. Received on Thu Dec 31 1998 - 04:14:15 CET