Re: Why is UGA size so large?

Noons <wizofoz2k_at_yahoo.com.au> wrote in message news:<424e2b9d$0$1886$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au>...
> Marcel D?rr apparently said,on my timestamp of 1/04/2005 6:43 PM:
>
> There you go: remove the last row and extend again. ;)
> That should solve your problem. I do recall this being documented
> somewhere in one of the old PL/SQL manuals. Only a total delete frees
> up the space. The other way used to be to keep an empty table around,
> same definition, and copy it to the large PL/SQL table.
> That shrunk the size, or so we were told.
>

So what I need to do is copy the sparse table to a temporary empty one, completely delete the sparse table and then copy the tempory table back, therefore making my initial table dense. Allthough I really don't like to take care of such memory mangement stuff, since now my application has to decide when it's worth to copy the collection twice to release the memory.

I refined my testcase a bit and found the following statement to be true:

The memory that is no longer used by deleted rows can be reused only for
new rows in *the same table of the same session*.

The Docu is a bit misleading on this, in the PL/SQL User's Guide and Reference Oracle 9i, on page 5-35 is stated:"Deleting Collection Elements (DELETE Method)
The amount of memory allocated to a nested table can increase or decrease
dynamically. As you delete elements, memory is freed page by page. If you
delete the entire table, all the memory is freed." Now they use two times the same word "freed", when in fact there is quite a difference in what is happening if you delete the whole collection or only a part of it.

Regards,
Marcel Received on Tue Apr 05 2005 - 10:28:21 CDT